Overflow error VBA

Question

I have this code below, and I'm getting an overflow error at the line: s = s + (x Mod 10) [first line in the Do Loop] Why? I declared x and s to be of type Double. Adding two doubles, why is this not working? Thanks for your help.

Public Sub bidon1()
    Dim i As Double, x As Double, s As Double, k As Byte, h As Byte
    Dim y(1 To 6) As Double

    For i = 1 To 1000000

        x = i ^ 3
        Do
            s = s + (x Mod 10)
            x = x \ 10
        Loop Until x = 0

        If s = x Then
            k = k + 1
            y(k) = x

            If y(6) > 0 Then

                For h = 1 To 6
                    Debug.Print y(h)
                Next
                Exit Sub
            End If
        End If
    Next

End Sub

Well, I'm not an experienced coder. The Do Loop is supposed to add up the digits of a number. — TylerC, Jul 06 '16 at 23:27
What do you mean by "add up the digits of a number"? 123 => 1 + 2 + 3 = 6? If so, there are vastly easier ways to do that. — John Coleman, Jul 06 '16 at 23:28
You are going to have problems summing the digits in `i^3`when `i= 999999`. (since it will round to 9.99997000003E+17). The only way to get the precision that you want is to use the decimal subtype of variants, and use repeated multiplication rather than exponentiation. — John Coleman, Jul 06 '16 at 23:47

score 3 · Answer 1 · edited May 23 '17 at 10:28

The problem is that the VBA mod operator coerces its arguments to be integers (if they are not already so). It is this implicit coercion which is causing the overflow. See this question: Mod with Doubles

On Edit: Based on your comments, you want to be able to add together the digits in a largish integer. The following function might help:

Function DigitSum(num As Variant) As Long
    'Takes a variant which represents an integer type
    'such as Integer, Long or Decimal
    'and returns the sum of its digits

    Dim sum As Long, i As Long, s As String

    s = CStr(num)

    For i = 1 To Len(s)
        sum = sum + Val(Mid(s, i, 1))
    Next i
    DigitSum = sum

End Function

The following test sub shows how it can be used to correctly get the sum of the digits in 999999^3:

Sub test()
    Dim x As Variant, y As Variant
    Debug.Print "Naive approach: " & DigitSum(999999 ^ 3)

    y = CDec(999999)
    x = y * y * y
    Debug.Print "CDec approach: " & DigitSum(x)
End Sub

Output:

Naive approach: 63
CDec approach: 108

Since 999999^3 = 999997000002999999, only the second result is accurate. The first result is only the sum of the digits in the string representation of the double 999999^3 = 9.99997000003E+17

That's probably it. Thank you for your help! – TylerC Jul 06 '16 at 23:41 — TylerC, Jul 06 '16 at 23:41

Overflow error VBA

1 Answers1