Microsoft Edge

Microsoft introduced a new browser called “Microsoft Edge” with their Windows 10 release. Now Microsoft edge is the default browser for the Windows 10.

After that lots of people got a question whether they can do web scraping using this new browser, like they did with the Internet explorer. For an example below is a very simple code we can use to automate internet explorer. It will open the Internet explorer window and navigate to the http://www.example.com. So can we do a similar program for Microsoft Edge?

Unfortunately as of today, 30th December 2020 Microsoft Edge still doesn’t support this kind of automations. So we can not create direct VBA programs to automate Microsoft edge. However, thanks to a developer(github user - florentbr) you can use a selenium based solution to automate Microsoft Edge. He has created a wrapper for Selenium which we can use for VBA projects. You can download that repository from github from the link below.

SeleniumBasic

Also note that internet explorer is also a built in component in Windows 10. So if you want to use the Internet explorer then you don’t need to install anything. You can readily use it.

Also see
    Learn when to use the getElementsByClassName method in web scraping
    How to scroll a web page number of pixels

Also it is possible to develop web scraping programs inside Excel in Windows 10 for Internet explorer like in other Windows versions. However some users are having problems finding this feature. If you also have difficulty finding the Internet explorer, simply follow these steps.

Click on the search box.

Then search for the Internet explorer.

VBA With Statement (Excel VBA)

With statement is a widely used statement in VBA. With statement is used to execute one or more statements on an object or on a user defined type. Also it is mandatory to use “End With'' after the statements. So this is the Syntax of the With statement.

With object      Statements End With

Now let’s look at how to use With statements in VBA projects. Let’s consider this sample form.

Suppose that we want to create the drop down list of the “Gender” combobox. We can do that using a With statement as follows. Name of the combobox is cboGender.

Private Sub UserForm_Initialize()      With cboGender          .AddItem "Male"          .AddItem "Female"      End With End Sub

According to above code, items are added to the dropdown list when the form is initialized. So this is what will happen when you click on the dropdown icon of the “Gender” combobox.

So in the above example code, we referred to the combo box once as we used the With statement. So if we rewrite that code without a With statement we have to refer to it twice like this.

Private Sub UserForm_Initialize()      cboGender.AddItem "Male"      cboGender.AddItem "Female" End Sub

So if we want to add lots of items to a combobox and if we don’t use a With statement then we have to refer to the combobox many times. But if we use With statement we can refer to the combobox once and add any amount of items to the list.

We can also use the With statement for objects related to the worksheets as well. First let’s look at how to delete a sheet using a With statement. Suppose we have a workbook with 5 sheets like this.

Following is the macro which uses the With statement to delete a worksheet. In this example macro is used to delete “Sheet4”.

Sub DeleteSheet() Dim WS As Worksheet     Set WS = Worksheets("Sheet4")     Application.DisplayAlerts = False     With WS          .Delete     End With     Application.DisplayAlerts = True End Sub

This would be the result after running the macro.

Next let’s see how we can use the With statement for Range objects. We will use Range(“C5”) for this example.

Below subroutine can change the value and can do some small changes to the formatting of the range.

Sub RangeExample()      With Range("C5")          .WrapText = True          .ColumnWidth = 25          .Value = "VBA With Statement - www.excelvbasolutions.com"      End With End Sub

Here is the result of the above macro.

Also you can use nested With statements in your subroutines as well. Here is an example.

Sub NestedWithStatementExample()      With Range("B3")          .Value = "Test"          With .Font              .Name = "Calibri"              .Size = 16              .Bold = True              .Underline = xlUnderlineStyleSingle              .Color = vbRed          End With      End With End Sub

This is the result of the above example subroutine.

Note that you have to use dot(.) before the object in the inner With statement. So in the above example we used

With .Font

Instead of

With Font

How to Use For Each Next in Excel VBA

For Each Next Statement is used to execute one or more statements on each item in a collection or array. So this is the syntax of the For Each Next statement.

For Each element in Collection/Array      Statements Next

However if you want you can add the "element" in the "Next" line as well. So then the syntax would be like this.

