In VBA code, if you have to refer to certain worksheet functions like Sum, Vlookup, etc, you can use them directly by using the Application object. So we will use the functions as –
Application.WorksheetFunction.Sum or Application.WorksheetFunction.Vlookup where WorksheetFunction is the method of the Application object.
Considering we have this test macro, if we type
We will get a popup showing the formulae refer below image
So for example, if you want to sum the values of this range in column A using vba –
Lets have a variable called SalesTotal which will save the sum in it. To get total in SalesTotal we will use the following VBA code in standard module:
Dim SalesTotal As Long
SalesTotal = Application.WorksheetFunction.Sum(Range(“A2:A6″))
To copy the above code to your file,
- Press Alt + F11 on the keyboard.
- On the left hand side, you will see Microsoft Excel Objects.
- Right click and select Insert.
- Then click on Module.
- Copy the code to the code window on the right.
When we use this sample macro, we will get a message showing the value which is stored in Sales Total and can be used in further code lines in the macro.
The output we will get is -
If you want the SalesTotal to be shown in cell A7, then you can change the code line from
Msgbox SalesTotal to Worksheets(“Sheet1”).Range(“A7”).Value = SalesTotal
Considering we also need the average sales for these figures in cell A9. We can use the code below
Dim SalesTotal As Long
SalesTotal = Application.WorksheetFunction.Sum(.Range(“A2:A6″))
.Range(“A7″).Value = SalesTotal
.Range(“A9″).Value = Application.WorksheetFunction.Average(.Range(“A2:A6″))
There are slight changes to this code as compared to the previous one.
- Instead of using a variable for the Average, I directly populated that value after calculation into cell A9. Hence, you will see the line .Range(“A9”).Value = Application.WorksheetFunction.Average(.Range(“A2:A6”))
- The msgbox line has been removed.
- The value in the variable SalesTotal goes to cell A7.
- Ideally, you can repeat the same code line which has been used for the average, for the Sum code line by replacing
SalesTotal = Application.WorksheetFunction.Sum(.Range(“A2:A6”))
.Range(“A7”).Value = SalesTotal
.Range(“A7”).Value = Application.WorksheetFunction.Sum(.Range(“A2:A6”))
However, it has been retained so that you can understand the different ways of coding the same task. Similarly, we can use other functions while using Application.WorksheetFunction. All those functions which we can use in the worksheet directly, we can use them here.
This makes it easier to use the functions in the code so we can calculate the formulae required without having to create a code for the formula.