Worksheet Functions in VBA

Description:

There are many built-in functions available in Excel Worksheet functions and VBA functions. We can find some functions in both Excel Worksheet functions and VBA functions, for examples TRIM, MIN, MAX, etc. We can not find some Excel Worksheet functions like VLOOKUP,HLOOKUP, SUMIF in VBA.

What if you want to use VLOOKUP in VBA. Yes, we do not have VLOOKUP function in VBA, however we can use Excel Worksheet functions in VBA. We will see how to use Excel worksheet function VBA.

How to use Excel worksheet function VBA?:

We can use all worksheet functions using Application.WorksheetFunction object. All the worksheet functions are available in Application.WorksheetFunction object.

Examples to use Excel worksheet function in Excel VBA

Here are the examples to use Excel worksheet function in Excel VBA. We will see some of the Excel worksheet functions with examples.

Example on using SUM Excel Worksheet Function in VBA

The below example code to show you how to use SUM Excel Worksheet Function in VBA. Here we will sum the range A1 to A15.

Sub ExampleWorksheetFunction_SUM()

     MsgBox Application.WorksheetFunction.Sum(Range("A1:A15"))

End Sub

Instructions:

1. Open an excel workbook
2. Enter some values in A1 to A15 in the active worksheet
3. Press Alt+F11 to open VBA Editor
4. Insert a Module for Insert Menu
5. Copy the above code and Paste in the code window
6. Save the file as macro enabled workbook
7. Press F5 to execute it
8. It should prompt a message with total of the given values

Example on using VLOOKUP Excel Worksheet Function in VBA

The below example code to show you how to use SUM Excel Worksheet Function in VBA. Here we will sum the range A1 to A15.

Assume that we have data as shown below, we want use the vlookup function to find the Age of a given person name.

It is good idea to assign the arguments to variables then use in the functions.

Sub ExampleWorksheetFunction_VLookup()

lookupVal = "Kelly"
lookupRange = Range("A1:B5")

MsgBox Application.WorksheetFunction.VLookup(lookupVal, lookupRange, 2, False)

End Sub

Instructions:

1. Open an excel workbook
2. Enter the data in A1 to B5 in the active worksheet as shown in the above screen-shot
3. Press Alt+F11 to open VBA Editor
4. Insert a Module for Insert Menu of VBE
5. Copy the above code and Paste in the code window
6. Save the file as macro enabled workbook
7. Press F5 to execute it
8. It should prompt a message with 28 as a lookup value for given value (Kelly) in the table.