For Each element in Collection/Array      Statements Next element

Now let’s look at how to use this statement in programs. Consider this sample excel sheet.

We have values here and there in this worksheet. So in this example we are going to color the cells which have values greater than five in yellow color. So the end result should look like this.

To do that we need to execute some statements on all the cells in the used range. So we can do that using For Each Next statement as follows.

Sub ForEachExample()      Dim Rng As Range      For Each Rng In ActiveSheet.UsedRange          If Rng.Value > 5 Then              Rng.Interior.Color = vbYellow          End If      Next End Sub

In the above code we execute the following statement on all the cells in the used range.

If Rng.Value > 5 Then      Rng.Interior.Color = vbYellow End If

Also if we need, we can use “Next Rng” instead of “Next” as well. So then the For Each statement would look like this.

For Each Rng In ActiveSheet.UsedRange      --------------------      Statements      -------------------- Next Rng

Next let’s look at how to use For Each statement for an array. I will explain it using the following Excel sheet.

This excel sheet has numbers and text in column A. We are going to write whether the value is numeric or non-numeric in column B. First we add the content of range A1:A10 to an array called “MyArray”. Then execute the set of statements on each element of the array. So the completed subroutine will look like this.

Sub ForEachExample2()      Dim WS As Worksheet      Dim MyArray() As Variant      Dim elm As Variant      Dim Counter As Integer      Set WS = ActiveSheet      MyArray = WS.Range("A1:A10")      Counter = 1      For Each elm In MyArray          If IsNumeric(elm) = True Then              WS.Range("B" & Counter).Value = "Numeric"          Else              WS.Range("B" & Counter).Value = "Non-Numeric"          End If          Counter = Counter + 1      Next End Sub

Here following statements are executed on each element of the array.

If IsNumeric(elm) = True Then      WS.Range("B" & Counter).Value = "Numeric" Else      WS.Range("B" & Counter).Value = "Non-Numeric" End If Counter = Counter + 1

Subroutine has used IsNumeric function to check whether the value is numeric or not. So this would be the result of the above macro.

Note that if we use For Each Next statement for collection, then the element variable should be either variant or an object. However if we use that statement for an array then the element variable should be only a variant.

You can also use “Exit For” inside the For Each Next statement to exit the loop when a certain condition is met after the evaluation. Consider this sample excel sheet. This sheet contains 8 colors in row A. What we are going to do is find where the word “Yellow” is in, and then output the row number of that cell.

We can do that using the code below. Here we have used a custom range instead of UsedRange.

Sub ForEachExample3()      Dim DataRange As Range      Dim Rng As Range      Set DataRange = ActiveSheet.Range("A1:A8")      For Each Rng In DataRange          If Rng.Value = "Yellow" Then              MsgBox "Yellow is in row " & Rng.Row              Exit For          End If      Next Rng End Sub

This is the result of the subroutine “ForEachExample3”.

You can use “Exit For” anywhere inside the For Each Next statement depending on your requirement. If a program executes to the "Exit For" then it will give control to the statement immediately following the “Next”. In the above example to the "End Sub" as there are no any other statements.

Add an Excel Macro to a Workbook

This post is for absolute beginners who are new to Excel VBA macros. There are lots of useful vba macros readily available on the internet. Or sometimes you may receive such macros from your friends via emails. If you are an absolute beginner for VBA macros you might don’t know how to add those macros to your existing workbook. So in this lesson I will show you how to add such macro to your existing workbook.

Assume you received the following code from a friend via email.

Sub ListAllCombinations() Dim counter As Long counter = 1 For i = 1 To 4      For j = 1 To 4           For k = 1 To 4                Range("E" & counter).Value = _                Range("A" & i).Value & "" & Range("B" & j).Value & "" & Range("C" & k).Value                counter = counter + 1           Next k      Next j Next i End Sub

What this code does is it lists all the possible combinations from columns A, B and C into column E. Above macro is only for the first 4 rows. Let’s assume this is your current workbook.

