Overflow in visual basic : data type usage

Question

The VBA program needs to perform factorial for numbers exceeding 100. I would like to know if there is a data type that can handle that big of a number or if I will have to simplify the combination problem.

The equation used is a simple combination, which is n!/(r!*(n-r)!). Should I simplify this problem to get the result, or would a data type that can handle the number will be sufficient.

Private Sub CommandButton1_Click()
    Dim j As Integer, c As Integer, b As Integer, p As Single, val As Long
    c = 100
    b = 105
    p = 0.9

    For j = c + 1 To b
        val = fact(b) / (fact(j) * fact(b - j))
        Cells(2, j - 100).value = val
    Next j
End Sub

Public Function fact(x As Integer) As Long
    Dim facts As Long, k As Integer
    For k = 1 To x
        facts = facts * x
    Next k
    Exit Function
End Function

I expect the output for the given combination, but the error shown is Run-Time error '6' Overflow.

Double will hold a 100!, but with loss of precision. Starting from 171!, not even Double will be able to hold, not even with loss of precision. — GSerg
– GSerg, Commented Aug 1, 2019 at 13:46
Still doesn't work. I think my main problem is that I'm multiplying two big factorial numbers. — Siddhant Sahni
– Siddhant Sahni, Commented Aug 1, 2019 at 13:50
You should try to simplify the problem and reduce it as much as possible. — Jeff Zeitlin
– Jeff Zeitlin, Commented Aug 1, 2019 at 13:52

Tom Sharpe · Accepted Answer · 2019-08-01 14:40:04Z

1

There are a couple of errors in the fact function that would give you an overflow even if you changed Long to Double:

facts not initialised
facts not returned to calling function

So it should look something like this:

 Sub test()
    Dim j As Integer, c As Integer, b As Integer, p As Single, val As Double
    c = 100
    b = 105
    p = 0.9    
    For j = c + 1 To b   
        val = fact(b) / (fact(j) * fact(b - j))
            Cells(2, j - 100).Value = val
    Next j
End Sub

Public Function fact(x As Integer) As Double
    Dim facts As Double, k As Integer
    facts = 1
    For k = 1 To x
        facts = facts * k
    Next k
    fact = facts
End Function

You can check this against Excel's Combin function - it gives the same results.

answered Aug 1, 2019 at 14:40

Tom Sharpe

35.1k5 gold badges29 silver badges47 bronze badges

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

Comments

Badja · Accepted Answer · 2019-08-01 13:46:32Z

0

Try Double instead of Integer.

A Double is defined as: ±5.0 × 10^324 to ±1.7 × 10^308

answered Aug 1, 2019 at 13:46

Badja

8751 gold badge10 silver badges36 bronze badges

1 Comment

Jeff Zeitlin Over a year ago

The problem with DOUBLE is that it's floating point, and you lose precision. There is no VB data type can handle numbers as large as 100! with integer exact precision.

James Igoe · Accepted Answer · 2023-10-30 12:39:30Z

A solution I have used with VBA is to create a VSTO add-in C# (or VB.NET), then reference that in VBA as an automation object. Since C# has a BigInt type, you can do the work in C#, without worrying about overflow.

In my specific case, I wanted to thread SQL executions, since the server was performing poorly, and the code I was working with, originally written by someone else, had each portfolio call (100 to 300 per process) executed 15 or more SQL queries. I created a C# add-in such that the VBA would add items to an array that the C# ran all of the items in parallel, saving huge amounts of time.

It would be fairly easy to write the function in .NET, then set up a facade using an interface that referenced the .NET class you create. I'm not sure if it is necessary, but there is a BigInteger type in System.Numerics that defines an "arbitrarily large signed integer." Once you have the interface from the add-in setup, you can extend as needed to provide more functions to VBA routines.

Note, if this route seems possible, a sample is provided here: https://github.com/JamesIgoe/SqlexecutionAddIn

Collectives™ on Stack Overflow

Overflow in visual basic : data type usage

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related