Learn by Doing: Intro to VBA via Adding Charts Example

Thanks to Nick Williams at Acuity Training for this guest post!

All content that follows is credited to Nick Williams, and was directly provided to Excel Exposure:

This article gives people who are new to Excel VBA a very good overview of the practicalities of how VBA code is written and used in Excel, as well as an introduction to some of the key concepts in VBA programming.

In Section 1 we show readers, step-by-step, how to open up the VBA editor, how to create modules into which we then cut and paste our pre-prepared code and finally we show them how to run and use the code. In step 2 we then step through the VBA code that we have just used at a relatively high level to explain what each section does and how it works. The aim of this is not to explain the detail of the code but to give beginners a high level introduction to the program writing process and also the types of things that VBA can be used for.

Section 1 – SETTING UP THE VBA EDITOR AND RUNNING OUR CODE

There are several different ways to add a chart to a spreadsheet using Excel VBA. In this example we use the Chart Sheet method which is the most efficient and offers the most control over position, size, and worksheet placement. This type of chart offers us the ability to adjust a wide variety of variables use our VBA code. The practical example we will use is a relatively short program that allows you to create a chart from a small number of inputs and then export it once you have created it.

First create a new Excel workbook. Before doing anything, in the newly created workbook, click the File tab in the far upper/left corner, click Save As, and click Browse. In the pop-up screen navigate to a location in your computer where you want to save this application and name your file. Before you click save, it is very important that under the File Name section you click on the dropdown menu, and select Excel Macro-Enabled Workbook, as per the illustration. For security reasons, new Excel files are created with this feature off by default, and if you want to run any VBA code you must have a Macro Enabled file.

Save As Macro

Now we need to create the template spreadsheet.

Note that although this article refers to Excel 2013 the steps are the same for all versions. The only difference is that when Excel 2013 creates a new workbook by default your workbook will contain 1 Sheet. In older versions your new workbook will be created with 3 worksheets by default.

For our purposes, it doesn’t matter. The code we’ve created, checks the number of worksheets and creates or deletes worksheets as needed. Which means that whichever version you’re using you can simply create a new workbook as explained above.

Activate the Developer Tab in your Excel Ribbon

Adding Charts with VBA image 1

If you don’t have your Developer tab activated, you need to do so from the Options menu. The Developer tab contains all of the VBA and Macro functionality within Excel. We will need access to this and so we must now set this up.

Just click the File tab, go to Options, and in the Excel Options window click the Customize Ribbon on the left side. Make sure that the checkbox in front of your Developer option is checked and then press OK:

Adding Charts with VBA Cusomize Ribbon

Now we’re ready for some action. Click on the newly discovered Developer tab, and click on the Visual Basic menu to the far left of the Developer tab’s menu options. You should now see the VBA Project Window.

Depending on the Add-ins activated in your Excel installation, in the left upper corner of the VBA Project window, you might see and array of tools available (atpvbaen, EuroTool and Solver in the picture below), or simply only your current workbook’s project.

As we haven’t named our workbook yet, your current workbook project will be “VBAProject(Book1)”. If the items below your project are not visible like in the above image, just click the project’s plus sign to expand it. Now we need to create Modules, so we can place our codes within.

Adding Charts with VBA 3

To create a Module, right click on the “Microsoft Excel Objects” folder, located underneath your VBAProject(Book1). In the popup menu choose Insert, Module. Create a total of 2 modules by repeating the action.

Not that we have created our modules we’re ready to insert our code. Just double click on Module2 to make sure that this is your current window and in the much larger right-hand window paste the code which can be found at the end of this article which is called CreateTemplate. Once you have done this your window should look like this:

Adding Charts with VBA 4

This code makes sure that our worksheet is appropriately set up and formatted before we then move to run our graphing code. For example, as mentioned above it will remove or add worksheets if necessary, and format the worksheets.

Next we need to insert the code which will actually create our chart. To do this Double click on Module1, and paste the code which you can find in Appendix 2 below. This is called CreateChart. As you’ll no doubt notice this piece of code is actually far shorter than the formatting code that we were working with earlier.

Fantastic. If you’ve managed to get the codes in both modules, click the Save icon located in the upper left corner and close the VBA project window.

Now it’s time to run the code. To do this go to the previously discovered Developer tab, and in the left upper corner and click on the Macros option.

