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

 

Peltier Tech Chart Utility

Build an Excel Add-In 4 – Create the Dialog

This is the fourth part of a series 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 this installment of the Build an Excel Add-In series, I will show how to create a custom dialog using a VBA UserForm, a set of controls, and related code.

Insert UserForm

The first step in building a dialog is to insert a UserForm. In the Visual Basic Editor (VBE) select the project in the Project Explorer pane, then from the Insert menu, choose UserForm.

Insert UserFrom from Menu

A new window is created, containing a blank UserForm.

New UserFrom

The UserForm is listed in the Project Explorer, in the Forms folder of the VB Project.

UserFrom Listed in the Project Explorer

A UserForm actually consists of two parts: the Designer window and the Code window. The code window contains all of the VBA procedures that make the dialog work. For example, in the code window you will find the code that responds to button clicks.

When the UserForm’s window is active, the Properties window can be used to edit the properties of the UserForm. For example, the name of the UserForm has been changed from UserForm1 (below left) to FPlotSetup (below right), and the caption has been changed from UserForm1 to PT Plotter.

Properties of the Original UserForm – – Properties of the Renamed UserForm

In addition to changing properties through the Properties window, the size of the dialog can be changed by dragging the white handles on the right and bottom edge of the UserForm.

Forms Controls

The blank UserForm shown above is transformed into the fully featured dialog shown below by adding and coding a set of controls. VBA offers a range of controls, and others can be used if additional capabilities are required. Care should be taken when using controls which are not built in, because of potential deployment complications.

Finished Add-In Dialog

The controls in this dialog are listed below, in top-down and left-to-right order:

  • lblChartData
  • refChartData
  • fraOrientation
    • optByRow
    • optByColumn
  • fraHeaders
    • txtHeaderRows
    • lblHeaderRows
    • txtHeaderColumns
    • lblHeaderColumns
  • fraChartType
    • optChartTypeXY
    • optChartTypeLine
    • optChartTypeBar
    • optChartTypeColumn
  • lblChartPosition
  • refChartPosition
  • lblChartPosition2
  • lblCopyright
  • btnOK
  • btnCancel

I have used a simple naming convention:

Three letter prefixes identify the type of control: lbl for label, ref for RefEdit, fra for frame, opt for option button, txt for text boxes, btn for command buttons (some developers use cmd for command button, but I think btn is more descriptive)). Not used in this dialog are chk for check box, mpg for multipage, lst for list box, cbo for combo box, scr for scroll bar, spn for spin button, img for image.

The purpose of each control is encoded into its name. For example, optChartTypeXY is for choosing the XY Chart Type option, refChartData is the RefEdit used to select the chart data, etc.

Controls Toolbox

When a UserForm is the active window, the Controls Toolbox appears. This is a toolbar-like window that contains VBA controls as well as other controls that you may have added.

Controls Toolbox

To add a control to the dialog, click on its icon in the Controls Toolbox. When the cursor is moved over the UserForm, it changes to a cross hair and the selected icon is shown below the icon to remind you what control you’re adding. Click and drag to draw a rectangle as large as the control you are adding.

Adding a Control to a UserForm

When you release the mouse, the control appears, highlighted with white resizing handles.

Control Added to a UserForm

After a control is added, its name and other properties can be set in the Property window, just as the name and caption of the UserForm itself were set.

Labels and Text Boxes

Labels simply contain text that guides the user through the dialog. The user cannot change the caption of a label, but the caption can be controlled by code in response to certain conditions.

Text boxes allow a user to specify values which can be represented as text. These values may be numerical, but it is up to the programmer to deal with number formats of entered and displayed numbers. In our dialog, text boxes are used to specify the number of header rows and columns in the chart source data.

Label and Text Box

Frames

Frames are used to give some order to the dialog, grouping together related controls. Our dialog has three frames, containing controls for selection of data orientation, header rows and columns, and chart type.

Option Buttons and Checkboxes

Option buttons are used to let a user select from among several options. All option buttons within a frame or with the same GroupName property are grouped together, such that only one can be true (selected) at once, and changing one to true automatically changes the others in its group to false. In this frame, only the XY option is selected.

