VBA Overflow error at for loop when variable declared As Double

Question

I'm having trouble with vba code from a course I'm taking. I'm getting an overflow error when I try to run it, and I'm not sure what the issue is.

edit: I used Excel on a Windows machine and it worked fine, my mac is giving me this error when the code is identical.

This is the code:


Option Explicit

Sub Prequiz7()

Dim i As Double, j As Double, pig As Double, k As Double

pig = 10: j = 16: i = 0

For i = 1 To 2 Step 0.5

   If (i >= 1.5) Then

      pig = pig + i

   End If

   For k = 1 To 2

      MsgBox pig + k

   Next k

Next i

MsgBox pig & " " & i & " " & j

Do While (j > 10)

   If (j < 12) Then

      Exit Do

   End If

   j = j - 2

Loop

MsgBox j

End Sub

This is the error

"Run time error: '6'

Overflow"

If I debug it highlights the Next k line

Does anyone know why this error is happening, it seems like declaring the variable for k and pig As Double should prevent the Overflow error.

If I take away Option Explicit and the Dim line, it will show 2 message box's instead of one, and the error happens at the Next i line

Ditto - runs through fine. Is there anything else happening? Try stepping through the code and keeping an eye on the variables in the Local window. As you are doing numeric calculations, the overflow error often means that you are dividing by zero, which you don't appear to be doing here. — Applecore
– Applecore, Commented May 9, 2020 at 18:23
You made me remote in to a windows PC and try it in that version of excel. It works fine. It's something about the mac version of excel I'm using I guess. — gabe17
– gabe17, Commented May 9, 2020 at 18:32
Can't test due to windows machine, but what happens when you set j and k as long rather than double? — OwlsSleeping
– OwlsSleeping, Commented May 9, 2020 at 18:41
I get the same error, but it happens at the 'if (i >= 1.5) Then' line. Someone suggested As Variant, but still resulted in the error. — gabe17
– gabe17, Commented May 9, 2020 at 19:02

gabe17 · Accepted Answer · 2020-05-09 18:59:43Z

1

Seems like a Microsoft Excel bug for Mac that's been around at least since July 2019.

It works fine using Microsoft Excel on Windows PC

https://techcommunity.microsoft.com/t5/excel/runtime-error-6-overflow-with-dim-double-macos-catalina-excel/m-p/786433

Overflow (Error 6) VBA 7.1 - Excel 2019- MAC

answered May 9, 2020 at 18:59

gabe17

413 silver badges12 bronze badges

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

1 Comment

Unnamed Over a year ago

It's very strange. In the first link it's suggested to declare variables as variant, so try that. But what if you explicitly tell the step of k?

Collectives™ on Stack Overflow

VBA Overflow error at for loop when variable declared As Double

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