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

    Home » Forums » AskWoody support » Productivity software by function » Visual Basic for Applications » Function doesn’t work if code runs? (Ex2003)

    Author
    Topic
    #456887

    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??

    Viewing 0 reply threads
    Author
    Replies
    • #1143424

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

      • #1143441

        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 blush

        • #1143565

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

          • #1143628

            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.

            • #1143666

              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..

            • #1143670

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

            • #1144098

              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.

            • #1144231

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

    Viewing 0 reply threads
    Reply To: Function doesn’t work if code runs? (Ex2003)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: