How to avoid Excel localizing object names and breaking macros?

Question

I have this line of code in my macro:

ActiveSheet.ChartObjects("Chart 6").Activate

When I open the file and run the macro on a non-English version of Excel, this code breaks. For example on the Japanese system I tested it on, I would need to change the previous line to:

ActiveSheet.ChartObjects("グラフ 6").Activate

(グラフ means Chart in Japanese)

This seems ridiculous as these are object names, not meant to be human readable anyway but used to identify objects. Is there any way of it working whatever the language of Excel?

Answer 1

Hi Laurent,

Yes, these names are used for programming purposes, but these names are also visible to the user, so it is an interesting issue... To solve it, you can simply refer to the Chart by it's integer index value.

For example, instead of:

ActiveSheet.ChartObjects("Chart 6").Activate

You could use:

ActiveSheet.ChartObjects(6).Activate

In the above, I assumed that "Chart 6" is indexed by the value 6, but it can really be any number. For example, if you've created and destroyed five previous charts, and this chart is now the only chart on the worksheet, the chart named "Chart 6" would actually be indexed by the integer value one (1).

So you will have to figure out which index number your chart corresponds to, and then use that index value in your code.

Make sense?

Update: Reply to Laurent

As for the name being displayed to the user, what if I named the graph "Motorboat"?

That's it! That's your answer!!

I was about to suggest that you stick to the portion of the name that does not change. For example, "Chart 6" is being changed to "グラフ 6". So, in this case, you should simply loop through all the charts looking for the one that ends in the name "6". But, even better, is to simply avoid words that will be automatically translated. So, instead of naming it, "Chart 6", name it, yes, "Motorboat" or anything else that would not be automatically translated.

Using this approach, instead of:

ActiveSheet.ChartObjects("Chart 6").Activate

You would use:

ActiveSheet.ChartObjects("Motorboat").Activate

Agreed that this is not ideal. There might even be a programmatic code name for charts that I'm not aware of, which, if it exists, would allow you to use any name you want. But, in the absence of such a feature, then using a name for your chart that would not be translated seems to be the best and easiest way to go.

Hope this helps!

Mike

Answer 2

It sounds like you need to call the objects in such a way that you reference the objects themselves and not by referencing the objects via their respective textual names.

One thing that's helped me in cases like this is to use For Each to iterate through a collection of objects. Such as:

Sub DoSomethingWillAllChartObjects()

Dim cs As ChartObject
Dim ws As Worksheet
Dim wb As Workbook

Set wb = ThisWorkbook

For Each ws In wb.Worksheets
    For Each cs In ws.ChartObjects
        Debug.Print cs.Name, cs.Index

    Next cs
Next ws

End Sub

The above code iterates through all charts in all worksheets in the given workbook and allows you to do something to those objects. You don't need to know the name of the chart to do something with that code.

Perhaps what you might do is use For Each to iterate through all items and then add some If ... Then logic to test to see if a particular ChartObject requires action in your code.