VBA UDF with Cell Value Variable Not Re-Caculating

[rickincanada]

Hello,

I have a created a VBA Function that declares and then uses the value from cell A1 on Sheet1 (data validation displays a list of acceptable values) inside the function. The problem I'm having is that I'm then using that function to calculate a value in cell A1 on Sheet2. When I change the value on Sheet1 the function does not re-calculate on Sheet2. I've tried adding something along these lines but it isn't working.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Address = "$A$1 Then
Worksheets("Sheet2").Activate
ActiveSheet.Calculate
Worksheets("Sheet1").Activate
Range("A1").Select
End If
End Sub

Please help!!

Thanks,
Rick

 

[GTO]

What is the function on Sheet2A1?

 

[rickincanada]

Function FinPayment(P As Double, J As Double, N As Double) As Double

Dim CalcMethod As String

CalcMethod = Sheets("Sheet1").Range("A1").Value

If CalcMethod = "Standard" Then

FinPayment = (P * (J / (1 - (1 + J) ^ -N))) / (1 + J)

ElseIf CalcMethod = "Simple" Then

FinPayment = P * (J / (1 - (1 + J) ^ -N))

End If

End Function

 

[GTO]

Quickly tested, but this seems to work:

Add .Volatile to your UDF:

Option Explicit
    
Function FinPayment(P As Double, J As Double, N As Double) As Double
Dim CalcMethod As String
    
    Application.Volatile
    CalcMethod = Sheets("Sheet1").Range("A1").Value
    If CalcMethod = "Standard" Then
        FinPayment = (P * (J / (1 - (1 + J) ^ -N))) / (1 + J)
    ElseIf CalcMethod = "Simple" Then
        FinPayment = P * (J / (1 - (1 + J) ^ -N))
    End If
End Function

...and force the sheet to calculate as you were...

Option Explicit
    
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 And Target.Address = "$A$1" Then
        Sheet2.Calculate
    End If
End Sub

Hope that helps,

Mark

 

[rickincanada]

Perfect, thanks!!!

 

[GTO]

You are most welcome