Check If Sheet Exists Using VBA

In this post you will learn how to check whether a particular worksheet exists inside a workbook. There are few different ways to check it. I will show you two methods here. In both methods we are going to use the name of the worksheet to identify the existence. First method will use a string comparison function and the second method will use an error handling technique.

Table of contents
Check if sheet exists - Method 1
Check if sheet exists - Method 2
Check if sheet exists and then delete using VBA
If sheet does not exist then skip

Here is a sample workbook which contains a few worksheets.

Method 1

This workbook has three worksheets. Names of the worksheets are “Input”, “Tmp” and “Output”. Assume we want to check if sheet “Tmp” exists inside this workbook. Here is the first function you can use to check that.

Function IsSheetExist(WB As Workbook, SheetName As String) As Boolean

     Dim WS As Worksheet

     For Each WS In WB.Worksheets
          If StrComp(SheetName, WS.Name, vbTextCompare) = 0 Then
               IsSheetExist = True
               Exit Function
          End If
     Next WS

End Function

And this is how you can call this function from a subroutine.

Sub Test_1()

     Dim WB_Data As Workbook
     Dim Result As Boolean

     Set WB_Data = ActiveWorkbook

     Result = IsSheetExist(WB_Data, "Tmp")

     MsgBox Result

End Sub

If you run the macro when the “Tmp” sheet is available inside the workbook then you will see this message box.

This is the result you will see when there is no “Tmp” sheet.

Below is the explanation for the first function.

This function has two parameters. And the data type of the return value is boolean.

Function IsSheetExist(WB As Workbook, SheetName As String) As Boolean

Function uses a For Each Next statement to iterate through the sheets of the given workbook.

For Each WS In WB.Worksheets

Next WS

StrComp function is used to compare the given name with each and every sheet name.

If StrComp(SheetName, WS.Name, vbTextCompare) = 0 Then

End If

Learn more about StrComp function

If a match is found then the function will return the value “True” and exit.

For Each WS In WB.Worksheets
     If StrComp(SheetName, WS.Name, vbTextCompare) = 0 Then
          IsSheetExist = True
          Exit Function
     End If
Next WS

If the function is unable to find a matching sheet name inside the For Each Next statement, the code will be executed until the “End Function” line. Then the function will return false as the default value of a VBA function is false.

Method 2

In this method we are going to use error handling techniques to check if a sheet exists in a workbook. Below is the complete code for the second function.

Function IsSheetExist(WB As Workbook, SheetName As String) As Boolean

     Dim WS As Worksheet

     On Error Resume Next
     Set WS = WB.Worksheets(SheetName)

     If Err <> 0 Then
          IsSheetExist = False
     Else
          IsSheetExist = True
     End If

     On Error GoTo 0

End Function

You can call this function from a subroutine same as we did above for the first function.

Set WS = WB.Worksheets(SheetName)

If there is no sheet named as SheetName then the above line will generate an error like this.

To prevent that run-time error “On Error Resume Next” statement is used before that line. So the program will execute the next lines without raising the error. Next the below part will identify whether there is an error or not and output return value for the function accordingly.

If Err <> 0 Then
     IsSheetExist = False
Else
     IsSheetExist = True
End If

In VBA we use <> for not equal. It is the opposite of = symbol. So Err<>0 means error is not equal to zero. So there is an error. Then we can decide that the error occurred due to there not being such a sheet. So we return false for the function. Else we can return true.

So we learnt two different ways to check if a sheet exists inside a workbook. Sometimes we have to take some other actions after checking the existence of a particular sheet. Now let’s look at a few examples where we need to take another action after checking the existence of a sheet.

Check if sheet exists and delete using VBA

Sometimes you may need to check whether a particular sheet exists and then delete it if it exists. Here is one way to do it.

Function DeleteIfSheetExist(WB As Workbook, SheetName As String) As Boolean

     Dim WS As Worksheet

     For Each WS In WB.Worksheets
          If StrComp(SheetName, WS.Name, vbTextCompare) = 0 Then
               Application.DisplayAlerts = False
               WS.Delete
               Application.DisplayAlerts = True
               Exit Function
          End If
     Next WS

End Function

You can call the above function inside a subroutine like this.

Sub Test_3()

     Dim WB_Data As Workbook

     Set WB_Data = ActiveWorkbook

     Call DeleteIfSheetExist(WB_Data, "Tmp")

End Sub

You might wonder why you need to check the existence of the sheet. You can delete the sheet straight away. Then if an error raises when there is no sheet with that name you can use “On Error Resume Next” to proceed without any interruption. Actually you can delete the sheet without checking its existence. But the problem is that errors can be raised due to different other reasons. For example, an error can be raised if you try to delete a sheet of a protected workbook. However there is a turnaround for that as well. You can identify the reason for the runtime error using the err number and then develop the code accordingly.

If sheet does not exist skip

