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

 

[AusSteelMan]

Thanks tusharm,

I was planning on trying again from a blank sheet, documenting carefully each and every step, to try and figure out what I actually did to either break it or fix it!!

I am pleased in one respect that you could get it to occur in both 2003 and 2007. Here at work I am on 2000, but have 2003 that I was going test on as well.

If I can reproduce the error(?) somewhere, I will let you know.

Regards,
Darren

 

[AusSteelMan]

Tushar,

I did the same test at home (XP, Excel 2003) this evening.

Chip's code was tested "as is" - without the volatile line of code and again with the code.

In its original form, the sringconcat that uses the cells with a formula as the arguments did not update as entries were made, did not update if F9 pressed, did update if Ctrl+Alt+F9.

With the volatile code, the result did not update as entry was made in that row, but did update in the 1st row when the second row was changed although now the secong row did not update. Now if F9 is pressed, the second line updates.

Below are some Jeanie extracts from when the volatile is present in the code.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	q	w	e	r	t	y		q,w,e,r,t,y		q,w,e,r,t,y		q	w	e	r	t	y
3	q	w	e	r	t	y		q,w,e,r,t,y		q,w,e,r,t,y		q	w	e	r	t	y
4	q	w	e	r	t	y		q,w,e,r,t,y		q,w,e,r,t,y		q	w	e	r	t	y
Sheet1

Col H executes a stringconcat using cols A to F as the Args
Col J executes a stringconcat using cols L to Q as the Args. These cells contains a formula to copy Cols A to F



Then changed A2 from q to z.
- L2 updated immediately
- H2 updated immediately
- J2 did not change

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	z	w	e	r	t	y		z,w,e,r,t,y		q,w,e,r,t,y		z	w	e	r	t	y
3	q	w	e	r	t	y		q,w,e,r,t,y		q,w,e,r,t,y		q	w	e	r	t	y
4	q	w	e	r	t	y		q,w,e,r,t,y		q,w,e,r,t,y		q	w	e	r	t	y
Sheet1

<table style="border-style: groove; border-color: rgb(0, 255, 0); font-family: Arial; font-size: 10pt; background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tr><td>
</td></tr><tr><td>
</td></tr></table>



Then changed A3 from q to x.
- L3 updated immediately
- H3 updated immediately
- J3 did not change
- J2 updated from q to z

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	z	w	e	r	t	y		z,w,e,r,t,y		z,w,e,r,t,y		z	w	e	r	t	y
3	x	w	e	r	t	y		x,w,e,r,t,y		q,w,e,r,t,y		x	w	e	r	t	y
4	q	w	e	r	t	y		q,w,e,r,t,y		q,w,e,r,t,y		q	w	e	r	t	y
Sheet1

<table style="border-style: groove; border-color: rgb(0, 255, 0); font-family: Arial; font-size: 10pt; background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tr><td>
</td></tr></table>



Pressing F9 now updates J3


Without going into much detail, the real sheet I intend using this scenario on does not use simply copy formulae in L to Q, so I cannot just use Cols A to F as args.

For now, that sheet seems to be OK and probably would not be noticed if there is a lag in updates.

This exercise for me now is about my learning and or bug identification as you suggested previously.

This may or may not help in you deciding if the bug is worth reporting.

It may also serve as a technical challenge to the many MVPs and enthusiats out there in MrExcel land.

Thanks again for your interest in helping me with the problem I had (and sorry if I have pratteled on too much).

Darren

 

[RoryA]

I have just tried that in 2003 SP2 and cannot replicate the issue. Everything updates as I would expect.

 

[tusharm]

[The below is with Excel 2007 with all the updates available through Microsoft Update.]

That motivated me to do a little more research.

If you *copy* a cell with the StringConcat function, the new cell will *lag* by one recalc cycle! If you enter the function from scratch, it will show the current result.

Enter the letters q, w, e, r, t in A3:E3. In F3 enter =StringConcat(",",TRUE,A3:E3) In G3 enter =A3 and copy to H3:K3.
*Copy* F3 to L3.
Change B3 to x. L3 will not change.
Change B3 to w. L3 will show q,x,e,r,t! That's a 1-recalc-cycle lag.
Change B3 to a. L3 will show q,w,e,r,t! Again, a 1-recalc-cycle lag.

Now, double-click in L3 and press ENTER (or click in L3, F2, ENTER), essentially entering the formula "by hand" in that cell. It will now be in sync with the rest of the sheet. Change B3 and L3 will show the correct value.

Now, copy F3 over to L3. The old 1-recal-lag will show up!

I have just tried that in 2003 SP2 and cannot replicate the issue. Everything updates as I would expect.

 

[AusSteelMan]

Just got to work, read the replies and tested some more.

Have found that there is a relationship with the order in which the formulas are entered, and if a 1-cycle-calc lag is present.

