How to create a line chart automatically - Excel VBA

Charts are another very important elements available in Excel. They play key role in data visualization. Charts have been important in comparing data and analytics. We often use them in reports and dashboards. Because they help to get data-driven insights for the users.Today I am going to show you how to create a chart automatically using VBA. Let’s consider this sample data.

This is a monthly profit of a shop. Now let’s create a chart to compare profit vs months. So months will be in x axis and profit will be in y axis. We can create the chart automatically using below code.

Sub CreateChart_Ex1() Dim WS As Worksheet Set WS = Worksheets("Sheet1") WS.Activate WS.Range("A1:B13").Select WS.Shapes.AddChart2(227, xlLine).Select End Sub

Note that you need to activate the sheet before select the range if you are in different sheet. WS.Activate doesn’t throw error even if you are in that same sheet. You will get this chart if you run above code.

However using this code, you can create a chart only in the worksheet where your data is in. Suppose you have data in one sheet and you need to create chart in another sheet. We have a solution for that too. Assume we have data in Sheet1 and we want to create chart in Sheet3. We can do it like this.

Sub CreateChart_Ex2() Dim WS As Worksheet Dim WS_New As Worksheet Set WS = Worksheets("Sheet1") Set WS_New = Worksheets("Sheet3") WS_New.Activate WS_New.Shapes.AddChart2(227, xlLine).Select ActiveChart.SetSourceData Source:=Range(WS.Name & "!$A$1:$B$13") End Sub

In above examples we created graphs for fixed range. How about if we require to create a chart for dynamic range. Then we have to find the last row of the data, assign it to a variable and then use that variable like this.

WS.Range("A1:B" & LastRow).Select

Also it is a good practice to define the variable at the beginning. You can define the LastRow as integer or long depending on the amount of rows you will have.

This post explains how to find the row number of the last non empty cell of a worksheet.

Best way to get the last row of any column of the excel sheet

So from this post we learnt how to create a chart with one series automatically. If you want to know how to create a chart with multiple series then check out this post.

Create line chart with multiple series using Excel VBA

Convert an Excel Sheet to PDF Using VBA

In my earlier post I explained how to save an Excel sheet as PDF manually. If you want to know how to do it, then check this post.

Save an Excel Sheet as PDF Manually

Today I’m going to teach you how to convert an Excel sheet to PDF automatically. We need this type of functionality, when we develop applications which output reports. Because lot of users need their reports as PDF files. Here is one such report generated by an Excel application.

Now let’s learn how to convert this to a PDF file. First we need to define our variables.

Dim WS As Worksheet Dim FileName As String Dim FilePath As String

I’m going to convert the activesheet to PDF. So I will assign activesheet to WS variable.

Set WS = ActiveSheet

Next we need to give the name to the PDF file

FileName = "Result PDF"

Also we should select the orientation. You should select portrait or landscape according to the length and width of your excel sheet. I will choose portrait for this example.

WS.PageSetup.Orientation = xlPortrait

If you need landscape then you should replace xlPortrait with xlLandscape. Now we have to select the saving location. In this example I will select the same folder where the application is.

FilePath = ThisWorkbook.Path

Or else you can assign specific file path like this.

FilePath = "C:\Users\EVS\Documents\PDF Reports"

We have assign the values to all our variables. And have set the orientation. So now we can convert the sheet as follows.

WS.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FilePath & "\" & FileName & ".pdf", _ Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False

Next we show the confirmation message.

MsgBox "Completed", vbInformation, ""

And here is the complete code to do the job.

Sub SaveAsPDF() Dim WS As Worksheet Dim FileName As String Dim FilePath As String Set WS = ActiveSheet FileName = "Result PDF" WS.PageSetup.Orientation = xlPortrait FilePath = ThisWorkbook.Path WS.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FilePath & "\" & FileName & ".pdf", _ Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False MsgBox "Completed", vbInformation, "" End Sub

When you run above code, result file will be created like this.

Save an Excel Sheet as PDF

Today I’m going to show you how to save an Excel sheet as PDF. Suppose we have an Excel sheet like this.

Now let's learn how to convert this Excel sheet to a PDF file. First click on the “File” menu.

Then click on “Save As”.

Click “Browse”.