Chart Type Frame with XY Option Selected

When the Line option is selected, the XY option is unselected.

Chart Type Frame with Line Option Selected

Check boxes are used to let a user set a true (checked) or false (unchecked) value. In the absence of event code in the UserForm, check box values are independent of each other.

UserForm Check Boxes

List Boxes and Combo Boxes

List boxes and combo boxes allow the user to select one or more items from a list. A list box is more user friendly because it shows the whole list (or at least a subset of the list) while the combo box only shows the selected item, unless the user clicks to expand the list.

List Box, Combo Box, and Expanded Combo Box

Command Buttons

Command Buttons allow a user to initiate an action, in our case, approving or canceling the session with the dialog. Clicking the button runs code that carries out the user’s command.

RefEdit Controls

RefEdit controls allow the user to select a range of cells with the mouse. While the user is selecting cells, the UserForm shrinks to the size of the RefEdit plus the title bar. The address of the selected range appears in the RefEdit and updates as the mouse is dragged, and the size of the selected range is displayed next to the cursor.

RefEdit in Minimized Dialog While Range is Selected

When the mouse is released, the UserForm returns to its original size, and the address remains in the RefEdit.

RefEdit Shows Address of Selected=

Read more about the RefEdit control in Using RefEdit Controls in Excel Dialogs.

Adding Code

There is a simple way to add code to the UserForm. Double click on a control, and the UserForm’s code window opens. An empty event procedure for the default method (action) of the control is added to the code window. For example, double clicking on the OK button adds the btnOK_Click event procedure stub to the code.

OK Button Click Event Procedure

You enter the code you want to run on the OK button click event into this procedure stub. The following shows a call to a function that validates the user’s selections. If selections are valid, the code hides the form, sending execution back to the procedure that showed the UserForm. If selections are invalid, the user sees a message and decides whether to continue or bail out.

OK Button Click Event Procedure

Notice the two dropdowns at the top of the code window. The selected control, btnOK, is shown in the left dropdown, and the Click event is shown in the right dropdown. You can use these dropdowns to select any event that’s available for any control. It’s a good idea to use these dropdowns to define your procedures, because some events include arguments, and the dropdowns will always get the arguments correct.

For example, the KeyDown event of a text box control includes the KeyCode (the ASCII code of the key that was pressed) and the Shift (whether Shift, Alt, and/or Ctrl were down when the key was pressed) arguments. When the procedure runs, Excel passes these values into the procedure, so the code knows which key was pressed, under which shift state.

Header Rows Textbox KeyDown Event Procedure

The UserForm code module can contain event procedures, as well as functions and subs that can be called by these event procedures. For example, the Validated function called by the btnOK_Click event is also found in the UserForm’s code. Procedures in the UserForm can also call procedures in other modules.

The Code Behind the Dialog

The complete listing of the Userform’s code is presented below.

The UserForm_Initialize event loads the selected range address into the RefEdit.

The Validated function checks the RefEdit for a valid range and the header text boxes for valid numbers of header rows and columns.

The various property procedures are used called by the main routine to extract information from the UserForm. I wrote about this technique in Property Procedures – Passing Information to and from Forms.

The mbCancel variable is True if the Cancel button is clicked. The Cancel property checks this variable and tells the main routine if the user canceled the dialog.

Option Explicit

Private mbCancel As Boolean

Private Sub btnCancel_Click()
  mbCancel = True
  Me.Hide
End Sub

Private Sub btnOK_Click()
  Dim sMessage As String
  Dim lResponse As VbMsgBoxResult

  If Validated(sMessage) Then
    mbCancel = False
    Me.Hide
  Else
    lResponse = MsgBox(sMessage, vbRetryCancel, "Invalid Information")
    If lResponse = vbCancel Then
      btnCancel_Click
    End If
  End If
End Sub

Public Property Get Cancel() As Boolean
  Cancel = mbCancel
End Property

Public Property Get DataOrientation() As XlRowCol
  Select Case True
    Case Me.optByColumn.Value
      DataOrientation = xlColumns
    Case Me.optByRow.Value
      DataOrientation = xlRows
  End Select
End Property

