Creating functions with formulas in excel in VBA

Question

I'm trying to create a function that calculates Drawdown. It would work as follows:

I have a series of quotes for a specific stock in column B: B (example)
I want to know the maximum drawdown, that is, how much would be the biggest decrease in the quote.

In this case the biggest indentation occurs in the yellow area!that is, the formula would look like: Drawdown = (MaxValue/Value)-1 ==> Drawdown = (13/9)-1

I tried as follows, with no result:

Public Function MDD(ByVal Selection0, ByVal Selection1)
    'Function Max DrawDown

    Dim i As Long
    Dim Drawdown0 As Long
    Dim Drawdown1 As Long
    
    i = 2
    
    Drawdown0 = "(" & Selection0 & "/MAX(" & Selection1 & ")) - 1"


    
    While i < Plan1.Range("B" & Rows.Count).End(xlUp).Row + 1
        Drawdown1 = "(" & Selection0 & "/MAX(" & Selection1 & ")) - 1"
        If Drawdown1 > Drawdown0 Then
            Drawdown0 = Drawdown1
        End If
        i = i + 1
    Wend
   
   MDD = Drawdown0
End Function

Sub lsMDD()
    Application.MacroOptions Macro:="MDD", Category:=4
End Sub

Where's the error?

It seems like you are conflating formulas and VBA.... Drawdown0 = "(" & Selection0 & "/MAX(" & Selection1 & ")) - 1" is trying to assign a string (text) to a Long. — BigBen
– BigBen, Commented Jun 23, 2020 at 18:00
@BigBen I need to use a formula to calculate or retrieve the maximum between the arranged values ... In this case, I am unsure how to do it. — Shurastey
– Shurastey, Commented Jun 23, 2020 at 18:02
Application.Max might be helpful. You have to actually translate the formula logic to VBA. — BigBen
– BigBen, Commented Jun 23, 2020 at 18:05

Excelosaurus · Accepted Answer · 2020-06-23 18:59:26Z

3

You don't need to iterate over the range. Look at Application.WorksheetFunction - it's got everything you need.

Public Function MDD(ByVal pRange As Variant) As Variant
    MDD = Application.WorksheetFunction.Max(pRange) / Application.WorksheetFunction.Min(pRange) - 1
End Function

edited Jun 23, 2020 at 18:59

answered Jun 23, 2020 at 18:14

Excelosaurus

2,8591 gold badge17 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Excelosaurus Over a year ago

MDD takes one argument, pRange, which is the range covering your source data, say A1:A10. Application.WorksheetFunction exposes many Excel worksheet functions to VBA. Here I just used WorksheetFunction's Max and Min to do the same as formula "=MAX(A1:10)/MIN(A1:A10)-1" would have done on the worksheet.

Shurastey Over a year ago

Great! Thank you! I will study how 'Application' functions (Previously: Worked perfectly! Could you please explain to me how this function works, if you are not asking too much?) Sorry...edited

Collectives™ on Stack Overflow

Creating functions with formulas in excel in VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related