In the pop-up window, you should the name of three macros: AddChart, CreateTemplate, ExportChartAsImage. Select the CreateTemplate macro, and hit the Run button.

Adding Charts with VBA 5

That’s it! From here on you not need to touch any other VBA code. Simply enter the appropriate values to set the size, type and other parameters of the chart you would like to create. Once you’ve done that you open the ChartComplete sheet and fill in your data that you want to create a chart from.

Once you have your values input, press the ConstructChart button and your chart will be automatically generated. If you would like to your Chart simply press the Export Chart button from the Settings sheet to export your chart as a Bitmap image. By default your chart image will be exported in the same location as your Excel file and so it would make sense to store it in its own folder so that you can easily find your exported charts.

SECTION 2 – STEPPING THROUGH THE CODE

In this section of the article we actually step through the code that creates the graphs explaining at a high level what it does and why. The code that you can see has a fair number of comments to help explain what it does as well on a line by line basis. The comments are shown by a line starting with an apostrophe, so wherever you see ‘ you know it is a comment. Comments in code are sections of text that the computer does not run as it is marked as a comment. Comments are inserted into well written code as they make looking at and amending code written in the past much simpler by explaining what different sections of the code actually does.

The VBA Code that creates and exports the chart is broken down into 7 sections as explained below:

1.    VARIABLES DECLARATIONS

In this section we “declare” all the variables that our code will use. This means that we tell Excel what we want to call the variables that our code will use and also, very importantly, what type of information our variable will be – so will it be numbers, text, dates etc etc.

So for example “Dim leftDistance As Integer” tells the computer that later on we will be using a variable called leftDistance and it will only ever have integer values. Where we define something as “String” that means text, as in a string of text. Where we define something as “Object” that is the graph chart. Objects are a more technical concept but refers to any of the constituent entities that make up Excel so Worksheets, charts, cell ranges and the like.

<<<<<<<<<<<<First declare all your variable used within the project>>>>>>>>>>>>SECTION 1
Dim leftDistance As Integer ‘distance from left
Dim topDistance As Integer ‘distance from top
Dim chartTitle As String ‘chart title
Dim chartWidth As Integer ‘chart width
Dim chartHeight As Integer ‘chart height
Dim chartNumBars As Integer ‘number of data bars
Dim cellWidth As Integer ‘standard excel cell width
Dim cellHeight As Integer ‘standard excel height
Dim chrt As Object ‘define the chart
Dim chartType As Integer ‘chart type
Dim chartBackgroundColor As Integer ‘chart background color
Dim chartPlotAreaColor As Integer ‘chart plot area color
‘<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<END SECTION 1>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

2.    CHECK THE USER INPUT AND MAKE SURE ALL FIELDS CONTAIN INTEGERS (EXCEPT THE CHART’S NAME)

Once we have defined our variables we must check all of the values input by the user to be sure that they are correctly entered, so in this case numeric.

This is a very important part of any program. Although to you as the builder of the program it might be obvious to enter a certain type of data into a certain field, it may not be to some of your users. You should never leave data types unchecked. If the user enters the wrong data type into an input box if may well mean that your program will fail to execute. So you must check the data before you process it.

The code below simply checks that all of the variables set to be an Integer data type contain numbers. If they don’t then it activates a message box asking the user to insert numeric values into the box.

‘<<<<<<<<<<<<First declare all your variable used within the project>>>>>>>>>>>>SECTION 2
‘check to see if the data entered by the user is numeric, except the chart title
For i = 4 To 11
If IsNumeric(Range(“E” & i)) Then
Else:
MsgBox “Incorrect Data Type”, vbOKOnly, “Enter enter some numeric values”
Range(“E” & i).Activate
Exit Sub
End If
Next i
‘<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<END SECTION 2>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

3.    SET THE DECLARED VARIABLES VALUES

Now that we have checked that the user has input the variables correctly we are ready to use these values. To do this we assign the various input values to the variables that we set up in step one.

‘<<<<<<<<<<<<Now we set the declared variables values>>>>>>>>>>>>SECTION 3
‘set size and position properties
cellWidth = 48
cellHeight = 15
leftDistance = Range(“E5”) * cellWidth
topDistance = Range(“E6”) * cellHeight
chartWidth = Range(“E7”) * cellWidth
chartHeight = Range(“E8”) * cellHeight
‘set chart colors, data bars, and name
chartTitle = Range(“E3”).Value
chartType = Range(“E9”).Value
chartBackgroundColor = Range(“E10”).Value
chartPlotAreaColor = Range(“E11”).Value
chartNumBars = Range(“E4”) + 1
‘<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<END SECTION 3>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

