Array() function whacks out Excel 2007

[Tom Kreutz]

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.92,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

 

[Rick Rothstein \(MVP - VB\)]

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]

Dear Rick,

I actually massaged/simplified the routine slightly for the newsgroup
posting. The real version is here.

Thanks!

Tom

Function GDP_A(year)
' This function returns the annual US chained GDP deflators, from 1947
to 2007.
' Array Indx holds the index data. Line 1 is the 1940s; line 2 is the
1950-1959...
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.92, 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)
If year < Ymin Or year > Ymax Then GDP_A = -999
End Function

 

[jaf]

Hi Tom,
You can double click on the file while holding down the control key to open
the workbook with macros disabled.
This should allow you to get into the VBA editor and check the references.

John