VBA function wont update in worksheet

[Diarmuid]

Hi, I've created a simple function using VBA to carry out some checks on
figures in a worksheet. It takes some figures from the worksheet to set
ranges and then carries out a comparison, returning a different text message
and setting cell formatting based on the comparison checks. This function is
then entered into each row of the worksheet. The problem I have is that in
order to get the function to update whenever I make changes to the comparison
figure I have to enter each cell where the function is , make a change and
then press the enter key. Pressing F9 does not seem to affect this either.
It may be something simple that I am doing as I am relatively new to
programming on spreadsheets. Any advice would be appreciated.

thanks

 

[Carim]

Hi,

Are you talking about an UDF ...?
Have you tried the instruction : Application.Volatile ...?

Suggestions would be easier if you were to share your function with
us ...

HTH

 

[Chip Pearson]

I am assuming that you have made sure that the calculation mode is set to
automatic. The general rule for writing functions in VBA that are called
from worksheet cells is to include *everything* that is needed by the
function as input parameters to the function. Excel will only calculate a
cell when it needs to be calculated; that is, when a precedent of the cell
containing the function is changed. If your function reads values from a
cell directly, Excel may not recalculate the function when that cell changes
because Excel doesn't and can't know what cells are being used within VBA
code.

An example will illustrate this clearly. Consider the following VBA
function.

Function MyAAA() As Long
MyAAA = Range("A1").Value * 10
End Function

With this function, which refers directly to cell A1, Excel will not
recalculate the function when A1 is changed, because Excel can't know that
the VBA code refers to A1. A much better function would be

Function MyAAA(TheCell As Range) As Long
MyAAA = TheCell.Value * 10
End Function

With this second function, the cell reference is passed to the function,
with a call like

=MyAAA(A1)

Since cell A1 is referenced in the formula, not the function, Excel will
properly recalculate the function.

It is possible to force Excel to recalculate the function when *any*
calculation is made, even if the function does not really need to be
recalculated. You can do this by including the following line of code as the
first line in the VBA Function:

Application.Volatile True

as in

Function MyAAA(TheCell As Range) As Long
Application.Volatile True
MyAAA = TheCell.Value * 10
End Function

The drawback of Application.Volatile is that it will cause unnecessary
calculations which may cause noticeable and perhaps unacceptable delays.

See also http://www.cpearson.com/Excel/WritingFunctionsInVBA.aspx .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Diarmuid]

Hi,

Thanks for your help, that probably explains the problem. The worksheet
only contains a couple of hundred rows so the application.volatile will
probably suffice. I will keep your other answer in mind though and use it
for future reference.

regards

Diarmuid Sinnott