4.    HERE WE DO SOME SANITY CHECK TO SEE IF THE USER DIDN’T ENTERED SOMETHING ELSE THAN WHAT WE EXPECT HIM TO DO SO

Having checked in step 2 very quickly that the chart variables where the correct data types now we need to check that the values that have been input to be charted are in the correct range. Where they aren’t we need to then prompt the user to re-enter data in the correct range. To encourage this you will see in the code below that after each message box (MsgBox in the code) we then activate a cell in the code for example when we want to activate E4 we have (range(“E4”).Activate) this is the cell that the user needs to make a change in.

‘<<<<<<<<<<<<Check to see if the user input data falls into your required standards>>>>>>>>>>>>>>SECTION 4

‘check to see if the user entered at least 2 data bars
If chartNumBars < 3 Then
MsgBox “Your chart must have at least 2 data bars”, vbOKOnly, “Not enough data bars”
Range(“E4”).Activate
Exit Sub
End If

‘make sure the chart title is not empty
If chartTitle = empty Then
MsgBox “Please enter a name for your chart”, vbOKOnly, “Enter chart title”
Range(“E3”).Activate
Exit Sub
End If

‘check the chart type code
Select Case chartType
Case Is = 1
Case Is = -4098
Case Is = -4100
Case Is = -4120
Case Is = -4101
Case Is = 4
Case Is = -4102
Case Is = 5
Case Is = -4151
Case Is = -4169

Case Else
MsgBox “Chart type code not existent. Please see the Chart Code Legend”, vbOKOnly, “Enter another Chart Type Code”
Range(“E9”).Activate
Exit Sub
End Select

‘check the chart background color code
If chartBackgroundColor > 0 And chartBackgroundColor < 72 Then
Else:
MsgBox “Allowable chart background color codes are 1 to 71”, vbOKOnly, “Enter another color code”
Range(“E10”).Activate
Exit Sub
End If

‘check the chart plot area background color code
If chartPlotAreaColor > 0 And chartPlotAreaColor < 72 Then
Else:
MsgBox “Allowable chart plot area color codes are 1 to 71”, vbOKOnly, “Enter another color code”
Range(“E11”).Activate
Exit Sub
End If

‘<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<END SECTION 4>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

5.    WE WILL NOW SET UP THE SHEET WHERE THE CHART GETS CREATED

Now that we are sure that all the data that we have is within the correct ranges and of the correct type and we have assigned it to variable so that we can use it within our code we are ready to get on and build our chart.

Firstly we need to set up the worksheet where our chart will be created. This code ensures that the sheet where the chart is created is nicely formatted and so easy to read.

‘<<<<<<<<<<<Prepare the ChartComplete sheet, where the final chart will be created>>>>>>>>>>>>SECTION 5
‘delete any existent chart before creating a new one
For Each chrt In Sheets(“ChartComplete”).ChartObjects
chrt.Delete
Next

‘clear the previous chart data
Worksheets(“ChartComplete”).Activate
Columns(“A:B”).Select
Selection.Clear

‘if the chart type is Area, 3D Area, 3D Column, 3D Line, Line, Radar, Scatter
‘label and highlight an extra cell where the user can set the legend label name
If chartType = 1 Or chartType = -4098 Or chartType = -4101 Or chartType = 4 Or chartType = -4151 Or chartType = -4169 Or chartType = -4100 Then
‘data label name
Range(“B1”).Offset = “LABEL NAME”
Range(“B1”).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
Columns(“B:B”).EntireColumn.AutoFit

End If

‘loop that formats the chart data area
For i = 2 To chartNumBars

‘set the labels default text
Cells(i, 1).Offset = “DATA” & Chr(32) & i – 1

‘format the label cells color
Cells(i, 1).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With

‘format the data value cells color
Cells(i, 2).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With

Next i
‘<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<END SECTION 5>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

6.    NOW IS TIME TO WRITE TO CODE THAT OUTPUTS THE CHART AND ITS PROPERTIES

