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
– TylerC, Commented Jul 6, 2016 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
– John Coleman, Commented Jul 6, 2016 at 23:28
You are going to have problems summing the digits in i^3when 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
– John Coleman, Commented Jul 6, 2016 at 23:47

Community · Accepted Answer · 2017-05-23 10:28:52Z

3

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

edited May 23, 2017 at 10:28

CommunityBot

11 silver badge

answered Jul 6, 2016 at 23:27

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

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

1 Comment

TylerC Over a year ago

That's probably it. Thank you for your help!

Collectives™ on Stack Overflow

Overflow error VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related