Custom Function calculation problem

I have developed a UDF for use in one workbook.

The problem is that if another open workbook is calculated when I return to the first workbook all cells using the UDF display #VALUE! and the workbook has to be recalculated before it can be used.

Is there a way of avoiding this irritation problem other than not opening more than one workbook.

Maybe something about the UDF, post the code.

It is used to calculate prices based on several relatively complex parameters and contains plenty of references to worksheet ranges.

I added Application.Volatile at the start but this seems to have no effect.

I would rather not post the code as it is business confidential.

Originally Posted by tonyph17

I would rather not post the code as it is business confidential.

Come on, get real. It is highly unlikely to contain something we haven't seen (many times) before.

It really is hard to provide a specific reason for your problem without the code.

Can I assume that by UDF you mean a Function procedure that is being called from a spreadsheet cell?

contains plenty of references to worksheet ranges.

One suggestion: Are these references passed to the function through the argument list, or are they hard coded into the function? If the function needs information from the spreadsheet, it is usually best to pass that information through the argument list to avoid problems when the calling sheet/workbook is not the active workbook. Passing information through the argument list also eliminates (most of the time anyway) the need for application.volatile as Excel can readily figure out how the UDF fits into the calculation dependency tree.

Most of the arguments are for various discount percentages and each argument is set to the value of the worksheet cell with the same name range.

Is the use of values from the worksheet what is causing the problem when another open worksheet is calculated? After all these years I am surprised if there is not an effective solution.

I am afraid I do not follow the point you are making - my ability with VBA is pretty limited.

Here is a snippet of code I am using -this is repeated a number of times to set the values of all the ranges I need to use:

Dim d_2009B As Single

d_2009B = Range("d_2009B").Value

The UDF works fine so long as I keep the workbook as the active workbook but displays #VALUE! in each cell when another open workbook is calculated. I then have to recalculate the workbook with the UDF when it is active.

mikerickson is correct in that you could make sure your references are all to specific workbook/worksheet combinations. However, I'd still say that best programming practice is to pass this as an argument:

function myfunction(d_2009b as single, d_2010b as single, d_2011b as single,...)
(statements as needed)
end function

called from a spreadsheet cell as =myfunction(d_2009b,d_2010b,d_2011b,...). This allows you to control what values are passed to the function, and allows Excel to see how the cell dependencies work so it can optimize the calculation event.

I have solved my problem by setting the values stored in the ranges as UDF constants rather than asking my UDF to read these values.

The various Case statements I use later in the UDF then pick up the appropriate Const value and work perfectly even if another workbook is open.