Updating Cells that Get Value from Macro

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

Hi.

I've written a VB macro that references a cell by name via the Range
function. I call the macro from various cells in my spreadsheet. The
value the macro returns becomes the value of the cell.

I'd like the cells that use the macro to automatically update when the
referenced cell value is changed. This is not happening. What do I
need to do to get that to work? If it can't be done, is there a
manual way to cause all cells on the worksheet to get refreshed all at
once? As it stands, the only way I can update the cells is to go
through them manually, one at a time, and click inside the formula bar
for each one. I'm sure there must be a better way.

Thanks for the better way :),

Ken
 
Have you macro put in a formula rather than a value.

Assume E9 is the cell they reference. In the cell where the value is place,
the macro should put the formula

=E9

rather than the value displayed in E9.

As an example.
 
Hi, and thanks for responding.

The problem is that the named cell is referenced from inside the
macro--not from inside the spreadsheet. For instance, consider this
macro:

Function AddIt(ByVal NumToAdd) As Integer
AddIt = Range("NumFromSheet").Value + NumToAdd
End Function

Also consider the following:

NumFromSheet is the name of cell A1.
A1 has the value 10.
Another cell, say B1, has its value set to: =AddIt(6)

B1 displays 16.

When I change NumFromSheet's value to 15, B1 still displays 16 until I
click into its formula to force an update at which time it displays
22.

I want to find out how to get B1 to update as a result of entering a
new value into NumFromSheet.

Thanks in advance!

Ken
 
Excel minimizes the amout of work it must perform on a recalc by not
recalculating cells that don't need it. It determines this by looking at the
arguments of the function of the cell. You have cleverly disguised the
dependency on A1 by concealing that dependency in the code, so Excel doesn't
see any dependency and never recalculates your formula. If you want to
continue using this construct, then the only other alternative is to make
the function volatile or leave it the way it is and combine it with a
volatile function.

Function AddIt(ByVal NumToAdd) As Integer
Application.Volatile
AddIt = Range("NumFromSheet").Value + NumToAdd
End Function

so whenever there is a calculation, cells with this formula will be
calculated. An alternative is to do

=Addit(6)+rand()*0

and leave your function the way it is. Rand() is volatile, so the whole
string will be reevaluated on each calculation.

Perhaps this is a simplification, but if this is your actual function, I
don't see what you are gaining over using

=NumFromSheet+6

Which will update.
 
Tom Ogilvy said:
Perhaps this is a simplification, but if this is your actual function, I
don't see what you are gaining over using
Click to expand...

It's not the actual function, just a simple representation of the
idea. I thought about your advice and realized all I needed to do was
add a parameter called something like Number and use it to pass in
NumFromSheet--instead of just referencing NumFromSheet directly in the
macro. Excel sees what's up and the update works. Thanks for the
guidance!

Ken
 
Back
Top