Function doesn’t work if code runs? (Ex2003)

Topic

New Reply

I have this line in VBA:

Application.VLookup(“Coded#1”, Range(“CodedItems”), 2)

If I run this in the immediate window before my code runs, it works (and I get the correct cell-value returned). Now I need this in a routine that is called after I change a cell. So I’ve started to set-up a WorkSheet_Change routine (where in the first line, I of course disable Events). Now here’s the strange part… if I set a code stop in this routine (with F9) and then go back to my immediate window and run the same line as shown above again, I get:

Run-time error ‘1004’:

Application-defined or object-defined error

Then I step through the code (there are no errors as I do not yet have the above line inserted) and end it. Now I run the line above again from the immediate window and all works again!!!

Note: I’m NOT changing the “CodedItems” range in the mean time, nor am I even accessing it…

Any idea why this doesn’t work??

Reply | Quote

Replies

Please attach a sample workbook, otherwise we don’t have the slightest idea what you’re doing.

Reply | Quote

OK, you forced 😉 me to do the right thing. I created a small version and then it did work. I figured it out… Where the line works like this when code is not running (regardless of the activesheet selected):

Application.VLookup(“Coded#1”, Range(“CodedItems”), 2)

It appears that if I change it to include the Sheet-reference, it works OK in code too, so the next line solved it:

Application.VLookup(“Coded#1”, Sheets(“MySheet”).Range(“CodedItems”), 2)

Oh well, guess the quality of this board is so good that it’s making me lazy… it’s quicker to ask than try… sorry

Reply | Quote

I was going to suggest that would be the issue. Good that you found it yourself en even better that you let us know!

Reply | Quote

Can you (and anyone else) tell me a bit more about the issue?

I have a sheet which uses a simple UDF, the UDF includes “Application.Volatile”. The UDF is sheet-independent – it operates on a single cell.

When I run code which hides certain duplicate rows against the sheet which uses the UDF, the UDF cells all return “#VALUE!”. I solved this by adding Application.CalculateFull at the end of the routine, but I’m puzzled by why running the code causes the UDF to “error out”, or to be reset in uncalculated status, or whatever happens.

Reply | Quote

To see if your UDF is sensitive to the problem, we need to see the code…

The OP did not qualify what sheet a range in the code belonged to, hence the error when the routine was run when another sheet was active.

To solve your issue, I’d suggest to set calc to manual before starting the hiding and turn it back on afterwards. As of Excel 2003, hiding/showing rows fires a recalculation..

Reply | Quote

Attached. Let me know if you’d like to see the sheet, I would prefer to censor it a bit before posting.

Reply | Quote

WHen the sub runs, the UDF errors on this line (don’t ask me why):
strT = rngCell.Text
And hence returns #Value! to its calling cell.
Change to:
strT = rngCell.Value
and you should be fine.

Reply | Quote

>As of Excel 2003, hiding/showing rows fires a recalculation..
Thanks!

Reply | Quote