VBA Chart Axis editing

**theDirtyHarry** · 11-04-2011, 09:47 AM

Hi there,

I am currently trying to transfer my Excel 2003 scipts to my new PC and Excel 2010. I am having few problems though, one of them is the following:

It seems to be no longer possible to format an axis title of a graph partly in superscript. What I want to do is to make Excel create a new graph with VBA, and in this process I also want to define the axis titles. One of them should read "unitname / s-1" where the "-1" part should be superscript. In Excel 2003 this worked fine with the following code:

With ActiveSheet.ChartObjects(i)
With .Chart
With .Axes
 .HasTitle = True
         .AxisTitle.Caption = "unitname / s-1"
         .AxisTitle.Characters(Start:=13, Length:=2).Font.Superscript = True
         .AxisTitle.HorizontalAlignment = xlCenter
      End With
   End With
End With

(Excuse the excessive use of "With" loops, I have several other things going on there in between as well.)
This no longer seems to work. What I have found is that if I set the last command to

.AxisTitle.Characters(Start:=13, Length:=2).Font.Superscript = True

it leads to the whole word (not just 2 characters!) being superscripted. However, this is not what I am looking for. Has anybody an idea what I could do here?

Also, the xlCenter command does not lead to any visible result neither.

Harry

PS: On a side note, I had to find out that obviously it is no longer ok to define the name of a dataset with something like this:

ActiveChart.SeriesCollection(1).Name = ActiveSheet.Range("A1:A3")

In Excel 2010, I can only get this to work with something like that:

ActiveChart.SeriesCollection(1).Name = ActiveSheet.Range("A1")

Is there a workaraound?

**Andy Pope** · 11-04-2011, 10:07 AM

Series name

activechart.SeriesCollection(1).name = "='" & activesheet.name & "'!" & activesheet.range("A1:a3").address

For me the superscript code works in xl2007 and xl2010. I'm sure this was an issue at one point so it maybe worth checking you have the latest patches and service packs applied.

**theDirtyHarry** · 11-07-2011, 06:29 AM

Hello Andy,

Thanks for answering my post.

I am using a corporate PC, therefore I have no option to manually force updates to install. As far as I can see though, it seems as if my versions of Windows and Office are up to date.

Concerning your code snippet I am afraid I cannot understand it fully. What should I use the Series name for? Could you please refine your answer?

Thanks a lot!

**Andy Pope** · 11-07-2011, 06:55 AM

So has your IT dept. updated you to SP1 fr Excel 2010?

Where you have a problem using multiple cells for the name range try using the code I posted instead of the code you posted, which was.

ActiveChart.SeriesCollection(1).Name = ActiveSheet.Range("A1:A3")

**theDirtyHarry** · 11-07-2011, 10:08 AM

I used

activechart.SeriesCollection(1).name = "='" & activesheet.name & "'!" & activesheet.range("A1:a3").address

as you suggested and it works like a charm. Thank you very much!
The problem with superscripting is persistent though. My Excel version number starts with 14.0.6106 so I suppose I am using a post SP1 version.

I did find a workaround in the meantime, but it is not entirely satisfying.
If I use

With ActiveSheet.ChartObjects(i)

    With .Chart

        With .Axes

            .HasTitle = True .AxisTitle.Caption = "unitname / s-1"
            .AxisTitle.HorizontalAlignment = xlCenter

        End With

        .Axes(xlValue, xlPrimary).AxisTitle.Select
        Selection.Format.TextFrame2.TextRange.Characters(14, 2).Font.BaselineOffset = 0.3

    End With

End With

I get the results I want. As you can see I am no longer using .Font.Superscript but .BaselineOffset. I found this when I recorded a macro and used it in my VBA code. This is not what seems to be the decisive change though - if I use it in the same With loop as I used .Font.Superscript in my initial post, it does not work either. Only if I place the command out of the innermost With loop the appropriate characters get superscripted, as intended. However, the problem with this is that now I superscript characters after aligning the axis title with

 .AxisTitle.HorizontalAlignment = xlCenter

and while this may not be a visible problem when only superscripting a few characters, it might get irritating as soon as a larger number is superscripted and therefore the dimension of the axis name is getting shorter. This is nitpicking, I know, but I am still curious where this behaviour might come from.

On another side note, changing the value of .AxisTitle.HorizontalAlignment seems not to really have an effect either. Neither using xlTop nor xlJustify changed the appearance of my graphs. I am slowly getting afraid my whole VBA code is pretty useless as a whole...

**Andy Pope** · 11-07-2011, 10:49 AM

Post SP1 wil have the text SP1 in full the version details.

The charting engine and OM charts a great deal between 2003 and 2007. Whilst some if not most of the coding will work you will need to test and adapt any chart and or shape code you have.