如何为存储表记录添加宏加确认机制，避免无新增行时误录入？

The issue here is that the Worksheet_Calculate event fires every time any calculation happens in the worksheet—not just when the feeder table's row count actually increases. Your current setup relies on GetTableSize() but doesn't track the last valid row count, so temporary calculation changes (like formula recalculations elsewhere) can trigger the macro incorrectly.

To fix this, we need to add a static variable that remembers the last known row count of the feeder table. We'll only run the record-adding logic if the current row count is greater than this stored value (meaning new rows were actually added).

Modified Code #

Here's how to update your macro:

Private Sub Worksheet_Calculate()
    ' Static variable retains its value between macro runs
    Static lastFeederRowCount As Long
    Dim currentFeederRowCount As Long
    
    currentFeederRowCount = GetTableSize()
    
    ' Initialize on first run: set last count to current without adding records
    If lastFeederRowCount = 0 Then
        lastFeederRowCount = currentFeederRowCount
        Exit Sub
    End If
    
    ' Only proceed if new rows were added to the feeder table
    If currentFeederRowCount > lastFeederRowCount Then
        ' Your existing code to add records to the storage table goes here
        ' ... (keep your original logic for adding records)
        
        ' Update the last count after successfully adding new records
        lastFeederRowCount = currentFeederRowCount
    End If
End Sub

Key Changes Explained #

• Static lastFeederRowCount As Long: This variable keeps its value even after the macro finishes running, so it remembers how many rows the feeder table had the last time the macro executed correctly.
• Initialization Check: The first time the macro runs, we set lastFeederRowCount to the current row count without adding any records—this prevents it from processing existing rows on the first trigger.
• Row Count Comparison: We only run your record-adding logic if the current row count is strictly greater than the last stored count. This ensures we only act when new rows are actually added to the feeder table.

Additional Notes #

• If you ever need to reset the stored row count (e.g., after deleting rows from the feeder table), you can either:
  • Close and reopen the workbook (static variables reset when the workbook closes), or
  • Add a small reset subroutine to manually set lastFeederRowCount to 0.
• Make sure GetTableSize() accurately returns the number of used rows in the feeder table (excluding headers if applicable) to avoid false positives.

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