Excel VBA库存校验逻辑异常：出库操作验证失效问题排查

Let’s break down what’s causing your two main problems—allowing outbound stock movements when there’s no stock on the first button click, and blocking all valid movements on subsequent clicks—and fix them step by step.

Key Issues in Your Original Code #

• Backwards Initial Validation: Your first check triggered a "no stock" message whenever it found a row that didn’t match your target reference, which is the opposite of what you need. You should calculate total available stock first, then validate against that total.
• Uncontrolled GoTo Usage: Jumping around with GoTo broke the loop flow on the first run and left filters active on subsequent runs, leading to incorrect stock calculations.
• Unclear Stock Summation: Your code references calculos!A1 for sums but doesn’t show how filtered totals get there, which likely led to wrong sumfinal values.
• Uncleared Filters: After using AutoFilter, you never reset the filter state, so subsequent runs worked with pre-filtered data instead of the full dataset.

Corrected VBA Code #

Start by adding Option Explicit at the top to catch undefined variable errors (always a good practice):

Option Explicit

Private Sub CommandButton1_Click()
    Dim wsRegistos As Worksheet
    Dim wsCalculos As Worksheet
    Dim tblRegistos As ListObject
    Dim lastRow As Long
    Dim targetRef As String
    Dim targetCC As String
    Dim qtyToOut As Double
    Dim totalEntrada As Double
    Dim totalSaida As Double
    Dim sumFinal As Double
    
    ' Set worksheet/table references to avoid repetition
    Set wsRegistos = ThisWorkbook.Worksheets("Registos")
    Set wsCalculos = ThisWorkbook.Worksheets("calculos")
    Set tblRegistos = wsRegistos.ListObjects("Tabela1")
    
    ' Pull input values from your form
    targetRef = registos.TextBox1.Value
    targetCC = registos.Label11.Caption
    qtyToOut = CDbl(registos.TextBox4.Value)
    
    ' First, validate required fields for SAÍDA
    If registos.ComboBox1.Value = "SAÍDA" Then
        If registos.TextBox1.Value = "" Or registos.TextBox2.Value = "" Or _
           registos.TextBox4.Value = "" Or registos.ComboBox5.Value = "" Then
            MsgBox "Insira todos os dados obrigatórios!"
            Exit Sub
        End If
    End If
    
    ' Clear any existing filters to start fresh
    If tblRegistos.AutoFilter.FilterMode Then
        tblRegistos.AutoFilter.ShowAllData
    End If
    
    ' Calculate total ENTRADA for target reference + CC
    With tblRegistos.Range
        .AutoFilter Field:=8, Criteria1:=targetRef
        .AutoFilter Field:=6, Criteria1:=targetCC
        .AutoFilter Field:=12, Criteria1:="ENTRADA"
        
        ' Use SUBTOTAL to sum visible rows (skips table header)
        totalEntrada = Application.Subtotal(9, .Columns(11))
    End With
    
    ' Calculate total SAÍDA for target reference + CC
    With tblRegistos.Range
        .AutoFilter Field:=12, Criteria1:="SAÍDA"
        totalSaida = Application.Subtotal(9, .Columns(11))
    End With
    
    ' Reset filters after calculations
    tblRegistos.AutoFilter.ShowAllData
    
    ' Compute available stock
    sumFinal = totalEntrada - totalSaida
    
    ' Validate stock before allowing SAÍDA
    If registos.ComboBox1.Value = "SAÍDA" Then
        If qtyToOut <= 0 Then
            MsgBox "Quantidade de saída deve ser maior que 0!"
            Exit Sub
        End If
        If qtyToOut > sumFinal Then
            MsgBox "Não foi possível concluir o movimento! Stock disponível: " & sumFinal
            Exit Sub
        End If
    End If
    
    ' Proceed to record the movement if all checks pass
    lastRow = wsRegistos.Cells(wsRegistos.Rows.Count, "A").End(xlUp).Row + 1
    
    ' Record the SAÍDA entry
    With wsRegistos.Cells(lastRow, 1)
        .Value = Now()
        .Offset(0, 4).Value = registos.Label20.Caption ' Ano fiscal
        .Offset(0, 5).Value = targetCC ' CC
        .Offset(0, 6).Value = a1logiin.TextBox1.Value ' Operário
        .Offset(0, 7).Value = targetRef ' Referência
        .Offset(0, 8).Value = registos.TextBox2.Value ' Ordem
        .Offset(0, 10).Value = qtyToOut ' Quantidade
        .Offset(0, 11).Value = "SAÍDA" ' Tipo de movimento
        .Offset(0, 12).Value = registos.ComboBox5.Value ' Estado
        .Offset(0, 13).Value = registos.ComboBox3.Value ' Código defeito
        .Offset(0, 15).Value = registos.ComboBox6.Value ' Origem defeito
        .Offset(0, 16).Value = registos.TextBox5.Value ' Observações
    End With
    
    ' Optional: Record corresponding ENTRADA (matches your original code's final section)
    If registos.ComboBox2.Value <> "" Then
        lastRow = lastRow + 1
        With wsRegistos.Cells(lastRow, 1)
            .Value = Now()
            .Offset(0, 4).Value = registos.Label20.Caption ' Ano fiscal
            .Offset(0, 5).Value = registos.ComboBox2.Value ' CC destino
            .Offset(0, 6).Value = a1logiin.TextBox1.Value ' Operário
            .Offset(0, 7).Value = targetRef ' Referência
            .Offset(0, 8).Value = registos.TextBox2.Value ' Ordem
            .Offset(0, 10).Value = qtyToOut ' Quantidade
            .Offset(0, 11).Value = "ENTRADA" ' Tipo de movimento
            ' Add any additional fields here as needed
        End With
    End If
    
    MsgBox "Dados introduzidos com sucesso!"
    
    ' Optional: Clear form fields after successful submission
    registos.TextBox1.Value = ""
    registos.TextBox2.Value = ""
    registos.TextBox4.Value = ""
    registos.ComboBox5.Value = ""
End Sub

What Changed & Why #

1. Explicit Variable Declarations: Option Explicit prevents typos and undefined variable bugs that can break logic silently.
2. Structured Validation: We first check for missing required fields, then calculate stock totals using SUBTOTAL (which only sums visible rows, ideal for filtered tables).
3. Filter Management: Filters are cleared before and after calculations to ensure each run uses the full, unfiltered dataset.
4. Removed GoTo: Replaced with Exit Sub for early termination, making the code flow linear and easier to debug.
5. Efficient Stock Calculation: Instead of looping through every row, we use table filters to quickly sum relevant entries, which is faster and more reliable.

This fixes both issues: it will block outbound movements when stock is insufficient on the first click, and allow valid movements on subsequent clicks because filters are reset and calculations are accurate.

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