Now let’s look at how to add that above code to the file. First you need to go to the “Developer” tab.

If the “Developer” tab is unavailable in your Excel application then read this post to see how to enable it.

How to show the Developer tab

Next click on the “Visual Basic” in the “Code” group.

It will take you to the VBA editor.

Next click on the insert menu.

Select module.

A new module will be added to the VBA Project.

You can delete “Option Explicit” for now. Otherwise you will get errors if any of the variables are not declared. Then you can paste the macro inside this new module.

Now the new macro is added to the existing workbook. If your existing file is a .xlsx file then you will need to save it as a .xlsm or any other macro supporting file format. Here are the different combinations you will get when you run the macro.

Record a Macro in Excel

Did you know that you can automate repetitive tasks using Excel macros? In this lesson I will teach you how to record a macro in Excel. Record macro is a great tool available in the Microsoft Excel application. It lets the user record all the manual tasks performed. Then that whole process can be executed again and again by one click. So you can understand how much this single tool can save. However this macro recorder tool has some limitations. But if you learn basic concepts of the VBA, then you will have endless opportunities to increase productivity.

Also if you are a programmer, you can use this “Record macro” tool to easily figure out ways to automate some specific tasks. For an example assume that you don’t know how to develop a code to delete a row. Then you can use this tool to find the way to do that. However note that the codes generated by the macro recorder are not always efficient. But it will be a really helpful tool if you are a beginner.

Record macro tool is not only available in Excel but also in some other VBA supporting applications like Microsoft Word and Powerpoint as well.

So now let’s look at how to record a macro in Excel. To do that, first we need to go to the Developer tab. Developer tab is not visible in default settings in Excel. So if you haven’t used the Developer tab before then you will need to show it first. This post explains how to show the developer tab in Excel.

How to show the Developer tab in Excel 2013

Once the Developer tab is enabled you can view all the tools available in that tab.

Now I am going to use the record macro tool for the following process.

• Create new sheet
• create a table inside that sheet. (this will be a two column table)
• Write the headers (subject and marks).
• Add 11 subjects in the first column.
• Add the sum function at the bottom of the second column.
• Format the table.

So this is complete process I am going to record using the record macro.

To start recording the macro we need to click on the “Record Macro” in the “ Code” group.

Once you click on that, a Record macro window will open like this.

Now you can give a name to your macro. Keep in mind that you can not use spaces and other invalid characters for the name. Here are all the rules applied to the macro name.

If you want you can add a shortcut key as well. However this is optional and you can proceed without selecting any key. Also there is a drop-down in the window called “Store macro in”. From that dropdown select “This Workbook” if you want to add the macro only to the current workbook. Then you can add a description from the “Description” text box. This is also optional and you can skip it if you don’t want to add description. Then click the OK button to start the recording.

So this is the sample table I created after clicking the record macro.

Once the process is completed you can click on the “Stop Recording” in the “Code” group to stop the recording.

Now the macro recording is completed. You can view the generated VBA code inside the Visual basic editor.

Code is generated inside Module1.

Don’t know how to open the Visual Basic Editor? Then read this post to learn how.

Getting started with Visual Basic Editor in Excel

Following is the complete code generated when creating the above table in a new worksheet.