Sometimes you may need to skip some processes if a sheet does not exist. For an example assume you want to call another subroutine if a sheet exists and skip if it doesn’t.

Sub CallAnotherSubIfSheetExist()

     Dim WB As Workbook
     Dim WS As Worksheet
     Dim SheetName As String

     Set WB = ActiveWorkbook
     SheetName = "Tmp"

     On Error Resume Next
     Set WS = WB.Worksheets(SheetName)

     If Err <> 0 Then
          'Do nothing
     Else
          On Error GoTo 0
          Call OtherSub
     End If

     On Error GoTo 0

End Sub

Also you can shorten the above if statement section like this as well.

Sub CallAnotherSubIfSheetExist()

     Dim WB As Workbook
     Dim WS As Worksheet
     Dim SheetName As String

     Set WB = ActiveWorkbook
     SheetName = "Tmp"

     On Error Resume Next
     Set WS = WB.Worksheets(SheetName)

     If Err = 0 Then
          On Error GoTo 0
          Call OtherSub
     End If

     On Error GoTo 0

End Sub

Clear Contents of Excel Sheet Except First Row Using VBA

In this post you will learn how to clear the contents of Excel sheet except the first row using VBA. This is a very common requirement for Excel VBA applications. Because VBA applications often need to clear existing reports and re-generate them. When doing this, the program doesn’t need to delete the header row. Because it is the same for the new reports. Also there may be some other situations where you want to develop a VBA code to delete content from the Excel sheet except the first row. For example, sometimes users may need to clear the existing result sheets except the header using buttons.

So now let’s look at how to develop a code to delete the worksheet contents without header row.

Let’s consider this sample worksheet. Name of the worksheet is “Order Information”.

There are few different ways to accomplish this. I will explain two methods in this post. You can use the first method if you know the last column you have data in. In the above sample sheet we have data upto column E. If you don’t know what the last column is or if the last column changes from time to time then you should use the second method shown at the end.

This is the complete code of the first method.

Sub DeleteContentsExceptHeader()

     Dim WS As Worksheet
     Dim LastRow As Long

     Set WS = Worksheets("Order Information")

     LastRow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

     If LastRow > 1 Then
         WS.Range("A2:E" & LastRow).ClearContents
     End If

End Sub

This is the result you will get when run the subroutine.

Here is the explanation for the first subroutine.

First we need to define two variables

Dim WS As Worksheet
Dim LastRow As Long

Next, assign the worksheet to the WS variable.

Set WS = Worksheets("Order Information")

Find the last row of the worksheet

LastRow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

Next we need to check whether the last row is greater than 1. If it is greater than 1, then we can clear the contents using the Range.ClearContents method.

If LastRow > 1 Then
     WS.Range("A2:E" & LastRow).ClearContents
End If

It is important to check whether the last row is greater than 1, because otherwise the “WS.Range("A2:E" & LastRow).ClearContents” statement will delete the header row if there is no data after row 1.

Now let’s move to the second method. As mentioned earlier, you can use this method if you don’t know the last column of the data or if the last column changes from time to time. Below is the complete code of the second method.

Sub DeleteContentsExceptHeader_Method2()

     Dim WS As Worksheet
     Dim LastRow As Long

     Set WS = Worksheets("Order Information")

     LastRow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

     If LastRow > 1 Then
         WS.Rows("2:" & LastRow).ClearContents
     End If

End Sub

In this method we are deleting entire rows from row number two to the last row. It is important to use If Statement (If LastRow > 1 Then) to avoid deletion of the first row in case there is no data after row 1.

How to Search For Multiple Strings Using VBA InStr Function

In the previous post we learnt how to use the InStr function to check whether a string contains a substring. Today I’m going to show you how to search for multiple strings using the VBA InStr function. For an example consider this sample string.

“Learn to develop Excel VBA applications”

Assume you want to check whether this string contains either Excel or VBA. We can’t use the InStr function as it is, to search for multiple values. But we can create our own function using the InStr function to do the job. I’m going to develop two types of solutions to search for multiple values using the VBA InStr function. One function will return true or false depending on whether it can find at least one of the multiple strings or not. And the other function will return the positions of each and every substring. So stay tuned.

Function 1 - Return true or false depending on whether at least one of the multiple values are available or not.

This function needs two input parameters. The string being searched and the multiple strings. Below is the complete code of the first function.

Function MultipleStringsSearch(SText As String, MultipleStrings() As String) As Boolean

     Dim i As Integer

    For i = LBound(MultipleStrings) To UBound(MultipleStrings)
        If Len(MultipleStrings(i)) > 0 Then
            If InStr(1, SText, MultipleStrings(i), vbTextCompare) > 0 Then
                MultipleStringSearch = True
                Exit Function
            End If
        End If
    Next i

End Function

Below is the explanation of the above code.

Here we pass the multiple values to the function as an array. Also this function returns either true or false. So the data type of the returned value is boolean.

Function MultipleStringsSearch(SText As String, MultipleStrings() As String) As Boolean

Then a For Next statement is used to iterate through each element of the MultipleStrings array.

For i = LBound(MultipleStrings) To UBound(MultipleStrings)

Next i

We should check whether the length of each string is higher than 0. Because the InStr function returns the start value when the length of the search string is 0. Click the link below to see more details.

InStr function return Start value when length of second string is 0

If Len(MultipleStrings(i)) > 0 Then

Then the InStr function is used to check whether the SText string contains each substring of the MultipleStrings array.

If InStr(1, SText, MultipleStrings(i), vbTextCompare) > 0 Then

Now let’s see how to call this function in subroutines.

Let’s use a somewhat long string as the first string. This will be the string being searched.

MyString = "When we develop Excel VBA applications sometimes we need the application to check whether some strings are included in other strings."

And the multiple strings we are searching for are “we”, “Excel” and “applications”.

Sub Test_1()

     Dim MyString As String
     Dim StringsArr(2) As String

     MyString = "When we develop Excel VBA applications sometimes we need the application to check whether some strings are included in other strings."

     StringsArr(0) = "we"
     StringsArr(1) = "Excel"
     StringsArr(2) = "applications"

     Debug.Print MultipleStringsSearch(MyString, StringsArr)

End Sub

In this subroutine, a fixed size array is declared to contain the multiple strings.

Dim StringsArr(2) As String

And then added the multiple strings to that array as follows.

StringsArr(0) = "we"
StringsArr(1) = "Excel"
StringsArr(2) = "applications"

Want to learn more about fixed size arrays? Check this post.

Fixed Size Arrays in VBA

This is the output of the Test_1 subroutine.

Here is the second example.

Sub Test_2()

     Dim MyString As String
     Dim StringsArr(2) As String

     MyString = "I went to home"

     StringsArr(0) = "Excel"
     StringsArr(1) = "vba"
     StringsArr(2) = "application"

     Debug.Print MultipleStringsSearch(MyString, StringsArr)

End Sub

As you can see the multiple strings are not found inside the MyString. So the output of the Test_2 subroutine is false.

In the above two examples, multiple strings were added to the StringsArr array one by one. However if you have the multiple values in a one string separated by commas, then you can use Split function to convert it to an array easily. See the below example.

Sub Test_3()

     Dim MyString As String
     Dim MultipleStrings As String
     Dim StringsArr() As String

     MyString = "When we develop Excel VBA applications sometimes we need the application to check whether some strings are included in other strings."

     MultipleStrings = "Excel,vba,application"

     StringsArr = Split(MultipleStrings, ",")

     Debug.Print MultipleStringsSearch(MyString, StringsArr)

End Sub

This is the output of the Test_3 subroutine.

Function 2 - Return the positions of each multiple string

Previous function returned true or false depending on whether the multiple values are available inside the string being searched or not. But this new function returns the position of each multiple string inside the main string. For example if the multiple strings are found inside the main string at the positions x, y and z then the function will return x,y,z in an array.

Function InStrResultForMultipleStrings(SText As String, MultipleStrings() As String) As Variant

     Dim i As Integer
     Dim ResultArr() As Variant
     Dim iStart As Integer
     Dim iEnd As Integer

     iStart = LBound(MultipleStrings)
     iEnd = UBound(MultipleStrings)
     ReDim ResultArr(iStart To iEnd)

     For i = LBound(MultipleStrings) To UBound(MultipleStrings)
         ResultArr(i) = InStr(1, SText, MultipleStrings(i), vbTextCompare)
     Next i

     InStrResultForMultipleStrings = ResultArr()

End Function

Input parameters for this second function are the same as the previous function. However this function returns a value of the variant data type instead of the boolean. Following example shows how to call this function within a subroutine.

Sub Test_4()

     Dim MyString As String
     Dim StringsArr(2) As String
     Dim Result() As Variant

     MyString = "Excel formulas and vba macros"

     StringsArr(0) = "Excel"
     StringsArr(1) = "vba"
     StringsArr(2) = "application"

     Result = InStrResultForMultipleStrings(MyString, StringsArr)

End Sub

Then set a breakpoint at the end of the subroutine and run the macro to see the result in the Locals window.

Word “Excel” is at the position 1 within the MyString and “vba” is at position 20. Also the substring "application" is not available inside the MyString. So the function returns 0 for that substring.

Also note that above two functions carry out a case insensitive search. If you want to do case sensitive search for multiple strings then change the compare type from “vbTextCompare” to “vbBinaryCompare”. So the first function should be changed like this.

