PDA

View Full Version : Solved: Format(i, "00") works in 2003, bad in '07



jwise
10-05-2007, 07:43 AM
I sent a co-worker my spreadsheet that has a macro. It has a statement:
shName = "PNL" & Format(i, "00")

This works fine on my Excel 2003. He uses Excel 2007 and the macro execution is halted at this statement, indicating that there is no "Format" function.

My gut feel is that there is some install option that is wrong if you are going to use macros, but since I don't have '07, I don't know. Has anyone seen this?

rory
10-05-2007, 07:58 AM
It almost certainly means there is a reference missing on his machine. Open the workbook on his machine, switch to the VBEditor, check under Tools-References for anything that starts with 'MISSING:'

jwise
10-05-2007, 08:08 AM
There were several "Missing". "atpvbaen" (maybe misspelled!) was among them. Why is this stuff not installed? Is there some option that was specified wrong? I never saw this kind of option when I installed. I have not installed 2007, but I installed 2003 twice recently, and probably two or three other times as well.

rory
10-05-2007, 08:11 AM
That's the Analysis Toolpak VBA add-in. In 2007, amongst all the other changes, the worksheet functions from the ATP were built into the main Excel program. Any standard Excel/Office references should be forwards compatible for the most part, but references to third party controls/libraries or things like ADO may not be.

jwise
11-08-2007, 10:25 AM
Thanks again for the help