MicroCharts our Excel sparkline tool enhances Excel with sparklines, bullet graphs and other in-cell charts. As MicroCharts is a font based charting technology you can utilize all the rich Excel text formatting capabilities:

• Text alignment
• Text orientation
• Font size
• Conditional formatting
• Automatic alignment when the row height changes

 
Use text alignment to align MicroCharts in the Excel grid

 

Create vertical MicroCharts using Text Orientation

 
Color-code bars using Conditional Formatting

The downside of the font based charting is that you have to redistribute the fonts, otherwise end users see weird Chinese characters in their reports.

We usually recommend our users either:

1) Redistribute the free MicroChart fonts. With this approach, prior to distribution, the owner uses the ‘Convert to Font Charts’ option on a copy of the report and distributes this version. This which works well for static reports. Note that if there is any interactivity through drop down menus which can change the MicroCharts then Excel based recipients will need MicroCharts itself installed.

2) Publish the reports to XLCubed Web Edition. Here the reports and MicroCharts can remain interactive, and the end user does not require any client side installation or fonts on their local pc. There is also the benefit that the reports can connect directly to cubes or relational databases.

With the font based approach, experience shows often end users won’t install the fonts, and send emails to the report author asking about the weird characters.

To overcome this problem you can write some VBA code to test if the MicroChart fonts are installed. John Walkenbach has some nice VBA code on his site that allows us to test if a font is installed:

Function FontIsInstalled(sFont) As Boolean
‘   Returns True if sFont is installed
    FontIsInstalled = False
    Set FontList = Application.CommandBars("Formatting").FindControl(ID:=1728)
‘   If Font control is missing, create a temp CommandBar
    If FontList Is Nothing Then
        Set TempBar = Application.CommandBars.Add
        Set FontList = TempBar.Controls.Add(ID:=1728)
    End If
    For i = 0 To FontList.ListCount – 1
        If FontList.List(i + 1) = sFont Then
            FontIsInstalled = True
            On Error Resume Next
            TempBar.Delete
            Exit Function
        End If
    Next i

‘   Delete temp CommandBar if it exists
    On Error Resume Next
    TempBar.Delete
End Function

To test if MicroCharts are installed you call FontIsInstalled("Micro Line Charts 3.0") and if the test fails you navigate to the URL of the MicroCharts font installer package:

Sub TestMicroChartsFonts()
  If Not FontIsInstalled("Micro Line Charts 3.0") Then
    MsgBox "The MicroChart fonts are not installed on your computer. " & Chr(13) & "Open MicroChartsFonts.zip, run the font installer (SparkLinerFontSetup.msi) to install the MicroChart fonts."

    Set wshshell = CreateObject("WScript.Shell")

    wshshell.Run http://www.bonavistasystems.com/Download2/MicroChartsFonts.zip
  End If
End Sub

Finally copy the Micro Chart test code in the report workbook and call it in the Workbook_Open handler:

Private Sub Workbook_Open()
  TestMicroChartsFonts
End Sub