Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error: variable not defined
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error: variable not defined
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. -- Regards, OssieMac "JHop" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error: variable not defined
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. -- Regards, OssieMac "OssieMac" wrote: 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. -- Regards, OssieMac "JHop" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error: variable not defined
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. -- Judy Hopkins "OssieMac" wrote: 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. -- Regards, OssieMac "OssieMac" wrote: 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. -- Regards, OssieMac "JHop" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error: variable not defined
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 -- Regards, OssieMac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error: variable not defined
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") -- Judy Hopkins "OssieMac" wrote: 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 -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable not Defined Compile error | Excel Programming | |||
Compile error, Variable not defined | Excel Programming | |||
Incremental Numbering: Compile Error - Variable not defined | Excel Programming | |||
Variable not defined compile error | Excel Programming | |||
Compile error, variable not defined | Excel Programming |