Vba Worksheet Function How To Use Worksheetfunction In Vba

Excel VBA Worksheet Functions

The best thing about VBA is how we use formulas in worksheets. Similarly, VBA too has its functions. However, if this is the best, it also has a beautiful thing. That is, “we can also use Worksheet functions in VBA.”

You heard it right. We can access Worksheet functions in VBA also. We can access some of the worksheet functions while writing and making them part of our code.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Worksheet Function (wallstreetmojo.com)

How to use Worksheet Functions in VBA?

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

Before entering any worksheet formula, you need to mention the “WorksheetFunction” object name, put a dot (.), and get a list of all the available functions under this object.

In this article, we will exclusively concentrate on how to use Worksheet functions in VBA codingVBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more, which will add more value to your coding knowledge.

#1 – Simple SUM Worksheet Functions

To start with Worksheet functions, apply the simple SUM function in excelSUM Function In ExcelThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges.read more to add numbers from the worksheet.

Assume you have monthly sales and 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 steps below to start applying the SUM function in Excel VBA.

Step 1: Create a simple, excel macroExcel MacroA macro in excel is a series of instructions in the form of code that helps automate manual tasks, thereby saving time. Excel executes those instructions in a step-by-step manner on the given data. For example, it can be used to automate repetitive tasks such as summation, cell formatting, information copying, etc. thereby rapidly replacing repetitious operations with a few clicks. read more 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 =

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 to access the SUM function from the worksheet, start the code as “WorksheetFunction.“

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.

Sub Worksheet_Function_Example1()

Range(“B14”).Value = WorksheetFunction.Sum

End Sub

Step 5: Reference the above numbers: Range (“B2:B13”).

Sub Worksheet_Function_Example1()

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

End Sub

Step 6: Similarly, for the next column, apply a similar code by changing the cell referencesCell ReferencesCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more.

Sub Worksheet_Function_Example1()

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

End Sub

Step 7: Run this code manually or use the F5 key to have a total in B14 and C14 cells.

We got our values. You need to notice that we don’t have any formula in the worksheet, but we just got the result of the SUM function in VBA.

#2 – Use VLOOKUP as a Worksheet Function

We will see how to use VLOOKUP in VBAHow To Use VLOOKUP In VBAThe functionality of VLOOKUP in VBA is similar to that of VLOOKUP in a worksheet, and the method of using VLOOKUP in VBA is through an application. Method WorksheetFunctionread more. Assume below is the data you have in your Excel sheet.

In the 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. So, follow the below steps to apply VLOOKUPApply VLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more.

Step 1: Create a simple Macro name in the sub procedure.

Sub Worksheet_Function_Example2()

End Sub

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

Sub Worksheet_Function_Example2()

Range (“F2”).Value =

End Sub

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

Sub Worksheet_Function_Example2()

Range (“F2”).Value = WorksheetFunction.Vlookup(

End Sub

Step 4: One of the problems here is syntax will not guide you to work with VLOOKUP. It would help if you knew the syntax you were working on.

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

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”).

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 second column so that the argument will be 2.

Sub Worksheet_Function_Example2()

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

End Sub

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

Sub Worksheet_Function_Example2()

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

End Sub

So, we have completed the coding part. Now, go to the Worksheet and select any of the ranges.

Now, go to your coding module and run the Macro using the 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. Then, insert one of the shapes into a worksheet.

Add a text value to the 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 to update the values.

Things to Remember

• To access worksheet functions, we need to write the word “WorksheetFunction” or “Application.WorksheetFunction”We do not have access to all the functions, only a few.We do not see the actual syntax of Worksheet functions, so we must be sure of the function we are using.

Recommended Articles

This article is a guide to VBA Worksheet Function. Here, we learn how to use Worksheet functions like SUM and VLOOKUP in Excel VBA and some simple to advanced examples. Below are some useful Excel articles related to VBA: –

• Date Function in VBADate Function In VBAVBA Date is a date and time function. It returns only the current date as per the system date you are using and has no arguments whatsoever. This function returns the current system date.read moreOFFSET in VBAOFFSET In VBAVBA OFFSET function facilitates the user to move down or return to a particular cell at a specific range, i.e., at a distance of a certain number of rows and columns from the current cell.read moreVBA MODVBA MODVBA MOD is the same as the application in mathematics. When a number is divided by its divisor, this function gives us the remainder from the division. However, it is not a function in VBA; rather it is an operator.read moreMID Function in VBAMID Function In VBAVBA MID function extracts the middle values from the supplied sentence or word. It is categorized under the String and Text function and is a worksheet function.read more