Dragging array UDF functions

[Eduardo]

Hi,

I built a array function and it is working pretty well, but when
drag it I get a "value" msg.

Thanks for any help.

Eduard

 

[Frank Kabel]

Hi
you may post the code of your UDF. Also the exact formula you use in
wour worksheet

 

[Guest]

Hi Eduardo
Are you using the appropriate reference type (absolute,relative)? And as Frank said, always a good idea to post the formula and code. Otherwise we really don't have any info to work with


Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- Eduardo > wrote: ----

Hi

I built a array function and it is working pretty well, but when
drag it I get a "value" msg

Thanks for any help

Eduard

 

[Eduardo]

Hi folks,

Thanks Frank and Mark for their answers, and sorry I did not posted th
code. Here it is.

I still don't know what to do. Thanks again.

Eduardo.

Public Function samLMR(x As Variant, Optional a As Double = 0#
Optional b As Double = 0#) As Variant
Dim xmom() As Double
Dim xm() As Double
Dim sum(8) As Double

Dim R As Integer
Dim C As Integer
Dim ReturnColumn As Boolean

R = Selection.Rows.Count
C = Selection.Columns.Count

n = x.Count
x = selectnumbers(x) ' sort the data
n = n - nfails

If R < C Then
nmom = C - 1
Else
nmom = R - 1
End If

zero = 0
one = 1

mn = Application.WorksheetFunction.Min(20, n)

If (nmom > mn) Then
AlertMSG (" *** error *** routine samLMR : parameter nmo
invalid.")
Exit Function
End If

For i = 1 To nmom
sum(i) = zero
Next i

If (a <> zero) Or (b <> zero) Then
If (a <= -one) Or (a >= b) Then
AlertMSG (" *** error *** routine samLMR : plotting-positio
parameters invalid.")
Exit Function
End If
'
' PLOTTING-POSITION ESTIMATES OF PWM'S
'
For i = 1 To n
ppos = (i + a) / (n + b)
term = x(i)
sum(1) = sum(1) + term
For j = 2 To nmom
term = term * ppos
sum(j) = sum(j) + term
Next j
Next i

For j = 1 To nmom
sum(j) = sum(j) / n
Next j
Else
'
' UNBIASED ESTIMATES OF PWM'S
'
For i = 1 To n
z = i
term = x(i)
sum(1) = sum(1) + term
For j = 2 To nmom
z = z - one
term = term * z
sum(j) = sum(j) + term
Next j
Next i
y = n
z = n
sum(1) = sum(1) / z
For j = 2 To nmom
y = y - one
z = z * y
sum(j) = sum(j) / z
Next j
End If ' (a <> zero) Or (b <> zero) Then ...

'
' L-MOMENTS
'

k = nmom
p0 = one
If (nmom - Fix(nmom / 2) * 2 = 1) Then
p0 = -one
End If

For kk = 2 To nmom
ak = k
p0 = -p0
p = p0
temp = p * sum(1)
For i = 1 To k - 1
AI = i
p = -p * (ak + AI - one) * (ak - AI) / (AI * AI)
temp = temp + p * sum(i + 1)
Next i
sum(k) = temp
k = k - 1
Next kk

ReDim xmom(nmom)
xmom(1) = sum(1)

If (nmom > 1) Then

xmom(2) = sum(2)

If (sum(2) = zero) Then
AlertMSG (" *** error *** routine samLMR : all data value
equal.")
Exit Function
End If

If (nmom > 2) Then
For k = 3 To nmom
xmom(k) = sum(k) / sum(2)
Next k
End If

End If

ReturnColumn = False
If R > 1 Then
If C > 1 Then
ReDim xm(R, C)
Else
ReDim xm(R)
ReturnColumn = True
End If
Else
ReDim xm(C)
End If

For i = 1 To nmom + 1
If i <= 2 Then
xm(i) = xmom(i)
ElseIf i = 3 Then
xm(i) = xmom(2) / xmom(1)
Else
xm(i) = xmom(i - 1)
End If
Next i

If ReturnColumn = True Then
samLMR = Application.WorksheetFunction.Transpose(xm)
Else
samLMR = xm
End If

End Function 'samLMR

 

[Frank Kabel]

Hi
without looking too much at your function I believe the main probelm is
that you rely within your function on the current selection of cells:
R = Selection.Rows.Count
C = Selection.Columns.Count

This is IMHO not robust. You should use a range as parameter to define
the range to use. e.g. if you select a different region and hit F9 (to
recalculate) the function will get a different result!

This may also be the reason for your error while dragging as you change
the current selection.

 

[Eduardo]

Thanks Frank for your answer. I am struggling to implement a solutio
for what you pointed out as a problem. I included

OutputRegion As Range

as parameter of my function and got rid of the selection region stuff
I tried to write the results of the function using:

i = 1
For Each Celula In OutputRegion
Celula.Value = xm(i)
i = i + 1
Next Celula

where Celula is defined as Range. The result I got is still "value".

Thanks again.

Eduardo

 

[Frank Kabel]

Hi Eduardo
a function cannot change the cell contents of other cells. No chance to
do that. A function can only return a value to the cell it resides in.