Finally once the new sheet is set up and well formatted we can create our chart.
First we add the chart with the ChartObjects.Add method and assign settings like distance from left (leftDistance), distance from top (topDistance), width (chartWidth), and height (chartHeight). The four property values will be replaced by the variables set in Section 2, whose values are set by the user.

We then set the chart source data by using the SetSourceData property of the chart object. To allow the user to set the number of data bars we use a variable (chartNumBars) and concatenate the number passed through the variable within the Range declaration.

The user has control over several other settings like the type of chart to output, the chart’s background color, the plot area background color, and the chart’s title.

‘<<<<<<<<<<<<<<<<<<<<<<<Here goes the code that creates the chart>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>SECTION 6
‘construct the new chart
Set chrt = Sheets(“ChartComplete”).ChartObjects.Add(Left:=leftDistance, _
Width:=chartWidth, Top:=topDistance, Height:=chartHeight) ‘set chart’s size and position

With chrt
.Chart.SetSourceData Source:=Sheets(“ChartComplete”).Range(“A1” & “:” & “B” & chartNumBars) ‘set the chart’s data source

.Chart.Type = chartType ‘set the chart type
.Chart.ChartArea.Interior.ColorIndex = chartBackgroundColor ‘set chart background color
.Chart.PlotArea.Interior.ColorIndex = chartPlotAreaColor ‘set chart plot area color
.Chart.HasTitle = True
.Chart.chartTitle.Text = chartTitle ‘set chart title
.Chart.Legend.Position = xlTop ‘set chart legend position
.Chart.SeriesCollection(1).Border.LineStyle = xlDash ‘set the bars line type
End With
‘<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<END SECTION 6>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

7.    CREATE THE PIECE OF CODE THAT WILL ALLOW THE USER TO EXPORT THE CHART AS A BITMAP IMAGE

First we declare a variable (imgPath) as string and pass it the workbooks path along with the current time stamp to avoid name collisions in case if the user exports multiple images.

We then use the Chart.Export method to export the newly created chart as a bitmap image. We pass the imgPath variable as the path to export the bitmap image to.

‘<<<<<<<<<<<<<<<<<<Here goes the code that allows the user to save the >>>>>>>>>>>>>>>>>>>>>SECTION 7
End Sub

Sub ExportChartAsImage()

Dim imgPath As String

‘ Saving chart as image
imgPath = ThisWorkbook.Path & “\Chart_” & Format(Now(), “DD_MM_YY_HH_MM_SS”) & “.bmp”
‘include the current time in the file name so the names won’t collide
Sheets(“ChartComplete”).ChartObjects(1).Chart.Export imgPath

End Sub

‘<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<END SECTION 7>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

A full reference of all properties can be found on Microsoft’s MSDN developer center or by clicking here. (Assign a reference to http://msdn.microsoft.com/enus/library/office/ff194426%28v=office.15%29.aspx)

APPENDIX 1 – CreateTemplate Code
Sub CreateTemplate()
‘————————–START PROCEDURE—–MAKE SURE THE WORKBOOK HAS 2 WORKSHEETS———————-
For i = 1 To Sheets.Count + 1 ‘loop all sheets
‘we add 1 to Sheets.Count, to be sure the loop executes if the workbook starts with one
Application.DisplayAlerts = False ‘disable the excel sheet delete alert
If i > 2 Then ‘if there are more than 2 sheets
Worksheets(1).Delete ‘delete the first sheet
End If
If i < 2 Then ‘if there are less than 2 sheets
Sheets.Add ‘add sheets
End If
Next i
‘————————–END PROCEDURE—————-

‘——————-START PROCEDURE—–SELECT EACH SHEET, RENAME IT, AND HIDE THE GRIDS————————
Sheets(2).Select ‘select the second sheet
Sheets(2).Name = “ChartComplete” ‘rename the second sheet
ActiveWindow.DisplayGridlines = False ‘hide the sheet gridlines
Sheets(1).Select ‘select the first sheet
Sheets(1).Name = “Settings” ‘rename the first sheet
ActiveWindow.DisplayGridlines = False ‘hide the sheet gridlines
‘——————END PROCEDURE————————

‘——————-START PROCEDURE—–CREATE AND FORMAT THE USER INPUT AREA————————
Range(“A3:D3”).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge ‘merge cells
Range(“A3:D3”).Select ‘select the merged cells
Selection.Copy ‘copy the selected merged cells
Range(“A4:A11”).Select
ActiveSheet.Paste ‘paste the formatting over
Application.CutCopyMode = False ‘clear the clipboard memory
Columns(“D:D”).ColumnWidth = 20 ‘increase column D width
Columns(“I:I”).ColumnWidth = 2.5 ‘increase column I width
Columns(“E:E”).ColumnWidth = 30 ‘increase column E width
Columns(“G:G”).ColumnWidth = 50 ‘increase column G width
Columns(“J:J”).ColumnWidth = 50 ‘increase column J width
Columns(“H:H”).ColumnWidth = 7 ‘increase column H width
Columns(“K:K”).ColumnWidth = 7 ‘increase column K width
Rows(“2:14”).Select
Selection.RowHeight = 23 ‘increase rows 2 to 14 height
Rows(“1:1”).Select
Selection.RowHeight = 30 ‘increase row 1 height
Range(“A3:D11,G2,J2,G4,G5,G6,G7,G8,G9,G10,G11,G12,G13,H4,H5,H6,H7,H8,H9,H10,H11,H12,H13,J4,J5,J6,J7,J8,J9,J10,J11,J12,J13,J14,K4,K5,K6,K7,K8,K9,K10,H11,K12,K13,K14”).Select
With Selection ‘apply borders
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Color = -1003520
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Color = -1003520
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Color = -1003520
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Color = -1003520
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).Color = -1003520
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.Font.ThemeColor = xlThemeColorAccent5
.Font.TintAndShade = -0.499984740745262
.Font.Size = 15
.Font.Bold = True
End With

