Why recalculation does not start?

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
 
V

vsoler

Hi,

Try

Application.volatile

as the first line of your UDF

Mike

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

Regards
 
P

Patrick Molloy

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top