Update user-defined functions

[confusion123]

Cell A1 of Sheet2 has a value of 1.

If I type in cell A1 of Sheet1:

=MyFn()

I get a value of 100, as expected because of the code below:

Option Explicit

Function MyFn() As Double
    
    Select Case Sheet2.Cells(1, 1).Value
    
        Case 1
    
            MyFn = 100
            
        Case 2
        
            MyFn = 2000
                
        Case Else
        
            MyFn = 30000
                
    End Select
    
End Function

However, if I change the value in cell A1 of Sheet2 to 2, I expected the value in cell A1 of Sheet1 to be 2000 AUTOMATICALLY but it doesn't. Instead, I have to manually type into cell A1 of Sheet1:

= MyFn()

then it returns a value of 2000.

I assume this is the normal behaviour of a user-defined function?

My workaround is to add the following event in Sheet1:

Option Explicit

Private Sub Worksheet_Activate()

    Me.Cells(1, 1).Formula = Me.Cells(1, 1).Formula

End Sub

Is this OK or is there a better solution, such as an event that when Sheet1 is activated, all formulae in Sheet1 are updated?

Thanks

 

[Marcelo Branco]

I would try something like

Function MyFn(myVal As Long) As Double
    
    Select Case myVal
        Case 1
            MyFn = 100
        Case 2
            MyFn = 2000
        Case Else
            MyFn = 30000
    End Select
    
End Function

In Sheet1 A1
=MyFn(Sheet2!A1)

M.

 

[Joe4]

So you want the value return by your UDF to update automatically as the value in A1 changes, right?
That does not happen by default unless you add "Application.Volatile", i.e.

Function MyFn() As Double

[COLOR=#ff0000]    Application.Volatile[/COLOR]

    Select Case Sheet2.Cells(1, 1).Value
        Case 1
            MyFn = 100
        Case 2
            MyFn = 2000
        Case Else
            MyFn = 30000
    End Select
    
End Function

 

[confusion123]

Thanks for both suggestions.