Array() function whacks out Excel 2007
 

I'm developing in Excel 2003 a workbook for wide public distribution.
One tester using Excel 2007 on either of two Windows Vista Enterprise
PCs finds that, whenever he opens my workbook and enables the VB
content, he is immediately thrown into the VB editor with the modal
dialog box: "Compile error: Can't find project or library." The code line:
Indx = Array(...
in the following routine is highlighted. And indeed, when I replace
this much-used function with a dummy version, the user has no more troubles.

When this error appears, there appears to be no option other than to
kill Excel using the Task Manager. It is unable to function after the
error occurs. A Google search on this error suggests that one resolve
an external reference in the usual fashion. But Tools/References is
always grayed out. (Perhaps because this routine is called so many
times. Perhaps I should create a new workbook where this routine is
called only once. Maybe then Tools/References... will NOT be grayed out
after I reset the routine.)

This routine does not offend any of the many Excel 2003 on Windows XP
instances that we have tested, and also runs on two other Excel 2007
installations on Windows XP.

Am I missing anything obvious?

Many thanks in advance.

Tom Kreutz


Function GDP_A(year)
Indx = Array(15.51,16.37,16.36,_
16.49,17.63,18.01,18.24,18.43,18.71,19.36,20.04,20 .51,20.75,_
21.04,21.28,21.57,21.8,22.13,22.54,23.18,23.9,24.9 2,26.15,_
27.54,28.92,30.17,31.85,34.72,38.01,40.2,42.76,45. 76,49.55,_
54.06,59.13,62.74,65.21,67.66,69.72,71.27,73.2,75. 71,78.57,_
81.61,84.46,86.4,88.39,90.27,92.12,93.86,95.42,96. 48,97.87,_
100#,102.4,104.19,106.41,109.46,113.01,116.57,119. 674)
Const Ymin = 1947,Ymax = 2007
GDP_A = Indx(year - Ymin)
End Function

Array() function whacks out Excel 2007
 

Did you copy/paste the code (**always** a good idea) you showed us or did
you type it into your message manually? I ask because you are missing a
space in front of the continuation character on each line of the Array
function.

Rick


"Tom Kreutz" wrote in message
...
I'm developing in Excel 2003 a workbook for wide public distribution.
One tester using Excel 2007 on either of two Windows Vista Enterprise PCs
finds that, whenever he opens my workbook and enables the VB content, he
is immediately thrown into the VB editor with the modal dialog box:
"Compile error: Can't find project or library." The code line:
Indx = Array(...
in the following routine is highlighted. And indeed, when I replace this
much-used function with a dummy version, the user has no more troubles.

When this error appears, there appears to be no option other than to
kill Excel using the Task Manager. It is unable to function after the
error occurs. A Google search on this error suggests that one resolve an
external reference in the usual fashion. But Tools/References is always
grayed out. (Perhaps because this routine is called so many times.
Perhaps I should create a new workbook where this routine is called only
once. Maybe then Tools/References... will NOT be grayed out after I reset
the routine.)

This routine does not offend any of the many Excel 2003 on Windows XP
instances that we have tested, and also runs on two other Excel 2007
installations on Windows XP.

Am I missing anything obvious?

Many thanks in advance.

Tom Kreutz


Function GDP_A(year)
Indx = Array(15.51,16.37,16.36,_
16.49,17.63,18.01,18.24,18.43,18.71,19.36,20.04,20 .51,20.75,_
21.04,21.28,21.57,21.8,22.13,22.54,23.18,23.9,24.9 2,26.15,_
27.54,28.92,30.17,31.85,34.72,38.01,40.2,42.76,45. 76,49.55,_
54.06,59.13,62.74,65.21,67.66,69.72,71.27,73.2,75. 71,78.57,_
81.61,84.46,86.4,88.39,90.27,92.12,93.86,95.42,96. 48,97.87,_
100#,102.4,104.19,106.41,109.46,113.01,116.57,119. 674)
Const Ymin = 1947,Ymax = 2007
GDP_A = Indx(year - Ymin)
End Function