Microsoft Excel & VBA

This article is not so about MS Excel however it's indirectly related to. My colleague asked me how to quick change decimal places in embedded charts in the PowerPoint application. I did a little research and I didn't find any solution for PowerPoint 2007 or 2010. I think Powerpoint doesn't have any button for doing that directly from Ribbon so I came up with my own Add-in.

See on the picture bellow for getting overview.



It's embedded chart from MS Excel which is editable after double click or from the Ribbon. Usually, one presentation contains more than 100 embedded charts and updating these charts is semi-automatic. Each quarter, new data are inserted and the source area is changed to match the newest data. The problem come up when user wants to change number of decimal places for a series. The number of decimal places are also dependent on client because each of them prefers different number of decimal places. How does it now is displayed on the next picture bellow



Advanced user can say that the "Linked to source" option can do that the same. Yes that's true, if we select the option then the number of decimal places will be based on number of decimal places in the source worksheet where we have the standard way how change the decimal places. My colleague prefers to change number of decimal places via button on the Ribbon menu because of there are plenty existing charts and also you have to edit source data = open source worksheet for any change.

As you can see, it's necessary to select a serie, then right click, then select "Data Format Label", select tab "Number" and set number of decimal places. For next serie you can use key F4 for repeating the last action but according to my colleague it doesn't work good in the Powerpoint 2010.
So I created an Add-in that changes the number of decimal places in loop according the following logic

Origin format    New format
     0                           0.0
     0.0                        0.00
     0.00                      0



The Add-in's function will work only for embedded charts and for all series at once because I wasn't able to get from VBA which serie has been selected. Also an embedded chart has to be selected!
Download the Add-in.

VBA code

Option Explicit

'********************************************************************************
'ChangeDecimalPlaces
'
' Purpose:  Changes decimal places for selected shape.
'           The selected shape must be a Chart then goes through all series
'           and DataLabels and changes decimal places according to the first
'           DataLabel in the first serie
'
' Inputs:   -none-
'
' Outputs:  -none-
'
' Created:  01/26/2011  Premysl Lazecky
'
' Modified: .
'
'********************************************************************************
Sub ChangeDecimalPlaces()
    
    Dim objChart    As Chart
    Dim objSerie    As Series
    Dim objLabel    As DataLabel
    Dim i           As Integer
    Dim j           As Integer
    Dim FirstFormat As String
    
    FirstFormat = vbNullString
    With Windows(1).Selection
        If .Type <> ppSelectionShapes Then
           GoTo ExitRoutine
        End If
        
         If .ShapeRange.Type <> msoChart And .ShapeRange.Type <> msoPlaceholder Then
            GoTo ExitRoutine
        End If
        
        Set objChart = .ShapeRange.Item(1).Chart
        
    End With
      
    For i = 1 To objChart.SeriesCollection.Count
        Set objSerie = objChart.SeriesCollection(i)
        If objSerie.HasDataLabels Then
           For j = 1 To objSerie.DataLabels.Count
               Set objLabel = objSerie.DataLabels(j)
              
               If Len(FirstFormat) = 0 Then
                  FirstFormat = objLabel.NumberFormatLocal
               End If
              
               Select Case FirstFormat
                    Case Is = "0"
                          objLabel.NumberFormatLocal = "0,0"
                    Case Is = "0,0"
                          objLabel.NumberFormatLocal = "0,00"
                    Case Is = "0,00"
                          objLabel.NumberFormatLocal = "0"
               End Select
              
               Select Case FirstFormat
                    Case Is = "0%"
                          objLabel.NumberFormatLocal = "0,0%"
                    Case Is = "0,0%"
                          objLabel.NumberFormatLocal = "0,00%"
                    Case Is = "0,00%"
                          objLabel.NumberFormatLocal = "0%"
               End Select
              
           Next j
        End If
    Next i
    
    
ExitRoutine:
    Set objChart = Nothing
    Set objSerie = Nothing
    Set objLabel = Nothing
    Exit Sub
    
ErrorHandler:
    Debug.Print "Error Number: " & Err.Number & vbNewLine & "Error Description: " & Err.Description
    GoTo ExitRoutine
End Sub