Public Property Get HeaderRows() As Long
  HeaderRows = Val(Me.txtHeaderRows.Text)
End Property

Public Property Get HeaderColumns() As Long
  HeaderColumns = Val(Me.txtHeaderColumns.Text)
End Property

Public Property Get ChartType() As XlChartType
  Select Case True
    Case Me.optChartTypeXY.Value
      ChartType = xlXYScatterLines
    Case Me.optChartTypeLine.Value
      ChartType = xlLineMarkers
    Case Me.optChartTypeBar.Value
      ChartType = xlBarClustered
    Case Me.optChartTypeColumn.Value
      ChartType = xlColumnClustered
  End Select
End Property

Public Property Get ChartDataRange() As Range
  Set ChartDataRange = Range(Me.refChartData.Text)
End Property

Public Property Get ChartPosition() As Range
  On Error Resume Next
  Set ChartPosition = Range(Me.refChartPosition.Text)
  On Error GoTo 0
End Property

Private Function Validated(sMsg As String) As Boolean

  ' Minimalist input validation

  Dim rTest As Range
  Dim iTest As Long

  Validated = True

  On Error Resume Next
  Set rTest = Range(Me.refChartData.Text)
  On Error GoTo 0
  If rTest Is Nothing Then
    Validated = False
    sMsg = "Invalid data range selected."
    GoTo ExitFunction
  End If

  iTest = Me.HeaderRows
  If iTest >= rTest.Rows.Count Then
    Validated = False
    sMsg = "Too many header rows specified."
    GoTo ExitFunction
  End If
  iTest = Me.HeaderColumns
  If iTest >= rTest.Columns.Count Then
    Validated = False
    If Len(sMsg) > 0 Then sMsg = sMsg & vbNewLine
    sMsg = sMsg & "Too many header columns specified."
    GoTo ExitFunction
  End If

ExitFunction:

End Function

Private Sub UserForm_Initialize()

  ' Define current range for RefEdit, if applicable

  Dim sAddress As String
  If TypeName(Selection) = "Range" Then
    If Selection.Cells.Count = 1 Then
      sAddress = Selection.CurrentRegion.Address(External:=True)
    Else
      sAddress = Selection.Address(External:=True)
    End If
    sAddress = Mid$(sAddress, InStr(sAddress, "]") + 1)
    If InStr(sAddress, "'") > 0 Then
      sAddress = "'" & sAddress
    End If
    Me.refChartData.Text = sAddress
  End If
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    btnCancel_Click
  End If
End Sub

In the next article of the series, Build an Excel Add-In 5 – Tie the Code Together, we will develop code to call this dialog, extract the user selections, and produce a chart using these settings.

Contents: How to Build an Excel Add-In

 

Peltier Tech Chart Utility

Build an Excel Add-In 3 – Auxiliary Modular Functions

In Build an Excel Add-In 1 – Basic Routine I coded a procedure in VBA to create a regular chart from an arbitrary rectangular range, including from a pivot table. In Build an Excel Add-In 2 – Enhanced Functionality I turned this routine into a modular function, that could be called from any code which passed in the appropriate settings as arguments to the function.

In this post I present an example of another modular function. I say “modular” because the function is not integrated into any other code, and thus can be called from any procedure that needs its functionality.

One big advantage of modular functions like this is that they can be called from many other procedures, and each time they execute the same way. If I want to make an adjustment to the function, I can make the change in one place, and the change is reflected every time it is run.

Another advantage of such modular functions is that they can be copied from one project and pasted into another, and you don’t have to carry out extensive testing to make sure that it works. A quick run through the code is usually sufficient to ensure proper execution.

The New Function

This rudimentary function is still a very useful one. It takes a chart as its sole argument, applies some formatting to the chart, then returns the chart as its value.

Given a chart variable called TheChart, the function is called like this:

Set TheChart = CleanUpChart(TheChart)

The function removes the borders on the chart area, plot area, and legend. It applies a uniform, non-auto-scaling font to all text elements. It removes borders on bar and column chart series and changes the gap spacing to 100%. It also widens the plot area to redue the wasted white space around the chart.

Here is the function in all of its glory. Feel free to adapt it to your own needs, using your own preferred formatting.

