运行时错误 6：溢出：Excel VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12469849/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Run-time error 6: Overflow: Excel VBA
提问by user1671860
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!!
我一直在尝试解决这个问题，但无法解决。我有以下 VBA 代码。运行时它会给出“运行时错误 6：溢出”。我想我已经为每个变量分配了正确的类型。请帮忙！！
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!!
每个单元格的结果可能介于 0 和 1 之间，最多八位小数。提前致谢！！
回答by Daniel
If ThisWorkbook.Sheets("UseTableBEA").Cells(432, b).Valueis ever 0, then ThisWorkbook.Sheets("UseTableBEA").Cells(a, b).Value / ThisWorkbook.Sheets("UseTableBEA").Cells(432, b).Valuewill cause the Overflow error if ThisWorkbook.Sheets("UseTableBEA").Cells(a, b).Valueis also 0.
如果ThisWorkbook.Sheets("UseTableBEA").Cells(432, b).Value是 0，那么如果也是 0，ThisWorkbook.Sheets("UseTableBEA").Cells(a, b).Value / ThisWorkbook.Sheets("UseTableBEA").Cells(432, b).Value就会导致溢出错误ThisWorkbook.Sheets("UseTableBEA").Cells(a, b).Value。
To avoid this, use an if statement to filter out the 0s, just update the comment with the actual logic you want:
为避免这种情况，请使用 if 语句过滤掉 0，只需使用您想要的实际逻辑更新注释：
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
回答by Alistair Weir
Dim a, b As Long
Dim a, b As Long
a is not declared as a long it is declared as a variant try this:
a 没有被声明为 long 它被声明为一个变体试试这个：
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 UseTableBEAyou can slim down your code:
如果你声明UseTableBEA你可以精简你的代码：
Dim Ws1 as Worksheet
Set Ws1 = Sheets("UseTableBEA")
You can then use Ws1where you have previously used ThisWorkbook.Sheets("UseTableBEA")
然后您可以使用Ws1您以前使用过的地方ThisWorkbook.Sheets("UseTableBEA")
