VBA Created Worksheet Function Not Recalculating

[Falcon Feet]

I created my own Excel worksheet function in VBA. The function (formula)
works correctly when entered in a cell, but it does not recalculate when the
cell that the formula references changes. FYI, the workbook's "Calculation"
option IS set to Automatic.

 

[Mike H]

Hi,

Put this as the first line of your function

Application.volatile

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Don Guillett]

As ALWAYS, post your code for comments

application.volatile

as your FIRST LINE in the macro

 

[Jim Thomlinson]

Post the function. There are a couple of ways to make it calculate. it
depends on the nature of the function which will be best.

1. you can add application.volatile to the start of the function. that makes
the function volatile similar to the now() function. The good new is that it
will always recaclualte. Teh bad new is that it recalc every time a calc runs
adding a lot of overhead.

2. Add a cell reference to as an argument to the function. If the ceel
referenced in the argument changes then the formula is dirtied and will recalc

public function SumStuff(byval MyCell as range) as double
SumStuff = MyCell + MyCell.Parent.Range("A1").value
end function

The function above would be used in a cell like =SumStuff(B1). It Adds B1 to
A1. If B1 changes then the formula will recalc. If A1 is changed then the
formula will not recalc as A1 is not one of the arguments being passed in.

 

[Dave Peterson]

Make sure you have excel in automatic calculation mode.

And make sure that your function gets all its ranges passed to when you write
the formula in the cell.

=myFunc(a1,a2,a3)

Option Explicit
Function myFunc(Rng1 as range, rng2 as range, rng3 as range) as double
dim myTot as double
mytot = 0
if isnumeric(rng1.value) then
mytot = mytot + rng1.value
end if
if isnumeric(rng2.value) then
mytot = mytot + rng2.value
end if
if isnumeric(rng3.value) then
mytot = mytot + rng3.value
end if
myFunc = mytot
End function

Not...
=myFunc2(a1)

Option Explicit
Function myFunc2(Rng1 as range) as double
dim myTot as double
mytot = 0
if isnumeric(rng1.value) then
mytot = mytot + rng1.value
end if
if isnumeric(rng1.offset(1,0).value) then
mytot = mytot + rng1.offset(1,0).value
end if
if isnumeric(rng1.offset(2,0).value) then
mytot = mytot + rng1.offset(2,0).value
end if
myFunc2 = mytot
End function

Excel only knows when to recalc the myfunc2 with the first cell changes--not
when A2 or A3 change.

Worst still, you won't/shouldn't be able to trust the results unless you look
right after a recalc.

 

[Chip Pearson]

Your function should get every value used in its calculation as an
input parameter to the function. E.g.,

Function GoodPlus(V1 As Double, V2 As Double) As Double
GoodPlus = V1+V2
End Function

If your function has cell references within it, Excel doesn't know
about them, so it will not calculate when one of those reference
values is changed. E.g.,

Function BadPlus(V1 As Double) As Double
BadPlus = V1 + Range("A1").Value
End Function

This function will not recalculate when A1 is changed, because Excel
has no knowledge that A1 is used by the function and doesn't see the
change in A1 to be a reason to recalculate the function.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com



On Fri, 26 Feb 2010 07:37:01 -0800, Falcon Feet <Falcon