Range(“E3:E11”).Select ‘style the user input cells
With Selection
.Interior.Pattern = xlSolid
.Interior.PatternColorIndex = xlAutomatic
.Interior.Color = 12611584
.Interior.TintAndShade = 0
.Interior.PatternTintAndShade = 0
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.Font.ThemeColor = xlThemeColorDark1
.Font.TintAndShade = 0
.Font.Size = 15
.Font.Bold = True
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).ColorIndex = xlAutomatic
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).ColorIndex = xlAutomatic
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).ColorIndex = xlAutomatic
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).ColorIndex = xlAutomatic
End With
Range(“A3,E3:E11”).Select ‘style headers
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
‘—————————-END PROCEDURE————————————

‘——————-START PROCEDURE—–CREATE AND FORMAT THE LEGEND AREA————————
Range(“G1:K1”).Select ‘——-LEGEND HEADER
Selection.Merge ‘merge cells
Selection.Style = “Accent4”
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Name = “Britannic Bold”
.Font.Strikethrough = False
.Font.Superscript = False
.Font.Subscript = False
.Font.OutlineFont = False
.Font.Shadow = False
.Font.Underline = xlUnderlineStyleNone
.Font.ThemeColor = xlThemeColorDark1
.Font.TintAndShade = 0
.Font.ThemeFont = xlThemeFontNone
.Font.Size = 30
End With

Range(“H2,K2”).Select ‘——-VALUE LABEL
Selection.Style = “Accent5”
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Size = 14
End With

Range(“G4:G13,J4:J14”).Select ‘——-Chart Tpes & Code Values + Chart Colors & Code Values FORMAT
Selection.Style = “Accent2”
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Font.Size = 12
.Font.Bold = True
End With

Range(“H4:H13,K4:K14”).Select ‘——-Chart Tpes & Code Values + Chart Colors & Code Values FORMAT
Selection.Style = “Accent3”
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Size = 12
End With

Range(“G3:H3,J3:K3”).Select ‘——-Chart Tpes & Code Values + Chart Colors & Code Values FORMAT
Selection.Style = “Accent6”

For i = 4 To 14 ‘Chart Colors & Code Values offset adiacent cells background colors

Select Case i
Case Is = 4 ‘black
Cells(4, 12).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Case Is = 6 ‘red
Cells(6, 12).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Case Is = 7 ‘green
Cells(7, 12).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Case Is = 8 ‘blue
Cells(8, 12).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Case Is = 9 ‘yellow
Cells(9, 12).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Case Is = 10 ‘pink
Cells(10, 12).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 16751103
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Case Is = 11 ‘light blue
Cells(11, 12).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
Case Is = 12 ‘dark blue
Cells(12, 12).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Case Is = 13 ‘purple
Cells(13, 12).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10498160
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Case Is = 14 ‘gray
Cells(14, 12).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
End Select

