EXCEL / VBA - function #VALUE error in spreadsheet

Question

(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

How does it jump out of the loop? If k = 1 then it will enter the loop, recall the function with k still equaling 1 and then enter the loop again...... and so on. Also, use Option Explicit at the top of your module (Tools ~ Options ~ Editor ~ Require Variable Declaration`). You've not declared any of your variables. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Mar 2, 2021 at 15:43
I might be wrong, but doesn't the function just return if n = 0 as in the if at the top, so in the For loop when n-1 reaches 0 it should just continue because the recursively called function just returns instantly? — Kyjko
– Kyjko, Commented Mar 2, 2021 at 15:57
Also could you tell me why do I need to explicitly declare my variables? Aren't they just local variables? — Kyjko
– Kyjko, Commented Mar 2, 2021 at 15:59
Returns don't stop the code from running you would need an exit function or elseifs like in Axuary's answer. Declaring variables is good practice, there is no reason for them to be variants and option explicit will catch typos. — Warcupine
– Warcupine, Commented Mar 2, 2021 at 16:01

Axuary · Accepted Answer · 2021-03-02 15:54:46Z

1

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

answered Mar 2, 2021 at 15:54

Axuary

1,5071 gold badge7 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

EXCEL / VBA - function #VALUE error in spreadsheet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related