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.

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.

120+ Professional Project Management Templates!
Save Up to 85% LIMITED TIME OFFER

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Browse All Templates
Excel VBA Project Management Templates

All-in-One Pack
120+ Project Management
Premium Templates
View Details

Essential Pack
50+ Project Management
Premium Templates
View Details
50+ Excel
Project Management
Templates Pack
View Details
50+ PowerPoint
Project Management
Templates Pack
View Details
25+ MS Word
Project Management
Templates Pack
View Details
Ultimate Project Management Template
View Details
Ultimate Resource Management Template
View Details
Project Portfolio Management Templates
View Details
By Last Updated: January 19, 2015Categories: VBATags:

Share This Story, Choose Your Platform!

Leave A Comment