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.

When to use the getElementsByClassName method

In the previous post we learnt why it is easier to use the getElementById than getElementsByClassName method. Read this post if you want to learn how to use the getElementById method in web scraping.

getElementsByClassName Vs getElementById

But you can’t use getElementById all the time. Because web developers don’t assign id's for all the elements. And sometimes we need to cope with these elements which don't have id’s. Here is an example.

There are two submit buttons in the above sample HTML code. This is the code of the first button.

And following is the code for the second button.

Assume that we want to click the second submit button. So how do we find that element? Can we use the easiest method explained in the last post? As you can see we can’t use that method as there is no id related to this second submit button. However this second button is assigned to a class called “a-button-input”. Because of that, we can use getElementsByClassName method to click the button.

Now the first thing we need to find is the index number of this element with the specified class. To find that we need to count the number of elements in the webpage before this submit button with the specified class. Let’s assume that there are five elements which belongs to same class before this submit button. As the index starts at 0, the index of this second submit button should be 5. Then we can click the second button as follows.

Set objIE = CreateObject("InternetExplorer.Application")

objIE.Top = 0
objIE.Left = 0
objIE.Width = 800
objIE.Height = 600

objIE.Visible = True

objIE.Navigate ("Url here")

Do
DoEvents
Loop Until objIE.readystate = 4

objIE.document.getElementsByClassName("a-button-input")(5).Click

Note that you need to change the number in the last line to suit with the index number. If the index number is x, then you can write it as follows.

objIE.document.getElementsByClassName("a-button-input")(x).Click

getElementsByClassName Vs getElementById

Programmers use various languages to develop programs to collect data from websites. Even the programmers who are using the same language use different techniques to extract the data. If you are new to web scraping, a typical question you will have is when to use which method. getElementsByClassName and getElementById are such two methods which often confuse the novices. Because lots of beginners have a doubt about why they should use getElementsByClassName or getElementById over the other in different situations.

So in this post I thought to teach you how to use these two methods appropriately. First thing we need to understand is that the websites are not developed aiming at helping web scraping. Web developers use various techniques and methods to have various functionalities, make the website pleasing to the eye, increase the speed and easy to make changes. So if we develop a web scraping program then we have to consider the inherent features of that particular website when doing the coding.

Now let’s consider the following HTML code. As you can see there are two input tags of type "submit" in this code. Assume we want to click the first submit button.

If you carefully examine the code, you can see that both buttons belong to the same class called "a-button-input". However both buttons have unique IDs as well. As the first button has a unique id, the easiest method to click that button is using the getElementById method. This is how you can do it.

Set objIE = CreateObject("InternetExplorer.Application")

objIE.Top = 0
objIE.Left = 0
objIE.Width = 800
objIE.Height = 600

objIE.Visible = True

objIE.Navigate ("Url here")

Do
DoEvents
Loop Until objIE.readystate = 4

objIE.document.getElementById ("button-search").Click

If you want to click the same button using the getElementsByClassName method, then you can do it as follows.

Set objIE = CreateObject("InternetExplorer.Application")

objIE.Top = 0
objIE.Left = 0
objIE.Width = 800
objIE.Height = 600

objIE.Visible = True

objIE.Navigate ("Url here")

Do
DoEvents
Loop Until objIE.readystate = 4

objIE.document.getElementsByClassName("a-button-input")(0).Click

Several elements can have the same class name. So in this method we have to use the index number of the element. Index starts at 0. You may not see a big difference in the above examples as there are only two input tags in the HTML code. But in real life, it is not simple like this. Some web pages have a large number of elements with the same class name. So then it is difficult to find the index number of the element we need. But if there is an id for that element, then we can use the getElementById method without thinking about the index number of the element.

Copy Excel Line Chart (Graph) Using VBA

From this lesson you can learn how to copy a chart using VBA. Let’s consider this sample excel line chart. Chart is on an activesheet.

Assume we want to create a copy of that chart in the same sheet. We can do that as follows.

Sub CopyChart()

     Dim WS As Worksheet

     Set WS = ActiveSheet

     WS.ChartObjects("Chart 4").Chart.ChartArea.Copy
     WS.Range("N5").Select
     WS.Paste