An alternate version of this function may convert 3D charts to their 2D equivalents, for example.

Function CleanUpChart(cht As Chart) As Chart
  
  ' PRETTIFY THIS CHART
  ' ' chart area: remove border, fix font size, disable font autoscaling
  ' ' legend: remove border, resize, reposition
  ' ' plot area: remove border and fill, maximize size, minimize margins
  ' ' gridlines: remove
  
  Dim dLgndWidth As Double
  Dim iLgndEntry As Long
  Dim iAxType As XlAxisType
  Dim iAxGroup As XlAxisGroup
  Dim iSrs As Long
  Dim bScreenUpdating As Boolean

  bScreenUpdating = Application.ScreenUpdating
  Application.ScreenUpdating = False
  
  With cht
    With .ChartArea
      .Border.LineStyle = xlNone
      .AutoScaleFont = False
      .Font.Size = 8
    End With
    With .Legend
      .Border.LineStyle = xlNone
      .Left = 0
      .Width = cht.ChartArea.Width
      dLgndWidth = .LegendEntries(1).Width
      For iLgndEntry = 2 To .LegendEntries.Count
        If dLgndWidth < .LegendEntries(iLgndEntry).Width Then
          dLgndWidth = .LegendEntries(iLgndEntry).Width
        End If
      Next
      .Width = dLgndWidth
      .Left = cht.ChartArea.Width
    End With
    With .PlotArea
      .Border.LineStyle = xlNone
      .Interior.ColorIndex = xlNone
      .Top = 0
      .Left = 0
      .Height = cht.ChartArea.Height
      .Width = cht.Legend.Left - 2
    End With
    For iAxGroup = xlPrimary To xlSecondary
      For iAxType = xlCategory To xlValue
        If .HasAxis(iAxType, iAxGroup) Then
          If .Axes(iAxType, iAxGroup).HasMajorGridlines Then
            .Axes(iAxType, iAxGroup).MajorGridlines.Delete
          End If
          If .Axes(iAxType, iAxGroup).HasMinorGridlines Then
            .Axes(iAxType, iAxGroup).MinorGridlines.Delete
          End If
        End If
      Next
    Next
    If .ChartType = xlColumnClustered Or .ChartType = xlBarClustered Then
      .ChartGroups(1).GapWidth = 100
      For iSrs = 1 To .SeriesCollection.Count
        .SeriesCollection(iSrs).Border.LineStyle = xlNone
      Next
    End If
    
  End With
  
  Set CleanUpChart = cht
  
  Application.ScreenUpdating = bScreenUpdating

End Function

Contents: How to Build an Excel Add-In

 

Peltier Tech Chart Utility

Build an Excel Add-In 2 – Enhanced Functionality

In Build an Excel Add-In 1 – Basic Routine I coded a procedure in VBA to create a regular chart from an arbitrary rectangular range, including from a pivot table. In this post I will begin the process of turning this routine into an Excel add-in.

The VBA procedure in Build an Excel Add-In 1 – Basic Routine is a subroutine which creates a chart using a number of built-in (hard-coded) options. In this post I will show how to make this subroutine into a modular function, which allows a calling procedure to pass these options into the procedure. This adds the flexibility that allows the function to create a variety of charts based on different options. In a future installment of this series I will build a dialog (a VBA UserForm) which allows a user to select these options.

Modular Function

The first step in the transformation is to turn the subroutine in the previous step:

Sub PT_Plot() 

into a function with parameters for the various options:

Function PT_Plotter_Chart(DataRange As Range, DataOrientation As XlRowCol, _
    HeaderRows As Long, HeaderColumns As Long, ChartType As XlChartType, _
    Optional PositionRange As Range) As Chart

The parameters passed into this function are:

DataRange: a Range object containing the chart's source data
DataOrientation: whether series are specified by xlColumns or xlRows
HeaderRows: number of header rows in the data range (series names or category values)
HeaderColumns: number of header columns in the data range (series names or category values)
ChartType: type of chart to create (XY, Line, Area, Column, Bar)
PositionRange (optional): range of cells that chart object will cover 

The result of the function is a reference to the chart created in the function.

