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.

Controlling PowerPoint from Excel - VBA

In this post I will show you how to automate PowerPoint from excel using vba. Let’s learn this using following example.

This is a simple database which contains data of several companies. Database contains data like vision, history, branches etc. of each company. So in this example we will create PowerPoint file for each company.

First, let’s learn how to start PowerPoint application from Excel.

Sub StartPowerPoint() Dim oPPTApp As PowerPoint.Application Set oPPTApp = CreateObject("PowerPoint.Application") oPPTApp.Visible = msoTrue End Sub

So if you run above macro it will launch PowerPoint application.

For this example we use PowerPoint template file which is saved at the folder where our database file is. I created this very simple PowerPoint template to show you how to automate various tasks from excel.

First we need to develop the program to loop through each row and create separate PowerPoint file for each company. So following is the starting part of our program.

Sub GeneratePowerPointFiles() Dim WS As Worksheet Dim i As Long Dim PptFileName As String Dim oPPTApp As PowerPoint.Application Dim oPPTFile As PowerPoint.Presentation Dim oPPTShape As PowerPoint.Shape Dim oPPTSlide As PowerPoint.Slide Set oPPTApp = CreateObject("PowerPoint.Application") oPPTApp.Visible = msoTrue Set WS = ActiveSheet 'find last row of WS WS_LastRow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row For i = 2 To WS_LastRow Next i End Sub

Then within each loop, we need to open the template and do the editing. So we can open the PowerPoint template file for each loop like this.

For i = 2 To WS_LastRow      If WS.Range("G" & i).Value <> "" Then           DestinationPPT = ThisWorkbook.Path & "\" & "Company Template.pptx"           Set oPPTFile = oPPTApp.Presentations.Open(FileName:=DestinationPPT)      End If Next i

Now let’s look at our first slide.

Here we need to put name of each company while loop through the rows. So we should replace “Company Name” with values in column A. To do that first we need to identify the names of objects inside the PowerPoint presentation. This post explains how to find names of objects in a PowerPoint presentation.

How to Find Names of Objects in a PowerPoint Slide

So if the name of the title object is “Title 1” then we can do it as follows.

oPPTFile.Slides(1).Shapes("Title 1").TextFrame.TextRange.Text = WS.Range("A" & i).Value

Now let’s move to our next slide.

We have a textbox in this slide. And the name of the textbox is “TextBox 4”. Here we need to put vision of each company.

oPPTFile.Slides(2).Shapes("TextBox 4").TextFrame.TextRange.Text = "Our Vision" & vbCrLf & vbCrLf & WS.Range("B" & i).Value

vbCrLf used to add space between title and the vision. And we have another different type of object in our next slide.

We have rectangle here and we need to put history of each company inside this rectangle. As the name of the rectangle is “Rectangle 3” we can automate it as follows.

oPPTFile.Slides(3).Shapes("Rectangle 3").TextFrame.TextRange.Text = WS.Range("C" & i).Value

Note that you need to put slide number before the object name. Because two different slides can have objects with same name. This is our next slide.

We have two types of objects in this slide. We need to put branch names in “Content Placeholder 2” object. We can do it like this.

oPPTFile.Slides(4).Shapes("Content Placeholder 2").TextFrame.TextRange.Text = WS.Range("D" & i).Value & vbCrLf & WS.Range("E" & i).Value & vbCrLf & WS.Range("F" & i).Value

Next we need to save the PowerPoint presentation with the name given in the column G.

PptFileName = WS.Range("G" & i).Value & ".pptx" PptFileNameString = oPPTFile.Path & "\" & PptFileName oPPTFile.SaveAs PptFileNameString

Also we need to close the original PowerPoint Template at the end of each loop.

'Close PowerPoint oPPTFile.Close Set oPPTFile = Nothing DoEvents

So below is the full code of this VBA program.

