Execution branches unexpectedly to UDF when stepping through

  • Any insight into this problem gratefully received:

    Excel 2003
    When stepping through a procedure, code execution jumps unexpectedly to a user defined volatile function stored in the (open) Personal workbook. There is no problem when the procedure is run, only when stepped through, so more of a major annoyance than a problem.

    Conditions under which this problem occurs:
    Wkbk A is open and has a worksheet open which contains a volatile function. (Not function specific – any UDF.) This function is stored in a module in Personal.xls.
    Wkbk B is the active workbook. It contains a code module which inter alia opens a text file. When stepping through the code, execution branches out of the current procedure to the first line of the volatile function. This occurs immediately after opening a text file.

    Some notes re when the problem occurs:
    - Only when opening a text file (Workbooks.OpenText Filename:= ); it does not occur when opening a csv file (Workbooks.Open Filename:= ).
    - The value returned by the volatile function on the open sheet in Wkbk A has changed to the #VALUE! If I use Edit | Links, the Status of Personal.xls is shown as 'Unknown'. Choosing 'Open Source' restores the correct values.
    - After manually terminating code execution, the active sheet is the text file that has just been opened.

  • Re: Execution branches unexpectedly to UDF when stepping through

    HI Zwana, welcome to OzGrid.

    My best guess is that upon opening the text file, All open workbooks are recalculated (as I believe is the default behaviour). As your UDF is in a seperate Workbook (and refererenced in another different Workbook), it is automatically flagged as Dirty and so will be recalculated too. Naturally this would only be obvious when stepping through the code.

    Having a play with the Application.Volatile() method in your UDF may help.

    "Why write code when you can copy and paste someone elses?"

  • Re: Execution branches unexpectedly to UDF when stepping through

    Hi CreamyEgg,
    Many thanks for your reply. As you suggested, upon opening the text file, the volatile udf is being recalculated. As a kludgy fix I tried turning on manual calc prior to opening the text file and turning auto calc back on after opening, but as soon as auto calc is turned back on the code immediately branches to the volatile udf.
    I played with the Application.Volatile method to the extent that I commented it out with the result that there was no prob with branching to the udf. However I need that (and other) udf's to be volatile.
    So no remedy at this stage.... anyone?

    AAMOI: The explanation for the #VALUE! being returned in the wksht containing the volatile udf was because I was manually aborting the code while the volatile udf was in the process of executing.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!