Function MultipleStringsSearchCaseSensitive(SText As String, MultipleStrings() As String) As Boolean

     Dim i As Integer

     For i = LBound(MultipleStrings) To UBound(MultipleStrings)
         If Len(MultipleStrings(i)) > 0 Then
             If InStr(1, SText, MultipleStrings(i), vbBinaryCompare) > 0 Then
                 MultipleStringsSearch = True
                 Exit Function
             End If
         End If
     Next i

End Function

You can do the same for the second function as well.

How to Check If String Contains an Another String - VBA

When we develop Excel VBA applications sometimes we need the application to check whether some strings are included in other strings. So in this lesson you will learn how to check if a string contains a substring using the VBA InStr function.

Table of contents
InStr function
Return values of VBA InStr function
VBA InStr case insensitive search
VBA InStr case sensitive search
If the second string occurs multiple times.
How to use VBA InStr Function for list of strings

InStr function

The InStr function has four parameters. Two optional and two required parameters. This is the syntax of the InStr function.

InStr([start], FirstString, SecondString, [CompareMethod])

Start - This is the starting point of the first string where you want to begin searching for the second string. If omitted, the function will search from first position.

FirstString - Function will search through this string to find the second string.

SecondString - This is the string the InStr function will search for.

CompareMethod - There are three options for this parameter. vbBinaryCompare, vbDatabaseCompare and vbTextCompare. But vbDatabaseCompare is only used for Microsoft Access. So you can use either vbTextCompare or vbBinaryCompare for Excel VBA Macros. If you select vbBinaryCompare then the VBA InStr function will carry out a binary comparison. So the function will see “A” and “a” as different. But if you choose vbTextCompare then the InStr function will carry out a textual comparison and it will see “A” and “a” as the same. You will get a clear understanding about these different types of comparisons from the examples below.

Return values of VBA InStr function

There are four types of return values for this function. Click on the links to see related examples.

0 - Second string is not found or Length of first string is 0 or Starting point is higher than length of first string or Starting point is higher than the occurrence position of the second string.

Null - First string is null or Second string is Null or both are Null

Start - Length of second string is 0

Position at first string where match is found - When second string is found within first string

Now let’s look at examples where we will get those return types.

Return 0

Second sting is not found

Sub Example_1()

     Debug.Print InStr(1, "Excel VBA Solutions", "PHP", vbTextCompare)

End Sub

Length of first string is 0

In this example Len(String1) equals 0. So the function returns 0.

Sub Example_2()

     Dim String1 As String
     Dim String2 As String

     String1 = ""
     String2 = "PHP"

     Debug.Print InStr(1, String1, "PHP", vbTextCompare)

End Sub

Starting point is higher than length of first string

Sub Example_3()

     Debug.Print InStr(20, "run macro", "macro", vbTextCompare)

End Sub

Here the length of the first string is 9. But the start is set to 20. So the function will return 0.

Start is higher than occurrence position of the second string inside first string

Sub Example_4()

     Debug.Print InStr(8, "Check this Excel tutorial", "this", vbTextCompare)

End Sub

In this example, the InStr function is searching for the string “this” inside the first string. And string “this” appears at the position 7 of the first string. But as the start is set to 8 the function returns 0. Because the InStr function can’t find the substring “this” after position 8.

Return Null

The InStr function returns Null on three occasions.

Return Null when first string is Null

Sub Example_5()

     Dim String1 As Variant
     Dim String2 As String

     String1 = Null
     String2 = "word"

     Debug.Print InStr(1, String1, String2, vbTextCompare)

End Sub

Here String1 has been declared as a variant because only the variant data type can hold Null values.

Return Null when second string is Null

Sub Example_6()

     Dim String1 As String
     Dim String2 As Variant

     String1 = "Excel VBA Solutions"
     String2 = Null

     Debug.Print InStr(1, String1, String2, vbTextCompare)

End Sub

Here String2 is declared as a variant because only the variant data type can hold the Null values.

Return Null when both first and second strings are Null

Sub Example_7()

     Dim String1 As Variant
     Dim String2 As Variant

     String1 = Null
     String2 = Null

     Debug.Print InStr(1, String1, String2, vbTextCompare)

End Sub

Return Start

The InStr function will return the start value on one occasion.

Return Start when length of second string is 0

Sub Example_8()

     Dim String1 As String
     Dim String2 As String

     String1 = "Excel VBA Solutions"
     String2 = ""

     Debug.Print InStr(4, String1, String2, vbTextCompare)

End Sub

Here the Len(String2) is equal to 0. And the start is 4. So the function will return 4.

Return the position where the match is found

When the second string is found within the first string, the function will return the position of the first string where the second string is found.

Sub Example_9()

     Dim String1 As String
     Dim String2 As String

     String1 = "How to check if string contains another string"
     String2 = "to"

     Debug.Print InStr(4, String1, String2, vbTextCompare)

End Sub

Here the word “to” can be found at the fifth position of the String1. So the function will return 5. Note that the function also considers spaces when determining the position.

