Excel VBA执行For循环时带公式单元格返回值变为0的问题

执行以下VBA代码时出现异常：MsgBox 1至3可正常返回Cells(6,20)等单元格的公式计算值，但进入For i = 1 To 33循环后，MsgBox4和5返回值为0；工作表中显示正常的Cells(6,30-33)单元格，在代码对应位置读取也返回0。此前代码可正常运行，排查后未找到原因。

相关代码 #

Private Sub incomeAdd_btn_Click()
    Dim reslt As Variant
    Dim myRange As Range
    Dim myCell As Range
    Dim addrss As String
    Dim val As Variant
    
    clearingTransaction = True
    
    MsgBox ("1: The value of cell(6, 20) is " & Cells(6, 20).Value)
    
    'Warn
    reslt = MsgBox("This will add a new transaction to the ledger! Do you want to Continue?", vbYesNo + vbDefaultButton2)
    If reslt = vbNo Then
        clearingTransaction = False
        Exit Sub
    End If
        
    Application.ScreenUpdating = False
    
    newTransaction = False
    IncomeAdd_btn.Enabled = False
    incomeModify_btn.Enabled = False
    incomeDelete_btn.Enabled = False
    
    'Blacken transaction bar cells
    Set myRange = Range("A6:F6, H6, J6:AG6")
    myRange.Interior.ColorIndex = 1
    
    'Get last row of logged income and assign nextRow value
    Dim lastRow As Long
    Dim maxRange As Range
    Dim nextRow As Long
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    'Set next empty row
    If lastRow = 18 Then
        Set maxRange = Range("A20")
        nextRow = 20
    Else
        Set maxRange = Range("A20:A" & lastRow)
        nextRow = lastRow + 1
    End If
    'Check certain non-required cells and assign default values if needed to cells...
    Set myRange = Range("A6,D6,E6")
    For Each myCell In myRange
        addrss = myCell.Address
        val = myCell.Value
        If (addrss = "$A$6") Then
            'Assign reference number
            Dim refNum As Integer
            If maxRange.Address = "$A$20" And IsEmpty(maxRange) Then
                refNum = 1
            Else
                refNum = getMaxValue(maxRange) + 1
            End If
            myCell.Value = refNum
        End If
        If (addrss = "$D$6") And IsEmpty(val) Then
            myCell.Value = "Miscellaneous"
        End If
        If (addrss = "$E$6") And IsEmpty(val) Then
            myCell.Value = "N/A"
        End If
    Next myCell
    
    MsgBox ("2: The value of cell(6, 20) is " & Cells(6, 20).Value)
    
    'Complete the transaction
    Dim i As Integer
    Dim myDate As Date
    Dim myAmt_ciderStill As Currency
    Dim myAmt_ciderSpark As Currency
    Dim myAmt_wineStill As Currency
    Dim myAmt_wineSpark As Currency
    Dim myAmt_maltBev As Currency
    Dim myAmt_liquor As Currency
    Dim myAmt_food As Currency
    Dim myAmt_merch As Currency
    Dim myAmt_rental As Currency
    Dim myAmt_exempt As Currency
    Dim ciderSold_still As Double
    Dim ciderSold_spark As Double
    
    Dim ciderSold_red As Integer
    Dim ciderSold_black As Integer
    Dim ciderSold_blue As Integer
    
    Dim wineSold_still As Double
    Dim wineSold_spark As Double
    Dim maltBevSold As Double
    Dim liquorSold As Double
    
    Dim fedTax As Currency
    Dim paLiqTax As Currency
    Dim paWinExTax As Currency
    Dim paSaleTax As Currency
    
    Dim myCat As String
    Dim myQuarter As Integer
    Dim myRow As Integer
    
    MsgBox ("3: The value of cell(6, 20) is " & Cells(6, 20).Value)
    
    'Add the transaction to the transaction log
    For i = 1 To 33
    
        If i = 20 Or i > 29 Then
            MsgBox ("4: The value of Range(""T6"").Value is " & Range("T6").Value)
            MsgBox ("5: Cells(6, " & i & ").Value = " & Cells(6, i).Value)
        End If
        
        val = Cells(6, i).Value
        ' 后续代码省略
        ...
End Sub

可能的解决方向 #

• 强制重新计算：代码中修改了A6、D6、E6单元格的值，可能导致关联公式未自动重算。在进入循环前添加计算指令：

  Application.Calculation = xlCalculationAutomatic
  Application.CalculateFull
  

• 禁用工作表事件：设置单元格背景色的操作可能触发工作表的Change或SelectionChange事件，导致公式被意外修改。在操作单元格前后禁用事件：

  Application.EnableEvents = False
  Set myRange = Range("A6:F6, H6, J6:AG6")
  myRange.Interior.ColorIndex = 1
  Application.EnableEvents = True
  

• 明确指定工作表：代码中使用Cells、Range时未指定工作表，可能误读其他工作表的单元格。修改为具体工作表引用，例如：

  With ThisWorkbook.Sheets("Ledger") ' 替换为你的工作表名称
      MsgBox ("1: The value of cell(6, 20) is " & .Cells(6, 20).Value)
      ' 所有Cells/Range前都加上.前缀
  End With
  

• 检查getMaxValue函数：该函数的逻辑可能导致计算异常，进而影响关联公式的结果。验证函数返回值是否正确，避免出现错误值或非预期数值。

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