Option Explicit Sub MyMacro() ' ' MyMacro Macro ' ' Sheets.Add After:=ActiveSheet Range("A2").Select ActiveCell.FormulaR1C1 = "Accounting" Range("A3").Select ActiveCell.FormulaR1C1 = "Art" Range("A4").Select ActiveCell.FormulaR1C1 = "Biology" Range("A5").Select ActiveCell.FormulaR1C1 = "Business Studies" Range("A6").Select ActiveCell.FormulaR1C1 = "Chemistry" Range("A7").Select ActiveCell.FormulaR1C1 = "Computer Science" Range("A8").Select ActiveCell.FormulaR1C1 = "English Language" Range("A9").Select ActiveCell.FormulaR1C1 = "Geography" Range("A10").Select ActiveCell.FormulaR1C1 = "History" Range("A11").Select ActiveCell.FormulaR1C1 = "Mathmatics" Range("A12").Select ActiveCell.FormulaR1C1 = "Physics" Range("A13").Select Columns("A:A").ColumnWidth = 20.86 Range("A1").Select ActiveCell.FormulaR1C1 = "Subject" Range("B1").Select ActiveCell.FormulaR1C1 = "Marks" Range("A13").Select ActiveCell.FormulaR1C1 = "Total" Range("B13").Select ActiveCell.FormulaR1C1 = "=SUM(R[-11]C:R[-1]C)" Range("A1:B13").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft)      .LineStyle = xlContinuous      .ColorIndex = 0      .TintAndShade = 0      .Weight = xlThin End With With Selection.Borders(xlEdgeTop)      .LineStyle = xlContinuous      .ColorIndex = 0      .TintAndShade = 0      .Weight = xlThin End With With Selection.Borders(xlEdgeBottom)      .LineStyle = xlContinuous      .ColorIndex = 0      .TintAndShade = 0      .Weight = xlThin End With With Selection.Borders(xlEdgeRight)      .LineStyle = xlContinuous      .ColorIndex = 0      .TintAndShade = 0      .Weight = xlThin End With With Selection.Borders(xlInsideVertical)      .LineStyle = xlContinuous      .ColorIndex = 0      .TintAndShade = 0      .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal)      .LineStyle = xlContinuous      .ColorIndex = 0      .TintAndShade = 0      .Weight = xlThin End With Range("A1:B1").Select With Selection.Interior      .Pattern = xlSolid      .PatternColorIndex = xlAutomatic      .ThemeColor = xlThemeColorDark1      .TintAndShade = 0      .PatternTintAndShade = 0 End With With Selection.Interior      .Pattern = xlSolid      .PatternColorIndex = xlAutomatic      .Color = 12611584      .TintAndShade = 0      .PatternTintAndShade = 0 End With With Selection.Font      .ThemeColor = xlThemeColorDark1      .TintAndShade = 0 End With Range("A3:B3,A5:B5,A7:B7,A9:B9,A11:B11").Select Range("A11").Activate With Selection.Interior      .Pattern = xlSolid      .PatternColorIndex = xlAutomatic      .ThemeColor = xlThemeColorAccent1      .TintAndShade = 0.799981688894314      .PatternTintAndShade = 0 End With Range("A13:B13").Select With Selection.Interior      .Pattern = xlSolid      .PatternColorIndex = xlAutomatic      .ThemeColor = xlThemeColorAccent5      .TintAndShade = 0.399975585192419      .PatternTintAndShade = 0 End With Range("E10").Select Columns("B:B").ColumnWidth = 10.71 Range("A1:B1").Select Selection.Font.Bold = True Columns("B:B").Select With Selection      .HorizontalAlignment = xlCenter      .VerticalAlignment = xlBottom      .WrapText = False      .Orientation = 0      .AddIndent = False      .IndentLevel = 0      .ShrinkToFit = False      .ReadingOrder = xlContext      .MergeCells = False End With Range("A13:B13").Select With Selection.Font      .ColorIndex = xlAutomatic      .TintAndShade = 0 End With Range("B7").Select End Sub

Using Val Function to Avoid CDbl Type Mismatch Error

In one of our earlier posts we learnt why we sometimes get “Type mismatch” error (Run-time error '13') when we use CDbl function to convert textbox values to double. So there were lots of reasons. One reason was due to no value in the textbox. In other words, if you try to convert the value of a textbox to a double using the CDbl function and if the textbox is empty then you will get a “Type mismatch” error.

Read this post if you want to learn more about CDbl Type mismatch error.

Type Mismatch Error (Run Time Error 13) When Converting to Double

In this post I will show you how you can use the Val function instead of CDbl to avoid this problem. So you will no longer get a “Type mismatch” error when the textbox is empty. So let’s consider the following example.

Sub TotalPriceCalculation()      Dim Price As Double      Dim Tax As Double      Price = CDbl(txtPrice.Value)      Tax = CDbl(txtTax.Value)      txtTotalPrice.Value = Price + Tax End Sub

