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

• 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