I have an excel spreadsheet with coding mixed between vba modules and excel functions. The spreadsheet has up to 8 columns that can be used. Several of the rows have named ranges. I found that I could perform a calculation for each column by referring to the named range (energy) in an excel formula. The formula has nested if-then-else statements which I would rather write in a vba module. I would like to use the named range in the vba function. however I cant find a way to reference the named range. Also the spreadsheet is about to get more complicated as I will be adding a new row (as well as new worsksheets with additional tables) that will increase the choices that must be made. I am reluctant to write the excel formula with even more nested if-then-else statements.

MUcalc-for-ee.xls
When you write a UDF - User Defined Function (e.g., without the Application.Volatile command), the UDF only calculates when one of its input parameters change.

e.g., your function:

=Linearinter22d(param1, param2.Value, param3.Value)

would update anytime param1, param2, or param3 values changed.

With Application.Volatile, the function would update anytime the spreadsheet calculated.

Here's more on Application.Volatile:http://www.decisionmodels.com/calcsecretsi.htm

Recall, you were looking for a way to reference a range and the current cell/column where your function resides. I provided that, with additional commentary that you can then improve on this function, should you choose to do so, to possibly eliminate the need for the Application.Volatile statement.

However, that begins perhaps to defeat the purpose of "simplifying the equations" in Excel Formulas and coding in VBA, which was your insight/decision. If you start putting those parameters back into the function, the result could be just as complex an excel formula as when you started! :)

I fixed a bug in my example - note the set param2, param3 needs to reference the destination cell's column.

Open in new window

Enjoy!Dave

MUcalc-for-ee-r2.xls