VBA And Worksheet Functions

In this chapter you will learn how to use Excel formula, Worksheet function and VBA Function by using the VBA.

Excel Formula

We can use excel formula same as we use in excel worksheet. For example, in the below given image we want get the Employee name in column B by using VLOOKUP from column F:G

Data to use formula

We can use Formula or Value property to put the Excel formula.

Sub Excel_Formula()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

sh.Range("B2").Formula = "=VLOOKUP(A2,F:G,2,0)"

Dim last_Row As Integer

last_Row = sh.Range("A" & Application.Rows.Count).End(xlUp).Row

sh.Range("B2:B" & last_Row).FillDown

End Sub

After executing above code we get the employee name on column B

Excel Formula by using VBA

Worksheet Function

We can also get the Employee name in column B by using Application.WorksheetFunction.

Sub Worksheet_Function()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Sheet1")

Dim last_Row As Integer

last_Row = sh.Range("A" & Application.Rows.Count).End(xlUp).Row

Dim i As Integer

For i = 2 To last_Row

sh.Range("B" & i).Value = Application.WorksheetFunction.VLookup(sh.Range("A" & i).Value, sh.Range("F:G"), 2, 0)

Next i

End Sub

After executing above code we can see the employee name on column B.

Worksheet Function

VBA Function

There are many inbuilt function available in VBA. You can use VBA function same as we use worksheet function.

For example, we want get the left 2 character form 

Sub VBA_Function()

MsgBox Left("PK-AnExcelExpert.com", 2)

End Sub

Below is the list of few useful VBA functions

VBA Formula

Description

Example

Return

Format

Applies a format to given value and returns the result as a string.

VBA.Format(0.65, "0%")

65%

InStr

Returns the place of a substring within a string.

VBA.InStr("PK-AnExcelExpert.com", "Excel")

6

Left

Returns a substring from the start of a given string.

VBA.Left("PK-AnExcelExpert.com", 2)

PK

Len

Returns the length of a given string.

VBA.Len("PK-AnExcelExpert.com")

20

LCase

Converts a given string to lower case text.

VBA.LCase("PK-AnExcelExpert.com")

pk-anexcelexpert.com

Mid

Returns a substring from the middle of a given string.

VBA.Mid("PK-AnExcelExpert.com", 6, 5)

Excel

Replace

Replaces a substring within a given text string.

VBA.Replace("PK-AnExcelExpert.com", "AnExcel", "PPT")

PK-PPTExpert.com

Right

Returns a substring from the end of a given string.

VBA.Right("PK-AnExcelExpert.com", 3)

com

StrReverse

Returns the character in Reverse order of given string

VBA.StrReverse("PK-AnExcelExpert.com")

moc.trepxElecxEnA-KP

Trim

Removes leading and trailing spaces from a given string.

VBA.Trim(" PK ")

PK

UCase

Converts a given string to upper case text.

VBA.UCase("PK-AnExcelExpert.com")

PK-ANEXCELEXPERT.COM

IsDate

Check the given value is a date or not.

VBA.IsDate(#1/1/2018#)

True

IsNumeric

Check the given value is a number or not.

VBA.IsNumeric("PK")

False

IsError

Check the given value is an error or not.

BA.IsError("PK")

False

Next Chapter >>User Defined Functions