VBA and Worksheet Functions - PK: An Excel Expert

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

Table of Contents

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

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

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.

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 “PK-AnExcelExpert.com”

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

Excel Formula

Worksheet Function

VBA Function

Next Chapter >>User Defined Functions