VBA Worksheet Function (Use Excel Functions in a Macro)

Key Notes

• You can use the WorksheetFunction property to access worksheet functions to use in a macro.

How to use a Worksheet Functions in VBA while Writing a Macro

Use the following steps to use a worksheet function in VBA.

• First, specify the cell where you want to insert the values returned by the function.
  
• After that, use an equal sign (=) and type Application.WorksheetFunction (as you can already in the Excel application, you can only use WorksheetFunction).
  
• Next, you need to enter a dot (.), and the moment you do that, you’ll get a list of functions that you have in the worksheet.
  
• From here, let’s use the TextJoin function to join text from the cell A1 and B1. Just like that, you use a function in the worksheet; when you enter starting parentheses, it shows you the arguments that you need to specify in the cell.
  
• As you can see in the above screenshot, it shows you the argument that you need to specify, but not the name of the arguments. So, you need to know the arguments before using them. Here are the arguments that you need to use in TextJoin.
  
• Now, let’s enter arguments in the function.
  
  1. delimiter: “ “
  2. ignore_empty: True
  3. text1: Range(“A1”)
  4. text2: Range(“B1”)

Here’s the full code.

Sub MyMacro()

Range("A1") = _
Application.WorksheetFunction.TextJoin _
(" ", True, Range("A2"), Range("A3"))

End Sub

And when you run this macro:

There is a total of 387 worksheet functions that you can use in VBA. But there are a few functions (Example: LEFT) that are not available to use.

Reason? There are In-Built functions in VBA that you can use while writing a VBA code. Let’s if you want to use a message box to get the value for a function. Let’s you want to get the maximum value from the named range “myValues”.

The code would be:

Sub MyMacro()

MsgBox Prompt:=WorksheetFunction.Max(Range("myValues"))

End Sub

And here’s how you get a message box:

Application.WorksheetFunction vs. Application

There are two different ways to refer to a worksheet function. Below are the two different lines of code that perform the same activity.

Sub MyMacro()

MsgBox Prompt:= _
Application.WorksheetFunction.Max(Range("myValues"))

MsgBox Prompt:= _
Application.Max(Range("myValues"))

End Sub

But there’s a benefit to using the second method. It returns an error code instead of showing the error directly.