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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Ultimate Project Management Template
Excel Template

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


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.

excel vlookup worksheet function in VBA

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.
LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template

 
Related Resource External VBA Reference