Problem of calculation with excel

  • Thread starter Thread starter Narbo
  • Start date Start date
N

Narbo

Hi everybody!

I have the following problem with VBA and Excel.
I have built a function with an array as input and returning another
array.
The function works properly when being used in one sheet. But when I
go to another sheet, recalculate (F9) and then come back to the first
sheet, all my values disappear and I have 0 everywhere instead of the
right values.
This problem only occurs when many columns use my used defined
function, so it may be a memory pb but I don t know

Can anybody tell me where this problem comes from?

Thanks

Alex

Here is the code of my function :

Function MaxDrawdown(series As Range) As Variant

Dim n As Integer
n = series.Count

Dim MyArray As Variant
MyArray = series

Dim min As Double
min = MyArray(1, 1)

Dim minpos As Integer
minpos = 1


For i = 1 To n

If MyArray(i, 1) <= min Then
minpos = i
min = MyArray(i, 1)

End If

Next i

Dim before As Integer
before = 0

Dim after As Integer
after = 0

Do While ((minpos - before) > 0 And MyArray(minpos - before, 1) < 0)

before = before + 1

If minpos - before = 0 Then
Exit Do
End If
Loop


Do While ((minpos + after) <= n And MyArray(minpos + after, 1) < 0)
after = after + 1
If (minpos + after) = n + 1 Then Exit Do
Loop


Dim result As Variant
ReDim result(100)

If min = 0 Then
For k = 0 To 36
result(k) = 0
Next
Else

For k = 0 To after + before - 2
result(k) = MyArray(minpos - before + k + 1, 1)
Next

If after + before < 38 Then
For k = after + before - 1 To 36
result(k) = 0
Next
End If
End If

MaxDrawdown = Application.WorksheetFunction.Transpose(result)

End Function
 
Back
Top