VBA to call Worksheet function

Home/VBA/VBA to call Worksheet function

We can use worksheet function in the VBA macros. We can call the worksheet functions using Application.WorksheetFunction. We can call any Worksheet function and use in our code. the below example will show you how to use the worksheet functions in VBA.



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

VBA to call Worksheet function

Here is the simple example to call the Excel worksheet function. We are uusing the ‘MAX’ worksheet function.

'VBA to call worksheet function
Sub sbVBA_to_call_worksheet_function()
MsgBox Application.WorksheetFunction.Max (Range("A1:D20"))
End Sub

When you execute this example, this will find the maximum value in the range A1: D10.

VBA to call Worksheet function – Instructions

Please follow the below step by step instructions to test this Example VBA Macro codes:

  • Step 1: Open a New Excel workbook
  • Step 2: Press Alt+F11 – This will open the VBA Editor (alternatively, you can open it from Developer Tab in Excel Ribbon)
  • Step 3: Insert a code module from then insert menu of the VBE
  • Step 4: Copy the above code and paste in the code module which have inserted in the above step
  • Step 5: Enter some input values in Range A1 to D10 for teing the macro to call the worksheet function in VBA
  • Step 5: Now press F5 to execute the code or F8 to debug the Macro to check the if Range A1 is blank or not

There are many functions available in VBA, however, VBA functions alone are not enough to deal with our daily automation tasks. And it is waste of the time to write user defined function when there are Worksheet function available. You can use the Worksheet function and save lot of time. And make sure, you are passing the correct parameters while using the functions.

There are lot of functions like Vlookup, Hlookup, Index, Match, Offset are very useful while automating our tasks. You can simply call them using Application.WorksheetFunction and use it in your code. If you wan to write the userdefined function for Vlookup, you know how much time one should spend such a function. And they are faster than user defined funtion.

By |January 20th, 2015|VBA|0 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

Leave A Comment

Related pages

if formulas in excel 2007checkbox en excelexcel concatenate sheetsexcel save worksheetlogical operators excelvba excel usedrangeactivesheet.namevba loop breakhow to expand columns in excelpivot table templatedata analysis tab in excel 2007formula to delete blank rows in excelvisual basics codesfree excel formulas with examples in excel sheetexamples of vlookup in excelnumber of rows in excel 2007workbook window definitionview hidden columns in excelchart layout exceldelete duplicate row in excelworksheets on cellshow to write a query in exceloffset excel exampleexcel cell blankisequal excelhow do you do a tick in excelexcel vba open pdf filefor loop vba exampleexcel combo box multiple selectionshow to delete all blank rows in excelvba data entryhow to arrange cells in excelcreate tick box in excelvba rowcountvlookup function in excel 2013insert column excel shortcutpivot table instructionsexcel password protect macroadvanced sql interview questions and answersshortcut to close excel fileshow developer tab in excel 2013vba fileformatapplication getopenfilenamesql query for beginnershow to use sumif function in excelprotect cells excel 2007excel labelingvba excel addressexcel vba copy range to another worksheetexcel vba unmerge cellsexcel vba macro examplesexcel averageif examplehow to get developer tab in excelcomplete excel tutorial pdfexcel macro sheetsdata type in vbaexcel macro duplicate sheetexcel unhide allms access vba export query to excelwhat is the shortcut key to hide row in excelweekly gantt chart excelinsert worksheet excellogical operations in excelmsgbox examplesexcel macro rowslearning sas by example data setsexcel macro select cellvba on errorexcel and vba tutorialexcel vba sample codedrop down macro excelvba code to unprotect workbookcombobox clearhow to highlight column in exceladodb.recordset vbavisual basic editor in excel 2007hyperlinks vbaexcel vba adodb