CODEWITHSUNDEEP
excelvba
Shurastey
Shurastey

Reputation: 39

Excel does not update function automatically

I have a spreadsheet for calculating maximum drawdown, that is, what is the highest percentage of drop in relation to the top. In this case it is the highest possible negative percentage. enter image description here

enter image description here

It can be seen that the biggest drop is between $ 90 and $ 40 from (13) to (14). My VBA looked like this:

Public Function MDD(ByVal iRange As Variant, ByVal fRange As Variant) As Variant
    Dim primLinha As Integer
    Dim coluna As Integer
    Dim ultLinha As Integer
    Dim contador As Integer
    Dim MDDTemp As Variant
       
    primLinha = Mid(iRange.Address(ReferenceStyle:=xlR1C1), Application.WorksheetFunction.Search("R", iRange.Address(ReferenceStyle:=xlR1C1)) + 1, Application.WorksheetFunction.Search("C", iRange.Address(ReferenceStyle:=xlR1C1)) - 2)
    coluna = Mid(iRange.Address(ReferenceStyle:=xlR1C1), Application.WorksheetFunction.Search("C", iRange.Address(ReferenceStyle:=xlR1C1)) + 1, Application.WorksheetFunction.Search("C", iRange.Address(ReferenceStyle:=xlR1C1)) + 1)
    ultLinha = Mid(fRange.Address(ReferenceStyle:=xlR1C1), Application.WorksheetFunction.Search("R", fRange.Address(ReferenceStyle:=xlR1C1)) + 1, Application.WorksheetFunction.Search("C", fRange.Address(ReferenceStyle:=xlR1C1)) - 2)
    contador = primLinha
    MDD = 0
    
    While contador <= ultLinha
        MDDTemp = Cells(contador, coluna) / Application.WorksheetFunction.Max(Range(Cells(primLinha, coluna), Cells(contador, coluna))) - 1
   
        If MDDTemp < MDD Then
           MDD = MDDTemp
        End If
      
        contador = contador + 1
    Wend
    
End Function

But I have a problem with the formula update! For example, if I have the formula = MDD (A2; A21) in cell D3 (the result is -55.6%), after that if I decide to update the data from $ 90 to $ 30, the formula in D3 continues to show -55, 6%, when it should show -62.5%, is only updated when I double click on D3 and then 'Enter'. I have already enabled the automatic update of Excel, but nothing. Is it a mistake in VBA?

My Test: enter image description here

Upvotes: 0

Views: 72

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Use a variant array to speed things up and use the entire range in the criterion:

Function MDD(rng As Range) As Double
 
    If rng.Cells.Count < 2 Or rng.Columns.Count > 1 Then Exit Function
    
    Dim rngArray As Variant
    rngArray = rng.Value
    
    Dim mx As Double
    mx = 0
    
    Dim i As Long
    For i = LBound(rngArray, 1) To UBound(rngArray, 1)
    
        If mx < rngArray(i, 1) Then mx = rngArray(i, 1)
        
        Dim MDDTemp As Double
        MDDTemp = (rngArray(i, 1) / mx) - 1
        
        If MDDTemp < MDD Then MDD = MDDTemp
    Next i


End Function

enter image description here

Upvotes: 2

Related Questions