Auto update a UDF value

[tiredofit]

I have a UDF that depends on the value on another sheet:

Option Explicit

Function MyFn(val As Double) As Double
    
    If Sheet1.Cells(1, 1).Value = 1 Then
    
        MyFn = val + 10
    
    Else
    
        MyFn = val + 20
    
    End If
    
End Function

So in Sheet2, cell B2, I type:

myfn(10)

which gives me a value of 20, IF the value in cell A1 of Sheet1 is 1.

The problem is if I change the value in cell A1 of Sheet1, the value in Sheet2, cell B2 is NOT automatically changed.

I have to physically go to cell B2 of Sheet2 and press F2.

What can I do so that by changing the value in cell A1 of Sheet1, the value in cell B2 of Sheet2 AUTOMATICALLY updates?

Thanks

 

[MickG]

Try this:-

Function MyFn(val As Double) As Double
[COLOR=#FF0000] Application.Volatile[/COLOR]
    If Sheet1.Cells(1, 1).Value = 1 Then
      MyFn = val + 10
    Else
       MyFn = val + 20
    End If
End Function

 

[tiredofit]

Thanks.

However, according to Chip Pearson:

Writing Your Own Functions In VBA

"This has the drawback, however, that the function is recalculated even if it doesn't need to be recalculated, which can cause a performance problem. In general, you shouldn't use Application.Volatile but instead design your UDF to accept as inputs everything it needs to properly caclulate the result."

Is it a reasonable compromise?

 

[RoryA]

A much better alternative is to pass all relevant ranges as arguments to the UDF.

 

[tiredofit]

That's what Chip said!

Can you give me pointers as to how might I achieve that?

For example:

Option Explicit
Function MyFn(firstval As Double, val As Double) As Double
    
    If firstval = 1 Then
    
        MyFn = val + 10
    
    Else
    
        MyFn = val + 20
    
    End If
    
End Function

this now doesn't take the value in cell A1 of Sheet1 into consideration.

 

[RoryA]

I didn't scroll across in the box!

 

[tiredofit]

Got it!

=myfn(Sheet1!A1, 10)