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



"vsoler" wrote:

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

On Sep 22, 9:50*pm, Mike H wrote:
Hi,

Try

Application.volatile

as the first line of your UDF

Mike

"vsoler" wrote:
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

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








"vsoler" wrote:

On Sep 22, 9:50 pm, Mike H wrote:
Hi,

Try

Application.volatile

as the first line of your UDF

Mike

"vsoler" wrote:
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

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

Regards