Pivot tables are similar in function to extraction and data mining in your spreadsheets. You create a pivot table by using a named range and filtering only the data you want to use for your reports. You can create a pivot table in the IDE or using Visual Basic. Both methods are legitimate and work well with Excel, but developers sometimes need to create a pivot table on-the-fly in a new or existing spreadsheet. This article covers pivot tables and how you can create one using VB.
Understanding Pivot Tables
The best way to understand pivot tables is to create one using the Excel IDE. The Excel IDE makes it easier to create pivot tables than code, so you can review the information and the steps to better understand how they work. Once you understand how they work, you can then use code to create one dynamically.
Pivot tables are created against a full spreadsheet of data, so you must open a spreadsheet that contains several columns and rows of data or create one as an example.
Click the Insert tab, and you'll find the Pivot Table button. Click the button and insert a new table. A dialog window opens where you can now configure the pivot table data.
The first text box contains a range of cells that you want to use for the filter data. The next section in the window asks you if you want to create the pivot table on a new spreadsheet or an existing one. It's better to copy the pivot table over to a new location to avoid accidentally overwriting any existing data.
The next setting is the pivot table field list. This list lets you filter and customize your report. Each field will be shown next to a check box. Check the box if you want to include the column and remove the check mark if you want to hide the column.
At the bottom of the dialog box, you'll find the field filters. Use this section to filter out data that you want to exclude and any values you want to add together. Once you are finished, you'll have a pivot report in a new spreadsheet that you can see in your spreadsheet. These steps created a pivot table using the Excel IDE, but you need to create one using code.
Create a Pivot Table Using VBA
Now that you have a pivot table in your workbook, you can practice with it to understand the way pivot tables work. Once you understand the way they work, you can create one using code. VBA lets you create a dynamic pivot table using the same techniques that we used in the previous section. The only difference is that the pivot table is created using code rather than the input dialog boxes that you used in the IDE.
This section will take you step by step in creating a pivot table in VBA code. Make sure before you start that you open the Visual Basic Editor and create a module from the project manager.
First, let's create some variables that we'll need to work with the pivot table, its attributes and the spreadsheet needed to copy the data.
Dim pivot As PivotTable
Dim data As Worksheet
Dim range As Range
Dim cache As PivotCache
Dim sheet As Worksheet
Dim field As PivotField
With the variables set up, you can now point to the two spreadsheets you want to use. Remember when we made the pivot table, we first highlighted all of the cells in the original spreadsheet and determined a new spreadsheet to copy the data to a new location. We need to set up the variables used to reference these two sheets.
Set data = Worksheets("Employees")
Set sheet = Worksheets("Sheet1")
In the code above, we first point to the Employees worksheet. This is the worksheet that contains our current data. The second sheet variable points to a spreadsheet named Sheet1. In this example, we assume that Sheet1 exists.
Now we need to set the data range for the data. The following code highlights the cells that contain the data range in the existing data worksheet.
Set range = data.Range("A1:E40")
In the code above, we now have a named range that we can use.
In the next set of code, we set up a pivot cache. This pivot cache creates a table that is then used to manipulate data. The following code sets up the data.
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= range, Version:=xlPivotTableVersion12).createPivotTable TableDestination:= pivot.Range("A1"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
In the above code, we create a pivot table cache. Notice that we use some of the predefined Excel variables to set our collection times. We also use the "ActiveWorkbook" class, which is used to identify the currently active workbook in the Excel file. The only items you need to change is the TableName, which is your table name for the pivot table.
The next step is to assign the new pivot table cache to its own variable. The following code sets the table to a variable.
Set pivot = pivot.PivotTables("PivotTable1")
In this example, we use PivotTable1 from the previous code. Make sure you change this value to a pivot table name that you use in the cache creation code.
The next set of code is used to set the pivot columns. Let's take a look at the code.
pivot.ManualUpdate = True
'add row, column and page (report filter) fields:
Set field = pivot.PivotFields("Year HIred")
field.Orientation = xlPageField
Set field = pivot.PivotFields("Name")
field.Orientation = xlRowField
Set field = pivot.PivotFields("Department")
field.Orientation = xlRowField
field.Position = 1
Set field = pivot.PivotFields("Salary")
field.Orientation = xlRowField
field.Position = 1
Set field = pivot.PivotFields("Country")
field.Orientation = xlColumnField
In the code above, we set the pivot table columns that we want to use in the table we copy to Sheet1. Notice that we have several columns related to the Employee table of data. We decide to work with the year the employee was hired, their name, department and country for location. Each of these fields is set to an orientation. Notice that in the orientation we set it to is the Excel pre-defined variables that we've seen in previous sections. With these pre-defined variables, you can set the field orientation. In this example, we set the orientation to rows.
The next step is to create fields used with functions. For instance, you might want to sum the salary of all employee records. You also might want to count the number of employees in the record set. You can use pre-defined variables that map to Excel functions to perform this action. The following code sums a salary column to find out the total annual salary for all employees.
With pivot.PivotFields ("Salary")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Position = 1
End With
In the code above, we make one more addition to our pivot table. We sum the Salary column and add the sum function to the sheet. We also format the number just for aesthetics. Remember that functions can also be used in VBA code. Excel has several predefined variables that hold functions related to common tasks. In this example, the task is to sum the salary column.
The final set of code sets the version property and the manual update property. The manual update function requires that the user updates the numbers manually. In most cases, you want to update the numbers automatically, so this property should be set to false. Let's take a look at the code.
pivot.ManualUpdate = False
MsgBox Application.Version
MsgBox pivot.Version
The above code is the final touches on the pivot table code. We set a version and, most importantly, set the manual update property to false. This means that Excel will automatically change the values as you update your pivot table. In most cases, you don't want to force the user to manually update the changes. This can mean more frustration when they change the values and don't understand that they must manually update values.
Versioning is important when you want to understand what code your users are using on their desktops. For instance, suppose you update your code with new logic. You want to identify if users are using the new version of your code. You can store the version of your code in a random cell field within any spreadsheet. With this version, you can identify what code your users are using if they have several copies of your spreadsheet across multiple directories.
When you change your code, you change this version number. You can restrict users from using your spreadsheet if they don't have a specific version that you set in your code. This is one way to ensure that users are only using an updated form of your code should you change the way your application functions.
Pivot tables aren't as popular as graphs and charts, but you still need to understand the way that they work. You won't come across too many spreadsheet requirements that ask you to create pivot tables from your VBA code, but you should know how to create them should you ever need to. This article covered pivot tables, and you can get a better understanding of how they work by creating your own spreadsheet of data and then changing data to see what happens to the pivot table. It's best to see a pivot table in action before coding one in VBA.
Charts
One of the most commonly used components in Excel is charts. Charts create a visual representation of your data, which is important during presentations and reports. Charts can be created using the Excel IDE or using Visual Basic. In this section, we'll focus on creating charts using a Visual Basic module in the VBE.
Inserting a Basic Chart
Before we get into more advanced features, we can discuss inserting a basic chart. From there, we can discuss more advanced custom charts and chart series options. Open the VBE and create a new module from the project explorer window.
First, we need to give the subroutine a name and create a shell where the chart creation code is placed. The following code shows you how to create a subroutine, although we have seen this code before.
Sub CreateChart()
End Sub
With the basic subroutine created, we can now create the code that generates the chart. Let's first create two variables used to hold the chart object and the named range.
Sub CreateChart()
Dim range As Range
Dim chart As Object
End Sub
Now we need to create a named range. We can use a named range that we created in the Excel workbook, or we can use a named cell range. In this example, we use a range of cells instead of a named range.
Sub CreateChart()
Dim range As Range
Dim chart As Object
Set range = ActiveSheet.Range("A1:M33")
End Sub
With the named range set, we can now create a variable for the chart. We add a chart and assign it a variable at the same time. Visual Basic uses the function AddChart2 to add a chart and then reference it using a variable that you define. The following code shows you how to create this code.
Dim chart As Object
Set range = ActiveSheet.Range("A1:M33")
Set chart = ActiveSheet.Shapes.AddChart2
End Sub
In the next line, notice that we used the ActiveSheet object again, but we then used the Shapes and AddChart2 function. A chart is technically a shape in Visual Basic, so this code creates a chart shape and then assigns it to the chart variable.
With the chart shape created, we can now assign it the range that we created previously. The next code assigns the chart the named range data.
Sub CreateChart()
Dim range As Range
Dim chart As Object
Set range = ActiveSheet.Range("A1:M33")
Set chart = ActiveSheet.Shapes.AddChart2
chart.Chart.SetSourceData Source:= range
End Sub
The next line assigns the source of the chart's data. We have a variable named range that contains some data, and then we add it to the chart using the chart's SetSourceData property. Once this property is set, your chart now has the right data attached to it.
The last property we need to set for a basic chart is the chart type. If you've ever worked with Excel charts, you know that there are several types such as bar, line and pie charts. The default is a bar chart, so you need to specify what type you want before you render the chart.
This last line of code renders the chart. The following code is the last part to complete the chart code.
Sub CreateChart()
Dim range As Range
Dim chart As Object
Set range = ActiveSheet.Range("A1:M33")
Set chart = ActiveSheet.Shapes.AddChart2
chart.Chart.SetSourceData Source:= range
chart.Chart.ChartType = xlXYScatterLines
End Sub
In the code above, we set the chart type for the chart object. Notice that we again use the xl variable reference, which means that the chart type is set as an Excel predefined variable. As you can see from the variable name, the chosen chart type is X, Y scatter lines.
Run this code to test your function. You'll need test data in the cells A1 to M33, or you can just run the code to see how Visual Basic places the chart on the spreadsheet.
If you've ever worked with charts before, you know that charts can have a title and a legend. Let's add the code needed to provide these attributes on your reports.
Sub CreateChart()
Dim range As Range
Dim chart As Object
Set range = ActiveSheet.Range("A1:M33")
Set chart = ActiveSheet.Shapes.AddChart2
chart.Chart.SetSourceData Source:= range
chart.Chart.ChartType = xlXYScatterLines
chart.Chart.HasTitle = True
chart.Chart.ChartTitle.Text = "New Graph"
End Sub
The above chart code adds a title to it. Notice that we needed two lines of code to add the title. We have to first set the HasTitle property to true, which turns on title visibility. The next line of code sets the Text property for the chart, which is "New Graph." You can give a chart any name that you need.
Now let's add a legend. You can add a legend on the left, top, bottom or right of the actual chart image. We'll set the legend to the right of the image. Take a look at the following code.
Sub CreateChart()
Dim range As Range
Dim chart As Object
Set range = ActiveSheet.Range("A1:M33")
Set chart = ActiveSheet.Shapes.AddChart2
chart.Chart.SetSourceData Source:= range
chart.Chart.ChartType = xlXYScatterLines
chart.Chart.HasTitle = True
chart.Chart.ChartTitle.Text = "New Graph"
chart.SetElement (msoElementLegendRight)
End Sub
The last line of code in the above subroutine sets the chart legend. The SetElement function is used to add the legend. We haven't seen an internal mso variable, but this variable is also predefined and indicates that you want to set the element to the right of the chart.
Exporting a Chart to an Image File
In many cases, you need to transfer a chart to an actual image file. This image file is then used to import information into other spreadsheets, display in HTML pages or inserted into presentation files. Regardless of what you need the image file for, you can export the chart image into a PNG, GIF or JPG image. Remember that the recipient must be able to open the file, so choose an image format that is compatible with your target user.
Luckily, you only need one line of code to export to an image file. Take a look at the following code.
Sub CreateChart()
Dim range As Range
Dim chart As Object
Set range = ActiveSheet.Range("A1:M33")
Set chart = ActiveSheet.Shapes.AddChart2
chart.Chart.SetSourceData Source:= range
chart.Chart.ChartType = xlXYScatterLines
chart.Chart.HasTitle = True
chart.Chart.ChartTitle.Text = "New Graph"
chart.SetElement (msoElementLegendRight)
ActiveChart.Export "C:\My Documents\ExportedChart.png"
End Sub
Notice that we added a line to our CreateChart code. The ActiveChart.Export function takes the currently active chart and exports it to the file location. In this example, we put the chart in the My Documents folder. We name the chart ExportedChart and give it the PNG file format. You can use any file format you want, but the PNG file format is compatible with most programs. It's also cross-platform friendly, so it will work with other operating systems even though you're exporting from the Windows operating system.
Adding a Pivot Chart
We discussed pivot charts in the last chapter. In this section, we'll show you how to create a pivot chart to your spreadsheets using VBA. A pivot chart is a visual representation of your pivot tables.
First, let's create the subroutine shell with the appropriate variables.
Sub CreateChart()
Dim pivot As PivotTable
End Sub
Notice instead of a range or a chart variable, we first create a pivot table. We now need to point the variable to the pivot table in your spreadsheet.
Sub CreateChart()
Dim pivot As PivotTable
Set pivot = ActiveSheet.PivotTables(1)
pivot.PivotSelect ("My Pivot")
End Sub
In the code above, we first set the pivot variable to the first pivot table on the spreadsheet. We then select it, which makes it the active pivot table on the sheet.
With the pivot table selected, we can now add the chart to the active worksheet. The following code adds the chart.
Sub CreateChart()
Dim pivot As PivotTable
Set pivot = ActiveSheet.PivotTables(1)
pivot.PivotSelect ("My Pivot")
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, _
Name:= pivot.Parent.Name
End Sub
Notice that we added a line of code using the Charts.Add function. This function uses several parameters. The first parameter is the location where you want the chart to be located. In this example, we set it to the currently active chart. We then give the chart a name. In this example, we give the chart the same name as the pivot table.
Finally, you want to deselect the pivot table and chart and reset the cursor to another cell. Usually, programmers automatically set the deselected cell as A1. Let's take a look at the final code.
Sub CreateChart()
Dim pivot As PivotTable
Set pivot = ActiveSheet.PivotTables(1)
pivot.PivotSelect ("My Pivot")
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, _
Name:= pivot.Parent.Name
Range("A1").Select
Application.ScreenUpdating = True
End Sub
The final bit of code sets the selected cell range to A1. This turns off highlighting of the pivot table, so the user can look at the results. We then set the main screen application update to true, so the pivot tables data and presentation updates to the new data.
Using charts is a main form of VBA code when you're working with Excel spreadsheets. Creating charts in VBA is almost as simple as creating one in the Excel IDE. Just remember that you need to give the chart a type or it will always default to a bar chart. Excel provides you with predefined variables to indicate the type of chart that you want to display. With subroutines that create charts automatically, you can create charts for your users and avoid having them set up charts for themselves. This makes a far more complex but convenient application for your users.
