# Use Any Formula: Worksheetfunction in VBA - VBA and VB.Net Tutorials, Education and Programming Services (2023)

## Use Any Formula: Worksheetfunction in VBA

by Lakshmi Ramakrishnanin Excel

Contents

## Formulas in EXCEL

Microsoft Excel offers several built-in functions that can be used as formulas in Excel worksheets. It displays the categories of functions available in the Insert Function window, as seen in the image below:

## Usage of Formula in an Excel Sheet

Just like how mathematical formulas help us tackle easy and quick calculations in math, the formulas offered by Ms. Excel help us maintain good databases with data and formulas that can be auto-calculated and stored for ease of use and reference.

### Example of an Excel Formula:

After typing the “SUM” formula in the formula bar or any cell, (what you type in the cell starting with an equal (=) sign will display on the formula bar), if we press the enter key, click on the check mark on the formula bar, or select some other cell, we can see the auto-calculated answer.

When the formula bar or the cell is not in edit mode, the check and cross marks on the formula bar are disabled.

## Usage of Formulas as Functions in VBA

Excel VBA offers most of these formulas as built-in functions that can be easily used in our code to achieve the same result. These are called Worksheetfunctions.

## What is a WorksheetFunction?

The application object of MS Excel has a method called “WorksheetFunction.” It can give access to most of the standard built-in functions that are available as “Formulas” in Excel.

`Application.<function call> `can be used straight away to access the functions. However, using `Application.worksheetfunction.<function call>` can help the programmer with Intellisense that can guide him through the parameters to be used to finish typing the function.

## A List of Worksheet Functions – VBA

Here is a list of some worksheet functions that are commonly used.

### Date and Time Functions

A valid date here means `IsDate(<valid date>)` returns the Boolean value “TRUE”

## Examples of Using Worksheetfunctions in VBA

### Deletion of Rows That are Blank in a Range

`Sub row_del_demo()' declare variableDim range_new' loop to iterate through the rowsFor i = 1 To 60 ' get the range of rows Set range_new = Worksheets("Wonders").Rows(i &amp;amp;amp;amp;amp; ":" &amp;amp;amp;amp;amp; i) ' find the number of blank columns in that row fill_cols = Application.WorksheetFunction.CountA(range_new) ' delete the entire row if all columns in it are blank If fill_cols = 0 Then Sheets("Wonders").Rows(i).Delete End IfNextEnd Sub`

### Using Worksheet Function to Find the Sum of Integers in an Array

`Sub worksh_demo()' declare variables and objectsDim a(10) As IntegerDim sum_10 As Integer' loop to assign integer values to all the 10 array elementsFor i = 0 To 9a(i) = i + 10Debug.Print a(i)Next' find the sum of all numbers in the array using the worksheetfunctionsum_10 = Application.WorksheetFunction.Sum(a)' Print the sumDebug.Print sum_10End Sub`

### Using Average and Sum Worksheet Functions

`Sub wksfun_Demo() ' declare variables Dim tot, avg, res, sci, soc, mat, eng, lan ' assigning values sci = 67 soc = 78 eng = 90 mat = 89 lan = 45 ' find the total using a worksheet function tot = WorksheetFunction.Sum(sci, soc, eng, lan, mat) Debug.Print "The total of all marks is " &amp;amp;amp;amp;amp; tot 'find the average marks avg = WorksheetFunction.Average(sci, soc, mat, eng, lan) Debug.Print "The average marks of the student is " &amp;amp;amp;amp;amp; avg End Sub`

## Worksheet Function That Uses the Data From Excel Sheet as Parameter

`Sub wksh_demo()maximum_value = WorksheetFunction.Max(range("D2:D9"))Debug.Print maximum_valueEnd Sub`

Output :

528

## Conclusion

Worksheetfunction object helps us use proper format at our convenience to code our program instead of using the macro recorded to code any formula. We can either use object variables from our procedure or cell values from worksheets / any other Excel workbooks as parameters while we use the Worksheetfunction. The application object in front of it is optional. (Application.Worksheetfunction.)

Tagged with: Excel, excel formulas, Formulas, Microsoft, Microsoft Excel, VBA, VBA For Excel, Worksheet, worksheetfunction, Worksheets

Top Articles
Latest Posts
Article information

Author: Foster Heidenreich CPA

Last Updated: 05/07/2023

Views: 6597

Rating: 4.6 / 5 (76 voted)

Author information

Name: Foster Heidenreich CPA

Birthday: 1995-01-14

Address: 55021 Usha Garden, North Larisa, DE 19209

Phone: +6812240846623

Job: Corporate Healthcare Strategist

Hobby: Singing, Listening to music, Rafting, LARPing, Gardening, Quilting, Rappelling

Introduction: My name is Foster Heidenreich CPA, I am a delightful, quaint, glorious, quaint, faithful, enchanting, fine person who loves writing and wants to share my knowledge and understanding with you.