Sub GeneratePowerPointFiles() Dim WS As Worksheet Dim i As Long Dim PptFileName As String Dim WebsiteAddress As String Dim oPPTApp As PowerPoint.Application Dim oPPTFile As PowerPoint.Presentation Dim oPPTShape As PowerPoint.Shape Dim oPPTSlide As PowerPoint.Slide Set oPPTApp = CreateObject("PowerPoint.Application") oPPTApp.Visible = msoTrue Set WS = ActiveSheet 'find last row of WS WS_LastRow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row For i = 2 To WS_LastRow     If WS.Range("G" & i).Value <> "" Then         DestinationPPT = ThisWorkbook.Path & "\" & "Company Template.pptx"         Set oPPTFile = oPPTApp.Presentations.Open(FileName:=DestinationPPT)         Application.Wait (Now + TimeValue("0:00:02"))         DoEvents         oPPTFile.Slides(1).Shapes("Title 1").TextFrame.TextRange.Text = WS.Range("A" & i).Value         oPPTFile.Slides(2).Shapes("TextBox 4").TextFrame.TextRange.Text = "Our Vision" & vbCrLf & vbCrLf & WS.Range("B" & i).Value         oPPTFile.Slides(3).Shapes("Rectangle 3").TextFrame.TextRange.Text = WS.Range("C" & i).Value         oPPTFile.Slides(4).Shapes("Content Placeholder 2").TextFrame.TextRange.Text = WS.Range("D" & i).Value & vbCrLf & _ WS.Range("E" & i).Value & vbCrLf & WS.Range("F" & i).Value         PptFileName = WS.Range("G" & i).Value & ".pptx"         PptFileNameString = oPPTFile.Path & "\" & PptFileName         oPPTFile.SaveAs PptFileNameString         'Close PowerPoint         oPPTFile.Close         Set oPPTFile = Nothing         DoEvents     End If Next i oPPTApp.Quit MsgBox "Completed!", vbInformation, "" End Sub

So the result files will be created in the folder where our original PowerPoint template file is.

How to Find Names of Objects in a PowerPoint Slide

Sometimes we need to control PowerPoint from excel. We may need to create or edit a PowerPoint presentation with excel sheet data. Or we may need to create several PowerPoint files using an Excel database and a PowerPoint template. So if you want to develop an Excel application which has this kind of requirement, first thing you need to do is finding the names of each objects within the PowerPoint presentation. So in this post I will explain how to find the name of any object within a PowerPoint presentation.

First, open your presentation.

Click on any object you want to find the name of. Then format tab will appear like this.

Now go to the “Format” tab. And click on the “Selection Pane” in the “Arrange” group.

When you click on the “Selection Pane”, Selection pane will appear right side of the PowerPoint Application.

Then you can find name of each object of current slide from that selection pane. In this example, names of objects are “Subtitle 2” and “Title 1”. And as I have selected the title in the slide, “Title 1” is highlighted in the “Selection pane”. This way you can find the name of any object in your slide.

Here is another example slide of that same PowerPoint presentation.

As you can see, this slide also has an object with name “Title 1”. So you can have objects with same name in different slides. Due to this reason we can’t reference objects only by their names. So we need to use slide number as well. See following example.

Dim oPPTApp As PowerPoint.Application Dim oPPTFile As PowerPoint.Presentation Set oPPTApp = CreateObject("PowerPoint.Application") oPPTApp.Visible = msoTrue DestinationPPT = "Path of the presentation here" Set oPPTFile = oPPTApp.Presentations.Open(FileName:=DestinationPPT) oPPTFile.Slides(1).Shapes("Title 1").TextFrame.TextRange.Text = "Slide 1 heading" oPPTFile.Slides(2).Shapes("Title 1").TextFrame.TextRange.Text = "Slide 2 heading"

Also, using this method you can find names of other types of objects as well. Here is an example slide with an image.

Remove Conditional Formatting From an Excel Sheet (VBA)

Conditional formatting enables you to format cells according to certain criterias. This is very useful when analyzing and presenting the data. But when we develop some vba programs, sometimes we need to remove existing conditional formatting of whole sheet or specific ranges to avoid it interfere with result. This is needed if our program also format the cells according to some criterias. So in this post let’s learn how to remove conditional formatting programmatically. It is easier than you might think.

Let’s consider this sample data.

Two conditional formatting criterias are applied to this worksheet. First one is applied to range C4:C17 and second one is applied to range D4:D17. Cells are highlighted in yellow color in C4:C17 range if the cell value is greater than 40. And cells are highlighted in light red color in D4:D17 range if the cell values are less than 5000.

Now let’s learn how to remove conditional formatting of whole sheet. We can do it using following code.

Sub RemoveConditionalFormatting() Dim WS As Worksheet Set WS = ActiveSheet WS.Cells.FormatConditions.Delete End Sub

This will remove conditional formatting from activesheet. So If we run this macro we will get follwing result.

Sometimes you may need to remove conditional formatting from specific area of your sheet. Not from whole sheet. So if we want to remove conditional formatting from certain range we can do it like this.

