Why recalculation does not start?

  • Thread starter Thread starter vsoler
  • Start date Start date
V

vsoler

I have the following function:

Option Explicit

Static Function abc(a, Optional b As Range)
a = a
If a = 0 Then
Dim m
m = Range("rr")
abc = 0
Else
abc = m(a, 1)
End If
End Function

In my sheet I have:
=abc(0,A1:A3)
=abc(1,A1:A3)
=abc(2,A1:A3)
=abc(3,A1:A3)

When I change a cell in A1:A3, why recalc does not start? I am forced
to use Ctrl-Alt-F9, which I don't like.

What's wrong with my code?

Vicente Soler
 
Hi,

Try

Application.volatile

as the first line of your UDF

Mike
Click to expand...

Thank you Mike, but can you tell me why it does not recalculate? I'd
like to understand why.

Regards
 
the function is meaningless

a=a ??

at one line m is assigned a vlaue from a range, but the function returns
zero; at another point m seesm to be an array, but it isn't populsted with
any data and an error results.

declaring variables is a MUST DO so as to prevent typing and syntactual
errors at the minimum

here's my guess at what you want:

Option Explicit
Public Function abc(a As Long, Optional b As Range)
Dim m As Variant
If a = 0 Then
abc = Range("rr").Value
Else
m = b
abc = m(1, a)
End If
End Function
 
Back
Top