End Sub

In the above macro we used “WS.Range("N5").Select” to select the N5 cell before paste the chart. Therefore the chart's top left corner was positioned on that cell.

However If the user has selected the chart or if the ChartObject(which contains the chart) has activated, then we can create a copy of the chart like this.

Sub CopyChart_Ex2()

     ActiveChart.ChartArea.Copy
     Range("I10").Select
     ActiveSheet.Paste

End Sub

Now let’s look at how we can copy charts to a different sheet. Assume we have a workbook with two sheets called “Sheet1” and “Sheet2” in “Sheet1” containing a chart named “Chart 4”. Below macro will copy “Chart 4” from “Sheet1” to “Sheet2”.


Sub CopyChartToDifferentSheet()

     Dim WS_Current As Worksheet
     Dim WS_New As Worksheet

     Set WS_Current = Worksheets("Sheet1")
     Set WS_New = Worksheets("Sheet2")

     WS_Current.ChartObjects("Chart 4").Chart.ChartArea.Copy
     WS_New.Paste

End Sub

Formatting Excel Line Charts Using VBA

We often use Excel charts in our worksheets. They are really important no matter for which field your spreadsheet is related to. Because charts can graphically represent data which helps to gain accurate understanding of the data. From our earlier lessons we learnt how to add charts with one series or multiple series automatically using vba. Check out these posts if you want to learn how.

Create a line chart (with one series)
Create line chart with multiple series

So today we are going to look at how we can format a line chart automatically using VBA. Here is some sample data I have in my excel sheet.

So if we insert a line chart in Excel 2013, then the default chart will look like this.

However there are lots of styles you can choose from for your chart. This is how you can change the style of your chart using VBA.

Sub ChartStyle33()

     Dim Cht As Chart
     Set Cht = ActiveSheet.ChartObjects("Chart 1").Chart
     Cht.ChartStyle = 33

End Sub

If we run the above code, the chart will change to something like this.

Note that, in the above code we refer to the chart using it’s name. So you will need to change the “Chart 1” with the name of your chart. Don’t know how to find the name of a chart? This post explain how to find the name of a chart manually.

Find the name of a chart in Excel

Read this post if you want to find the name of the active chart using VBA.

How to find the name of an active chart using VBA

In the above macro, we changed the style of the chart using it’s name. However instead of the name we can refer to the chart using ActiveChart property, if we want to change the style of the active chart. Then we can rewrite the above code as follows.

Sub ChartStyle43()

     Dim Cht As Chart
     Set Cht = ActiveChart
     Cht.ChartStyle = 43

End Sub

Note that I used a different number for the Cht.ChartStyle in this example. So the result will look like this.


Now you may have thought what are the numbers you can use for Cht.ChartStyle. You can use numbers from 1 to 48 for the Cht.ChartStyle. In addition to those styles, the following are also available in Excel 2013.

    342
    332
    239
    237
    236
    235
    234
    233
    232
    231
    230
    228
    227

How to find the name of an active chart using VBA

In an earlier lesson we looked at how to create a chart automatically. Read this post if you want to know how.

How to create a line chart automatically - Excel VBA

Today I’m going to teach you how to find the name of an active chart using VBA. When you create a chart either manually or automatically using VBA then it becomes active chart until you deselect it. Also if you select an existing chart in an excel sheet, then it also becomes active chart. Assume we have an active chart like this in our excel sheet.

Name of the sheet is “Sheet4”. Now let’s try to find the name of the chart using VBA. So you might think we can easily find the name of this active chart as follows.

Sub ActiveaChartName()

Debug.Print ActiveChart.Name

End Sub

But this doesn’t work. This is what we will get if we run above code.

We have an extra name addition to the chart name in the beginning. That extra name is the sheet name. So we need to do some additional work to extract the chart name. Make sure you have selected at least one chart when you run above code. Otherwise you will get below run-time error.

So now let’s see how we can extract only the chart name from the ActiveChart.Name. If we analyze the default chart names given for the charts when we create them, we can see that it has the following format.

Chart + ChartNumber

