Variable not defined error only on exit from Excel

**Vindaloo** · 09-30-2006, 04:13 PM

Hi,

I'm getting an error that only occurs when exiting from Excel. The error is Compile error: Variable not defined for the following line:

Please Login or Register  to view this content.

where chkAirsure is a checkbox.

This line is contained within Private Sub cboPostage_Change, where cboPostage is a combobox on the same sheet. Basically the idea is that the checkbox is enabled or disabled depending on the value selected in the combobox. It all works perfectly when changing the value in the combobox, but if I exit Excel whilst this sheet is open then I get the error, even if I just open the sheet and then exit Excel immediately without changing anything (why should this subroutine even be executing in this case?)

I have Option Explicit at the top of the code - removing this gets rid of the problem, but I want to keep it in for obvious reasons and sort out the problem properly.

Any help greatly appreciated,
Vindaloo

**Mallycat** · 09-30-2006, 06:18 PM

mmm, not sure. If the problem goes away when you remove Option Explicit, it would suggest you haven't dimentioned the object. I haven't uses checkboxes so I'm not even sure what the object type is, but try adding
DIM chkAirsure as object

**Vindaloo** · 10-01-2006, 06:58 AM

Thanks for the reply.

I wouldn't expect to have to dimension the object as it already exists. As it stands at the moment the checkboxes and combobox work perfectly well - it's only when I exit Excel with the workbook still open that the problem happens.

Anyway, I tried dimensioning the object and got the error Object variable or With block variable not set.

I've spent a bit more time looking at this - I have 3 similar checkboxes that are controlled in the same way. 2 of them produce the error, but when I comment these 2 out in the cboPostage_Change code the third one works ok! I've compared the properties of these checkboxes and can't find any differences at all, so I'm still stumped.

Note that I don't get this error if I close the workbook first, and then exit Excel.
I am using Excel 2003.

Vindaloo

**Vindaloo** · 10-02-2006, 08:13 AM

I've now removed everything from the workbook apart from the problematic combobox, and the problem still remains. I copied and pasted the combobox, duplicated the code, and the problem doesn't exist for this new combobox!

I've attached the workbook - if anyone can explain what is going on then I'd be most grateful! There's instructions within the sheet to explain how to reproduce the error.

Thanks,
Vindaloo

**Mallycat** · 10-02-2006, 05:57 PM

I have followed the instructions, and haven't been able to reproduce the error. Ie it is working error free on my PC. Maybe there is something else in your setup that is causing the problem. Do you have a copy of some code in another book that could be causing the problem?

**Bryan Hessey** · 10-02-2006, 07:15 PM

Originally Posted by Mallycat

I have followed the instructions, and haven't been able to reproduce the error. Ie it is working error free on my PC. Maybe there is something else in your setup that is causing the problem. Do you have a copy of some code in another book that could be causing the problem?

Hi Mallycat, Vindaloo

The error is caused by 'Option Explicit' and the use of an undefined variable.

Either remove the Explicit option (not reccommended)
or
Dim chktest

----

**Vindaloo** · 10-03-2006, 05:13 AM

Thanks for the replies.

Bryan, removing the Option Explicit under normal circumstances would ignore the error, but won't solve it. However in this case when I remove it I get run time error 424 object required. The variable is not undefined - the combobox and checkbox work fine all the time the workbook is open - it's only when I exit Excel that the problem occurs.
I originally had this problem on my PC at home, and I created the example workbook and tested it at work on a different pc, and still got the same problem - therefore it can't be another workbook causing it.

I've now resolved it by copying and pasting the combobox, deleting the original, and renaming the copy to the same name as the original. Everything is therefore exactly how it was before, but the problem has gone. This leads me to believe that it was some kind of internal error that couldn't be resolved by usual means.

Thanks for the help,
Vindaloo

**Bryan Hessey** · 10-03-2006, 05:51 AM

Originally Posted by Vindaloo

Thanks for the replies.

Bryan, removing the Option Explicit under normal circumstances would ignore the error, but won't solve it. However in this case when I remove it I get run time error 424 object required. The variable is not undefined - the combobox and checkbox work fine all the time the workbook is open - it's only when I exit Excel that the problem occurs.
I originally had this problem on my PC at home, and I created the example workbook and tested it at work on a different pc, and still got the same problem - therefore it can't be another workbook causing it.

I've now resolved it by copying and pasting the combobox, deleting the original, and renaming the copy to the same name as the original. Everything is therefore exactly how it was before, but the problem has gone. This leads me to believe that it was some kind of internal error that couldn't be resolved by usual means.

Thanks for the help,
Vindaloo

Good to see. I was able to reproduce your error as you described with your attachment, and avoided the error by Dim'ing the variable, but, whatever works for you is always good. Thanks for your response.

Cheers.
---

**Vindaloo** · 10-03-2006, 06:00 AM

...and avoided the error by Dim'ing the variable

Bryan, would you usually Dim a variable in order to access a checkbox? What would you Dim it as? I'd be interested to see the code you used to get round the problem.

Regards,
Vindaloo

**Bryan Hessey** · 10-03-2006, 07:39 AM

Originally Posted by Vindaloo

Bryan, would you usually Dim a variable in order to access a checkbox? What would you Dim it as? I'd be interested to see the code you used to get round the problem.

Regards,
Vindaloo

Yes, me too.

Further testing indicates it wasn't the 'Dim' that cured the error, but rather the 'reset' that continued with the close.

Attached shows the two errors I was able to re-porduce, the cure for your error does indeed appear to be to replace that (number one) box.

What did I dim it as? - can't remember, but 'Long' 'String' and 'Integer' are my usual 3 'guesses' in a test situation, however, as indicated above it was erroneus to have thought that prevented the error.

I can replace the box and no error occurs, yet a review of the box options only the '13 lines' would appear to not totally conform.

No other suggestions atm.

---

added, the attachment missed the mail, as it is over the 100kb limit, so imagine a word document screen capture showing the 'Compile Error, Vaiable not defined' and the 'Run-time error '424' - object required' and pm me if you would like a copy.

**Vindaloo** · 10-03-2006, 08:13 AM

Thanks for your time Bryan. I'm going to put this down to an internal error and nothing to do with my workbook, objects or code, considering I couldn't reproduce it from scratch.

Regards,
Vindaloo

**Bryan Hessey** · 10-03-2006, 05:10 PM

Originally Posted by Vindaloo

Thanks for your time Bryan. I'm going to put this down to an internal error and nothing to do with my workbook, objects or code, considering I couldn't reproduce it from scratch.

Regards,
Vindaloo

Just a thought, since Matt couldn't reproduce the error, what system/Excel is being used, ie, could this be a '97 generated item which now errors in 2003 versions and requires the object to be re-gennerated.

I am using XP Prof 2002 SP 2 with Excel 2003 (11.6560.6568) SP2

How about yourself and Matt ?

---