Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

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.


Find Microsoft Edge in Windows 10 start menu

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?

Web scraping vba code for Internet explorer

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 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.

Click on Windows 10 search box to find Internet explorer

Then search for the Internet explorer.

Search for Internet explorer feature in Windows 10

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.

Existing excel workbook with data

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

Developer tab in Excel ribbon

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.

Visual basic icon in the code group

It will take you to the VBA editor.


VBA editor

Next click on the insert menu.

Insert menu

Select module.

Select module

A new module will be added to the VBA Project.

New module in 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.

New excel macro inside the 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.

Result sheet after running 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.

Contact Form

Name

Email *

Message *