Next i
‘————————END PROCEDURE———————————

‘——————-START PROCEDURE—–ADD THE BUTTONS, FORMAT THEM, AND ASSIGN THEIR MACROS————————
ActiveSheet.Buttons.Add(7.5, 10.5, 123, 30).Select ‘add the Create Chart Button
Selection.OnAction = “AddChart” ‘add the respective macro
Selection.Characters.Text = “Create Chart” ‘rename the Create Chart Button
With Selection ‘format the Create Chart Button
.Font.Name = “Calibri”
.Font.FontStyle = “Bold”
.Font.Size = 16
.Font.Strikethrough = False
.Font.Superscript = False
.Font.Subscript = False
.Font.OutlineFont = False
.Font.Shadow = False
.Font.Underline = xlUnderlineStyleNone
.Font.ColorIndex = 5
.Placement = xlFreeFloating
.PrintObject = False
End With

ActiveSheet.Buttons.Add(144, 10.5, 123, 30).Select ‘add the Export Chart Button
Selection.OnAction = “ExportChartAsImage” ‘add the respective macro
Selection.Characters.Text = “Export Chart” ‘rename the Export Chart Button
With Selection ‘format the Create Chart Button
.Font.Name = “Calibri”
.Font.FontStyle = “Bold”
.Font.Size = 16
.Font.Strikethrough = False
.Font.Superscript = False
.Font.Subscript = False
.Font.OutlineFont = False
.Font.Shadow = False
.Font.Underline = xlUnderlineStyleNone
.Font.ColorIndex = 1
.Placement = xlFreeFloating
.PrintObject = False
End With
‘————————END PROCEDURE———————————

‘——————-START PROCEDURE—–OFFSET ALL LABELS————————
Range(“A3”).Offset = “Chart Title”
Range(“A4”).Offset = “Chart data bars count”
Range(“A5”).Offset = “Chart distance from left in cells”
Range(“A6”).Offset = “Chart distance from top in cells”
Range(“A7”).Offset = “Chart width in cells”
Range(“A8”).Offset = “Chart height in cells”
Range(“A9”).Offset = “Chart Type”
Range(“A10”).Offset = “Chart Background Color”
Range(“A11”).Offset = “Chart Plot Area Color”
Range(“G1”).Offset = “LEGEND”
Range(“G2”).Offset = “Chart Types & Code Values”
Range(“G4”).Offset = “AREA”
Range(“G5”).Offset = “3D AREA”
Range(“G6”).Offset = “3D COLUMN”
Range(“G7”).Offset = “DOUGHNUT”
Range(“G8”).Offset = “3D LINE”
Range(“G9”).Offset = “LINE”
Range(“G10”).Offset = “3D PIE”
Range(“G11”).Offset = “PIE”
Range(“G12”).Offset = “RADAR”
Range(“G13”).Offset = “SCATTER”
Range(“H4”).Offset = “1”
Range(“H5”).Offset = “-4098”
Range(“H6”).Offset = “-4100”
Range(“H7”).Offset = “-4120”
Range(“H8”).Offset = “-4101”
Range(“H9”).Offset = “4”
Range(“H10”).Offset = “-4102”
Range(“H11”).Offset = “5”
Range(“H12”).Offset = “-4151”
Range(“H13”).Offset = “-4169”
Range(“H2”).Offset = “Value”
Range(“K2”).Offset = “Value”
Range(“J2”).Offset = “Chart Colors & Code Values”

Range(“J4”).Offset = “BLACK”
Range(“J5”).Offset = “WHITE”
Range(“J6”).Offset = “RED”
Range(“J7”).Offset = “GREEN”
Range(“J8”).Offset = “BLUE”
Range(“J9”).Offset = “YELLOW”
Range(“J10”).Offset = “PINK”
Range(“J11”).Offset = “LIGHT BLUE”
Range(“J12”).Offset = “DARK BLUE”
Range(“J13”).Offset = “PURPLE”
Range(“J14”).Offset = “GRAY”
Range(“K4”).Offset = “1”
Range(“K5”).Offset = “2”
Range(“K6”).Offset = “3”
Range(“K7”).Offset = “4”
Range(“K8”).Offset = “5”
Range(“K9”).Offset = “6”
Range(“K10”).Offset = “7”
Range(“K11”).Offset = “8”
Range(“K12”).Offset = “11”
Range(“K13”).Offset = “13”
Range(“K14”).Offset = “16”
Range(“H2”).Offset = “Value”
Range(“K2”).Offset = “Value”
Range(“J2”).Offset = “Chart Colors & Code Values”
‘————————END PROCEDURE———————————
End Sub

