[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

VBA UDF Calculation (not

Greg Longtin

12/14/2006 9:02:00 PM

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


4 Answers

Tim Williams

12/14/2006 9:07:00 PM

0

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

--
Tim Williams
Palo Alto, CA


"Greg Longtin" <ReLongtinMove@att.net> wrote in message news:eu28KM8HHHA.2236@TK2MSFTNGP02.phx.gbl...
> 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
>
>

Greg Longtin

12/14/2006 9:39:00 PM

0

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

12/15/2006 2:03:00 AM

0

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" <ReLongtinMove@att.net> wrote in message
news:eegE3g8HHHA.536@TK2MSFTNGP02.phx.gbl...
> 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
>
>

Greg Longtin

12/16/2006 6:38:00 PM

0

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