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:
To copy the above code to your file,
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
There are slight changes to this code as compared to the previous one.
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.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.