VBA InStr Case Sensitivity

When you use the InStr function sometimes you may want to do case insensitive searches and sometimes case sensitive searches. So how do we control the case sensitivity? We can use the fourth parameter of the function to control the case sensitivity of the searches.

VBA InStr case insensitive search

In Excel VBA we can use one of the two values for the fourth parameter of the VBA InStr function. vbTextCompare or vbBinaryCompare. Because vbDatabaseCompare is only related to Microsoft Access. So far in our examples we used the vbTextCompare as the fourth parameter. If we use vbTextCompare as the fourth parameter, then the function will do case insensitive search.

Sub Example_10()

     Dim String1 As String
     Dim String2 As String

     String1 = "Excel VBA Solutions"
     String2 = "vba"

     Debug.Print InStr(4, String1, String2, vbTextCompare)

End Sub

Here the word “VBA” in uppercase in the String1 and “vba” in lowercase in the String2. As we have used vbTextCompare as the fourth parameter, the InStr function will do case insensitive search and will return 7.

VBA InStr case sensitive search

So we learnt how to do case insensitive search using the VBA InStr function from the above example. We can use vbBinaryCompare as the fourth parameter to do case sensitive searches.

Sub Example_11()

     Dim String1 As String
     Dim String2 As String

     String1 = "Excel VBA Solutions"
     String2 = "vba"

     Debug.Print InStr(4, String1, String2, vbBinaryCompare)

End Sub

Here the word “VBA” is in uppercase in the String1 and “vba” is in lowercase in the String2. As we have used vbBinaryCompare as the fourth parameter, search will be case sensitive and function will return 0.

Now let’s use the word “VBA” in uppercase in both strings and check how it works with the vbBinaryCompare option.

Sub Example_12()

     Dim String1 As String
     Dim String2 As String

     String1 = "Excel VBA Solutions"
     String2 = "VBA"

     Debug.Print InStr(4, String1, String2, vbBinaryCompare)

End Sub

If the second string occurs multiple times.

Sometimes the second string can occur multiple times inside the first string. If this happens the InStr function will return the position of the first occurrence of the second string starting from the start point.

Sub Example_13()

     Dim String1 As String
     Dim String2 As String

     String1 = "Excel formulas, Excel macros and Excel charts"
     String2 = "Excel"

     Debug.Print InStr(1, String1, String2, vbTextCompare)

End Sub

In this example word Excel occurs 3 times inside the String1. As the start is 1, the InStr function will return the position of first occurrence which is 1.

Here are the same example strings with a different start.

Sub Example_14()

     Dim String1 As String
     Dim String2 As String

     String1 = "Excel formulas, Excel macros and Excel charts"
     String2 = "Excel"

     Debug.Print InStr(5, String1, String2, vbTextCompare)

End Sub

In this example the start is set to 5. So now the function will search for the word “Excel” inside the String1 from the fifth position onward. Therefore in this example, the function will return the position of the second occurrence of the word “Excel”.

How to use VBA InStr Function for list of strings

However in practical situations you may not need to check if one string contains a substring. Instead you may need to check if a list of strings contains a particular substring and output the results. So now let’s look at how to accomplish such a task with the help of the For Next statement.

Let’s consider this sample Excel sheet.

This sample Excel sheet contains a list of post titles of this blog in column A. I’m going to find which titles have the word “Excel” and write “Found” Or “Not Found” in column B. Let’s name the subroutine as CheckForWordExcel

Sub CheckForWordExcel()

End Sub

First we need to declare a few variables.

Dim WS As Worksheet
Dim PostTitle As String
Dim i As Integer

If the name of the worksheet is “Sheet1”, we can assign the sheet to the WS variable as follows.

Set WS = Worksheets("Sheet1")

Assume there are titles up to the 100th row. So we can use a For Next statement like this.

For i = 2 To 100

Next i

In each iteration we can assign the post titles to the PostTitle variable like this.

For i = 2 To 100
     PostTitle = WS.Range("A" & i).Value
Next i

Now we can use the InStr function to check whether the word “Excel” is available in each title.

InStr(1, PostTitle, "Excel", vbTextCompare)

Here the second string is “Excel” and the length of it is higher than 0. Therefore InStr function should return positive value only when substring “Excel” found inside the PostTitle. So we can use an If statement inside the For Next Loop like this.

For i = 2 To 100
     PostTitle = WS.Range("A" & i).Value

     If InStr(1, PostTitle, "Excel", vbTextCompare) > 0 Then
         WS.Range("B" & i).Value = "Found"
     Else
         WS.Range("B" & i).Value = "Not Found"
     End If
Next i

So here is the full code of the subroutine.

