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.

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 *