For example, Excel names the charts like this. Chart 1, Chart 2, Chart 3 etc. So we can use the split function to extract the chart name from the ActiveChart.Name easily.

Sub ActiveaChartName()

Dim SheetAndChartName As String
Dim ChartName As String
Dim WrdArray() As String

SheetAndChartName = ActiveChart.Name

WrdArray() = Split(SheetAndChartName)

ChartName = WrdArray(UBound(WrdArray) - 1) & " " & WrdArray(UBound(WrdArray))

Debug.Print ChartName

End Sub

If you want to learn more about split function, read this post.

Split Function

Above macro will print the chart name correctly in the immediate window as follows.

Note that this will only work if the user hasn’t altered the default chart name.

Find the name of a chart in Excel

Did you know that whenever you create a chart in Excel a unique name is given to it at the time of creation. This name is not important to you if you are only doing manual work with the data and charts. But knowing the name might be very important if you do any automations related to charts using VBA. Because if we know the name we can directly reference that chart in our codes. So in this post I will show you how to find the name of a chart in Excel.

This excel sheet contains a table and a chart. Now let’s see how to find the name of the chart. To find that, first we need to select the chart of which we need to find the name. Here we have only one chart. If you have more than one, you need to select only the one you want to find the name of.

Above image shows how Excel changed when we selected the chart. It marks the data in the table which is related to the selected chart. And also as you can see two new tabs appeared at the end of the tabs. These two tabs are “DESIGN” and “FORMAT” tabs. To find the name of the chart you need to select the Format tab. Then click on the “Selection Pane” in the “Arrange” group of the Format tab.

When you click on the “Selection Pane”, that pane will appear on the right side of the excel sheet. Also the name of the chart will be highlighted like this.


In this example we have only one chart. But if we have multiple charts then only the selected chart will be highlighted. For an example consider the below image of a Selection Pane. According to this pane this excel sheet has 4 charts and only the “Chart 8” is selected.

Create line chart with multiple series using Excel VBA

From one of our earlier posts we learnt how to create a line chart with one series automatically using VBA. Read this post if you want to know how.

How to create a line chart automatically - Excel VBA

In this post I will show you how we can create line charts with multiple series using VBA. Let’s consider this sample table. For this data we need to create a line chart with 2 series.

As you can see we have our data in range A1:C13. So when this sheet is the active sheet, we can create 2 series line chart for this data as follows.

Sub CreateChart_2Series()

     Dim WS As Worksheet

     Set WS = ActiveSheet

     WS.Range("A1:C13").Select
     ActiveSheet.Shapes.AddChart2(227, xlLine).Select

End Sub

Note that you need to replace the A1:C13 with the range you want to create the line chart for. Here is the result of the above macro.

This is another sample table for which we can create a line chart with 3 series.

To create a line chart for all the data, the only change we need to do for our first macro is changing the range from A1:C13 to A1:D13.

Sub CreateChart_3Series()

     Dim WS As Worksheet

     Set WS = ActiveSheet

     WS.Range("A1:D13").Select
     ActiveSheet.Shapes.AddChart2(227, xlLine).Select

End Sub

Line chart with 3 series.

Convert Multiple Excel Sheets to a Single PDF File Manually

From an earlier post we learnt how to convert one excel sheet to a PDF manually. If you want to know how, then please check this post.
Save an Excel Sheet as PDF

This Excel tutorial explains how to convert multiple sheets from your excel workbook to a one single PDF file manually. So you can convert sheets you wish from your excel file to a one PDF file. Assume we have an Excel file with 5 sheets like this.

If we want to convert only Sheet 1 and Sheet 3 to one PDF file, then these are the steps we need to follow.

First we need to select Sheet 1 and Sheet 3. You can select multiple sheets from excel file by clicking on sheet names while pressing Ctrl key on your keyboard.

Now we have selected Sheet 1 and Sheet 3. Next we need to click on the “File” menu.

Then click on the “Save as”

Next click on the “Browse”

It will open the “Save as” dialog box like this.

Now browse for the folder you want to save the file. Next enter a suitable name to the “File name” field. After that click on the “Save as type” dropdown. Select PDF from the list.

Once you select the PDF from the list, Save as dialog box will show you some more options. So click on the “Options” button at the bottom.

