####
**Best Online Course :**
SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English

- Live Projects & Case Studies

- Job Placement Assistance

- Get 20% off till July 14, 2017

This tutorial shows you how to enter worksheet functions in Excel VBA.

The function must be preceded by the word "

Suppose you have data in range A2:A8. You want to sum numbers entered in the range A2:A8.

Suppose you have data in column A and B. You want to sum the columns of numbers.

Suppose you have data in column A and B. You want to count the number of used cells in the columns.

Suppose you have data in range A2:A8. You want to count the number of cells that have value greater than 20.

**How to enter worksheet function in Excel VBA**The function must be preceded by the word "

**WorkheetFunction**" and then place "**.**" (period).**Download the workbook used in the examples shown below.****The data are shown in the image below:****Example 1 : Sum numbers in a range**Suppose you have data in range A2:A8. You want to sum numbers entered in the range A2:A8.

Sub SumRange()

Dim result As Double

result = WorksheetFunction.Sum(Range("A2:A8"))

MsgBox result

End Sub

**Example 2 : Sum numbers in two columns**Suppose you have data in column A and B. You want to sum the columns of numbers.

Sub SumColumn()

Dim cresult As Double

cresult = WorksheetFunction.Sum(Columns(1), Columns(2))

MsgBox cresult

End Sub

**Example 3 : Count the number of used cells in two columns**Suppose you have data in column A and B. You want to count the number of used cells in the columns.

Sub countcolumn()

Dim cresult As Double

cresult = WorksheetFunction.CountA(Columns(1), Columns(2))

MsgBox cresult

End Sub

**Example 4 : Conditional count of a range of numbers**Suppose you have data in range A2:A8. You want to count the number of cells that have value greater than 20.

Sub condcount()

Dim cresult As Double

cresult = WorksheetFunction.CountIf(Range("A2:A8"), ">20")

MsgBox cresult

End Sub

**Example 5 : VLOOKUP Function****The data used in this example is shown in the image below :**

Sub look()

Dim cresult As Double

cresult = WorksheetFunction.VLookup("Deepanshu", Range("D2:E5"), 2, 0)

MsgBox cresult

End Sub

**Download the workbook**

USEFUL INFORMATION WITH REAL TIME SCENARIOS ..

ReplyDelete