Worksheet functions in Excel VBA

Last Updated on January 11, 2023 by token

Worksheet functions – How to use the Excel function in VBA 

Have you ever wondered how you can use the Excel function in VBA? Worksheet functions come with help. As you know, the Excel spreadsheet offers a number of useful functions. The resource of functions that you can use in the VBA code is a bit more limited. Thanks to VBA, you can easily use some of the functions offered by Excel data sheet. 

These are not all available functions, but they significantly extend programming in VBA. In case you want to use the data sheet function, you can call it with a short command in any VBA code: 

Application.WorksheetFunction.worksheet_function_name

The name of the function itself has to be written with English naming. Below is an example of the use of a round function, which we know from Excel or VBA under the name ROUND(). The example contains two versions. The first version is the use of the sheet function. The second example shows the same function that the VBA code offers us. The result itself is displayed in the form of the VBA MsgBox window.

Sub WorksheetFunctionExample()

 MsgBox "Datasheet function result: " & Application.WorksheetFunction.Round(123.666, 2)
 MsgBox "VBA function result: " & Round(123.666, 2)

End Sub

As you can see in this example, the results of using sheet and vba functions are the same.

VBA sheet functions – Tasks (You can enter the solution in a comment)

1. Try out the function of max and min on any numerical data using the VBA sheet functions.
2. Check if the VLOOKUP function () can be called via the VBA code. The solution can be found in this article.