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

multi select listboxmacro password removerfileline accessdelete row vbahow to protect excel sheetremoving password from excel 2007remove duplicates excel 20133d bubble chart excel 2010pivot table excel vbaexcel hiding columnsexcel sales dashboarduses of pivot table in excel 2007generate xml from excelvba excel pivot tableexplain cut copy and pasteproject manager excel templatepowerpoint swot analysis templatecreating excel dashboards 2010sql query excelcreating an if statement in excelfor each vba excelcolor vbaforgot excel workbook password 2010what is a vba macrovisual basic command buttonwriting vba macrosvbscript message box numbered list and valuesvba color index numbersexcel find duplicates in a columnexcel vba protect workbookexcel vba absolute valuehow to unlock a sheet in excelexcel vb macroexcel macro input boxhow to create project timeline in excelrecordset in vbaexcel flag duplicatesexcel delete duplicate linesnested if and statements in excel 2010vba excel developervba code to close excel filevba active worksheetvba expertsremoving blank rows in excelexcel create userformhow to use the vlookup function in excel 2010while loop in vbavba close fileexcel charting toolsms excel find duplicatesmacro tutorial excelpopulate combobox excelexcel 2007 shortcut keyexcel vba exercisescalculate weeks between two dates excelshort cut keys for excel 2007excel formulas if statementsvlookup excel formulas with exampleshow to change width of a column in excelexcel hide rowseliminate duplicates excelhow to arrange in alphabetical order in excelexcel chart titlevba open csv filehow to find and delete duplicates in excelexcel vba countif functionvba coding in excel for beginnerscapacity planning template excelvba array of stringsdelete column vbasql interview questions and answers with examplesvb msgbox syntaxexcel programme templatevba rowexcel worksheet name in cellwhat is meant by vlookupexcel vba manualhow to create a stacked bar chart in excel 2007excel bubble chart templatehow to program vbaado connectiondatabase macroswriting macros excelvba offsetcalculating force worksheetvba excel book