title: Chart data label hell
body: <p>I'm running into a rather aggravating issue with a macro that scales the font size in charts. The way the larger tool works is, our company has two different 16x9 templates -- one using 10x5.62 dimensions and one using 13.33x7.5 dimensions. When slides from these two templates are combined, PowerPoint does a lousy job scaling the content.</p>
  
  <p>So the overall tool, which is working great except for this one problem, makes a copy of the active presentation, changes its dimensions to 10x5.62, and then goes through every shape in the presentation and makes position/size and font size exactly 75% of the original presentation. I'm able to assume A/B comparisons between all shapes because the Source and Destination presentations are identical except for the page size.</p>
  
  <p>The one problem is that when the tool is handling charts -- <em>some</em> charts (but not all or even most) are reporting an incorrect font size for certain elements. i.e., the size appears to be set to 13.3 in the user interface, but the object model is reporting 18. As a result, when the multiplication of the font size takes place, the new font size is too large. The full tool handles chart titles, axis titles, legend items, and data labels all separately -- but for simplicity's sake, I'm just including the sub that handles chart labels:</p>
  
  <pre><code>Sub RefontsizeChartLabels(dChrt As Chart, sChrt As Chart)
  
  On Error GoTo Errhandler
  
  Dim i As Integer
  Dim j As Integer
  
  Dim dSeriesVar As Series
  Dim sSeriesVar As Series
  Dim dDataLabelsVar As DataLabel
  Dim sDataLabelsVar As DataLabel
  Dim dPointVar As Point
  Dim sPointVar As Point
  Dim destRange2 As TextRange2
  Dim sourceRange2 As TextRange2
  
  Dim isAutoText As Boolean
  
  
  For i = 1 To dChrt.SeriesCollection.Count
      Set dSeriesVar = dChrt.SeriesCollection(i)
      Set sSeriesVar = sChrt.SeriesCollection(i)
      For j = 1 To dSeriesVar.Points.Count
          Set dPointVar = dSeriesVar.Points(j)
          Set sPointVar = sSeriesVar.Points(j)
          If dPointVar.HasDataLabel = True Then
              Set sDataLabelsVar = sSeriesVar.DataLabels(j)
              Set dDataLabelsVar = dSeriesVar.DataLabels(j)
              isAutoText = dDataLabelsVar.AutoText
              Set destRange2 = dDataLabelsVar.Format.TextFrame2.TextRange
              Set sourceRange2 = sDataLabelsVar.Format.TextFrame2.TextRange
              RefontsizeChartShapeRange destRange2, sourceRange2
              dDataLabelsVar.AutoText = isAutoText
          End If
      Next
  Next
  
  Exit Sub
  
  Errhandler:
      Debug.Print "Error: " &amp; Err.Description
  
  End Sub 
  </code></pre>
  
  <p>Just for completeness, you can see that the RefontsizeChartShapeRange sub is doing a simple multiplication operation on the .Font.Size property</p>
  
  <pre><code>Public Sub RefontsizeChartShapeRange(destRange2 As TextRange2, sourceRange2 As TextRange2)
  
  Debug.Print "IN_RefontsizeChartShapeRange"
  On Error GoTo Errhandler
  
  Dim i As Long
  
  With destRange2.Font
      .Size = sourceRange2.Font.Size * scaleConstant
  End With
  
  Exit Sub
  
  Errhandler:
  Debug.Print "Error: " &amp; Err.Description
  
  End Sub
  </code></pre>
  
  <p>At first I thought it had something to do with an Autoscale property being set, but messing around with it didn't seem to fix anything. Stepping through the code seems to imply that simply searching for certain chart elements with VBA and/or recording them to variables sets the object model itself out of whack. The business with dDataLabelsVar.AutoText above was a workaround for one issue I was having with the data labels losing their number formatting after changing the font size, but it doesn't fix the issue with the individual labels saying they are 18 points when they appear to be 13.3. </p>
  
  <p>Any help would be much appreciated.</p>