Posts by zwana

    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.

    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.