Problem - Spreadsheet update takes (relatively) forever usin


  • I have the following function that displays two alternative text strings based on whether a control cell is blank or not, a typical use is
    = SW_blank3( B4, "N/A", B4 &C4 &D4)


    Public Function SW_Blank3( str_test, str_blank, str_nonblank)
    If str_test="" Then
    SW_Blank3 = str_blank
    Else
    SW_Blank3 = str_nonblank
    End If
    End Function


    The problem is that this introduces a huge time overhead - everytime the workbook has to update cells, it can take about 15-20s to do so, and on a 1GHz Celeron that is like forever. I have switched to manual calculation updates - so some of the pain is gone, but I end up needing to update (I use F9 for a complete workbook update) quite often during development anyway.


    I have found the following workarounds help somewhat


    1) A single cell can be updated by simply modifying this cell (re-type last char in formula).


    2) When I do need to update the whole workbook, I have found that setting a breakpoint in the first line of SW_blank3 can help. The first time that it executes, VBA pauses at the breakpoint. Remove the breakpoint and then continue (F5) - it runs noticeably faster.


    Any ideas?


    Best Regards,
    Paul
    Tue, 2003-02-11 06:57:11.14 (JD 2'452'681.79)

  • Hi Paul


    Welcome to the OzGrid Excel Forum


    How many of the custom functions do you have in the Workbook? Also, why use a Custom Funtion and not an IF funtion?


    It looks like you may be causing your UDF's to loop, so try changing them to:




    Code
    Public Function SW_Blank3(str_test, str_blank, str_nonblank)
    Dim vResult
        If str_test = VbNullString Then
            vResult = str_blank
        Else
            vResult = str_nonblank
        End If
    SW_Blank3 = vResult
    End Function
  • (In case this doesn't add to the correct thread, it is
    Problem - Spreadsheet update takes (relatively) forever using a simple macro ?
    http://www.ozgrid.com/forum/vi…hp?tid=236&page=1#pid1043 )


    Win-2000 Ver 5.0 Build 2195 SR-2
    Excel-97 SR-2


    I forgot another very useful workaround to speedup calculation by only updating a range of cells - and this spawns a new question.



    3) To update a range of cells, mark the range and modify the cells in this range, all cells selected will be updated (ONLY IF IT IS A RECTANGULAR RANGE)


    I had only attempted this with rectangular ranges of selected cells before this post, mark the range, modify the cells using


    Ctrl-F = Alt-R = Alt-A


    which is actually
    [Edit [Find {=} [Replace] {=} [Replace All] ]]


    This will update only the cells in the range selected.









    Then I tried selecting disjoint ranges, and I have found strange behaviour.






    In the first fragment, 101 in col A is the cell off which all other cells are keyed so changing this should cause all other cells to change. Remember to set calculate to manual to try this
    [Tools [Options [Calculate [Manual]]]]



    I changed cell A1 from 11 to 101 in the first test


    In the "REVERSE ORDER" case I selected cells as follows (they are indicated as they appear after the update) - these cells have the text YES next to them
    Col E : 14:20 Now hold down the Ctrl key to make a disjoint range selection
    Col B : 14:20
    Col D : 13
    Col A : 102


    Then I did the "=" -> "=" text change - observe that only the 102 changed


    REVERSE ORDER
    A B C D E F
    101
    102 YES 13 YES
    14 YES 14 YES
    15 YES 15 YES
    16 YES 16 YES
    17 YES 17 YES
    18 YES 18 YES
    19 YES 19 YES
    20 YES 20 YES
    21 NO 21 NO
    22 NO



    Then I updated the whole page (F9), set A1 to 11 and
    I marked exactly the same range, but in reverse order :


    Col A : 102
    Col D : 13
    Col B : 14:20
    Col E : 14:20


    Then I did the "=" -> "=" text change - observe that 1005 to 1010 in the E column were not updated.


    FORWARD ORDER
    A B C D E F
    11
    12 YES 13 YES
    14 YES 14 YES
    15 YES 1005 YES
    16 YES 1006 YES
    17 YES 1007 YES
    18 YES 1008 YES
    19 YES 1009 YES
    20 YES 1010 YES
    1011 NO 1011 NO
    1012 NO


    Ideas ?


    Best Regards,
    Paul
    Tue, 2003-02-11 07:47:17.47 (JD 2'452'681.82)

  • Dave,


    > 6: If you get help from a person, take the time to post back to them with 2 words, "Thank You".


    Thanks for your reply - and this is standard in all electronic communication from me.
    BTW - you actually need bullet point 7: on this in your terms and conditions.


    I posted back before I saw you had replied - sorry we are out of sequence now.



    > Welcome to the OzGrid Excel Forum
    Thanks, may it be a mutually edifying membership.



    > How many of the custom functions do you have in the Workbook?
    Probably 10 - 20, all variations on the above theme. The problem only occurs if I invoke that particular function - if I take out reference to it, execution speed for the whole workbook reverts to < 1sec.


    &gt; Also, why use a Custom Funtion and not an IF funtion?
    Because in cell functions blow up in size when the repeated argument (B4) is not a cell reference but an expression (I very often use long lookup formulae). I graduated from all cell formulae to macros when I saw how much easier it became to manage complex expressions by encapsulating them as arguments to macros.


    &gt; It looks like you may be causing your UDF's to loop, so try changing them to:
    &gt; If str_test = VbNullString Then
    &gt; vResult = str_blank
    &gt; SW_Blank3 = vResult
    Thanks for the tip, it is typically something like this that will fix the problem. I will try it out when I can get back to the slow workbook.


    Last question, why did all the spaces get scrunched out of
    REVERSE ORDER
    .A.....B.....C....D......E.....F
    101....................
    102....YES........13....YES....
    .......14....YES........14....YES
    .......15....YES........15....YES


    It appeared as follows in my post


    REVERSE ORDER
    A B C D E F
    101
    102 YES 13 YES
    14 YES 14 YES
    15 YES 15 YES
    16 YES 16 YES


    My previous post used white space in the indented code fragment and it came through OK?


    I went to a lot of effort to convert the TABs that excel generates in the cell range dump, and my lovely formatted text now looks horrible in perpetuity!



    &gt; Dave Hawley
    &gt; Super Administrator
    &gt; *******************


    That's a lot of brass stars.
    Do all prolific posters get promotion to such levels? (_o^o_)


    Best Regards,
    Paul
    Tue, 2003-02-11 8:11:20.97 (JD 2'452'681.84)

Participate now!

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