Microsoft Excel is very powerful software used for reporting as well as for data management. There are so many useful commands available in Excel which can be run using the Excel GUI and same kinds of commands we can also run in macro through VBScript.
In this article we will be discussing about some of the useful functions which we can use in Excel Macro. If you would like to know basics of Macro, you can visit Basics of Microsoft Excel Macro and Some useful Tips.
These are the two functions which we can use when we want to do some setting or want to hide some of the sheets before close or after opening the Excel sheet.
When we open Excel file and if it has having many sheets available. Now if we want to activate or select any particular sheet immediately after opening the Excel then we can use below functions.
Sheets(1). Select – (Below line of code will select first sheet from the available sheets).
Number 1 indicates the sheet number. If we want to select sheet which is available at second position then you can specify 2 in place of 1 like below.
When we want to perform any action on Worksheet change then below function will help you.
Private Sub Worksheet_Change(ByVal Target As Range)
This function gets called every time when you make any change in the Sheet data. Change of data means not just editing existing data from the row/column but also if we will select any row/column then also this above function will get called.
In above function ‘Target’ parameter is an object of row/column on which we have performed any action / edited existing data / entered any new value. Using ‘Target‘ you can also get the value of target row/column.
To retrieve the value in Worksheet_Change() function we can use below line of code.
Using the Target.Count we can find out whether we selected multiple rows/columns or just one row/column.
To check whether only one row/column is modified/selected in the sheet the below line of code will help.
If Target.count = 1 Then
To get the selected row number then we can use Target.Row.
If we want to check or perform any kind of operation on any particular column only then we can use below line of code.
If Not Application.Intersect(Target, Me.Columns(“A”)) Is Nothing Then
Above line will help you to find out whether we done any changes in the sheet if from Column A or not and If it is from Column A then control will come inside the IF loop.
We can also get text/value from any Row/Column of the Sheet using the below line of code.
Dim ValueValue = ActiveWorkbook.Worksheets(“Sheet1”).Cells(Row, Column)
Here instead of “Sheet1” (sheet name) we can also specify sheet number as an integer like below.
Value = ActiveWorkbook.Worksheets(1).Cells(Row, Column)
We can also perform formatting in Macro. Let me give you some of the very useful and frequently used functions for cell formatting.
Return color of the selected cell in Worksheet_Change() function.
ActiveWorkbook.Worksheets(“Sheet1”).Cells(Row, Column).Value = “Set Value @ Row, Column”
Make Cell Font Bold.
ActiveWorkbook.Worksheets(“Sheet1”).Cells(Row, Column).Font.Bold = True
Change Font Color.
ActiveWorkbook.Worksheets(“Sheet1”).Cells(Row, Column).Font.Color = 16
Change Cell Interior Color.
ActiveWorkbook.Worksheets(“Sheet1”).Cells(Row, Column).Interior.ColorIndex = 18
Above those numbers 16, 18 are the color index.
These are the very basic functions we can use in the macro. I hope this will help you while writing your own macro in the Excel.