When you click on that button, “Options” window will open like this. Then make sure that the “Active sheet(s)” option is selected under the “Publish what” section.

Now click ok. It will close the “Options” window. Then click the “Save” button in the “Save as” dialog box. Selected sheets will be converted to a one pdf file successfully. So this is how we can convert multiple excel sheets to a one PDF file manually. Also you can convert an entire workbook to a single PDF file as well. Check this post if you want to know how.
Convert an Entire Workbook to a Single PDF File

BESSELJ Function

Today I’m going to show you how to use an another engineering function in Excel. This new function is called Besselj. It returns the Bessel function Jn(x).

Syntax of the function is BESSELJ(x,n). So we need to input two parameters (x and n) for this function. Both are required to return a value. When you input those two arguments, function will be evaluated at x. And n is the order of the Bessel function. You can use this function in Excel like this.

Here I have used cell addresses to input the arguments. But you can also use values directly like this as well.

So now let’s find the Jn(x) values for x values from -5 to 5 when the order of the function is 1.

If we draw a graph of Jn(x) for the above values it will look like this.

If we change the order of the Bessel function to 2, then we will get the following result.

This is the graph for the above table.

BESSELI Function

This Excel tutorial explains how to use an Engineering function in Excel. Excel BESSELI function returns the modified Bessel functions In(x).

Syntax of the BESSELI function is BESSELI(x,n). So this function has two arguments. Both of them are mandatory. x is the value at which to evaluate the function. n represents the order of the function. And n should be positive. Function will return an error if n is less than 0.

And this is how you can use the function in the Excel.

In the above example I have passed the arguments by cell addresses. However you can pass the arguments like this as well

=BESSELI(-5,1)

This table shows the In(x) for x values from -5 to 5. Order of the function is 1.

And here is the graph for above table.

Then I changed the order of the function to 2 and calculated the In(x).

This is the graph for order 2.

DateSerial Function (VBA)

In this post I will explain how to use DateSerial function in VBA. DateSerial function is a quite useful function we can use in VBA. If we input year, month and day this function returns the relevant date.

So we need to input three parameters to the function. All of them are mandatory.

Year - Integer type value which represents the year (ex - 2020)
Month - Integer type value which represents the month (ex - 10)
Day - Integer type value which represents the day (ex - 20)

Below example shows you how you can use this function in VBA

Sub DateSerialExample() Dim SalesDataDate As Date

Dim iYear As Integer
Dim iMonth As Integer
Dim iDay As Integer

iYear = 1965
iMonth = 10
iDay = 25

SalesDataDate = DateSerial(iYear, iMonth, iDay)

Debug.Print SalesDataDate

End Sub

You will get this result if you run above macro

And if you want to change the format of the date then you can do it like this.

Sub DateSerialExample()

Dim SalesDataDate As Date

Dim iYear As Integer
Dim iMonth As Integer
Dim iDay As Integer

iYear = 1965
iMonth = 10
iDay = 25

SalesDataDate = DateSerial(iYear, iMonth, iDay)

Debug.Print Format(SalesDataDate, "dd-mmm-yyyy")

End Sub

So you will get following result.

How to Add Additional Controls in Excel VBA

In this post, I will show you how to add additional controls such as Windows Media Player, Adobe PDF Reader and Microsoft ListView Control in Excel VBA.

So here are the steps you need to follow. First create a blank workbook.

Then click somewhere in the excel sheet and press Alt+F11 to open the VBA editor. Then click on the Insert menu and select Userform.

Once you select the Userform, a new userform will be created like this.

Also the Toolbox will appear to the side of the form as well.

Some of the controls are already in this toolbox such as Textbox, Label and ListBox etc. Now let’s look at how to add additional controls to it. To do that we need to click on the Tools menu and click on Additional Controls...

It will open the Additional Controls Window like this.

Now you can add any additional control you like. For this example let’s add Windows Media Player. Select the checkbox in front of the Windows Media Player and then click OK.

This will add Windows Media Player icon to our Toolbox like this.

Now we can add Windows Media Player to our userform.

Popular Posts

Contact Form

Name

Email *

Message *