VBA Worksheet Function

VBA Worksheet Function (Table of Contents)

• Simple SUM Worksheet Functions in VBA
• Use VLOOKUP in VBA as a Worksheet Function

Worksheet function in VBA is used when we have to refer to a specific worksheet, normally when we create a module in VBA the code executes in the currently active sheet of the workbook but if we want to execute the code in the specific worksheet we use worksheet function, this function has various uses and applications in VBA.

WorksheetFunctions in VBA

The best thing about VBA is, like how we use formulas in worksheet similarly VBA too has its own functions. If this is the best then it has a beautiful thing as well that is “we can use worksheet functions in Excel VBA as well”.

Yes!!! You heard it right, we can access to worksheet functions in Excel VBA also. We can access some of the worksheet functions while writing the code and make it part of our code.

How to Use Worksheet Functions in Excel VBA?

Below are the examples of how to use worksheet function in Excel using VBA.

In the worksheet, all the formulas start with equal (=) sign, similarly in VBA coding in order to access worksheet formulas we should use the word “WorksheetFunction”.

Before you enter any worksheet formula you need to mention the “WorksheetFunction” object name then put a dot (.) then you will get a list of all the available function under this object.

In this article, we will exclusively concentrate on how to use worksheet function in Excel VBA coding which will add more value to your coding knowledge.

#1 – Simple SUM Worksheet Functions in Excel VBA

Ok, to start off with worksheet functions in Excel VBA apply the simple SUM function to add numbers from the worksheet.

Assume you have monthly sales & cost data in the worksheet like the below one.

In B14 and C14 we need to arrive at the total of the above numbers. Follow the below steps to start the process of applying the “SUM” function in Excel VBA.

Step 1: Create a simple macro name.

Code:

Sub Worksheet_Function_Example1()

End Sub

Step 2: Since we need the result in cell B14 start the code as Range (“B14”).Value =

Code:

Sub Worksheet_Function_Example1()

  Range("B14").Value =

End Sub

Step 3: In B14 we need the value as the result of the sum of the numbers. So in order to access SUM function from worksheet start the code as “WorksheetFunction”.

Code:

Sub Worksheet_Function_Example1()

Range("B14").Value = WorksheetFunction.

End Sub

Step 4: The moment you put a dot (.) it will start to display the functions available. So select SUM from this.

Code:

Sub Worksheet_Function_Example1()

   Range("B14").Value = WorksheetFunction.Sum

End Sub

Step 5: Now give the reference of the above numbers i.e. Range (“B2:B13”).

Code:

Sub Worksheet_Function_Example1()

  Range("B14").Value = WorksheetFunction.Sum(Range("B2:B13"))

End Sub

Step 6: Similarly for next column apply the similar code by changing the cell references.

Code:

Sub Worksheet_Function_Example1()

  Range("B14").Value = WorksheetFunction.Sum(Range("B2:B13"))
  Range("C14").Value = WorksheetFunction.Sum(Range("C2:C13"))

End Sub

Step 7: Now run this code manually or using the F5 key to have a total in B14 & C14 cells.

Wow, we got our values. One thing you need to notice here is we don’t have any formula in the worksheet but we just got the result of the “SUM” function in Excel VBA.

#2 – Use VLOOKUP in Excel VBA as a WorksheetFunction

We have seen the way of using worksheet function in Excel VBA. We will see how to use VLOOKUP in VBA.

Assume below is the data you have in your excel sheet.

In E2 cell you had created a drop-down list of all the zones.

Based on the selection you made in the E2 cell we need to fetch the Pin Code for the respective zone. But this time through VBA VLOOKUP, not worksheet VLOOKUP. Follow the below steps to apply VLOOKUP in Excel VBA.

Step 1: Create a simple macro name in the Sub Procedure.

Code:

Sub Worksheet_Function_Example2()

End Sub

Step 2: We need the result in the F2 cell. So start the code as Range (“F2”).Value =

Code:

Sub Worksheet_Function_Example2()

  Range ("F2").Value =

End Sub

Step 3: To access worksheet function VLOOKUP start the code as “WorksheetFunction.VLOOKUP”.

Code:

Sub Worksheet_Function_Example2()

  Range ("F2").Value = WorksheetFunction.Vlookup(

End Sub

Step 4: One of the problems here is syntax will not give you any sort of guidance to work with VLOOKUP. You need to be absolutely sure about the syntax you are working on.

The first syntax of VLOOKUP is “Lookup Value”. In this case, our lookup value is E2 cell value, so write the code as Range (“E2”).Value

Code:

Sub Worksheet_Function_Example2()

  Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value,

End Sub

Step 5: Now the second argument is our table array, in this case, our table array range is from A2 to B6. So the code will be Range (“A2:B6”)

Code:

Sub Worksheet_Function_Example2()

  Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value,Range ("A2:B6"),

End Sub

Step 6: The Third argument will be from which column we need the data from the table array. Here we need the data from the 2^nd column, so the argument will be 2.

Code:

Sub Worksheet_Function_Example2()

  Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value,Range ("A2:B6"),2,

End Sub

Step 7: Final argument is range lookup, we need exact match so the argument is zero (0).

Code:

Sub Worksheet_Function_Example2()

  Range("F2").Value = WorksheetFunction.VLookup(Range("E2").Value, Range("A2:B6"), 2, 0)

End Sub

So, we are done with the coding part. Now go to the worksheet and select any of the range.

Now go to your coding module and run the macro Using F5 key or manually to get the pin code of the selected zone.

We cannot go back and run the macro every time, so let’s assign a macro to shapes. Insert one of the shapes in a worksheet.

Add text value to inserted shape.

Now right click and assign the macro name to this shape.

Click on ok after selecting the macro name.

Now, this shape holds the code of our VLOOKUP formula. So whenever you change the zone name click on the button, it will update the values.

Things to Remember About Worksheet Function in Excel VBA

• To access worksheet functions in VBA we need to write the word “WorksheetFunction” or “Application.WorksheetFunction”
• We don’t have all the functions to access only a few we can access.
• We don’t see the actual syntax of VBA worksheet functions, so we need to be absolutely sure of the function we are using.

Recommended Articles

This has been a guide to VBA WorksheetFunction. Here we learn how to use WorksheetFunctions like Sum Function and VLookup Function in Excel VBA along with some simple to advanced examples. Below are some useful excel articles related to VBA –

• Examples of VLOOKUP Table Array in Excel
• VBA Set
• Date Function in VBA
• VBA OFFSET
• MOD Function in VBA
• MID Function in VBA