## 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 variable
Dim range_new

' loop to iterate through the rows
For 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 If
Next

End Sub
```

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

```Sub worksh_demo()

' declare variables and objects
Dim a(10) As Integer
Dim sum_10 As Integer

' loop to assign integer values to all the 10 array elements
For i = 0 To 9
a(i) = i + 10
Debug.Print a(i)
Next

' find the sum of all numbers in the array using the worksheetfunction
sum_10 = Application.WorksheetFunction.Sum(a)

' Print the sum
Debug.Print sum_10

End 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_value
End 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.)