APPENDIX 2 – CreateChart Code

Sub AddChart()

‘<<<<<<<<<<<<First declare all your variable used within the project>>>>>>>>>>>>SECTION 1
Dim leftDistance As Integer ‘distance from left
Dim topDistance As Integer ‘distance from top
Dim chartTitle As String ‘chart title
Dim chartWidth As Integer ‘chart width
Dim chartHeight As Integer ‘chart height
Dim chartNumBars As Integer ‘number of data bars
Dim cellWidth As Integer ‘standard excel cell width
Dim cellHeight As Integer ‘standard excel height
Dim chrt As Object ‘define the chart
Dim chartType As Integer ‘chart type
Dim chartBackgroundColor As Integer ‘chart background color
Dim chartPlotAreaColor As Integer ‘chart plot area color
‘<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<END SECTION 1>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

‘<<<<<<<<<<<<First declare all your variable used within the project>>>>>>>>>>>>SECTION 2
‘check to see if the data entered by the user is numeric, except the chart title
For i = 4 To 11
If IsNumeric(Range(“E” & i)) Then
Else:
MsgBox “Incorrect Data Type”, vbOKOnly, “Enter enter some numeric values”
Range(“E” & i).Activate
Exit Sub
End If
Next i
‘<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<END SECTION 2>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

‘<<<<<<<<<<<<Now we set the declared variables values>>>>>>>>>>>>SECTION 3
‘set size and position properties
cellWidth = 48
cellHeight = 15
leftDistance = Range(“E5”) * cellWidth
topDistance = Range(“E6”) * cellHeight
chartWidth = Range(“E7”) * cellWidth
chartHeight = Range(“E8”) * cellHeight
‘set chart colors, data bars, and name
chartTitle = Range(“E3”).Value
chartType = Range(“E9”).Value
chartBackgroundColor = Range(“E10”).Value
chartPlotAreaColor = Range(“E11”).Value
chartNumBars = Range(“E4”) + 1
‘<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<END SECTION 3>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

‘<<<<<<<<<<<<Check to see if the user input data falls into your required standards>>>>>>>>>>>>>>SECTION 4
‘check to see if the user enteret at least 2 data bars
If chartNumBars < 3 Then
MsgBox “Your chart must have at least 2 data bars”, vbOKOnly, “Not enough data bars”
Range(“E4”).Activate
Exit Sub
End If

‘make sure the chart title is not empty
If chartTitle = empty Then
MsgBox “Please enter a name for your chart”, vbOKOnly, “Enter chart title”
Range(“E3”).Activate
Exit Sub
End If

‘check the chart type code
Select Case chartType
Case Is = 1
Case Is = -4098
Case Is = -4100
Case Is = -4120
Case Is = -4101
Case Is = 4
Case Is = -4102
Case Is = 5
Case Is = -4151
Case Is = -4169

Case Else
MsgBox “Chart type code not existent. Please see the Chart Code Legend”, vbOKOnly, “Enter another Chart Type Code”
Range(“E9”).Activate
Exit Sub
End Select

‘check the chart background color code
If chartBackgroundColor > 0 And chartBackgroundColor < 72 Then
Else:
MsgBox “Allowable chart background color codes are 1 to 71”, vbOKOnly, “Enter another color code”
Range(“E10”).Activate
Exit Sub
End If

‘check the chart plot area background color code
If chartPlotAreaColor > 0 And chartPlotAreaColor < 72 Then
Else:
MsgBox “Allowable chart plot area color codes are 1 to 71”, vbOKOnly, “Enter another color code”
Range(“E11”).Activate
Exit Sub
End If

‘<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<END SECTION 4>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

‘<<<<<<<<<<<Prepare the ChartComplete sheet, where the final chart will be created>>>>>>>>>>>>SECTION 5
‘delete any existent chart before creating a new one
For Each chrt In Sheets(“ChartComplete”).ChartObjects
chrt.Delete
Next

‘clear the previous chart data
Worksheets(“ChartComplete”).Activate
Columns(“A:B”).Select
Selection.Clear

