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


advanced pivot table techniqueshr round interview questions and answers for experienced pdfhow to delete comment in excelvlookup with examplemaking a dashboard in exceloffset function in excelcan you find duplicates in excelcreate macro button excel 2010cool excel macrosexcel mergecellssumif functionexcel difference between two datesvlookup between two sheetshow to use sum ifvba access databasecreating check boxes in excelhow to build a macro in excelexcel vba print userformpivots excelmerging worksheets in excelconsolidate worksheetsexcel run macrosoftware project plan template excelascending in excelexcel vba option buttonvba macro commandsvba cleanvba clear arraylookup excel examplesvba excel queryexcel vba reference celladd developer tab to excel 2007vba open file for inputddl in sqlexcel vba copy filetotal number of rows and columns in excel 2007excel difference between two dates in monthsthe number of rows in a worksheet isunprotect sheet with passwordexcel vba create folderaverageifs exampledatabase dml ddlbubble chart excel 2010cells.select vbahow to alphabetize rows in exceladding columns excelsort ascending in excelhow to remove password protection in excel 2010lookup function exampleweekly project status report template powerpointmsgbox example vbaexcel vba loop through sheetsexcel macro to delete blank rowshow to delete unwanted rows in excelkpi dashboard in excelcell function worksheetsql query practice questions answers pdfexcel vba datesusing the sumif functionexcel combo box macrohow to unlock an excel spreadsheet without the passwordconcatenate excel functionhow to put alphabetical order in excelfunction of vlookup in excelhow to use advanced filter in excel 2010how to color excel cellscount in vbaaccess 2013 activex controlsvba adodb recordsethow to adjust column width in excelpaste special dividesql test questions and answers pdfvba quotecopy paste in vbatesting faqs with answers pdf