VBA UDF Calculation (not)

[Greg Longtin]

To all,

I've got a UDF, but Excel will only calc it when the cell containing it is
edited, ie, F2, enter.

How can I force a calc? I tried 'Calculate' on the SheetActivate event, and
nothing happens...

TIA,

Greg

 

[Tim Williams]

What are the inputs to the UDF? Does it use any data not passed as a parameter?

 

[Greg Longtin]

Tim,

What are the inputs to the UDF? Does it use any data not passed as a
parameter?

One cell value, which is the name a worksheet in the workbook to use for the
calculation.

Greg

 

[NickHK]

Greg,
Excel normally does not recalculate functions if their inputs have not
changed.
But may be application.Volatile will give the desired behaviour.

NickHK

 

[Greg Longtin]

Tim,

Found a solution. Simply put, the udf find a date in other sheets, and it's
used in a summary sheet. I orginally set it up with one parameter, which
tells it what sheet to use. The workbook is a summary of our truck
maintanance (one sheet per), and the udf is used in the summary page.

Anyway, the summary sheet has a cell that uses TODAY() for revision date. I
added this as a parameter to the UDF, and although I'm not using it for
anything, it forces the recalc.

Thanks,

Greg