User-defined function and Application.Volatile

[tiredofit]

As far as I'm aware, Application.Volatile is rarely used, possibly frowned upon.

I have a udf that takes parameters:

Public Function MyFunction(ByRef a As Integer, ByRef b As Integer, ByRef c As Integer) As Integer

   MyFunction = a + b + c

End Function

As it is, this function will kick off if any of its three parameters changes.

However, parameter a is linked to another cell, say d.

If d changes, in order for my udf to update automatically, I suppose I would change the code to:

Public Function MyFunction(ByRef a As Integer, ByRef b As Integer, ByRef c As Integer) As Integer

    Application.Volatile

    MyFunction = a + b + c

End Function

My question is: is this a badly desgined spreadsheet?

Should parameter a NOT have to be linked to d?

 

[severynm]

My question is: is this a badly designed spreadsheet?

If this UDF is used a few times in the workbook, probably not. If it is used hundreds of times or more, probably yes.

Also, if this workbook will be used in Excel 365 only, you can make use of the Lambda function to define a UDF without requiring VBA.

 

[StephenCrump]

However, parameter a is linked to another cell, say d.

If d changes, in order for my udf to update automatically, I suppose I would change the code to ...

If d changes, and hence a changes, doesn't your MyFunction update automatically (without Application.Volatile)?

 

[tiredofit]

If this UDF is used a few times in the workbook, probably not. If it is used hundreds of times or more, probably yes.

Also, if this workbook will be used in Excel 365 only, you can make use of the Lambda function to define a UDF without requiring VBA.

Thanks, I'll try that.

 

[tiredofit]

If d changes, and hence a changes, doesn't your MyFunction update automatically (without Application.Volatile)?

Yes, indeed it does.

However, my spreadsheet is more complicated, in that on another sheet, there are calculations (using VBA) which depend on the udf results.

 

[RoryA]

However, my spreadsheet is more complicated, in that on another sheet, there are calculations (using VBA) which depend on the udf results.

I don't see how that relates. If the inputs to your UDF change, your UDF will recalculate. Since they are all direct arguments, it should not be necessary to make it volatile.

 

[tiredofit]

I don't see how that relates. If the inputs to your UDF change, your UDF will recalculate. Since they are all direct arguments, it should not be necessary to make it volatile.

I've figured it out.

My udf takes two arguments (say a and b) and uses a value (c) on another sheet.

So the udf only updates if either a or b is changed but if only c chnages, it won't update.

 

[RoryA]

My udf takes two arguments (say a and b) and uses a value (c) on another sheet

Which is completely unlike the function you posted.

 

[tiredofit]

Which is completely unlike the function you posted.

Yes, it was my mistake.

Having said that, this has led me to find out something strange. Here is the link:

https://www.mrexcel.com/board/threads/step-into-udf.1262009/