Stringconcat (Chip Pearson) UDF loops 10 times during Sub execution making everything slow

[AusSteelMan]

Hi,

I have a workbook where one sheet uses information on 2 hidden sheets 1. for drop down dynamic range references; and
2. for lookup references

I am working on a template workbook where the main sheet has headings in Row 1 (A to AE and in hidden helper columns BA to BE). Rows 2 to 5 are blank except for forumla in K, O and BA to BE). Cells A1 to AE5 have borders.

I also have a filled in example sheet with around 400 lines below the heading.

I have the StringConcat function (Credit to Chip Pearson) in Module 1 of my VBAProject.

Also in Module 1 is several simple Subs, none of which directly call on the Function.

I also have some sheet code on my only visble sheet.


PROBLEM
The problem I am having is that when some of the module based Subs run, the function code starts (many loops in that code) mid stream, and I have no idea why.

Note:
The subs and function all work succesfully, but are just slow in exectuing, especially when there are around 400 lines in a completed worksheet

A couple of my codes are not effected, but 1 is effected sevral times while executing. I can really notice the lag when processing what should be simple tasks. The status bar shows calculation %age: it takes 5 to 8 seconds of lag to do anything while it calculates.

To check, I used the Breakpoint and the End statement of the function and stepped to see what happens next. The result was 10 complete cycles of the function code - where there is no apparent action taking place. Unfortunately, the UDF code has many For Each loops.

Note:
I cannot figure out why it is even doing it 10 times. The function is not used on the sheet 10 times (4 times in the template and 400 times in the example)

I am happy to post code if someone thinks they can help, or even send the whole workbook if appropriate.


Can anybody offer help. I am getting short on time and ability to just sit here and fault find.

Regards and thanks,
ASM

 

[Ed in Aus]

Do you need the UDF to actually run during this code? if you don't maybe application.EnableEvents = False in the code might be what you need.

don't forget to turn it back on though.

hope that helps

 

[AusSteelMan]

Thanks Ed, but unfortunately it didn't help.

Since my last post, I have discovered that it is not always 10 loops. I am still testing but it appears to loop twice as many times as there are rows (discounting the header row), so it may be related to number of times the code is used (once per row).

Meant to press Submit on this about 18 hours ago, but thought of other things to try.

In the one cell where the formula is used, it is actually used twice as shown

=IF(ISERROR(stringconcat(" --> ",TRUE,BA5:BF5)),"",stringconcat(" --> ",TRUE,BA5:BF5))

There seems to be corrolation, but doesn't seem to be exact.

Another thought is that it has to do with the arguments it calls on.

Still don't know and would like some help if anybody can offer suggestions

I will keep testing.

ASM

 

[sous2817]

CAn you post the code of one of the subs that seems to cause the UDF calculation?

Are you setting calculation to manual at the start of the sub and then to automatic at the end of the sub? That may help....

 

[AusSteelMan]

NEW REVELATION!!!!!!

The StringConcat UDF runs everytime any cell value is changed, not just when a sub runs.

I was replying to Sous just now while retesting to confirm exactly what happens so I could describe accurately. While testing, I had put a Breakpoint on the first line of the function

Function StringConcat(Sep As String, SkipEmpty As Boolean, ParamArray Args()) As String

I had left the breakpoint in place and typed text into cell A2, pressed enter - expecting to move down to A3 so I could type something else - when I got a critical beep because the code was waiting to execute!!!

It looped the entire function (not just inside the function) 20 times (I currently have 20 rows in the worksheet where 1 cell in each row uses the function).

Tried again in other cells and got the same thing.

So to clarify where I have Code
Function: StringConcat is in module 1

Subs in Module 1:
InsertRowAtCurrentCellSelection
DeleteRowAtCurrentCellSelection (code is shown below with more description of problem)
Autosize_every_row
FreezePanesForStrategy
UnfreezePanes
FreezePanesForHeaderRow
PrintStrategySheet

