CODEWITHSUNDEEP
excelvba
Siddhant Sahni
Siddhant Sahni

Reputation: 157

Overflow in visual basic : data type usage

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.

Image 1

Upvotes: 1

Views: 544

Answers (3)

James Igoe
James Igoe

Reputation: 483

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

Upvotes: 0

Tom Sharpe
Tom Sharpe

Reputation: 34230

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.

Upvotes: 1

Badja
Badja

Reputation: 875

Try Double instead of Integer.

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

Upvotes: 0

Related Questions