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.