It will open Save as dialog box. Choose the folder where you want to save your PDF file.

Select PDF format from the “Select as type” drop-down.

And give suitable name for the PDF file. Then click “Save”.

I have chosen the saving location as the folder, where I have this original excel file. So the PDF file will created like this.

Also if you need, you can convert an Excel sheet to PDF automatically using VBA. Read this post if you want to know how.

Convert an Excel Sheet to PDF Using VBA

Missing Reference - Excel VBA

I recently received an Excel file from internet. And I wanted to do some small modifications to it. It was macro enabled excel file. Then I opened the VBA editor and also checked the references. If you don’t know what references are, they are pointers to Type libraries or DLL files.

There was reference to office web components add-in. But it was not available on my system. So it was listed as a “MISSING” reference. When we copy excel file from one device to another, Type libraries and DLL files are not transferred with it. Only the pointer are transferred. This is the reason for missing references. However in this file, functionality related to that add-in was not needed anymore. So I removed that part from the code. But I didn’t remove the tick from that missing reference.

Next I ran the macro. Then I got a compile error.

When I click OK button it highlighted the code like this.

So it was highlighting a code related to Microsoft scripting runtime. But Microsoft scripting runtime was available in my system and there was no any error in this coding part or reference. Then I realized that this was occurred due to tick in the Missing reference. And I found that the program work without error, when I remove that tick from the missing library. So if you have a missing reference in your vba project then this can cause very strange compile errors like this.

Add Hyperlinks in PowerPoint - Excel VBA

In this post I will explain you how to add a hyperlink to a powerpoint shape. We often need to add hyperlinks in PowerPoint Presentations. It may be a website, email address, or another slide in same presentation. Sometimes we need to add hyperlink to a shape object of a slide. And sometimes we need to add hyperlink to specific text inside a object of a slide. So let’s learn how to do this both. First let’s learn how to add a hyperlink to a whole object of a slide. So let’s consider this sample slide.

Assume that the name of this file is “Sample Presentation.pptx” and both Excel and PowerPoint files are in same folder . First we need to launch the PowerPoint application and open the presentation file.

Dim oPPTApp As PowerPoint.Application Dim oPPTFile As PowerPoint.Presentation Set oPPTApp = CreateObject("PowerPoint.Application") oPPTApp.Visible = msoTrue DestinationPPT = ThisWorkbook.Path & "\" & "Sample Presentation.pptx" Set oPPTFile = oPPTApp.Presentations.Open(FileName:=DestinationPPT)

So if you run above code, program will launch PowerPoint application and will open the presentation file. Then we need to add hyperlink to this object.

Name of this object is “24-Point Star 3”. And let’s add the "http://www.excelvbasolutions.com" as address. So we can add the hyperlink as follows.

With oPPTFile.Slides(5).Shapes("24-Point Star 3").ActionSettings(ppMouseClick)      .Action = ppActionHyperlink      .Hyperlink.Address = "http://www.excelvbasolutions.com" End With

There are times we don’t need to add hyperlink to whole object. Instead, we need to add hyperlink to specific text inside an object. Here is an example. Suppose that we need to add hyperlink only to text “blog” of this slide.

Assume this is the 6th slide of our presentation. We have a rectangle in this slide. The name of the rectangle is “Rectangle 3”. Now we are going to add the hyperlink only to text “blog” inside the rectangle. We can do it like this.

With oPPTFile.Slides(6).Shapes("Rectangle 3").TextFrame.TextRange.Find("Blog").ActionSettings(ppMouseClick)      .Action = ppActionHyperlink      .Hyperlink.Address = "http://www.excelvbasolutions.com" End With

So we will get following result if we run above code.

Next let’s learn how to add an email address as hyperlink.

We are going to add hyperlink to word “email”. Assume that the slide number is 7. Name of the object is “Rectangle 4”. So we can do it using following code.

Dim EmailAddress As String EmailAddress = "Your email address here" With oPPTFile.Slides(7).Shapes("Rectangle 4").TextFrame.TextRange.Find("email").ActionSettings(ppMouseClick)      .Action = ppActionHyperlink      .Hyperlink.Address = "mailto:" & EmailAddress End With

Then the result will look like this.