Update of UDF (User Defined Function)

[Peter Karlström]

Hi

I'm developing a COM Addin and an Automation Addin for Excel XP and 2003.
The COM Addin is loaded at startup and it checks that the Automation Addin
is loaded. All this works fine.

The Automation Addin consists of a single function, GetProp, which returns
a CustomDocumentProperty value.
I can for instance type =GetProp("su_title") in a cell and return the value
from
that perticular property.

The "problem" is that the next time I open the spreadsheet, the value is not
updated until I edit the cell (F2) and presses Enter.
Also, the formula reads =EXComFunctions.XLFunctions.GetProp("su_title")
rather than =GetProp("su_title") until I edit the cell (F2) and presses Enter.

F9 (Updating) does not make any difference.

Does anyone know how to solve this?

Thanks in advance

 

[Jim Thomlinson]

There is notihing about a change to a document property that will cause the
cell to believe that it needs to be recalculated so it will not change until
you edit the formula and it must be re-evaluated. To fix this you can add
Application.Volatile
to they function. This will make the function volatile (similar to now())
and it will be re-evaluated every time a calculation occures.

 

[Peter Karlström]

Hi Jim

Thanks for your reply.
Maybe I was fuzzy about the art of my problem.

The Automation Addins works fine and updates automatically but only
after I edit the cell and pressed Enter 1 first time.
After that, every change in data updates if I for instance change a value
in another cell.

I use Volatile in the code, but it seems that something must "wake the code
up"
before it automatically updates.

Any suggestions?

Regards

 

[Jim Thomlinson]

I have not tried using Volatile in a COM addin. Have you tried...

Shift + Ctrl + ALT + F9

Which is a full calculation of all formulas. Did that refresh your value...

 

[Niek Otten]

Even Application.Volatile will only recalculate the function if Excel thinks any recalculation is necessary.

In the worksheet, use
=YourFunction()+NOW()*0

This should force a recalc on loading
Please let us know if it helps

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi Jim
|
| Thanks for your reply.
| Maybe I was fuzzy about the art of my problem.
|
| The Automation Addins works fine and updates automatically but only
| after I edit the cell and pressed Enter 1 first time.
| After that, every change in data updates if I for instance change a value
| in another cell.
|
| I use Volatile in the code, but it seems that something must "wake the code
| up"
| before it automatically updates.
|
| Any suggestions?
|
| Regards
| --
| Peter Karlström
| Midrange AB
| Sweden
|
|
| "Jim Thomlinson" wrote:
|
| > There is notihing about a change to a document property that will cause the
| > cell to believe that it needs to be recalculated so it will not change until
| > you edit the formula and it must be re-evaluated. To fix this you can add
| > Application.Volatile
| > to they function. This will make the function volatile (similar to now())
| > and it will be re-evaluated every time a calculation occures.
| > --
| > HTH...
| >
| > Jim Thomlinson
| >
| >
| > "Peter Karlström" wrote:
| >
| > > Hi
| > >
| > > I'm developing a COM Addin and an Automation Addin for Excel XP and 2003.
| > > The COM Addin is loaded at startup and it checks that the Automation Addin
| > > is loaded. All this works fine.
| > >
| > > The Automation Addin consists of a single function, GetProp, which returns
| > > a CustomDocumentProperty value.
| > > I can for instance type =GetProp("su_title") in a cell and return the value
| > > from
| > > that perticular property.
| > >
| > > The "problem" is that the next time I open the spreadsheet, the value is not
| > > updated until I edit the cell (F2) and presses Enter.
| > > Also, the formula reads =EXComFunctions.XLFunctions.GetProp("su_title")
| > > rather than =GetProp("su_title") until I edit the cell (F2) and presses Enter.
| > >
| > > F9 (Updating) does not make any difference.
| > >
| > > Does anyone know how to solve this?
| > >
| > > Thanks in advance
| > >
| > > --
| > > Peter Karlström
| > > Midrange AB
| > > Sweden

 

[Peter Karlström]

Hi Jim

Thanks. It actually did it!

So, I solved my problem by running the following code in the WorkBookOpen
event: ExcelApp.CalculateFull, which apparently is the same as
CTRL+Alt+Shift+F9.

Regards