Auto update a UDF value

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,256
I have a UDF that depends on the value on another sheet:

Code:
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:

Code:
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Code:
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

Well-known Member
Joined
Apr 11, 2013
Messages
1,256
Thanks.

However, according to Chip Pearson:

Rich (BB code):
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?
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
A much better alternative is to pass all relevant ranges as arguments to the UDF.
 

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,256

ADVERTISEMENT

That's what Chip said!

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

For example:

Code:
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.
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I didn't scroll across in the box!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,004
Messages
5,526,223
Members
409,688
Latest member
Mc Junior

This Week's Hot Topics

Top