StringConcat (Chip Pearson) calculation delay

[AusSteelMan]

Hi everyone.

I have used Chip's StringConcat code (http://www.cpearson.com/Excel/stringconcatenation.aspx).

When I first entered the function code as a formula on the worksheet, the result was instant and correct.

However, if I change the contents of one of the cells in the stringconcat range, the result does not change.

I need to edit (eg F2 then enter) either the cell with the text or the cell with the formula for the result to update.
If I highlight the column where the formula is used and Find / Replace to change "=" for "=", they all update at once (tip I learn't here)


Has anyone experienced this or have any ideas why it may happen?


Notes:
I am using Excel 2000 (work)
I have 6 cols of info (I to N),
These 6 columns contain formulae that basically copies plain text from the same row in Cols A to F. (Since not all the left hand side cells are full, the formula copies down the value above if the cell isblank, or copies the cell value if there istext)
Formula is in Cell G2 and reads

=stringconcat("; ",TRUE,I2:N2)

Code is stored in the Module1
I have 1 header row (Row 1)
I have 16 rows of text (Rows 2 to 17)
Not all rows have text in all 6 columns, but the are no blank cells betweeen data working left to right
I need our IT Admin to install Excel Genie!!

I am going to try it on Excel 2003 at home this evening and will install the program there so I can improve my posts.

Thanks
Darren

 

[RyanS]

Check that your calculation is set to automatic.

I have tried in Excel 2007 and it works fine. I'm sure 2003 will also work.

 

[wsjackman]

you may have to add the word "volitile" in the udf for it to update as you change values

 

[AusSteelMan]

Thanks for your help so far.

I have tested on a new worksheet, and found the problem occurs when Stringconcat is using the cells with formulae for the reference cells.

So not existing workbook conflict/related. Calc is automatic.

In Cols A to F: I put the letters q w e r t and y into row 7
In Col I: I used Chip's Stringconcat with "," as the sep, TRUE for skipping cells, and range being A7:F7
In Col N: I entered "=A7" and filled across to Col S
In Col K: Stringconcat now using range as N7:S7

If I change a value in any of A:F, (say change y to u in Col F)
- the result in I is immediate,
- the change in S is immediate,
- There is no change in N's result, until I edit either F or K, and then N will update. Need to go through edit process again if another change is made (ie it repeats everytime). Note:editing S7 has no impact.

So maybe "Volitile" is the answer, but right now is out of my capabilities to know where to put it in Chip's code.

Any ideas?

Darren

 

[wsjackman]

try the line after the dim statements by itself

 

[AusSteelMan]

OK, so I tried the following:

Application.Volatile (True)

after the Dim statements
and also just

Application.Volatile

Both statements allowed pressing F9 to update the result, whereas previously with out the statments I had to press "Ctrl + Alt + F9"

I will keep researching.

Thanks wsjackman.

Darren

 

[AusSteelMan]

Discovered something else now.

If I have several rows as described in the first post, being volatile "sort of" works.

So now I change A7 from q to a, press enter.
- I7 updates immediately, as does N7, but K7 remains the same
Now I update A8 from q to s, press enter.
- I8 updates, as does N8. but now K8 updates also (exactly what I am after) and so does K7.

As I continue down, all changes are made correctly.


Now found something else.

If I start on any other row, Col K changes immediately.
But whenever I do the top row (7 in this case) there is a lag until I change any other cell.
So working up in Col A from 10:
Rows 10, 9, 8 all update in Col K, but as i do row 7 nothing happens in K7.
Continuing to row 6 (which is completely blank) as soon as I enter anything in a cell, K7 updates.

All seems a little freaky, but for what I am doing in a real sheet, no one will probably notice anyway (due to where they enter text and where the Stringconcat are located to each other)

Still seems not-quite-right, so would like opinions if anyone can replicate what I am doing and comment.

Thanks,
Darren

 

[RoryA]

Does this happen in any workbook, or just in one particular one? If just one, can you post a censored copy somewhere (or email it)?

 

[AusSteelMan]

OK,

Now I am somewhere between very happy and reasonably disgruntled.

Happy because:
Everything is working as it should, my task is very near the end, learn't heaps along the way.

Disgruntled because:
The problem described in the post stopped being a problem for reasons I can not be sure of.

I added the "Volatile" code and only made a difference to F9 vs. C+A+F9.

Eventually I commented the volatile line out and now things are working properly, despite the code reverting to original.

I commented the line out as a stab in the dark to fix a new problem. When a piece of Sheet code was being executed (after selecting a value from a drop-down box) the result of that code had changed. Turns out it was an unrelated problem (I caused by starting a new sheet to keep testing my stringconcat problem) which is now OK. But then noticed that the Stringconcat now was functioning correctly.

Anyway, it seems my issue is resolved and the stringconcat code is in its original form from Chip.

Thanks to those that took an interest in helping me. Sorry I couldn't be more informative about what had happened.

Regards,
Darren

 

[tusharm]

That's very interesting. I could duplicate the problem you had with both Excel 2007 and Excel 2003 and was debating whether it would be worth reporting a bug to Microsoft.

OK,

Now I am somewhere between very happy and reasonably disgruntled.

Happy because:
Everything is working as it should, my task is very near the end, learn't heaps along the way.

Disgruntled because:
The problem described in the post stopped being a problem for reasons I can not be sure of.

I added the "Volatile" code and only made a difference to F9 vs. C+A+F9.

Eventually I commented the volatile line out and now things are working properly, despite the code reverting to original.

I commented the line out as a stab in the dark to fix a new problem. When a piece of Sheet code was being executed (after selecting a value from a drop-down box) the result of that code had changed. Turns out it was an unrelated problem (I caused by starting a new sheet to keep testing my stringconcat problem) which is now OK. But then noticed that the Stringconcat now was functioning correctly.

Anyway, it seems my issue is resolved and the stringconcat code is in its original form from Chip.

Thanks to those that took an interest in helping me. Sorry I couldn't be more informative about what had happened.

Regards,
Darren