Worksheet Functions in VBA

Home/Excel VBA/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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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.
LIMITED TIME OFFER
By |February 10th, 2013|Excel VBA|0 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

Leave A Comment


Related pages


writing macros for excelextract hyperlink excelhow to password protect a spreadsheethow to unprotect excel sheet without passwordfso copyfilevba progress bar excelcombining excel worksheetsdefine activexexcel programmervba copy foldercombobox templatedefault row height in excellookup excel examplemacro vba excelprogram excel macrosvba userform initializeformula for finding average in excelexcel for loopsproject management chart excelhow to insert hyperlinks in excelexplain pivot tablesexcel database querywhile loop in excel vbahyperlink on excelhow do i concatenate in excelvlookup function in excelrows count vbafso filecopywhat is the use of macro in excel 2007unlock password excel filesum offset excelexcel macro cellsexcel vba filefilterdml examplelogical formulas in excel 2007excel vba activecell row numbervba tableexcel vba calculateappend tables in exceladding filters to excelasap utilities 64 bit downloadvba closeexcel sum ifproforma of minutes of meetingchanging row to column in excelwhat is ado connectionexcel vba range selectionconcatenate in excelvba application in excelopening xlsm filesunhiding columns in excelexcel vba not equalunprotect a spreadsheetexcel consolidate rowsexcel vba vbcrlfadvanced vlookup exampleslearn vba programmingno of rows in excel 2007vba excel copy sheetvba filter criteriabase sas tutorial for beginnersbreak password protected excelcolorindex exceliferror excel 2013excel macros advancedcombobox vba excel userformremove password from excel 2010 workbookhow to use excel match functionadodb openexcel tutorial vbaexcel vba worksheet nameiif in excelhow to check if there are duplicates in excel