Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Function doesn't work if code runs? (Ex2003)

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

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function doesn't work if code runs? (Ex2003)

    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", <font color=blue>Sheets("MySheet").</font color=blue>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 <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function doesn't work if code runs? (Ex2003)

    I was going to suggest that would be the issue. Good that you found it yourself en even better that you let us know!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Function doesn't work if code runs? (Ex2003)

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function doesn't work if code runs? (Ex2003)

    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..
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Function doesn't work if code runs? (Ex2003)

    Attached. Let me know if you'd like to see the sheet, I would prefer to censor it a bit before posting.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function doesn't work if code runs? (Ex2003)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function doesn't work if code runs? (Ex2003)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •