Compile Error: variable not defined

[JHop]

I just distributed a new version of a complex workbook with lots of VBA code
to users who had been running an earlier version. I made some changes to the
code, tested the new version rigorously before sending it out, and it ran
fine. Now at least 2 users are reporting that they get a "Compile error:
variable not defined" when they try to run the main macro. I rechecked my
copy and ran it without error. Then I opened the copy they sent back to me
and ran it without error! I tried compiling both copies and they compiled
without error. So it works for me but not them--and they had successfully
run the prior version, so it doesn't seem like it would be something in their
environments. What could possibly be causing this error?

Judy Hopkins

 

[OssieMac]

Did you distribute it as an entire workbook or did the users have to copy the
code into another workbook? If the latter, then they could have 'Require
variable declaration' turned on in VBA Options which in turn places 'Option
Explicit' at the top of each module in VBA when it is initially opened.

With your copy, ensure that Option Explicit is at the top of each module and
try compiling the code again from the Debug menu option and it will probably
show you what variable is not defined.

If this is not the problem then I am fresh out of ideas.

 

[OssieMac]

Something else I just thought of and I cannot remember all the details. I
believe that I have seen a similar problem where a user does not have a
specific addin installed that is required for a specific function to work and
VBA thought that the function being used was a variable. Therefore check what
addins you have installed and what the other users have installed.

 

[JHop]

Dear OssieMac--
Thanks so much! I'm sure this is it, though I can't ask the users to check
until Monday. The macros require the Analysis ToolPak, and if they've
installed a new version of Excel recently, they probably didn't do that. I
did double-check to make sure each module is headed by Option Explicit, and
it is. I have always had "require variable declaration" checked.

 

[OssieMac]

Hi again Judy,

I have just found something else that was giving me a problem and returns
the same error you are getting so thought I should share it with you.

The below code example directly out of xl2007 VBA helps demonstrate it.
(Slightly different to the example in xl2002 but principle is the same.)

For the following code to work you must select Tools-> References in the VBA
Editor and check Microsoft Office n.0 Object Library
Where
n = 12 for xl2007
n = 11 for xl2003
n = 10 for xl2002
n = 9 for xl2000

If the above is not checked, then you get the error with msoFileDialogOpen
and even if you declare that variable, the code still fails.

In xl2002 I found that it was necessary to close Excel and re-open it after
selecting the option before it would work. In xl2007 this was not necessary.

Sub UseFileDialogOpen()

Dim lngCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

End Sub

 

[JHop]

Thanks for this additional info. I'll file it away for possible future use.
But the solution to my problem turned out to be different. Once I got the
exact location of the break from the user, it was easy to Google and fix. In
another news group I learned that the Excel constant xlSortNormal was not
available in Excel 2000 and earlier. (I had added a new sort routine and got
the Sort statement from the macro recorder, which included
"DataOption1:=xlSortNormal")