Test 1:
The volatile statment is active in Chip's code.
Enter q, w, e, r, & t into A2 to E2
Enter =stringconcat(",",true,A2:E2) in to F2. q,w,e,r,t appears.
Enter =A2 into G2. Fill/copy or manually enter across to K2
Enter =stringconcat(",",true,G2:K2) in to L2. q,w,e,r,t appears.
Change A2 from q to z:
- F2 updates, G2 updates, L2 does not
Change B2 from w to x:
- F2 updates, H2 updates, L2 updates from q,w,e,r,t to z,w,e,r,t but not z,x,e,r,t - 1-cycle lag!!

Test 2
The volatile statment is active in Chip's code.
Enter q, w, e, r, & t into A4 to E4
Enter =stringconcat(",",true,A4:E4) in to F4. q,w,e,r,t appears.
Enter =stringconcat(",",true,G4:K4) in to L4. #VALUE! appears.
Enter =A4 into G4. Fill/copy or manually enter across to K4. L4 now displays q,w,e,r,t.

Change A2 from q to z:
- F4 updates, G4 updates, L4 updates to z,w,e,r,t - no lag
Change B2 from w to x:
- F4 updates, H4 updates, L4 updates to z,x,e,r,t - no lag

Test 3
The volatile statment is active in Chip's code.
Back on Row 2, where ther is still a lag
Reset A:F as q, w, e, r & t
Press F9 to ensure L2 is up to date
Change A2 from q to z:
- F2 updates, G2 updates, L2 does not
Edit L2, make no change, Enter
L2 updates
Change B2 from w to x :
- F2 updates, H2 updates, L2 does not
Change C2 from e to c:
- F2 updates, I2 updates, L2 updates from z,w,e,r,t to z,x,e,r,t but not z,x,c,r,t: still a lag
Press F9
- L2 updates to z,x,c,r,t
Change A2 from z to a:
L2 has no change
Edit G2 (F2 and enter is how I did it)
L2 updates correctly to a,x,c,r,t
Change B2 from x to s:
L2 does not update
Edit K2 - NOT H2
L2 updates correctly to a,s,c,r,t
Change D2 from r to f:
L2 does not update
Edit J2
L2 updates correctly to a,s,c,f,t
Change E2 from t to g (remember coresponding K2 has been "edited" already):
L2 updates correctly - NO LAG

Test 4
Change A2 to y, B2 to h, C2 to n, D2 to j & E2 to k and study the lags/not lags carefully. Basically where ever the corresponding cell in G:K has been edited, L2 responds immediately and there is still a lag on the others. These lagged ones catch up hen a cell that has been edited is changed in A:E

Conclusions:
The order of entering the formulas is very critical when using argument cells that have been generated rather than user entered.

Stringconcat must have been entered before the argument cells it refers to are entered.


I repeated the entire exercise without the volatile code.
Underlined text highlights where the result was different from first set of tests.

Test 1a
identical result

Test 2a
Identical result

Test 3a
Reset A:F as q, w, e, r & t
Had to press C+A+F9 to ensure L2 was up to date (not just F9)
Change A2 from q to z:
- F2 updates, G2 updates, L2 does not
Edit L2, make no change, Enter
L2 updates
Change B2 from w to x :
- F2 updates, H2 updates, L2 does not
Change C2 from e to c:
- F2 updates, I2 updates, L2 updates from z,w,e,r,t to z,x,e,r,t but not z,x,c,r,t: still a lag
Again need C+A+F9
- L2 updates to z,x,c,r,t
Change A2 from z to a:
L2 has no change
Edit G2 (F2 and enter is how I did it)
This time L2 did not update
Change B2 from x to s:
L2 updated to a,x,c,r,t but not a,s,c,r,t - LAG
Edit K2 - NOT H2
Made no difference to L2
Change D2 from r to f:
L2 updated to a,s,c,r,t but not a,s,c,f,t - LAG
Edit J2
Made no difference to L2
Change E2 from t to g (remember coresponding K2 has been "edited" already):
L2 updates correctly - NO LAG - and catches up on all the lagged changes

Test 4a
Similar scenario to test 4, the updates of L2 are linked to which of the G:K cells have been "edited/entered" AFTER the stringconcat is in place

Conclusion:
* Volatile means only F9 is required to update the stringconcat function where formula was entered before the function

* This UDF that relies on formula derived arguments needs to be in place BEFORE the argument formula(s) are in place


Thanks for reading/wading through all of this.

Hope it can be worth something to someone. If not, at least we know that this and maybe other UDFs that rely on formula derived arguments need to be in place first for everything to work properly.

Regards,
Darren

Darren

 

[RoryA]

My initial suspicion (with no testing) would be that it may be down to the use of ParamArray. If I get a chance tomorrow, I'll see if replacing it with a straight range argument makes any difference.