VBA changes Excel 2002 -> Excel 2007

Question

Where can I find a complete list of changes in VBA from Excel XP (2002) to Excel 2007? I have a workbook that relies heavily on VBA which I have been working on in 2002, and when I opened it in 2007, I noticed that some things had changed. For instance, my workbook does lots of 3d rotation; I discovered that Shape.ThreeD.RotationX has become Shape.ThreeD.RotationY and vice-versa. I also discovered that I have to negate the rotation values, so that

Shape.ThreeD.RotationX = 90

becomes

Shape.ThreeD.RotationY = -90

I am sure that there are other changes as well, which I have probably missed. I haven't seen the above changes detailed anywhere, so I would like to know:

Which sites detail changes such as the ones I have outline above?

Answer 1

1. Open Excel 2007 and go into the VBA editor (Alt+F11)
2. Open up Help (F1)
3. Click What's New
4. Click Object Model Changes Since Microsoft Office XP (2002)

Voila!

This list is fairly comprehensive as well as interactive, so I won't reproduce it here. But it also has changes since the 97, 2000, and 2003 versions.

Answer 2

Here are some of the changes that I have noticed so far:

Shape.ThreeD.RotationX = 90

has become

Shape.ThreeD.RotationY = -90

(i.e. X and Y axis have been swapped around for 3d rotation on autoshapes, and the positive / negative directions along these axis are the opposite way round).

Excel no longer seems to honour the

Shape.ThreeD.ExtrusionColorType = msoExtrusionColorAutomatic

directive (it just gives a black extrusion, at least on my tests).

ThreeD shapes must now have their lighting direction set explicitly, e.g.

Shape.ThreeD.PresetLightingDirection = msoLightingRight

whereas previously this didn't seem to be necessary.

These are just some of the things that I have discovered so far. I am sure that there are many more - I will add them as I find them (I will try to put things relating to auto-shapes in this answer, and group other changes / problems in separate answers to make it easier to browse).

If you have code that makes use of

Application.ScreenUpdating = False

to speed itself up, you have to explicitly call

Application.ScreenUpdating = True

before showing a MsgBox or InputBox if you want changes to display on screen (whereas 2002 would always refresh the screen before showing any dialog box).

Answer 3

An example:

Create two columns of numbers Start recording Insert scatter chart Stop recording Delete chart Replay macro

What Benjol has said is incorrect. I just tried to reproduce your scenario in Excel 2007, and it does not reproduce. FYI, you can create a macro using the macro recorder that creates a scatter chart based on two columns of numbers and replay that macro to re-create the chart.