evaluate function upon startup

  • Thread starter Thread starter vinnie
  • Start date Start date
V

vinnie

Hello,

I have a VBA built in function applied to a set of summations at the bottom
of my sheet. I am also running another VBA macro that evaluates this sheet
and operates on it. My question is that how can I make excel evaluate my set
up summations using that function I've built upon startup.


Currently, I'd have to manually go into the function's formula line (hit F2
on cell) , and then press enter to get excel to evaluate it. I'd like a vba
code to make excel do this automatically for me. Thanks.
 
It sounds like you're writing about a user defined function.

If it's at all possible, it's much better to pass the range of cells that the
calculation needs to the function:

For instance, if you have a UDF called =mysum(), it's better to call your
function:

=mysum(a1:a10)

then to have your code resize the range internally so you could just call it
like: =mysum(a1)

If you don't share the cells that are referred to in your function with excel
(via the worksheet function call), then excel doesn't know when to recalculate.
So it just sits there waiting for you to do the F2|enter thing.

Some workarounds (that I try not to use!):

You could force a recalculation (manually or via code).
You could add:

application.volatile = true

to the top of your function's code:

option explicit
function mysum(rng as range) as double
application.volatile = true
'your code
end function.

But now excel will recalculate your function when it has to recalculate
something else--this can slow things down--or if you change something that
doesn't cause a recalculation, then your function's value may still not be
correct.

====
There are times when you have to do this: If the change you make doesn't force
a recalculation. A typical example is when you want to sum a range of
cells--but only if the font is red/bold/etc. Changing the cell's formatting
doesn't cause a recalc.

So you can use the "application.volatile = true" line and make sure you force a
recalc before you trust your function's result.

====
All that said, you may want to post your UDF to see if anyone has more ideas to
help.
 
Back
Top