Solved: Arrary Function, Not updating Correctly on Different Sheets

[MrRhodes2004]

Attached is a workbook that has the function, shown below. I have inherited this function and some of the original information is show in the comments.

At this point, the function does what it needs to do. It shows the equation in the referenced cell. However, when you use the function in multiple worksheets in the same workbook, the function has issues. In the attached workbook, there are the same equations in the same cells but have a random number in each.

They will update when you are on the sheet if you cause a change. But if you switch between sheets, the function does not update properly and will show the contents of the previous sheet.

This causes a problem when printing. Multiple sheets will all show the same equations even though the information is different.

I have tried forcing a full recalc before print but the last page updated is the information shown on all sheets. I am running out of ideas.

Any ideas? A better solution to the function?




[vba]Function SF(r As Range, Z As Integer) As String
'Previously called gfrv()
'Posted origionally by Harlan Grove(HrlnGrv@aol.com) on microsoft.public.excel.questions 2002-02-27
'Modified, 09/22/05 : now user does not need to refresh equation for function to work
'Modified, 09/22/05 : now has ability to specify number of decimal places to display.

Const crep As String = "(([A-Za-z0-9_]+|'[^']+')!)?\$?[A-Z]{1,2}\$?[0-9]+"
Const mrep As String = "(([A-Za-z0-9_]+:[A-Za-z0-9_]+|'[^']+:[^']+')\!)|(\$?[A-Z]{1,2}\$?[0-9]+:\$?[A-Z]{1,2}\$?[0-9]+)"
Dim v As Variant, n As Long
Dim regex As Object, matches As Object, m As Object
SF = Mid(r.Formula, 2)
Set regex = CreateObject("vbscript.regexp")
regex.Global = True
regex.Pattern = mrep
Set matches = regex.Execute(SF)
If matches.Count > 0 Then Exit Function
regex.Pattern = crep
Set matches = regex.Execute(SF)
n = matches.Count - 1
For n = n To 0 Step -1
Set m = matches.Item(n)
v = Evaluate(m.Value)
If IsNumeric(Val(v)) = True Then
v = Application.WorksheetFunction.Round(v, Z)
End If
SF = Left(SF, m.FirstIndex) & CStr(v) & _
Mid(SF, m.FirstIndex + m.Length + 1)

Next n
End Function[/vba]

[SamT]

Screwiest thing I ever saw. None of these changes made a difference, but I could get it to fail every time by adding more calls to the function (More formulas on the sheet.)

BTW, wasn't the original showing two values as the result? ICR. With Result and Result2, it shows a range address and a value.

[VBA]Function SF(r As Range, Z As Integer) As String
'Previously called gfrv()
'Posted origionally by Harlan Grove(HrlnGrv@aol.com) on microsoft.public.excel.questions 2002-02-27
'Modified , 09/22/05 : now user does not need to refresh equation for function to work
'Modified , 09/22/05 : now has ability to specify number of decimal places to display.

Const crep As String = "(([A-Za-z0-9_]+|'[^']+')!)?\$?[A-Z]{1,2}\$?[0-9]+"
Const mrep As String = "(([A-Za-z0-9_]+:[A-Za-z0-9_]+|'[^']+:[^']+')\!)|(\$?[A-Z]{1,2}\$?[0-9]+:\$?[A-Z]{1,2}\$?[0-9]+)"

Dim v As Variant, n As Long
Dim regex As Object, matches As Object, m As Object
Dim Result As String
Dim Result2 As String

' Application.Calculation = xlCalculationSemiautomatic
'Application.Calculation = xlCalculationManual
Result = Mid(r.Formula, 2)

Set regex = CreateObject("vbscript.regexp")
regex.Global = True

regex.Pattern = mrep
Set matches = regex.Execute(Result)
If matches.Count > 0 Then Exit Function

regex.Pattern = crep
Set matches = regex.Execute(Result)
n = matches.Count - 1

For n = n To 0 Step -1
Set m = matches.Item(n)
v = Evaluate(m.Value)
If IsNumeric(Val(v)) = True Then
v = Application.WorksheetFunction.Round(v, Z)
End If
Result2 = Left(Result, m.FirstIndex) & CStr(v) & _
Mid(Result, m.FirstIndex + m.Length + 1)

Next n
'Application.Calculate
'Application.Calculation = xlCalculationAutomatic
SF = Result2
End Function

[/VBA]

[MrRhodes2004]

I don't remember it having two results. The original code from Harlan was:
https://groups.google.com/forum/#!ms...0/IIyo2RaMin4J

I wish I knew more about how to control CreateObject("vbscript.regexp").

It seems to not know which worksheet it is in. Therefore it replaces any other cell calling the same function and reference. I have tried to force it to look at the specific worksheet that it is on without luck.

If I can figure it out, it will be a handy tool to have!

[snb]

Why don't you use:

=C3&"*"&TEXT(D3;"0.0000")

[SamT]

I see that Harlan used Application.Volatile. Have you tried the function with
[vba]Application.Volatile(False)[/vba]As he recommended?

[GTO]

Hi All,

Not overly tested, but I added a third sheet (same basic formulas, except different hard numbers), and this seems to work.

[vba] 'ensure the cell address being evaluated includes the sheet name
v = Evaluate(r.Parent.Name & "!" & m.Value)[/vba]

I do not believe that the matches returned by RegExp are the issue. Rather, I think that without the sheet reference, Evaluate is simply choosing the cell to evaluate based on the sheet with the current focus.

Hope that helps

Mark

[p45cal]

I do not believe that the matches returned by RegExp are the issue. Rather, I think that without the sheet reference, Evaluate is simply choosing the cell to evaluate based on the sheet with the current focus.

I completely agree with Mark, I'd come to the conclusion before sleep took over last night. I was testing, and though still not completely tested I kept the original:
[VBA] v = Evaluate(m.Value)[/VBA]
but added immediately after:
[VBA]
If InStr(m.Value, "!") = 0 Then v = Evaluate("'" & r.Parent.Name & "'!" & m.Value)
[/VBA]to cope with pre-existing sheet refs in the formula and sheet names with spaces in.
The test for an existing sheet reference is a bit basic, I know.

[MrRhodes2004]

Mark and P45!!!

Thank you VERY much. I think that fixed my issue! I will test in several locations but I think we have a winner!

BTW, P45, I was just on another forum where you discussing the Caller function and I was trying to figure out how to use it in this condition.

[p45cal]

p45, I was just on another forum where you discussing the Caller function and I was trying to figure out how to use it in this condition.

I don't think it would be much use since it returns the range that the userdefuined function itself is in (unless you will never ever have it refer to a cell containing a formula on another sheet); it's safer to use the passed argument's parent.name (r.parent.name).

Which forum was that then, and what thread?

[GTO]

...but added immediately after:
[vba]
If InStr(m.Value, "!") = 0 Then v = Evaluate("'" & r.Parent.Name & "'!" & m.Value)
[/vba]to cope with pre-existing sheet refs in the formula and sheet names with spaces in.
The test for an existing sheet reference is a bit basic, I know.

I didn't even think of either of those. Nice catch Pascal, Thanks.