UDF Excel that accepts both Range and Array as Parameter

[Kanzisa]

I am trying to create a new UDF MaxDrawdown_General_Purpose that takes the values from selected cells or range of cells and at the same time that takes the value from an array in VBA ambient. How can i merge 2 functions below in a new one MaxDrawdown_General_Purpose ? Thank you in advance.

Function MaxDrawdown(data() As Double) As Variant 'Runs only in VBA ambient
         Dim N As Integer
         Dim i As Integer
         Dim highestPrice As Double
         Dim currentPrice As Double
         Dim currentDrawdown As Double
            
         N = UBound(data)
        
         If N > 0 Then
             highestPrice = 1
             MaxDrawdown = 0
    
             For i = 0 To N - 1
                 currentPrice = highestPrice * (1 + data(i) / 100)
                 currentDrawdown = (highestPrice - currentPrice) /      highestPrice
                 highestPrice = IIf(currentPrice > highestPrice,      currentPrice, highestPrice)
                 MaxDrawdown = IIf(currentDrawdown > MaxDrawdown,      currentDrawdown, MaxDrawdown)
             Next i
    
             MaxDrawdown = 100 * MaxDrawdown
         Else
             MaxDrawdown = "undefined"
         End If
     End Function
    
    
    
Function MaxDrawdownExcel2(rng As Range) As Double  'Runs only in Excel
        
         Dim N As Integer
         Dim i As Integer
         Dim highestPrice As Double
         Dim currentPrice As Double
         Dim currentDrawdown As Double
         Dim data() As Variant
        
         data = rng.Value
     
         N = UBound(data)
    
         If N > 0 Then
             highestPrice = 1
             MaxDrawdownExcel2 = 0
    
             For i = 1 To N
                 currentPrice = highestPrice * (1 + data(i, 1) / 100)
                 currentDrawdown = (highestPrice - currentPrice) /      highestPrice
                 highestPrice = IIf(currentPrice > highestPrice,      currentPrice, highestPrice)
                 MaxDrawdownExcel2 = IIf(currentDrawdown >      MaxDrawdownExcel2, currentDrawdown, MaxDrawdownExcel2)
             Next i
    
             MaxDrawdownExcel2 = 100 * MaxDrawdownExcel2
         Else
             MaxDrawdownExcel2 = "undefined"
         End If
     End Function

 

[Domenic]

First, declare data as Variant.

Then, check whether data is a Range object. If so, convert it to an array.

Then, instead of using index, use For Each/Next to loop through each item in the array. The reason for this is that data may contain either a one dimensional or two dimensional array. And so the For Each/Next loop easily handles both these situations.

Function MaxDrawdown_General_Purpose(data As Variant) As Variant

    Dim highestPrice As Double
    Dim MaxDrawdown As Double
    Dim itm As Variant

    If TypeOf data Is Range Then
        data = data
    End If
    
    highestPrice = 1
    MaxDrawdown = 0

    For Each itm In data
        currentPrice = highestPrice * (1 + itm / 100)
        currentDrawdown = (highestPrice - currentPrice) / highestPrice
        highestPrice = IIf(currentPrice > highestPrice, currentPrice, highestPrice)
        MaxDrawdown = IIf(currentDrawdown > MaxDrawdown, currentDrawdown, MaxDrawdown)
    Next itm

    MaxDrawdown = 100 * MaxDrawdown
    
End Function

Hope this helps!

 

[Kanzisa]

Thank you for your suggestions.
I run your code (with some integration data=data.Value) but the output is ""

Function MaxDrawdown_General_Purpose(data As Variant) As Variant

Dim highestPrice As Double
Dim MaxDrawdown As Double
Dim itm As Variant
Dim currentPrice As Double

Dim currentDrawdown As Double

If TypeOf data Is Range Then
data = data.Value
End If

highestPrice = 1
MaxDrawdown = 0

For Each itm In data
currentPrice = highestPrice * (1 + itm / 100)
currentDrawdown = (highestPrice - currentPrice) / highestPrice
highestPrice = IIf(currentPrice > highestPrice, currentPrice, highestPrice)
MaxDrawdown = IIf(currentDrawdown > MaxDrawdown, currentDrawdown, MaxDrawdown)
Next itm

MaxDrawdown = 100 * MaxDrawdown

End Function

I seems to me that using the test "If TypeOf data Is Range might not work as expected if data is an array. To me, we should check the data type differently

If TypeName(data) = "Range" Then
data = data.Value
End If.

But I consider myself only a practitioner, i wouldn't know how to procede.

 

[Domenic]

There should be no need to make any changes to my code. It should remain as data = data