In a 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 we can use –
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.
So you can use these lines of code in your regular macro – (we have it as a sample macro)
When we use this sample macro, we will get a message showing the value which is stored in SalesTotal and can be used in further code lines in the macro.
The output we will get is -
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.