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
SW_Blank3 = str_nonblank
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.
Tue, 2003-02-11 06:57:11.14 (JD 2'452'681.79)