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]