0

I'm currently struggling on an assignment (probability class) which require creating a function (VBA excel) that contains a variable number of sums one inside the other based on a parameter. I'm not necessarly looking for the exact answer but just a similar a post or a better way to ask my question more clearly. I did make a function that show what the calculation would be if the parameter was 1,2 or 3. but how would i apply it if the parameter could be any natural number bigger then 3? If it sounds really complicated to do, just say it, I might be looking at the whole thing the wrong way.

Function prob_ruine_discret_exacte(u As Integer, k As Integer, prime As Integer, probas() As Variant) As Double
    'sum of probas = 1 with each value smaller than 1
    
    Dim sumProb(1 To k) As Variant
    Dim h(1 To k) As Long
    
   sumProb = 0
   
   If k = 1 Then
    For h(1) = 0 To WorksheetFunction.Min(u + prime, UBound(probas))
        sumProb(1) = sumProb(1) + probas(h(1))
    Next
   End If
   
    If k = 2 Then
        For h(2) = 0 To WorksheetFunction.Min(u + prime, UBound(probas))
            sumProb(2) = sumProb(2) + sumProb(1) * probas(u + prime - h(2))
            sumProb(1) = 0
            For h(1) = 0 To WorksheetFunction.Min(h(2) + prime, UBound(probas))
                sumProb(1) = sumProb(1) + probas(h(1))
            Next
        Next
   End If
   
   If k = 3 Then
        For h(3) = 0 To WorksheetFunction.Min(u + prime, UBound(probas))
            sumProb(3) = sumProb(3) + sumProb(2) * probas(u + prime - h(3))
            sumProb(2) = 0
            For h(2) = 0 To WorksheetFunction.Min(l + prime, UBound(probas))
                sumProb(2) = sumProb(2) + sumProb(1) * probas(l + prime - h(2))
                sumProb(1) = 0
                For h(1) = 0 To WorksheetFunction.Min(h(2) + prime, UBound(probas))
                    sumProb(1) = sumProb(1) + probas(h(1))
                Next
            Next
        Next
   End If
  prob_ruine_discret_exacte = 1 - (sumProb(k))
End Function
New contributor
Victor is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
16
  • 1
    Do you have a good reason for using Variant here? Commented Nov 26 at 19:14
  • 2
    Take a step back from your computer (yes, literally) and then take another look at your VBA code and you'll start to see a pattern: When k = 2 then its inner-loop is the same as when k = 1; similarly, when k = 3 then its inner-loop is the same as when k = 2 (which in-turn has the same logic as k = 1. ...and this means you can use recursion! Commented Nov 26 at 19:18
  • 4
    With recursion, you don't make a loop - that's the point. Instead, you make it reentrant. Commented Nov 26 at 19:49
  • 4
    Please add an example of how you would call this function from VBA, so that anyone who wants to test it doesn't have to make any assumptions. Commented Nov 26 at 21:23
  • 2
    Is this algorithm really correct? It looks a bit strange, because, for example, with k=2, SumProb(1) is used in the calculation before its recalculation, i.e. the first loop statement actually uses the previously calculated value of SumProb(1). Similarly, with k=3. In addition, the undeclared variable l is used at k=3. Was it supposed to be 1? Commented Nov 26 at 21:51

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.