txtPrice, txtTax and txtTotalPrice are text boxes. txtTotalPrice textbox displays the total value of txtPrice and txtTax values. Note that we are entering the tax amount to the simple calculator. Not the tax percentage. However we will get a “Type mismatch” error if txtPrice or txtTax is empty. To avoid that issue we can modify the above subroutine using Val function as follows.

Sub TotalPriceCalculation()      Dim Price As Double      Dim Tax As Double      Price = Val(txtPrice.Value)      Tax = Val(txtTax.Value)      txtTotalPrice.Value = Price + Tax End Sub

Now if you call the subroutine when the textbox is empty, it won’t give any error. It will show the result in the txtTotalPrice textbox.

However remember that the program will still give a result even if the user enters incorrect values like this.

So this solution may not be suitable for some situations.

IsNumeric Function (VBA Function)

Today’s post is about another very useful vba function called “IsNumeric” function. This function evaluates whether the argument is a number or not. So if all the characters are numeric then IsNumeric function will return True. If any character of the argument is not numeric then the function will return false. However the function will still return true if you use dot(.) as a decimal point or comma (,) as a thousand separator.

IsNumeric is a built-in VBA function. However it is not available in worksheets. You can only use it in the VBA programs. (In VBA editor)

The syntax of the function is as follows. There is one parameter for this function.

IsNumeric(chars)

Parameter is required.

IsNumeric function returns
True - If the argument can be evaluated as number
False - If the argument can not be evaluated as number.

Keep in mind that IsNumeric("4,56") will return true. But IsNumeric("4/56") or IsNumeric("4^56") will return false.

Now let’s look at some examples.

Example 1

Sub IsNumericFunctionExample1() Dim S As String S = "156" Debug.Print IsNumeric(S) End Sub

Example 2

Sub IsNumericFunctionExamples2() Dim S As Variant S = "45.2" Debug.Print IsNumeric(S) End Sub

Example 3

Sub IsNumericFunctionExample3() Dim S As String S = "www" Debug.Print IsNumeric(S) End Sub

Example 4

Sub IsNumericFunctionExample4() Dim S As String S = "ww33w" Debug.Print IsNumeric(S) End Sub

Example 5

Sub IsNumericFunctionExample5() Dim S As String S = "4,565" Debug.Print IsNumeric(S) End Sub

Example 6

Sub IsNumericFunctionExample6() Dim S As String S = "4/56" Debug.Print IsNumeric(S) End Sub

Using the IsNumeric Function to Avoid CDbl Type Mismatch Error

In the last post I explained about one reason why we get “Type mismatch (Run Time Error 13)” when we use the CDbl function. If we try to convert a value of an empty textbox to double then we get Type mismatch error. To solve that we came up with this below solution.

Sub TotalAmountCalculation()      Dim Amount1 As Double      Dim Amount2 As Double      Amount1 = 0      Amount2 = 0      If frmInput.txtAmount1.Value <> "" Then          Amount1 = CDbl(frmInput.txtAmount1.Value)      End If      If frmInput.txtAmount2.Value <> "" Then          Amount2 = CDbl(frmInput.txtAmount2.Value)      End If      frmInput.txtTotalAmountPaid.Value = Amount1 + Amount2 End Sub

frmInput is the name of the form. txtAmount1 and txtAmount2 are the names of “Amount 1” and “Amount 2” textboxes. And the txtTotalAmountPaid is the name of the “Total Amount Paid” textbox. Also the “Total Amount Paid” textbox is updated whenever a change takes place in “Amount 1” or “Amount 2” textboxes. Read this post if you like to see full details about the above solution.

CDbl - Type Mismatch Error When Textbox is Empty (How to Solve)

So now we won’t get Type mismatch error when a textbox is empty. But still there is a problem. Assume that the user entered a value like this.

