0

I am trying to sum squares with VBA.

I get an overflow error when i = 182 and sum = 1992991.

I hoped that declaring sum as long would take care of big numbers.

Sub NaturalNumbers()

Dim i As Integer
Dim sum As Long

sum = 0

For i = 1 To 1000
    sum = sum + (i * i)

    Range("A" & i) = i
    Range("D" & i) = sum
Next

MsgBox "Sum is " & sum

End Sub
Community
  • 1
  • 1
  • 1
    An `Integer` cannot hold a number larger than 32,767 - Use a `Long` instead. There is no advantage to using an `Integer` – braX Nov 21 '19 at 02:48
  • https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long – BigBen Nov 21 '19 at 02:49
  • `i * i` causes an overflow when `i = 182` because as already noted `182*182` falls outside the possible range of an `Integer`. – BigBen Nov 21 '19 at 02:52
  • VBA knows `i` is an integer, so when `i * i` is evaluated, it expects the result to be an integer, so if the result is larger than an integer can hold, you get an error. If you were to multiply `i` by the value of `182`, it would still evaluate it as an `Integer` - but if `i` (being an `Integer` with a value of 184) is multiplied by `lng` (being a `Long` with the same value of 184) then VBA evaluates the operation as a `Long` instead, because it expects that the result could be larger than an `Integer` can handle. There's nothing strange about it. In short, just use `Long` instead. – braX Nov 21 '19 at 04:55
  • Okay great thanks. This seems to make sense. VBA assumes that i*i shold also be an integer. Hence an overflow error. Thanks for your replies!!! –  Nov 21 '19 at 06:37
  • I am unable to mark any of the answers as my answer(or upvote). How can I do that? –  Nov 21 '19 at 06:38

0 Answers0