The calling procedure has defined the values of these parameters, and our function has to adjust its execution to account for these values.

Chart Source Data Range

In the original sub, if the selection is not a range, execution exits the procedure. Otherwise the selected range becomes the source data range:

  If TypeName(Selection) <> "Range" Then
    MsgBox "Select a range and try again.", vbOKOnly, "No Range Selected"
    GoTo ExitProcedure
  End If
  Set rData = Selection

In the new function, DataRange As Range is passed into the function.

Header Rows and Columns, and Data Orientation

The original sub assumes that there is one header row and one header column. The range containing the series values is offset one row down and one column right of the source data range, and reduced in size by one row and one column. In addition, it assumes that series are defined by columns, so the series names are defined as the top row, the category values as the left column, and the number of series as the number of columns in the series values range.

  With rData
    Set rValues = .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1)
    nSrs = .Columns.Count - 1
    Set rNames = .Offset(, 1).Resize(1, nSrs)
    Set rCats = .Offset(1).Resize(.Rows.Count - 1, 1)
  End With

The new function adjusts the position and size of the range containing the series values by the number of header rows and columns it is passed. The data orientation helps to define the number of series, the series names, and the category values.

  With DataRange
    Set rValues = .Offset(HeaderRows, HeaderColumns) _
        .Resize(.Rows.Count - HeaderRows, .Columns.Count - HeaderColumns)
    Select Case DataOrientation
      Case xlColumns
        nSrs = .Columns.Count - HeaderColumns
        Set rNames = .Offset(, HeaderColumns).Resize(HeaderRows, nSrs)
        Set rCats = .Offset(HeaderRows) _
            .Resize(.Rows.Count - HeaderRows, HeaderColumns)
      Case xlRows
        nSrs = .Rows.Count - HeaderRows
        Set rNames = .Offset(HeaderRows).Resize(nSrs, HeaderColumns)
        Set rCats = .Offset(, HeaderColumns) _
            .Resize(HeaderRows, .Columns.Count - HeaderColumns)
    End Select
  End With 

Series are added to the chart as columns or as rows, based on the data orientation:

  For iSrs = 1 To nSrs
    Set Srs = cht.SeriesCollection.NewSeries
    With Srs
      Select Case DataOrientation
        Case xlColumns
          .Values = rValues.Columns(iSrs)
          .XValues = rCats
          .Name = "=" & rNames.Columns(iSrs).Address(ReferenceStyle:=xlR1C1, External:=True)
        Case xlRows
          .Values = rValues.Rows(iSrs)
          .XValues = rCats
          .Name = "=" & rNames.Rows(iSrs).Address(ReferenceStyle:=xlR1C1, External:=True)
      End Select
    End With
  Next 

Chart Type

The original sub does not specify a chart type, so it creates a chart of the default type. If the user has not specified their own default, then the Excel default chart type, 2D clustered column, is used.

The new function applies the specified chart type to the finished chart:

  cht.ChartType = ChartType 

Chart Position

The original sub mimics the Excel 2003 default chart object position, which is half the size of the active window, centered within the active window.

  Set rScroll = ActiveSheet.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
  With ActiveWindow
    dWidth = .UsableWidth / 2
    dHeight = .UsableHeight / 2
    dLeft = rScroll.Left + dWidth / 2
    dTop = rScroll.Top + dHeight / 2
  End With

If it is passed a range defining the chart position, the new function covers this range with the chart object.

    With PositionRange
      dLeft = .Left
      dTop = .Top
      dWidth = .Width
      dHeight = .Height
    End With