Sub CheckForWordExcel()

     Dim WS As Worksheet
     Dim PostTitle As String
     Dim i As Integer

     Set WS = Worksheets("Sheet1")

     For i = 2 To 100
         PostTitle = WS.Range("A" & i).Value

         If InStr(1, PostTitle, "Excel", vbTextCompare) > 0 Then
             WS.Range("B" & i).Value = "Found"
         Else
             WS.Range("B" & i).Value = "Not Found"
         End If
     Next i

End Sub

How to Put Double Quotes in a String in VBA

In this lesson you will learn how to put double quotes in a string in Excel VBA. Sometimes your VBA application needs to output some text in an Excel sheet, in a form control or in the immediate window. For an example, assume you want to write Hello World! In cell A1 of the Sheet1. You can simply do it like this.

Sub WriteHelloWorldInSheet()

Worksheets("Sheet1").Range("A1").Value = "Hello World!"

End Sub

Or you can simply print this in the immediate window like this.

Sub PrintHelloWorldInImmediateWindow()

Debug.Print "Hello World!"

End Sub

But what if you want to output something like this in an Excel sheet or immediate window.,

"I admired both of them, especially Kevin," John said.

Excel VBA will give us a syntax error if we follow the above methods.

And if you try to output this in the immediate window, text will convert to a strange form like this.

So how do we put quotation marks in a string? There are two ways to solve this problem.

Method 1

Add an extra double quote in front of each quotation mark. So the quotation marks will be doubled. If you want to show two double quotes inside the string, then you will have to write four double quotes. I will explain this little more with our sample string.

As you can see there are two types of quotation marks in this image. Two red color double quotes and two green color double quotes. Red color quotes are ourter quotes. You don’t need to do anything with them. You have to double only the green color double quotes. So the final code should look like this.

Here is the complete subroutine.

Sub DoubleQuotesExample()

Worksheets("Sheet1").Range("A1").Value = """I admired both of them, especially Kevin,"" John said."

End Sub

You can use this same technique to print string in the immediate window as well.

Sub DoubleQuotesExampleImmediateWindow()

Debug.Print """I admired both of them, especially Kevin,"" John said."

End Sub

Next let’s look at how to use this method when there are consecutive double quotes.

Example - Output following in the cell A1 of the Sheet 1

"Apple""Mango""Orange"

So the rule of this method is, you have to add an extra double quote for each quotation mark. So the completed subroutine should look like this.

Sub ConsecutiveDoubleQuotes()

Worksheets("Sheet1").Range("A1").Value = """Apple""""Mango""""Orange"""

End Sub

Method 2

Use the ASCII code of the double quote. In this method you can use Chr(34) where you want the double quotes. So let’s consider our first example string again.

"I admired both of them, especially Kevin," John said.

We can replace the double quotes with Chr(34) like this.

Chr(34) & "I admired both of them, especially Kevin," & Chr(34) & " John said."

Remember to use ampersand(&) to concatenate text strings with Chr(34). Here is the completed subroutine to write that string to cell A1 of the Sheet1.

Sub DoubleQuotesExample_Method2()

Worksheets("Sheet1").Range("A1").Value = Chr(34) & "I admired both of them, especially Kevin," & Chr(34) & " John said."

End Sub

And here is how you can use this second method to print a string with double quotes in the immediate window.

Sub PrintDoubleQuotesImmediateWindow_Method2()

Debug.Print Chr(34) & "I admired both of them, especially Kevin," & Chr(34) & " John said."

End Sub

Next let’s look at how to use this method when there are consecutive double quotes in the strings. So let’s try to print the string below in the immediate windows using this second method.

"Apple""Mango""Orange"

The rule is, you need to replace " with Chr(34) and concatenate with ampersand(&). Therefore we can rewrite the above string like this.

Chr(34) & "Apple" Chr(34) & Chr(34) & "Mango" Chr(34) & Chr(34) & "Orange" & Chr(34)

Here is the completed subroutine

Sub ConsecutiveDoubleQuotes_Method2()

Debug.Print Chr(34) & "Apple"; Chr(34) & Chr(34) & "Mango"; Chr(34) & Chr(34) & "Orange" & Chr(34)

End Sub

You can also use these ASCII codes for various other characters. Check this page to see what characters you can print with ASCII codes.

ASCII Table

How to Add a Formula to a Cell Using VBA

In this lesson you can learn how to add a formula to a cell using vba. There are several ways to insert formulas to cells automatically. We can use properties like Formula, Value and FormulaR1C1 of the Range object. This post explains five different ways to add formulas to cells.

Table of contents
How to add formula to cell using VBA
Add formula to cell and fill down using VBA
Add sum formula to cell using VBA
How to add If formula to cell using VBA
Add formula to cell with quotes using VBA
Add Vlookup formula to cell using VBA

We use formulas to calculate various things in Excel. Sometimes you may need to enter the same formula to hundreds or thousands of rows or columns only changing the row numbers or columns. For an example let’s consider this sample Excel sheet.

In this Excel sheet I have added a very simple formula to the D2 cell.

