How to make VBA count from 0 and not 1 in Excel Macro

We have written a macro that generates a host of charts and then transfers them to Word. This works as expected in Excel 2007, when accessing shapes like this: Shapes(0)... But in Excel 2开发者_开发百科010 we had to say: Shapes(1). Is there a way of telling Excel to start counting from 0?

---

This is a hangover from a decision by some bright Microsoft Engineer to make VB collection indexes start with 1. I think this was when VB4 was designed.

The justification was that it is "easier" to have indexes running from 1 to N rather than 0 to N-1.

As a result, many COM object models including Excel follow this model, so that collections like Worksheets, Shapes etc are all indexed from 1 to N.

I am surprised at your assertion that Shapes(0) worked as expected in Excel 2007. It certainly didn't in Excel 2003, and it seems unlikely that this would have been changed.

Update

I've tested this in Excel 2007 and Shapes(0) does indeed reference the first Shape in the collection - the same as Shapes(1) as far as I can see. This is a change between Excel 2003 and Excel 2007 for which I can't find any information - I would suspect it's a bug in Excel 2007.

From what you say, it sounds like Excel 2010 has reverted to the same behaviour as Excel 2003 (i.e. the Excel 2007 bug has been fixed). If you want code that works in all versions of Excel, use index numbers from 1 to N.

---

Sounds - from the brief stats shown here - like an Excel 2010 bug.

In general, all scripts written for Excel 2007 ought to run in Excel 2010 unchanged.