"All of a sudden" a range seems to dematerialize

[tfurnivall]

Didn't want to sound over-dramatic, but two lines of code that have been working perfectly for months seem to have acquired a problem.

The lines in question are these:

Please Login or Register  to view this content.

To explain the context, ClassSheet is a worksheet name "Config", which is the current ActiveSheet. ComboCol is a string constant "J".
cboName is a string variable containg "cboOrderClass". This value is located in cell J3 of the Config sheet.

The intent is first to set up a searchable column (J) (the first line), and then to find the place, in that column, where cboOrderClass is found (the second line), and create a range (BoxRange) at that address..

This has been working well for ages, and is a part of my toolbox to store ComboBox values in a configuration Worksheet.

The first line works exactly as intended. In the debugger I can examine any of the attributes of range ComboBox, and they are returned, most importantly the address, which is returned as "$J:$J".

The second line returns the dreaded Run-time error '1004' - Application-defined or object-defined error.

Of course, this aborts the program, so I can't see which object is causing the problem. However, I've just used ClassSheet to set up ComboRange, so ComboBox should survive the transition from one line to the next, right? Also, I can't see if BoxRange is really not being set - even though the value I'm looking for is iin cell J3, which is certainly part of column J.

As I said, this has been working well for months, and now, all of a sudden the ComboRange seems to disappear. (I'm assuming that this is the object where the error is occurring. One known cause of this trying to access a range on an inactive sheet, but the ActiveSheet is Config (shown by Debug) - and we have to access the sheet to create COmboBox (which works) so that should not be the problem.

I'm black and blue from beating my head against a wall - can any one provide balm for my soul (and my ego), and give me a hint?

Thanks,

Tony

I can send the spreadsheet, but these are just two lines of code in several hundred (within the application).

[tfurnivall]

An update.

I omitted to try the Universal Specific and reboot the whole computer.

mirabile dictu the application now works!

This seems to indicate to me that I had a memory leak somewhere which had been filling up my memory with dangling pointers. Any recommendations for tracking this down in VBA code? I try and delete any local objects in the same procedure I declare them, but there are several that hang around for the entire lifetime of the app.

I'll leave this open for a few days, but I think it might be - at least temporarily - fixed.

Tony

[Artik]

The behavior of the code execution itself remains a mystery to me (Err 1004). Nevertheless, I want to point out that the Range.Find method was not called quite correctly.

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don't specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

So, it should be:

Please Login or Register  to view this content.

The SearchOrder argument was omitted because we are searching a single-column range anyway.
However, even not finding the result should, in my opinion, return Nothing, not an error. It seems that something has actually gone wrong with Excel.

Artik

[tfurnivall]

Hi Artik,

mea maxime culpa!

I've done no testing to see if this is truly a single resource across the whole of Excel (and, indeed, all my other uses of Range.Find do provide all parameters), but I could see how that if that is the case, it would cause an Object error (if, say, the previous use was in a different sheet, and a different column, such that there was absolutely zero intersection between the current search column (Config.Classsheet.Columns("J")) and a previous search (typically column "A" of whatever worksheet)

Yes - it's a horrible design decision, and yes, it is documented. (But still....;-)

Reminds me of a large multi-user problem I had on a main-frame computer, about 30 years ago, where the documentation was much more obscure than this Excel instance. Single user testing could not trigger the problem, but within 5 minutes of the system going into beta testing, with 40 users, the problem becamse painfully manifest! It was a one line fix, but it was the most difficult line to own and to insert!

Thanks again. I suspect this was, indeed, a memory leak, but I'll add the other parameters to the two relevant calls.

Take care,

Tony