=B2+C2

So what if we want to add similar formulas for all the rows in column D. So the D3 cell will have the formula as =B3+C3 and D4 will have the formula as =B4+D4 and so on. Luckily we don’t need to type the formulas manually in all rows. There is a much easier way to do this. First select the cell containing the formula. Then take the cursor to the bottom right corner of the cell. Mouse pointer will change to a + sign. Then left click and drag the mouse until the end of the rows.

However if you want to add the same formula again and again for lots of Excel sheets then you can use a VBA macro to speed up the process. First let’s look at how to add a formula to one cell using vba.

How to add formula to cell using VBA

Lets see how we can enter above simple formula(=B2+C2) to cell D2 using VBA

Method 1

In this method we are going to use the Formula property of the Range object.

Sub AddFormula_Method1()

     Dim WS As Worksheet

     Set WS = Worksheets("Sheet1")

     WS.Range("D2").Formula = "=B2+C2"

End Sub

Method 2

We can also use the Value property of the Range object to add a formula to a cell.

Sub AddFormula_Method2()

    Dim WS As Worksheet

    Set WS = Worksheets("Sheet1")

    WS.Range("D2").Value = "=B2+C2"

End Sub

Method 3

Next method is to use the FormulaR1C1 property of the Range object. There are few different ways to use FormulaR1C1 property. We can use absolute reference, relative reference or use both types of references inside the same formula.

In the absolute reference method cells are referred to using numbers. Excel sheets have numbers for each row. So you should think similarly for columns. So column A is number 1. Column B is number 2 etc. Then when writing the formula use R before the row number and C before the column number. So the cell A1 is referred to by R1C1. A2 is referred to by R2C1. B3 is referred to by R3C2 etc.

This is how you can use the absolute reference.

Sub AddFormula_Method3A()

    Dim WS As Worksheet

    Set WS = Worksheets("Sheet1")

    WS.Range("D2").FormulaR1C1 = "=R2C2+R2C3"

End Sub

If you use the absolute reference, the formula will be added like this.

If you use the manual drag method explained above to fill down other rows, then the same formula will be copied to all the rows.

In Majority cases this is not how you want to fill down the formula. However this won’t happen in the relative method. In the relative method, cells are given numbers relative to the cell where the formula is entered. You should use negative numbers when referring to the cells in upward direction or left. Also the numbers should be placed within the square brackets. And you can omit [0] when referring to cells on the same row or column. So you can use RC[-2] instead of R[0]C[-2]. The macro recorder also generates relative reference type code, if you enter a formula to a cell while enabling the macro recorder.

Below example shows how to put formula =B2+C2 in D2 cell using relative reference method.

Sub AddFormula_Method3B()

    Dim WS As Worksheet

    Set WS = Worksheets("Sheet1")

    WS.Range("D2").FormulaR1C1 = "=RC[-2]+RC[-1]"

End Sub

Now use the drag method to fill down all the rows.

You can see that the formulas are changed according to the row numbers.

Also you can use both relative and absolute references in the same formula. Here is a typical example where you need a formula with both reference types.

We can add the formula to calculate Total Amount like this.

Sub AddFormula_Method3C()

     Dim WS As Worksheet

     Set WS = Worksheets("Sheet2")

     WS.Range("C5").FormulaR1C1 = "=RC[-1]+RC[-1]*R2C2"

End Sub

In this formula we have a absolute reference after the * symbol. So when we fill down the formula using the drag method that part will remain the same for all the rows. Hence we will get correct results for all the rows.

Add formula to cell and fill down using VBA

So now you've learnt various methods to add a formula to a cell. Next let’s look at how to fill down the other rows with the added formula using VBA.

Assume we have to calculate cell D2 value using =B2+C2 formula and fill down up to 1000 rows. First let’s see how we can modify the first method to do this. Let’s name this subroutine as “AddFormula_Method1_1000Rows”

Sub AddFormula_Method1_1000Rows()

End Sub

Then we need an additional variable for the For Next statement

Dim WS As Worksheet
Dim i As Integer

Next, assign the worksheet to WS variable

Set WS = Worksheets("Sheet1")

Now we can add the For Next statement like this.

For i = 2 To 1000
     WS.Range("D" & i).Formula = "=B" & i & "+C" & i
Next i

Here I have used "D" & i instead of D2 and "=B" & i & "+C" & i instead of "=B2+C2". So the formula keeps changing like =B3+C3, =B4+C4, =B5+C5 etc. when iterated through the For Next loop.

Below is the full code of the subroutine.

Sub AddFormula_Method1_1000Rows()

     Dim WS As Worksheet
     Dim i As Integer

     Set WS = Worksheets("Sheet1")

     For i = 2 To 1000
         WS.Range("D" & i).Formula = "=B" & i & "+C" & i
     Next i

End Sub

So that’s how you can use VBA to add formulas to cells with variables.

