This is the fifth in a series of posts that shows the steps involved in building an Excel add-in. In Build an Excel Add-In 1 – Basic Routine I showed a VBA procedure that creates a regular chart from an arbitrary rectangular range, including from a pivot table. In Build an Excel Add-In 2 – Enhanced Functionality I turned modularized this routine, so it could be called from any code that passed the appropriate settings into the function as arguments. In Build an Excel Add-In 3 – Auxiliary Modular Functions I included an additional modular function which cleans up the chart created in the previous article’s code. Because this function is widely useful, it will be made more general in a future article, after this series is finished. In Build an Excel Add-In 4 – Create the Dialog I showed a simple dialog that asked the user to identify the data to plot and select a few simple options.
In this installment of the Build an Excel Add-In series, I will show a VBA procedure that ties together all of the pieces that we’ve built so far. This master procedure is really a short list of the other procedures that do the individual tasks.
Here is the master procedure in its entirety. Below this, the individual pieces will be explained.
Sub PT_Plotter_Dialog() ' GET USER SPECIFICATIONS ' ' Using custom dialog ' ' Get data range and orientation ' ' Get header rows and columns ' ' Get chart type ' ' Get optional chart position and size Dim frmPlotSetup As FPlotSetup Dim rChartData As Range Dim Orientation As XlRowCol Dim iHeaderRows As Long Dim iHeaderCols As Long Dim ChtType As XlChartType Dim rPosition As Range Dim bCancel As Boolean Dim PT_Plot As Chart Set frmPlotSetup = New FPlotSetup With frmPlotSetup .Show bCancel = .Cancel If Not bCancel Then Set rChartData = .ChartDataRange Orientation = .DataOrientation iHeaderRows = .HeaderRows iHeaderCols = .HeaderColumns ChtType = .ChartType Set rPosition = .ChartPosition End If End With Set frmPlotSetup = Nothing If Not bCancel Then Application.ScreenUpdating = False ' make the chart Set PT_Plot = PT_Plotter_Chart(rChartData, Orientation, _ iHeaderRows, iHeaderCols, ChtType, rPosition) ' clean up the chart Set PT_Plot = CleanUpChart(PT_Plot) Application.ScreenUpdating = True End If ExitSub: End Sub
After the declarations, the program calls the dialog to get the user’s settings.
' declare a new instance of the dialog class ' this may be overkill, but it's done this way in PED Set frmPlotSetup = New FPlotSetup With frmPlotSetup ' display the dialog .Show ' execution returns here when user dismisses dialog ' first see whether user canceled bCancel = .Cancel If Not bCancel Then ' if user did not cancel, we need the information Set rChartData = .ChartDataRange Orientation = .DataOrientation iHeaderRows = .HeaderRows iHeaderCols = .HeaderColumns ChtType = .ChartType Set rPosition = .ChartPosition End If End With ' we're done with the form, so destroy it Set frmPlotSetup = Nothing
Now that we’ve retrieved the user settings, it’s time to make the chart, then clean it up.
' skip the rest if the user has canceled If Not bCancel Then ' prevent seizures and speed execution Application.ScreenUpdating = False ' call the PT_Plotter_Chart function, pass in user info, ' function returns the chart Set PT_Plot = PT_Plotter_Chart(rChartData, Orientation, _ iHeaderRows, iHeaderCols, ChtType, rPosition) ' call the CleanUpChart function to, uh, clean up the chart ' pass in the chart, and it is returned in cleaned state Set PT_Plot = CleanUpChart(PT_Plot) ' turn display updating back on Application.ScreenUpdating = True End If
In the next two articles, we’ll create the user interface for Classic Excel (menu and command bar elements in Excel 2000 through 2003) and for New Excel (ribbon elements in Excel 2007 and 2010). There is some clean up to do, including modifications to make the add-in work for both Classic and new Excel, and a trick to improve the chances of compatibility with as many users as possible. Finally I will cover deployment using Inno Setup, an awesome piece of customizable freeware for packaging up your add-in.
Contents: How to Build an Excel Add-In
- Build an Excel Add-In 1 – Basic Routine
- Build an Excel Add-In 2 – Enhanced Functionality
- Build an Excel Add-In 3 – Auxiliary Modular Functions
- Build an Excel Add-In 4 – Create the Dialog
- Build an Excel Add-In 5 – Tie the Code Together
- Build an Excel Add-In 6 – Interface for 2003
- Build an Excel Add-In 7 – Interface for 2007
- Build an Excel Add-In 8 – Last Steps
- Build an Excel Add-In 9 – Deployment