Run-time error 6: Overflow: Excel VBA

Question

I have been trying to resolve this but could not. I have the following VBA code. While running it gives "run time error 6: Overflow". I guess I have assigned a right type to each variable. Please help!!

Sub UseCoeff()
Dim a, b As Long
Dim Value1 As Double

ThisWorkbook.Sheets("UseTableBEA").Activate
For b = 2 To 427
    For a = 2 To 431

        Value1 = ThisWorkbook.Sheets("UseTableBEA").Cells(a, b).Value / ThisWorkbook.Sheets("UseTableBEA").Cells(432, b).Value
        ThisWorkbook.Sheets("UseCoeff").Cells(a, b).Value = Value1

    Next a
Next b
End Sub

Each cell will have a result that may be between 0 and 1 up to eight decimal places. Thanks in advance!!

Solution 1

If ThisWorkbook.Sheets("UseTableBEA").Cells(432, b).Value is ever 0, then ThisWorkbook.Sheets("UseTableBEA").Cells(a, b).Value / ThisWorkbook.Sheets("UseTableBEA").Cells(432, b).Value will cause the Overflow error if ThisWorkbook.Sheets("UseTableBEA").Cells(a, b).Value is also 0.

To avoid this, use an if statement to filter out the 0s, just update the comment with the actual logic you want:

If ThisWorkbook.Sheets("UseTableBEA").Cells(432, b).Value = 0 Then
    Value1 = 'Whatever you want but probably 0.
Else
    Value1 = ThisWorkbook.Sheets("UseTableBEA").Cells(a, b).Value / ThisWorkbook.Sheets("UseTableBEA").Cells(432, b).Value
End If

Solution 2

Dim a, b As Long

a is not declared as a long it is declared as a variant try this:

Dim a as long, b as Long

Edit: Having tested this, it still produces a Stack Overflow error. However it is good practice to declare your variables properly.

You also don't need this line:

ThisWorkbook.Sheets("Sheet1").Activate

and if you declare UseTableBEA you can slim down your code:

Dim Ws1 as Worksheet
Set Ws1 = Sheets("UseTableBEA")

You can then use Ws1 where you have previously used ThisWorkbook.Sheets("UseTableBEA")

Comments

[Alistair Weir]

Just did some testing and if I use a zero I get a divide by zero error opposed to overflow error.

[Daniel]

@AlistairWeir Ah, I was slightly inaccurate. You get a divide by zero error if you divide anything that is not 0 by zero. You get an overflow error if you divide 0 by 0.

[user1671860]

Thanks Alistair!! Excellent suggestion.