‘if the chart type is Area, 3D Area, 3D Column, 3D Line, Line, Radar, Scatter
‘label and highlight an extra cell where the user can set the legend label name
If chartType = 1 Or chartType = -4098 Or chartType = -4101 Or chartType = 4 Or chartType = -4151 Or chartType = -4169 Or chartType = -4100 Then
‘data label name
Range(“B1”).Offset = “LABEL NAME”
Range(“B1”).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
Columns(“B:B”).EntireColumn.AutoFit

End If

‘loop that formats the chart data area
For i = 2 To chartNumBars

‘set the labels default text
Cells(i, 1).Offset = “DATA” & Chr(32) & i – 1

‘format the label cells color
Cells(i, 1).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With

‘format the data value cells color
Cells(i, 2).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With

Next i
‘<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<END SECTION 5>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

‘<<<<<<<<<<<<<<<<<<<<<<<Here goes the code that creates the chart>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>SECTION 6
‘construct the new chart
Set chrt = Sheets(“ChartComplete”).ChartObjects.Add(Left:=leftDistance, _
Width:=chartWidth, Top:=topDistance, Height:=chartHeight) ‘set chart’s size and position

With chrt
.Chart.SetSourceData Source:=Sheets(“ChartComplete”).Range(“A1” & “:” & “B” & chartNumBars) ‘set the chart’s data source

.Chart.Type = chartType ‘set the chart type
.Chart.ChartArea.Interior.ColorIndex = chartBackgroundColor ‘set chart background color
.Chart.PlotArea.Interior.ColorIndex = chartPlotAreaColor ‘set chart plot area color
.Chart.HasTitle = True
.Chart.chartTitle.Text = chartTitle ‘set chart title
.Chart.Legend.Position = xlTop ‘set chart legend position
.Chart.SeriesCollection(1).Border.LineStyle = xlDash ‘set the bars line type
End With
‘<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<END SECTION 6>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

‘<<<<<<<<<<<<<<<<<<Here goes the code that allows the user to save the >>>>>>>>>>>>>>>>>>>>>SECTION 7

End Sub

Sub ExportChartAsImage()

Dim imgPath As String

‘ Saving chart as image
imgPath = ThisWorkbook.Path & “\Chart_” & Format(Now(), “DD_MM_YY_HH_MM_SS”) & “.bmp”
‘include the current time in the file name so the names won’t collide
Sheets(“ChartComplete”).ChartObjects(1).Chart.Export imgPath

End Sub

‘<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<END SECTION 7>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Hope you enjoyed this lesson! Thanks again to Nick who works as a tutor on Acuity Training’s Excel VBA course for putting it together.

 

 

 

12 thoughts on “Learn by Doing: Intro to VBA via Adding Charts Example

  1. Hello Nick! I am havind a problem with the “Selection.style= “accent4” part. The spreadsheet keep telling that the number of arguments is invalid, and when i get it to check that paints this part in yellow. Can you help me please?

  2. Yeah,
    It’s a little pain..and you just need the patience to go through the entire code to retype almost every apostrophe and quotation mark because they aren’t transferable (that’s why everything looks red). I did and it worked fine.

    • Thanks, did the same and worked out as well.
      Also it’s not that much of a pain if you use the search function (ctrl + f) to find and replace all apostrophes and quotations marks in one go.

  3. The code did not work when I pasted it. I don’t know enough about VBA to go any further to identify the issue

  4. Hello nickacuity,

    First off, I would like to say that this site has been an amazing learning experience for me. But I believe that some the cut and paste options are very unclear. I don’t know where I should stop cutting and pasting and it takes a lot of page up’ing and down’ing to make sure that it is correct. Second, the apostrophe’s in the code aren’t transferring correctly to my machine (running excel 2010) causing me to having to find and replace all of them. Is there any way to fix this?? Thanks again!.

    • Hello, I have encountered a similar problem. The code comes up all in red and the debugger says there is a syntax error on nearly every line. Any help on fixing this would be useful. Thank you

      • Hello all, I encountered the problem and found a quick way to resolve the issue.

        -Copy and past either Appendix to a blank “Word” document in “Keep Text Only Format” or “A”.
        -Copy the Appendix you just created in the “Word” document and paste to “Visual Basic”.

        It should correct the problem and the code should appear green.

        Rob

Post any comments, feedback or questions below!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s