我正在尝试运行以下代码，但我得到一个 runtime error 6: overflow 用于我试图做的最后一次计算。程序在到达 MLtot = MLwin / (MLwin + MLloss) 时停止。其他两个计算可以毫无问题地完成。
我也尝试将我的变量设置为不同的数据类型来解决这个问题，但没有运气。
Sub AutoWinPercent()
Dim Swin As Variant, Sloss As Variant, OUwin As Variant, OUloss As Variant
Dim MLwin As Variant, MLloss As Variant
Dim Stot As Double, OUtot As Double, MLtot As Double
For i = 1 To 17
Swin = WorksheetFunction.CountIfs(Range("A3:A258"), i, Range("Y3:Y258"), "WIN")
Sloss = WorksheetFunction.CountIfs(Range("A3:A258"), i, Range("Y3:Y258"), "LOSS")
OUwin = WorksheetFunction.CountIfs(Range("A3:A258"), i, Range("Z3:Z258"), "WIN")
OUloss = WorksheetFunction.CountIfs(Range("A3:A258"), i, Range("Z3:Z258"), "LOSS")
MLwin = WorksheetFunction.CountIfs(Range("A3:A258"), i, Range("AA3:AA258"), "WIN")
MLloss = WorksheetFunction.CountIfs(Range("A3:A258"), i, Range("AA3:AA258"), "LOSS")
j = i + 2 'sets the starting row of where the percentages will be placed.
Stot = Swin / (Swin + Sloss)
OUtot = OUwin / (OUwin + OUloss)
MLtot = MLwin / (MLwin + MLloss)
Range("AC" & j).Value = Stot 'Spread
Range("AD" & j).Value = OUtot 'OU
Range("AE" & j).Value = MLtot 'ML
Next i
End Sub回答1
鉴于 overflow 错误是由零除引起的，请考虑使用用户定义的函数来检查分母中的零并根据需要有条件地处理计算。避免使用 On Error Resume Next 快速修复，因为它会抑制其他错误并使调试问题变得困难。
此外，作为 VBA 中的最佳实践，请确保限定所有对象（即，.Range 到 Worksheet，Worksheet 到 Workbook）。下面使用 With 块。请根据实际工作表名称进行调整。此外，Dim 使用的所有变量，例如 i 和 j（除非公开声明）。
功能（与 Sub 或标准模块放在同一模块中）
Function CalcExpression(numerator As Variant, denominator As Variant) As Variant
Dim val As Variant
If denominator = 0 Then
val = ""
Else
val = numerator / denominator
End If
CalcExpression = val
End Function子程序
Sub AutoWinPercent()
On Error Goto ErrHandler
Dim Swin As Variant, Sloss As Variant
Dim OUwin As Variant, OUloss As Variant
Dim MLwin As Variant, MLloss As Variant
Dim Stot As Variant, OUtot As Variant, MLtot As Variant
Dim i As Long, j As Long
With ThisWorkbook.Worksheets("myWorksheet")
For i = 1 To 17
Swin = WorksheetFunction.CountIfs(.Range("A3:A258"), i, .Range("Y3:Y258"), "WIN")
Sloss = WorksheetFunction.CountIfs(.Range("A3:A258"), i, .Range("Y3:Y258"), "LOSS")
OUwin = WorksheetFunction.CountIfs(.Range("A3:A258"), i, .Range("Z3:Z258"), "WIN")
OUloss = WorksheetFunction.CountIfs(.Range("A3:A258"), i, .Range("Z3:Z258"), "LOSS")
MLwin = WorksheetFunction.CountIfs(.Range("A3:A258"), i, .Range("AA3:AA258"), "WIN")
MLloss = WorksheetFunction.CountIfs(.Range("A3:A258"), i, .Range("AA3:AA258"), "LOSS")
Stot = CalcExpression(Swin, (Swin + Sloss))
OUtot = CalcExpression(OUwin, (OUwin + OUloss))
MLtot = CalcExpression(MLwin, (MLwin + MLloss))
j = i + 2 'sets the starting row of where the percentages will be placed.
.Range("AC" & j).Value = Stot 'Spread
.Range("AD" & j).Value = OUtot 'OU
.Range("AE" & j).Value = MLtot 'ML
Next i
End With
ExitHandler:
Exit Sub
ErrHandler:
MsgBox Err.Number & " - " & Err.Description, vbCritical, "RUNTIME ERROR"
Resume ExitHandler
End Sub