Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-22-2006, 12:34 PM   #1
standrd
Guest
 
Posts: n/a
User Defined Function not recalculating

Hi,

I'd be grateful if anyone can help me with a User Defined Function problem, a simple example of which is shown in the attached file.

The problem is that my function ("cof_pricer") works perfectly well, but when the data (C3:C5) is changed, the function doesn't automatically recalculate In each case, I have to go into the respective cells (E3:E5), 'F2' to edit the cell, press enter, & the correct calculation takes place.

This is obviously less than satisfactory, and I need to get the function to recalculate automatically every time the data changes. I have included a second (very simple function) called Two_mth_Ave (d4:d5), which DOES recalculate on changing the data, to illustrate that it isn't Excel that's at fault!

There is presumably something in my VBA coding that needs tweaking.

Does anyone have any ideas?

Many thanks,

David Standring.
Attached Files
File Type: zip excelproblem.zip (13.1 KB, 233 views)

  Reply With Quote
Old 02-23-2006, 04:11 AM   #2
Rod D
Registered User
 
Join Date: Feb 2006
Location: Midlands, UK
Posts: 87
Thanks: 0
Thanked 0 Times in 0 Posts
Rod D is on a distinguished road
Hi David

The problem
Your function is not recalculating because it is not "dependant" on the cells being changed. i.e. Your function is looking for a change in the Month Number column as a trigger to re-calc - which is not happening. It does not have the "intelligence" to see that it needs to recalc because the % has changed in another column. That is why when you F2 it, it forces a recalculation.

The solution:
Append this line immediately below the DIM statements.

Application.Volatile

This will force the re-calc of the function on ANY cell change.

or
Include (somehow!) the % value in the function definition, so that it "sees" the change and initiates a re-calc.
The Warning:
If you use the Application.Volatile in many UDF's, or the same UDF many times in a worksheet, you can SERIOUSLY downgrade the performance of your spreadsheet as all these functions will re-calculate on every cell change...

HTH
Rgds
Rod
Rod D is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Expires after certain date Atholl Modules & VBA 11 06-10-2018 06:18 PM
Error - User defined type not defined adamnorsworthy General 15 09-28-2011 03:14 PM
"Cannot define a public user defined type within an object module" u0107 Modules & VBA 3 02-02-2004 07:03 PM
User defined type - not defined Howlsta Modules & VBA 2 08-10-2001 08:42 AM




All times are GMT -8. The time now is 05:53 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World