Auto/Re-Calculate user function

[Brian K. Sheperd]

I created a VBA/XLA function to extract matching data from an Access
database (similar to a vlookup function); however, if I change the values in
the database, I cannot get the formulas in excel to update. If I hit F2 on
the cell of the part number and hit enter, or if I change the part number,
then the function to fill in the price will update. F9 or re-opening the
file doesn't do anything. I would like to have it update when the file is
opened. Is there anything that I can do to get this to work as I was hoping
for?



Thanks,

Brian

 

[Tom Ogilvy]

Ctrl+Alt+F9

 

[Brian K. Sheperd]

I just tried the Ctrl+Alt+F9, but it did not do anything. I am using Office
2000 -- is that combination good for all office versions? Or do I need to
enable something in the options area?

Thanks,
Brian

 

[Tom Ogilvy]

That does a full recalc. If it doesn't work for you, I am not aware of a
combination that does more. I suspose it is possible that you have some
unhandled error in a UDF that might be causing calculation to quit.

Another thing you could try is to select one cell or all cells and doing
Edit=>Replace
What: =
With: =

so you replace all equal signs with equal signs. This should be equivalent
to doing F2 in every cell (of course you could select a smaller range and do
it as well).

 

[Charles Williams]

Excel does not recalculate your function when you change a value in the
database because Excel does not know the value has changed.

If you force Excel to calculate your function then if it is working
correctly it should retrieve the correct value from the database.
The methods Tom has given you will force Excel to calculate your formula,
and therefore should work: suggest you try debug to see why ctrl/alt/f9 does
not work for you.

If you only want to retrieve from the database when you open th Excel
workbook, you could add Application.Calculatefull (assuming you are using a
version of Excel later than XL 97) to the auto_open or workbook_Open
subs/events.

If you add Application.volatile to your function it will be recalculated at
every calculation.


--
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

 

[Brian K. Sheperd]

The function does work in a basic sense. If you key in a new part number or
change the part number the function will grab the correct value from the
database. I thought that if I force a calculation that it would recalculate
the sheet. I even tried the Tools-Options-Calculation -- Calc Now and Calc
Sheet, but they wouldn't update the function. My next step was going to
look at the auto-open events. I was just going to try to edit each part
number and replace it with the same value, but I would prefer not to take
that step. I will try the application.calculatefull and the
application.volatile. Do I just add these items in the beginning? Are they
boolean (i.e., application.volatile = true)?
Thanks,
Brian

 

[Charles Williams]

F9, Calc Now and Calc Sheet all do recalculates (excel only recalculates the
cells it thinks need calculating (changed or downstream dependent on a
change that Excel knows about).

Ctrl-Alt-F9 (all at the same time) and Application.CalculateFull trigger
full calculations (excel calculates ALL formulae regardless of whether it
thinks they need recalculating or not).

Application.Volatile is a statement that you can add to your function
definition (anywhere after the Function and before the End Function,
although conventionally it goes at the top after the Dim statements). This
causes Excel to always think your function needs recalculating, which is
probably inefficient and slows things down.

Application.CalculateFull is a statement that you can add to a Sub (its not
allowed in a worksheet function) and is a method that asks Excel to do a
full calculation.

you dont need the =true bit for either of these statements.

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com