Build an Excel Add-In 5 – Tie the Code Together

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