VBA代码出现Capacity Overflow Error Type 6（6号容量溢出错误）求助

Let's break down why you're hitting that Capacity Overflow error and fix it step by step:

1. The Root Cause of the Overflow #

Your value variable is declared as Long — this is a 32-bit integer type that only supports values between -2147483648 and 2147483647. When you run the division Cel.Offset(0, -2).value / Cel.Offset(0, -4).value, two scenarios trigger the overflow:

• The result is a decimal number (Long can’t store decimals, so forcing a float into an integer type throws an error).
• The resulting integer exceeds the maximum/minimum limit of the Long type.

2. Hidden Risk: Division by Zero #

Your current code doesn’t check if the divisor (Cel.Offset(0, -4).value) is zero. Even if you fix the overflow, this will eventually throw a Division by Zero error that crashes your macro.

3. Fixed Problematic Code Block #

Here’s the corrected version of the code between your markers, with clear explanations of changes:

'------------------------------------'
Dim value As Double ' Changed from Long to Double to support decimals and larger values
For Each Cel In Range("H2", Range("H2").End(xlDown))
    ' First check if divisor is not zero to avoid crash
    If Cel.Offset(0, -4).value <> 0 Then
        If Cel = 0 And Cel.Offset(0, 1) = 0 Then
            value = Cel.Offset(0, -2).value / Cel.Offset(0, -4).value
            Cel.Offset(0, 2).value = value
        Else
            Cel.Offset(0, 2).value = 0
        End If
    Else
        ' Handle division by zero case (set to 0 or add a warning if needed)
        Cel.Offset(0, 2).value = 0
    End If
Next Cel
'------------------------------------'

Key Improvements: #

• Variable type update: Switched value from Long to Double — Double is a floating-point type that handles decimals and has a massive range (up to ~1.7e308), eliminating overflow from both decimal results and large integers.
• Division safety check: Added a guard clause to skip division when the divisor is zero, preventing unexpected crashes.
• Preserved original logic: Kept your core calculation rules intact while adding necessary safety layers.

4. Bonus: Avoid Accidental Full Column Selection (Optional) #

If column H has empty cells between H2 and the last row of data, Range("H2").End(xlDown) might stop early. To ensure you only iterate over rows with actual data, use this instead:

Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "H").End(xlUp).Row
For Each Cel In ws.Range("H2:H" & lastRow)

This locks your loop to only the relevant data rows in column H.

内容的提问来源于stack exchange，提问作者quannamtran