Sub RemoveConditionalFormattingFromRange() Dim WS As Worksheet Set WS = ActiveSheet WS.Range("D4:D17").Cells.FormatConditions.Delete End Sub

This will remove conditional formatting from only D4:D17 range. You can replace “D4:D17” of above code with the range you want. You will get following result when you run above code.

Also if we need, we can remove conditional formatting from entire column or row as well. Following code will remove conditional formatting from column C.

Sub RemoveConditionalFormattingFromEntireColumn() Dim WS As Worksheet Set WS = ActiveSheet WS.Columns(3).Cells.FormatConditions.Delete End Sub

Here is the result you will get if you run above code for sample data.

And this next examples shows how to remove conditional formatting from multiple rows.

Sub RemoveConditionalFormattingFromRows() Dim WS As Worksheet Set WS = ActiveSheet WS.Rows("1:100").Cells.FormatConditions.Delete End Sub

This will remove conditional formatting from rows 1 to 100.

Loop Through All the Controls of a VBA UserForm

There are various controls we can use inside a vba userform. When we design user interface, we use relevant controls according to the requirements of the user. We don’t need all the controls for all the applications we create. We put the controls according to how the user want to input, process and output data. Also there are some special controls like “Image” and “Scroll Bar”. This userform contains some widely used controls we use in our applications.

I haven’t put the controls in a meaningful order. Because I only created this to explain you how to access the each of these controls. Otherwise these controls should properly labeled and placed in a meaningful way. Also I didn’t use the conventional naming method to name the controls. These controls have their default names.

In some advance applications, we need to loop through all of these controls and take the actions according to the conditions. So in this post I will show you how to loop through all the controls of a userform. First, let’s try to loop through all the controls and print the name of each of the controls in immediate window.

Private Sub CommandButton1_Click() Dim c As Control For Each c In Me.Controls      Debug.Print c.Name Next End Sub

I have written the code in CommandButton1. So if we click the button we will get following result.

Sometimes we need to detect particular control types of a userform. So if we need to do that first we need to find a way to detect type of each of the controls. We can do it like this. This code will output control name with it’s type.

Private Sub CommandButton1_Click() Dim c As Control For Each c In Me.Controls      Debug.Print "Name - " & c.Name & " Type - " & TypeName(c) Next End Sub

You will get following result when you click the button.

Then if you modify this code little bit, you can read the values of only particular controls. Following example will print the values of textboxes.

Private Sub CommandButton1_Click() Dim c As Control For Each c In Me.Controls      If StrComp(TypeName(c), "Textbox", vbTextCompare) = 0 Then          Debug.Print c.Value      End If Next End Sub

First I entered some text before click the button.

When you click on the CommandButton1 you will get this result.

Remove Characters From Left or Right of a String

String manipulation is very important in any programing language. It is same for VBA as well. In this post let’s learn an another very important string manipulation technique. So today’s lesson is about how to remove characters from left or right of a string.

Let’s assume we have a string type variable call “MyString”.

Dim MyString As String MyString = "abcde123"

This string has 8 characters. So if we want to remove 3 characters from right side how do we do that? We can simply use left function to do the job. Here is how.

Dim MyString As String Dim NewString As String MyString = "abcde123" NewString = Left(MyString, 5) Debug.Print NewString

MyString has 8 characters. If we want to remove 3 from right, that means we need first 5 characters from left. So if you run above code, you will see the value of NewString in the immediate window as follows.

Similarly if we want to remove characters from left then we can use right function for that. Following code shows how you can remove first 2 characters from left.

Dim MyString As String Dim NewString As String MyString = "abcde123" NewString = Right(MyString, 6) Debug.Print NewString

If you run above code you will see this result in immediate window.

So what if, if you want to remove characters from variable length string. Then we can’t use constant value with left or right functions. But we can solve this with “Len” function. We can calculate the length of the string using “Len” function. Then we can subtract amount of characters we want to remove. Following code will remove first 2 characters from right side of a string.

Dim MyString As String Dim NewString As String MyString = "abcde123" NewString = Left(MyString, Len(MyString) - 2) Debug.Print NewString

This is the result you will get.

And following example shows how to remove first 3 characters from left of a variable length string.

Dim MyString As String Dim NewString As String MyString = "abcde123" NewString = Right(MyString, Len(MyString) - 3) Debug.Print NewString

And you will see this result in immediate window.