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!!

Which version of Excel are you using? In Excel 2003 you can't have 427 columns... — Passerby
– Passerby, Commented Sep 18, 2012 at 4:05

Daniel · Accepted Answer · 2012-09-18 14:20:57Z

3

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

edited Sep 18, 2012 at 14:20

answered Sep 18, 2012 at 4:18

Daniel

13.1k3 gold badges39 silver badges64 bronze badges

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

2 Comments

Alistair Weir Over a year ago

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

Daniel Over a year ago

@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.

Alistair Weir · Accepted Answer · 2012-09-18 12:58:32Z

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")

edited Sep 18, 2012 at 12:58

answered Sep 18, 2012 at 12:47

Alistair Weir

1,8496 gold badges28 silver badges48 bronze badges

Collectives™ on Stack Overflow

Run-time error 6: Overflow: Excel VBA

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related