Otherwise, the function mimics the Excel 2003 default chart object position, using the largest pane instead of the entire active window. If the chart will be smaller than a minimum defined size, this defined minimum will be used rather than half the size of the largest pane.

    With ActiveWindow
      dWidth = .UsableWidth / 2
      dHeight = .UsableHeight / 2
      If .SplitColumn > 0 Then
        If ActiveSheet.Range("A1").Resize(, .SplitColumn).Width > dWidth Then
          dWidth = ActiveSheet.Range("A1").Resize(, .SplitColumn).Width / 2
          If dWidth < 200 Then dWidth = 200
          dLeft = ActiveSheet.Columns(.Panes(1).ScrollColumn).Left + dWidth / 2
        Else
          dWidth = dWidth - ActiveSheet.Range("A1").Resize(, .SplitColumn).Width / 2
          If dWidth < 200 Then dWidth = 200
          dLeft = ActiveSheet.Columns(.Panes(.Panes.Count).ScrollColumn).Left + dWidth / 2
        End If
      Else
        If dWidth < 200 Then dWidth = 200
        dLeft = ActiveSheet.Columns(.Panes(1).ScrollColumn).Left + dWidth / 2
      End If
      If dLeft < 40 Then dLeft = 40
      If .SplitRow > 0 Then
        If ActiveSheet.Range("A1").Resize(.SplitRow).Height > dHeight Then
          dHeight = ActiveSheet.Range("A1").Resize(.SplitRow).Height / 2
          If dHeight < 125 Then dHeight = 125
          dTop = ActiveSheet.Rows(.Panes(1).ScrollRow).Top + dHeight / 2
        Else
          dHeight = dHeight - ActiveSheet.Range("A1").Resize(.SplitRow).Height / 2
          If dHeight < 125 Then dHeight = 125
          dTop = ActiveSheet.Rows(.Panes(.Panes.Count).ScrollRow).Top + dHeight / 2
        End If
      Else
        If dHeight < 125 Then dHeight = 125
        dTop = ActiveSheet.Rows(.Panes(.Panes.Count).ScrollRow).Top + dHeight / 2
      End If
    If dTop < 25 Then dTop = 25
    End With

This set of calculations based on the splitting of the window into multiple panes is too complicated for this post, but it may be covered in a future post.

Returned Value

The function returns the chart that is created in the function:

  Set PT_Plotter_Chart = cht

Entire Code of New Function

The following is the listing of the updated procedure:

Function PT_Plotter_Chart(DataRange As Range, DataOrientation As XlRowCol, _
    HeaderRows As Long, HeaderColumns As Long, ChartType As XlChartType, _
    Optional PositionRange As Range) As Chart

  ' CREATE AND POPULATE A CHART
  ' ' Start with defined data range
  ' ' Put chart in specified (or default) position
  ' ' Use specified chart type
  ' ' Plot by row or column as specified
  ' ' Use specified rows/columns for category values
  ' ' Use specified columns/rows for series names
  ' ' Use position range (if specified) for position of chart object
  ' ' Otherwise center chart object in largest pane of active window

  Dim ChtOb As ChartObject
  Dim cht As Chart
  Dim wsPosition As Worksheet
  Dim dLeft As Double, dTop As Double, dWidth As Double, dHeight As Double
  Dim rActive As Range
  Dim rScroll As Range
  Dim rCats As Range
  Dim rNames As Range
  Dim rValues As Range
  Dim iSrs As Long, nSrs As Long
  Dim Srs As Series
  Dim bScreenUpdating As Boolean

  bScreenUpdating = Application.ScreenUpdating
  Application.ScreenUpdating = False

  If TypeName(Selection) = "Range" Then
    Set rActive = ActiveCell
    Set rScroll = ActiveSheet.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
  End If
  If PositionRange Is Nothing Then
    ' Position unspecified
    ' Define using 2003 configuration
    ' Put into largest pane (whole window if unsplit)
    ' Half the pane's width and height, centered in pane
    With ActiveWindow
      dWidth = .UsableWidth / 2
      dHeight = .UsableHeight / 2

      If .SplitColumn > 0 Then
        If ActiveSheet.Range("A1").Resize(, .SplitColumn).Width > dWidth Then
          dWidth = ActiveSheet.Range("A1").Resize(, .SplitColumn).Width / 2
          If dWidth < 200 Then dWidth = 200
          dLeft = ActiveSheet.Columns(.Panes(1).ScrollColumn).Left + dWidth / 2
        Else
          dWidth = dWidth - ActiveSheet.Range("A1").Resize(, .SplitColumn).Width / 2
          If dWidth < 200 Then dWidth = 200
          dLeft = ActiveSheet.Columns(.Panes(.Panes.Count).ScrollColumn).Left + dWidth / 2
        End If
      Else
        If dWidth < 200 Then dWidth = 200
        dLeft = ActiveSheet.Columns(.Panes(1).ScrollColumn).Left + dWidth / 2
      End If
      If dLeft < 40 Then dLeft = 40

      If .SplitRow > 0 Then
        If ActiveSheet.Range("A1").Resize(.SplitRow).Height > dHeight Then
          dHeight = ActiveSheet.Range("A1").Resize(.SplitRow).Height / 2
          If dHeight < 125 Then dHeight = 125
          dTop = ActiveSheet.Rows(.Panes(1).ScrollRow).Top + dHeight / 2
        Else
          dHeight = dHeight - ActiveSheet.Range("A1").Resize(.SplitRow).Height / 2
          If dHeight < 125 Then dHeight = 125
          dTop = ActiveSheet.Rows(.Panes(.Panes.Count).ScrollRow).Top + dHeight / 2
        End If
      Else
        If dHeight < 125 Then dHeight = 125
        dTop = ActiveSheet.Rows(.Panes(.Panes.Count).ScrollRow).Top + dHeight / 2
      End If
      If dTop < 25 Then dTop = 25

    End With
    Set wsPosition = ActiveSheet
  Else
    ' Position specified
    With PositionRange
      dLeft = .Left
      dTop = .Top
      dWidth = .Width
      dHeight = .Height
      Set wsPosition = .Parent
    End With
  End If

  With DataRange
    Set rValues = .Offset(HeaderRows, HeaderColumns) _
        .Resize(.Rows.Count - HeaderRows, .Columns.Count - HeaderColumns)
    Select Case DataOrientation
      Case xlColumns
        nSrs = .Columns.Count - HeaderColumns
        Set rNames = .Offset(, HeaderColumns).Resize(HeaderRows, nSrs)
        Set rCats = .Offset(HeaderRows) _
            .Resize(.Rows.Count - HeaderRows, HeaderColumns)
      Case xlRows
        nSrs = .Rows.Count - HeaderRows
        Set rNames = .Offset(HeaderRows).Resize(nSrs, HeaderColumns)
        Set rCats = .Offset(, HeaderColumns) _
            .Resize(HeaderRows, .Columns.Count - HeaderColumns)
    End Select
  End With

  ' NEED ACTIVE CELL NOT IN PIVOT TABLE WHEN CREATING CHART
  If Not rActive Is Nothing Then
    ActiveSheet.Columns(1).Cells(ActiveSheet.Rows.Count).Select
  End If

  Set cht = wsPosition.ChartObjects.Add(dLeft, dTop, dWidth, dHeight).Chart
  ' probably blank, but remove all series just in case
  For iSrs = cht.SeriesCollection.Count To 1 Step -1
    cht.SeriesCollection.Count(iSrs).Delete
  Next

  ' add series from data range
  For iSrs = 1 To nSrs
    Set Srs = cht.SeriesCollection.NewSeries
    With Srs
      Select Case DataOrientation
        Case xlColumns
          .Values = rValues.Columns(iSrs)
          .XValues = rCats
          .Name = "=" & rNames.Columns(iSrs).Address(ReferenceStyle:=xlR1C1, External:=True)
        Case xlRows
          .Values = rValues.Rows(iSrs)
          .XValues = rCats
          .Name = "=" & rNames.Rows(iSrs).Address(ReferenceStyle:=xlR1C1, External:=True)
      End Select
    End With
  Next
  cht.ChartType = ChartType

  Set PT_Plotter_Chart = cht

  ' restore original window configuration
  ActiveWindow.ScrollRow = rScroll.Row
  ActiveWindow.ScrollColumn = rScroll.Column
  If Not rActive Is Nothing Then
    DataRange.Select
    rActive.Activate
  End If

  Application.ScreenUpdating = bScreenUpdating

End Function

Contents: How to Build an Excel Add-In

.Name = “=” & rNames.Columns(iSrs).Address(ReferenceStyle:=xlR1C1, External:=True)

 

Peltier Tech Chart Utility

Peltier Tech Charts for Excel 3.0

 

Create Excel dashboards quickly with Plug-N-Play reports.