As you can see the user has entered a letter instead of a value. This can happen by mistake. Sometimes users might enter a $ symbol intentionally. But then CDbl can not cast that value to double. If you want to disregard the incorrect values entered by the user, then you can use a subroutine like this. Here we have used a VBA function called “IsNumeric” to check whether the user has entered a numeric value. Value will be converted to a double only if it is numeric.

Sub TotalAmountCalculation()      Dim Amount1 As Double      Dim Amount2 As Double      Amount1 = 0      Amount2 = 0      If IsNumeric(frmInput.txtAmount1.Value) = True Then          Amount1 = CDbl(frmInput.txtAmount1.Value)      End If      If IsNumeric(frmInput.txtAmount2.Value) = True Then          Amount2 = CDbl(frmInput.txtAmount2.Value)      End If      frmInput.txtTotalAmountPaid.Value = Amount1 + Amount2 End Sub

Note that you can use IsNumeric(frmInput.txtAmount1.Value) instead of IsNumeric(frmInput.txtAmount1.Value) = True

So then you can rewrite the above code as follows.

Sub TotalAmountCalculation()      Dim Amount1 As Double      Dim Amount2 As Double      Amount1 = 0      Amount2 = 0      If IsNumeric(frmInput.txtAmount1.Value) Then          Amount1 = CDbl(frmInput.txtAmount1.Value)      End If      If IsNumeric(frmInput.txtAmount2.Value) Then          Amount2 = CDbl(frmInput.txtAmount2.Value)      End If      frmInput.txtTotalAmountPaid.Value = Amount1 + Amount2 End Sub

Below is a sample result we will get.

Now in this case the user has entered an incorrect value to “Amount 2”. But the program has calculated value ignoring that. But as you can see this is not a good approach. Because if a user enters a non numeric value by mistake then still the program will give a result ignoring it. So the best way to handle this is by giving the warning to the user saying that he/she has entered invalid value. And then the program should not output any result. To do that we can make a change to above subroutine as follows.

Sub TotalAmountCalculation()      Dim Amount1 As Double      Dim Amount2 As Double      Amount1 = 0      Amount2 = 0      If frmInput.txtAmount1.Value <> "" Then          If IsNumeric(frmInput.txtAmount1.Value) = True Then              Amount1 = CDbl(frmInput.txtAmount1.Value)          Else              MsgBox "You have entered incorrect value for Amount 1", , "Warning"              frmInput.txtTotalAmountPaid.Value = ""              Exit Sub          End If      End If      If frmInput.txtAmount2.Value <> "" Then          If IsNumeric(frmInput.txtAmount2.Value) = True Then              Amount2 = CDbl(frmInput.txtAmount2.Value)          Else              MsgBox "You have entered incorrect value for Amount 2", , "Warning"              frmInput.txtTotalAmountPaid.Value = ""              Exit Sub          End If      End If      frmInput.txtTotalAmountPaid.Value = Amount1 + Amount2 End Sub

Note that the program needs to check whether the value is numeric or not, only if the textbox is not empty. So in addition to the IsNumeric function we need to use frmInput.txtAmount1.Value <> "" as well. Also the following line has been used to clear the result if there is any invalid value in Amount 1 or Amount 2 textboxes.

frmInput.txtTotalAmountPaid.Value = ""

So now the program displays a warning message if there is any invalid input in the textboxes.

Results will be cleared when click ok.

If you like to learn more about the IsNumeric function, the read this post

IsNumeric Function (VBA Function)

CDbl - Type Mismatch Error When Textbox is Empty (How to Solve)

In the earlier post we learnt why we sometimes get “Type mismatch” error when we convert textbox values to double using the CDbl function. This error can occur due to several reasons.

Read this post if you want to know what the reasons are.

Type Mismatch Error (Run Time Error 13) When Converting to Double

Converting an empty string is one reason for this “Type mismatch” error. In this post I will explain how to avoid the error when a textbox is empty. So let’s consider the following sample subroutine.

Sub TotalAmountCalculationEx1()      Dim Value1 As Double      Dim Value2 As Double      Value1 = CDbl(frmInput.txtAmount1.Value)      Value2 = CDbl(frmInput.txtAmount2.Value)      frmInput.txtTotalAmountPaid.Value = Value1 + Value2 End Sub

