Programming a Chart and VBA 2003, 2007 and 2010

[ErikJan]

I have an unanswered separate thread on this but it's getting more complex and larger so i hope it's OK to re-do some of this.

First of all; I developed code which sets-up a chart on a sheet; it's a little bit special as I'm e.g. adding a textbox (and position that in the upper right corner of the plot area) and do some other stuff.

When I went to Excel 2007, suddenly there were errors in the sheet. I tried to get help to fix them but when I did that @ home using Excel 2010 and then tried that here @ work in 2007 it failed again...

So... here I am now focusing on 2007 (not 2010! --- someone who could help me find all the VBA differences between VBA 2003, 2007 and 2010 would also be appreciated).

I have several issues; hope it's OK if I do them one by one.

(1) My secondary Y-axis label is "tan(d)" where the "d" should be a Greek 'delta' sign. Here's pieces from my 2003 code:

Code: Select all

        With .Axes(xlValue, xlSecondary)    'tan(delta)
            .HasTitle = True
            With .AxisTitle
                .Characters.Text = "tan(d)"
                .Font.Name = "Arial"
                .Font.Size = 10
                With .Characters(Start:=5, Length:=1).Font
                    .Name = "Symbol"
                End With

This does not work in 2007... The way I normally do this is simply recording a Macro when I do the actions manually. Now I CAN do this manually but when I check the recorded VBA code, there is nothing that makes the text use a different font for the delta.... So how do I do this?

[HansV]

Do you get an error message? If so, what does it say?
(Your code works OK for me in Excel 2010; I don't have Excel 2007 anymore, so I can't test there)

[ErikJan]

Nope, no error messages on this one. All that i see is that it changes the whole string to "Symbol" font...

[HansV]

That's strange. I hope that someone who has Excel 2007 will test it.

[Don Wells]

That's strange. I hope that someone who has Excel 2007 will test it.

On both my 2003 and 2007 machine, your code produces:

2011-09-20_2004.png

Thanks to support from Hans, I would suggest that you try the following code:

Code: Select all

Public Sub Test()
Dim str As String

  str = "tan(" & ChrW(&H394) & ")"

  ActiveSheet.ChartObjects("Chart 1").Activate
  With ActiveChart
    With .Axes(xlValue, xlSecondary)
      .HasTitle = True
      With .AxisTitle
        .Font.Name = "Arial"
        .Font.Size = 10
        .Characters.Text = "tan(" & ChrW(&H394) & ")"
        End With
      End With
    End With
End Sub

It works properly on both 2003 and 2007.

[Becks]

My version of Excel 2007 replicated ErikJan's behaviour. To get the lower case delta, use Don's code but use ChrW(&H4B4)
version: Excel 2007 (12.0.6504.5001) SP2 MSO (12.0.6554.5001)

Regards
Kevin

[ErikJan]

OK, that one works (note that I needed a small-delta, so I ended up using: "tan(" & ChrW(&H3B4) & ")" )

So here's a next problem which I solved (see below, but I'm sharing anyway)... I'm writing a text box into the plot area of a chart:

