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