frmInput is a form. txtAmount1 and txtAmount2 are textboxes. txtTotalAmountPaid is also a textbox. When this subroutine is executed, sum of values in txtAmount1 and txtAmount2 will be displayed in the txtTotalAmountPaid textbox. But this program won’t work if any of those two textboxes is empty. Then it will give Type mismatch error (Run-time error '13'). To avoid this error which occurs when a textbox is empty we can use the following steps. First we can assign value 0 to variables Value1 and Value2. So these will be the default values of the two variables. Then we can use a If statements to check whether textboxes are empty or not. Value will be converted to a double only if the textbox is not empty.

Sub TotalAmountCalculation()      Dim Value1 As Double      Dim Value2 As Double      Value1 = 0      Value2 = 0      If frmInput.txtAmount1.Value <> "" Then          Value1 = CDbl(frmInput.txtAmount1.Value)      End If      If frmInput.txtAmount2.Value <> "" Then          Value2 = CDbl(frmInput.txtAmount2.Value)      End If      frmInput.txtTotalAmountPaid.Value = Value1 + Value2 End Sub

In VBA we use <> to check the Inequality. So the following line checks whether the value of txtAmount1 is unequal to "" or not.

If frmInput.txtAmount1.Value <> "" Then

In VBA "" represents an empty string.

If frmInput.txtAmount1.Value <> "" is true then it will execute the below line

Value1 = CDbl(frmInput.txtAmount1.Value)

This is the same for txtAmount2 as well.

Type Mismatch Error (Run Time Error 13) When Converting to Double

Have you get Type Mismatch Error (Run time error 13) when trying to convert a textbox value to a double. (using CDbl function). If so I will explain the reason for that and how to overcome the error.

I will explain this using a sample project. Let’s consider this simple data entry application. Below is an image of the user interface of the program.

Using this application users can select upto two payment methods and then can enter the amounts separately. Whenever “Amount 1“ or “Amount 2“ is updated then “Total Amount Paid” should be updated automatically and instantly. So to do this we can create a subroutine in a separate module and then can use the change events of the “Amount 1” and “Amount 2” textboxes to trigger that subroutine.

Following are the codes of change events of two textboxes.

Private Sub txtAmount1_Change()      Call TotalAmountCalculation End Sub Private Sub txtAmount2_Change()      Call TotalAmountCalculation End Sub

And we can put the below code in a separate module. This is a simple subroutine which calculates the total amount.

Sub TotalAmountCalculation()      Dim Amount1 As Double      Dim Amount2 As Double      Amount1 = CDbl(frmInput.txtAmount1.Value)      Amount2 = CDbl(frmInput.txtAmount2.Value)      frmInput.txtTotalAmountPaid.Value = Amount1 + Amount2 End Sub

As you can see, the CDbl function is used to convert the values to double. But if we try to enter a value to one of the textboxes, we will get Type Mismatch Error. (Run Time Error 13).

Why does it happen? So the reason is this. We tried to enter a value to “Amount 1” textbox. But at that time “Amount 2” textbox is empty. So when the TotalAmountCalculation subroutine is triggered, the program tries to convert “Amount 2” textbox value also to a double.

Amount2 = CDbl(frmInput.txtAmount2.Value)

But as the “Amount 2” textbox is empty, we get an error. Because CDbl can’t convert an empty string to a double. CDbl can convert only numeric values. For an example following will give Type mismatch error.

Sub ConvertToDouble()      Dim ConvertedValue As Double      ConvertedValue = CDbl("122RE")      Debug.Print ConvertedValue End Sub

But below will print 122 in the immediate window.

Sub ConvertToDouble()      Dim ConvertedValue As Double      ConvertedValue = CDbl("122")      Debug.Print ConvertedValue End Sub

Because 122 is a numeric value, but 122RE is not.

So how do we solve the problem with the textboxes. Here are two possible solutions.

1. Convert to double only if the textbox is not empty.
2. Use Val function instead of CDbl