Excel 2007 Data Labels

[Carol Olson]

I have some VBA code that was used in Excel 2003 to reposition some chart
labels. It ran fine in Excel 2003. I'm now running the same code in Excel
2007.

The macro uses the Series Collection Datalabels(index).Left in order to
calculate the position to move the label. I'm encountering some strange
behavior and wondered if anyone else ran into this. If I run the macro as
usual (F5), I get a different value returned from Datalabels(index).Left
versus if I run the macro stepping into it (F8).

In other words, I get the correct result when I step into the macro (F8)
versus running it(F5).

Hope this makes senses.

 

[Jon Peltier]

Why not post the section of code where you adjust the labels? That way we
might have a chance to see what's different.

- Jon

 

[Carol Olson]

Here is the code that runs differently. This is my first time posting to
this forum so any suggestions are helpful. Thanks.

'Apply opposite direction to data labels
Set chtTemp = ActiveChart
With chtTemp
sngMaxLeft = .ChartArea.Width
Debug.Print "SngMaxLeft Chart Area Width is"; sngMaxLeft

With .SeriesCollection(1)
vntValues = .Values 'Returns a Variant value that represents
a collection of all the values in the series.
.ApplyDataLabels AutoText:=True, LegendKey:=False,
ShowSeriesName:=False, _
ShowCategoryName:=True, ShowValue:=False, _
ShowPercentage:=False, ShowBubbleSize:=False
With .DataLabels
.Position = xlLabelPositionInsideBase
.Font.Name = "Arial"
End With
' get left positions for data labels
sngLeftP = .DataLabels(1).Left
Debug.Print "sngLeftP is " & sngLeftP
Debug.Print " Datalabels 1 Left is " & .DataLabels(1).Left
Debug.Print "Datalabels 2 left is " & .DataLabels(2).Left

For intPoint = 1 To .Points.count
If .DataLabels(intPoint).Left > sngLeftP Then
sngLeftN = .DataLabels(intPoint).Left
Debug.Print "sngLeftN is " & sngLeftN
Exit For
'ElseIf .DataLabels(intPoint).Left < sngLeftP Then
ElseIf .DataLabels(intPoint).Left <= sngLeftP Then
sngLeftN = sngLeftP
Debug.Print "sngLeftN is " & sngLeftN
sngLeftP = .DataLabels(intPoint).Left
Debug.Print "sngLeftP is " & sngLeftP
Exit For
End If
Next

' apply opposite position to labels
For intPoint = 1 To .Points.count
'Debug.Print "Points.Count = " & .Points.count
If vntValues(intPoint) >= 0 Then
' calculate data label width and offset
sngLeftP = .DataLabels(intPoint).Left
Debug.Print "A sngLeftP is " & sngLeftP
Debug.Print "B sngMaxLeft is " & sngMaxLeft
.DataLabels(intPoint).Left = sngMaxLeft
Debug.Print "C DataLabels IntPoint Left is " &
..DataLabels(intPoint).Left
.DataLabels(intPoint).Left = (sngLeftN - (sngMaxLeft
- .DataLabels(intPoint).Left))
Debug.Print "D sngLeftN is "; sngLeftN
Debug.Print "E sngMaxLeft is "; sngMaxLeft
Debug.Print "F Left starting point: ";
..DataLabels(intPoint).Left
Else
' used fixed position
.DataLabels(intPoint).Left = sngLeftN
Debug.Print "G sngLeftN is " & sngLeftN
End If
Next

End With
End With

 

[Jon Peltier]

Excel 2007 sometimes takes longer to redraw a chart after changes than Excel
2003 did. When you F8 through the code, there's plenty of time for the
changes to take place, but at F5 speeds, Excel 2007 may not keep up. The
remedy is to insert DoEvents wherever there is a problem. For example, I've
inserted DoEvents into a section of your code, right before you access the
property that is causing problems:

With .DataLabels
.Position = xlLabelPositionInsideBase
.Font.Name = "Arial"
End With
DoEvents
' get left positions for data labels
sngLeftP = .DataLabels(1).Left

You didn't say where in the code the datalabel property was incorrect, I
just picked the first one I encountered.

DoEvents never hurts. All it does is tell VBA to let the system do whatever
it needs to do, like update a display or ring the bell to tell you an email
just came in.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

 

[Carol Olson]

I am going to try that. I actually get a different result each time I use
..DataLabels(1).Left. It starts the first time it is used within the
..SeriesCollection(1). Right after the comment "get left positions for data
labels".

I really appreciate you responding to my post. I've been quite perplexed by
this.

 

[Carol Olson]

I tried putting the DoEvents in right before the first time I find a
difference, which is the same spot as in your example. I still received the
same result as without the DoEvents. When I run using F5, the result of
sngLeftP is 513.0739 but when I F8, the results is 285.0667.

 

[Jon Peltier]

Hmmm. I haven't had to program much in 2007 yet, so I'm guessing.

Do you have Application.ScreenUpdating = False somewhere before this part of
the procedure? It goes against all that is sensible in Excel, but you might
have to change this to true, so Excel 2007 knows where the label goes when
it's allowed to update the screen. Then DoEvents, then set updating to
false, then read the property.

- Jon

 

[Carol Olson]

I had the screen updating on so I decided to try and use the timer function
and force it to pause. This seems to work. I don't know why the DoEvents
would not work. Anyway, you pointed me in the right direction. Thanks for
you help!

Carol