StringConcat (Chip Pearson) calculation delay

A

AusSteelMan

Board Regular
Joined
Sep 4, 2009
Messages
208
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
Code: 
=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
 
Sort by date Sort by votes
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.
 
Upvote 0
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
 
Upvote 0
OK, so I tried the following:

Application.Volatile (True)
Click to expand...
after the Dim statements
and also just
Application.Volatile
Click to expand...

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
 
Upvote 0
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
 
Upvote 0
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)?
 
Upvote 0
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.:mad:

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
 
Upvote 0
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. ;-)
AusSteelMan said:
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.:mad:

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
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Data Validation List with OFFSET wont accept text
Replies
3
Views
134
DSCfromCFA
D
G
  • Solved
Combining month label with year
Replies
2
Views
140
gauchofife
G
H
  • Question Question
Excel issue VBA - copy and past content
Replies
1
Views
91
Micron
M
R
  • Question Question
Search for a Name or Phone number among multiple sheets
2
Replies
11
Views
194
alansidman
alansidman
B
  • Solved
VB for Searching for ~ 20 words in a large table of text
Replies
3
Views
284
BobTheSpreadsheetBuilder
B

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top