Next example shows how to modify the absolute reference type of FormulaR1C1 method to add formulas upto 1000 rows.

Sub AddFormula_Method3A_1000Rows()

     Dim WS As Worksheet
     Dim i As Integer

     Set WS = Worksheets("Sheet1")

     For i = 2 To 1000
         WS.Range("D" & i).FormulaR1C1 = "=R" & i & "C2+R" & i & "C3"
     Next i

End Sub

You don’t need to do any change to the formula section when modifying the relative reference type of the FormulaR1C1 method.

Sub AddFormula_Method3B_1000Rows()

     Dim WS As Worksheet
     Dim i As Integer

     Set WS = Worksheets("Sheet1")

     For i = 2 To 1000
         WS.Range("D" & i).FormulaR1C1 = "=RC[-2]+RC[-1]"
     Next i

End Sub

Use similar techniques to modify other two types of subroutines to add formulas for multiple rows. Now you know how to add formulas to cells with a variable. Next let’s look at how to add formulas with some inbuilt functions using VBA.

How to add sum formula to a cell using VBA

Suppose we want the total of column D in the D16 cell. So this is the formula we need to create.

=SUM(D2:D15)

Now let’s see how to add this using VBA. Let’s name this subroutine as SumFormula.

Sub SumFormula()

End Sub

First let’s declare a few variables.

Dim WS As Worksheet
Dim StartingRow As Long
Dim EndingRow As Long

Assign the worksheet to the variable.

Set WS = Worksheets("Sheet3")

Assign the starting row and the ending row to relevant variables.

StartingRow = 2
EndingRow = 1

Then the final step is to create the formula with the above variables.

WS.Range("D16").Formula = "=SUM(D" & StartingRow & ":D" & EndingRow & ")"

Below is the full code to add the Sum formula using VBA.

Sub SumFormula()

     Dim WS As Worksheet
     Dim StartingRow As Long
     Dim EndingRow As Long

     Set WS = Worksheets("Sheet3")
     StartingRow = 2
     EndingRow = 15

     WS.Range("D16").Formula = "=SUM(D" & StartingRow & ":D" & EndingRow & ")"

End Sub

How to add If Formula to a cell using VBA

If function is a very popular inbuilt worksheet function available in Microsoft Excel. This function has 3 arguments. Two of them are optional.

Now let’s see how to add a If formula to a cell using VBA. Here is a typical example where we need a simple If function.

This is the results of students on an examination. Here we have names of students in column A and their marks in column B. Students should get “Pass” if he/she has marks equal or higher than 40. If marks are less than 40 then Excel should show the “Fail” in column C. We can simply obtain this result by adding an If function to column C. Below is the function we need in the C2 cell.

=IF(B2>=40,"Pass","Fail")

Now let’s look at how to add this If Formula to a C2 cell using VBA. Once you know how to add it then you can use the For Next statement to fill the rest of the rows like we did above. We discussed a few different ways to add formulas to a range object using VBA. For this particular example I’m going to use the Formula property of the Range object.

So now let’s see how we can develop this macro. Let’s name this subroutine as “AddIfFormula”

Sub AddIfFormula()

End Sub

However we can’t simply add this If formula using the Formula property like we did before. Because this If formula has quotes inside it. So if we try to add the formula to the cell with quotes, then we get a syntax error.

Add formula to cell with quotes

There are two ways to add the formula to a cell with quotes.

Method 1

Sub AddIfFormula_Method1()

     Dim WS As Worksheet

     Set WS = Worksheets("Sheet4")

     WS.Range("C2").Formula = "=IF(B2>=40,""Pass"",""Fail"")"

End Sub

Method 2

Sub AddIfFormula_Method2()

     Dim WS As Worksheet

     Set WS = Worksheets("Sheet4")

     WS.Range("C2").Formula = "=IF(B2>=40," & Chr(34) & "Pass" & Chr(34) & "," & Chr(34) & "Fail" & Chr(34) & ")"

End Sub

Add vlookup formula to cell using VBA

Finally I will show you how to add a vlookup formula to a cell using VBA. So I created a very simple example where we can use a Vlookup function. Assume we have this section in the Sheet5 of the same workbook.

So here when we change the name of the student in the C2 cell, his/her pass or fail status should automatically be shown in the C3 cell. If the original data(data we used in the above “If formula” example) is listed in the Sheet4 then we can write a Vlookup formula for the C3 cell like this.

=VLOOKUP(Sheet5!C2,Sheet4!A2:C200,3,FALSE)

We can use the Formula property of the Range object to add this Vlookup formula to the C3 using VBA.

Sub AddVlookupFormula()

     Dim WS As Worksheet

     Set WS = Worksheets("Sheet5")

     WS.Range("C3").Formula = "=VLOOKUP(Sheet5!C2,Sheet4!A2:C200,3,FALSE)"

End Sub