Sub in Sheet9:
Private Sub worksheet_change(ByVal Target As Range)

Sub DeleteRowAtCurrentCellSelection()
 
 
    If MsgBox("This will delete the entire row that is currently selected.  This action CANNOT be undone.  Are you sure you want to continue ?", vbOKCancel, "Delete Row Confirmation") = vbCancel Then Exit Sub
 
    ActiveCell.EntireRow.Delete
 
    Calculate
 
 
End Sub

I put Breakpoints at each line of this code and ran it.

• Pressed F8 when "Msgbox" highlighted and the Msgbox popped up correctly
• Pressed F8 when "ActiveCell.EntireRow.Delete" highlighted. The row deleted then the UDF (which has a breakpoint on the first line as described above) started. I clicked on the "Play" button so the UDF executed and the first line highlighted again. Repeated 19 more time (20 total) before the next line of the Sub was highlighted.
• Pressed F8 when "Calculate" was highlighted. [Presumably] the sheet calculated, then the UDF looped 20 times again before "End Sub" highlighted

I am wondering if I should have code in different modules, or if there is something about the UDF that is acting like a worksheet_change event. I really am stumped.

Regards,
ASM

 

[AusSteelMan]

So I have tried making calculation manual, but this did not work either.

The UDF still kicks in, but goes into error since it requires ongoing calculations for it to work at all.

Still trying...

ASM

 

[AusSteelMan]

Sorry, I forgot to add this next revelation.

With a breakpoint is at the start of the UDF, I can see that making ANY ENTRY on to the sheet, even just a text entry to a cell, causes the UDF to start.

So it seems to me that there is something with the UDF that is causing that to happen - or is that how UDFs actually are designed to exist. Calculate everytime anything is changed on the sheet???

If so, this problem is no longer a problem. It just means the sheet will run slowly when there is alot of data on the sheet (36 cols with (on average) 200 rows)

Does anybody know the answer to this???

Thanks,
ASM

 

[mikerickson]

If a UDF starts with Application.Volatile then it will calculate anytime any cell is changed, otherwise it will calculate only when one of its precedent cells change.
(Or if one of the arguments of the function uses a Volatile function, e.g. SUM(A1:A10) calculates only when A1:A10 is changed, SUM(INDIRECT("A1:A10")) calculates when any cell is changed.)

But in either case, setting Calcultion to xlManual should prevent the re-calculation.
Setting Calculation back to xlAutomatic will trigger a re-calculation. so I'd reset that outside of your loop.

On the one hand, you might try removing the Application.Volatile.
On the other hand, if Mr. Pearson put that line there, it's probably for a good reason.

As a last ditch workaround you could declare a Public Variable UDFDisabled As Boolean and add the line If UDFDisabled then Exit Function to the start of the UDF, then setting that variable True/False would be a way to control the UDF.

 

[AusSteelMan]

Mike,

Thankyou for your help.

I commented out the volatile code, and it did not resolve the problem. You are correct in saying it is there for a good reason, without it the UDF basically ends up 1 calculation behind where it should be. See this link for further details if you like.
http://www.mrexcel.com/forum/showthread.php?t=423655

I tried manually putting the calculation into manual (Tools...Manual) and there was an effect (I must has made an error when I tried it before??). When I pressed F9, the code ran through. So if I had many changes, it would only run through once of course, so would save time.

For my own learning, I will give your other code ideas a try.

That said, I don't think many end users will notice the calc delay, or at least not until the sheet has been extensively filled in or they try and paste data in on mass (which would be a very abnormal situation anyway). So I think I will issue this version and work on improvements over time for a new version.

I do have another conflict (code based I think), but will start a new thread probably as I am reasonably sure it is not related to this problem (maybe the UDF, but not the recalcing).

Thanks Sous2817 for your ideas

Thanks to Ed for reviewing a copy of the workbook.

Best regards,
ASM