CODEWITHSUNDEEP
excelvba
Kyjko
Kyjko

Reputation: 25

EXCEL / VBA - function #VALUE error in spreadsheet

(I want to calculate the number of possible ways of getting 'sum' out of 'n' dice throws (dices have 'k' faces from 1..k).)

When i want to use this function in an excel spreadsheet, I get a #VALUE error no matter what inputs i try.
I have other functions in the same module, used in the same sheet that work fine\

Function how_many_ways(n, k, sum)
    If n = 0 Then
        If sum = 0 Then
            how_many_ways = 1
        Else
            how_many_ways = 0
        End If
    End If
    
    If sum < 0 Or k * n < sum Or n > sum Then
        how_many_ways = 0
    End If
    
    res = 0
    For i = 1 To k
        res = res + how_many_ways(n - 1, k, sum - i)
    Next i
    
    how_many_ways = res
    
End Function

Upvotes: 0

Views: 105

Answers (1)

Axuary
Axuary

Reputation: 1507

It always runs the for loop so it recurs infinitely. Try this.

Function how_many_ways(n, k, sum)
    If n = 0 Then
        If sum = 0 Then
            how_many_ways = 1
        Else
            how_many_ways = 0
        End If
    ElseIf sum < 0 Or k * n < sum Or n > sum Then
        how_many_ways = 0
    Else
       res = 0
       For i = 1 To k
           res = res + how_many_ways(n - 1, k, sum - i)
       Next i
       how_many_ways = res
    End If
End Function

Upvotes: 1

Related Questions