Code: Select all

        ActiveChart.Shapes.AddLabel(msoTextOrientationHorizontal, CornerX, CornerY, 100#, 100#).TextFrame.Characters.Text = <some variable text sting>
        ActiveChart.Shapes("Text Box 1").Select

In 2003 this worked. In 2007, the text box is added OK, but when I select it, is shows no name in the Excel sheet name aera!. So when I try to do something with it (positioning in the chart for example); I can't get to it anymore. Consequently, I get an error on the second line as there is no "Text Box 1".

So I selected the box manually and then did "? Selection.name" in the immediate pane in the VBE editor... there I found the name was "TextBox 1" (so no space between Text and Box) anymore... When I change the slect statement above, the error disappears...

There might be more, but I'll leave this update here for "digestion"

Note: I have contacted MS and received some info on the differences between VBA2003, 2007 and 2010. I'll check it out and when helpful, I'll post here!

[HansV]

There are several ways you can handle this. For example:

Code: Select all

        With ActiveChart.Shapes.AddLabel(msoTextOrientationHorizontal, CornerX, CornerY, 100#, 100#)
            .TextFrame.Characters.Text = <some variable text string>
            ' Set other properties here
            ...
        End With

or

Code: Select all

        Dim shp As Shape
        Set shp = ActiveChart.Shapes.AddLabel(msoTextOrientationHorizontal, CornerX, CornerY, 100#, 100#)
        shp.TextFrame.Characters.Text = <some variable text string>
        ' Set other properties here
        ...

You can set the name yourself:

Code: Select all

        ActiveChart.Shapes.AddLabel(msoTextOrientationHorizontal, CornerX, CornerY, 100#, 100#).Name = "MyLabel"
        ActiveChart.Shapes("MyLabel").TextFrame.Characters.Text = <some variable text string>
        ' Set other properties here
        ...

[ErikJan]

OK, the third mystery (note that in 2003 I always manually recorded macros and learned from that, I am finding out that not everything is recorded anymore in 2007 - including this one).

Still placing my textbox in the plot area

Code: Select all

        ActiveChart.Shapes.AddLabel(msoTextOrientationHorizontal, CornerX, CornerY, 100#, 100#).TextFrame.Characters.Text = <variable text>
        ActiveChart.Shapes("TextBox 1").Select
        With Selection
            .ShapeRange.Fill.ForeColor.SchemeColor = 22
            .ShapeRange.Fill.BackColor.SchemeColor = 23
            .ShapeRange.Fill.TwoColorGradient msoGradientHorizontal, 1
            .AutoScaleFont = False
            .Font.FontStyle = "Regular"
            .Font.Size = 10
            .Left = CornerX + 140 - .Width - 31    'correct for real width + add margin
        End With

In 2003, I did not use 100#, 100# as size when I created the box but just 0, 0. Then when I put my variable text in the box, it resized automatically and in the last line I could adjust the left margin based on the new width of the textbox.
In 2007, the 0,0 doesn't work anymore so I plugged in an arbitrary 100, 100.

Now I see that the box doesn't autofit and I need that to find out the width (which in turn I need to position the box). Luckily there is an Autofit option, and that works when I do this manually. So... I recorded it but nothing!!!
I explored the properties of ActiveChart.Shapes("TextBox 1") and there is no autoshape or autofit... yet i can do this manually... anyone???

[ErikJan]

Like your examples Hans, I'll implement that, thank you (and yes, it does make much more sense like that!)

[HansV]

Now I see that the box doesn't autofit and I need that to find out the width (which in turn I need to position the box). Luckily there is an Autofit option, and that works when I do this manually. So... I recorded it but nothing!!!
I explored the properties of ActiveChart.Shapes("TextBox 1") and there is no autoshape or autofit... yet i can do this manually... anyone???

See my reply Post 59565 in an earlier thread, in particular the line that sets the AutoSize property of the TextFrame.

[ErikJan]

Code: Select all

        ActiveChart.Shapes.AddLabel(msoTextOrientationHorizontal, CornerX, CornerY, 100#, 100#).Select
        With Selection
                .ShapeRange.TextFrame.Characters.Text = "Max(" & Tan_d & ") = " & _
                                             Format(MaxT, "##.##") & TUnit
            .ShapeRange.TextFrame.AutoSize = msoAutoSizeShapeToFitText

I get errors on the first ShapeRange AND the second one (in 2007)

[HansV]

How about

Code: Select all

        With ActiveChart.Shapes.AddLabel(msoTextOrientationHorizontal, CornerX, CornerY, 100#, 100#)
            .TextFrame.Characters.Text = "Max(" & Tan_d & ") = " & _
                                             Format(MaxT, "##.##") & TUnit
            .TextFrame.AutoSize = msoAutoSizeShapeToFitText

(You'll have to adjust the code further down)

[ErikJan]

Code: Select all

        With ActiveChart.Shapes.AddLabel(msoTextOrientationHorizontal, CornerX, CornerY, 100#, 100#)
            .TextFrame.Characters.Text = "Max(" & Tan_d & ") = " & Format(MaxT, "##.##") & TUnit
            .TextFrame.AutoSize = msoAutoSizeShapeToFitText
            .Fill.ForeColor.SchemeColor = 22
            .Fill.BackColor.SchemeColor = 23
            .Fill.TwoColorGradient msoGradientHorizontal, 1
            .AutoScaleFont = False
            .Font.FontStyle = "Regular"
            .Font.Size = 10
            '.Font.Background = xlOpaque
            '.Characters(Start:=9, Length:=1).Font.Name = "Symbol"
            .Left = CornerX + 140 - .Width - 31    'correct for real width + add margin
        End With

Almost there, now I fail in at the AutoScaleFont line and below... I can't see what to place in front, .TextFrame does not seem to work

[HansV]

AutoScaleFont has been deprecated in Excel 2007, I'd leave it out.

[ErikJan]

But Font.FontStyle and Font.Size also fail...

[HansV]

Try

Code: Select all

            .TextFrame.Characters.Font.FontStyle = "Regular"
            .TextFrame.Characters.Font.Size = 10

[ErikJan]

Works! I'm happy and will continue testing! You prove again that the city of Leiden has brought many brilliant people to this world :-)