Why recalculation does not start?
 

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

Why recalculation does not start?
 

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

Why recalculation does not start?
 

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

Why recalculation does not start?
 

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