如何仅上传用户表单中有值的控件？VBA现金流量表开发问题

你的核心问题是互斥的借贷控件总有一个为空值，而CDbl无法直接转换空字符串，导致运行时错误。以下是两种直接可行的解决方案，同时附带输入验证优化：

方案1：直接在赋值时添加空值/非数字判断 #

通过IIf结合IsNumeric函数，对空值或非数字输入返回0（保证单元格为可计算的数值格式），有效避免CDbl报错：

Private Sub cmdAddRecord_Click()
'Used to add new transaction records to the database

    Dim lastrow As Long
    ' 明确指定目标工作表，避免ActiveSheet的潜在风险
    With Sheets("Spending Account")
        lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        .Cells(lastrow + 1, "A").Value = DTPicker1
        .Cells(lastrow + 1, "B").Value = cboVendorDetails
        .Cells(lastrow + 1, "C").Value = cboTransactionType
        
        ' 处理借方金额：空值/非数字时写入0，否则转Double
        .Cells(lastrow + 1, "D").Value = IIf(IsNumeric(Me.txtTransactionAmountDebit) And Me.txtTransactionAmountDebit <> "", CDbl(Me.txtTransactionAmountDebit), 0)
        ' 处理贷方金额：空值/非数字时写入0，否则转Double
        .Cells(lastrow + 1, "E").Value = IIf(IsNumeric(Me.txtTransactionAmountCredit) And Me.txtTransactionAmountCredit <> "", CDbl(Me.txtTransactionAmountCredit), 0)
        
        .Cells(lastrow + 1, "F").Value = cboTransactionStatus
    End With

    With Sheets("Spending Account")
        Application.Goto Reference:=.Cells(.Rows.Count, "A").End(xlUp).Offset(-20), Scroll:=True
    End With

    Unload Me
    frmRegularTransactions.Show

End Sub

方案2：编写自定义安全转换函数 #

如果需要在多个模块复用空值转换逻辑，可以封装一个辅助函数，简化代码：

' 自定义函数：安全转换为Double，空值/非数字返回0
Private Function SafeCDbl(inputVal As Variant) As Double
    If IsNumeric(inputVal) And inputVal <> "" Then
        SafeCDbl = CDbl(inputVal)
    Else
        SafeCDbl = 0
    End If
End Function

Private Sub cmdAddRecord_Click()
'Used to add new transaction records to the database

    Dim lastrow As Long
    With Sheets("Spending Account")
        lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        .Cells(lastrow + 1, "A").Value = DTPicker1
        .Cells(lastrow + 1, "B").Value = cboVendorDetails
        .Cells(lastrow + 1, "C").Value = cboTransactionType
        
        ' 调用自定义函数处理金额
        .Cells(lastrow + 1, "D").Value = SafeCDbl(Me.txtTransactionAmountDebit)
        .Cells(lastrow + 1, "E").Value = SafeCDbl(Me.txtTransactionAmountCredit)
        
        .Cells(lastrow + 1, "F").Value = cboTransactionStatus
    End With

    With Sheets("Spending Account")
        Application.Goto Reference:=.Cells(.Rows.Count, "A").End(xlUp).Offset(-20), Scroll:=True
    End With

    Unload Me
    frmRegularTransactions.Show

End Sub

额外优化：添加输入验证 #

为避免用户未填写任何金额的情况，可在写入前添加验证逻辑：

Private Sub cmdAddRecord_Click()
'Used to add new transaction records to the database

    Dim lastrow As Long
    Dim debitVal As Double, creditVal As Double
    
    ' 先处理金额值
    debitVal = IIf(IsNumeric(Me.txtTransactionAmountDebit) And Me.txtTransactionAmountDebit <> "", CDbl(Me.txtTransactionAmountDebit), 0)
    creditVal = IIf(IsNumeric(Me.txtTransactionAmountCredit) And Me.txtTransactionAmountCredit <> "", CDbl(Me.txtTransactionAmountCredit), 0)
    
    ' 验证至少有一个金额不为0
    If debitVal = 0 And creditVal = 0 Then
        MsgBox "请输入借方或贷方金额！", vbExclamation, "输入错误"
        Exit Sub
    End If

    With Sheets("Spending Account")
        lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        .Cells(lastrow + 1, "A").Value = DTPicker1
        .Cells(lastrow + 1, "B").Value = cboVendorDetails
        .Cells(lastrow + 1, "C").Value = cboTransactionType
        .Cells(lastrow + 1, "D").Value = debitVal
        .Cells(lastrow + 1, "E").Value = creditVal
        .Cells(lastrow + 1, "F").Value = cboTransactionStatus
    End With

    With Sheets("Spending Account")
        Application.Goto Reference:=.Cells(.Rows.Count, "A").End(xlUp).Offset(-20), Scroll:=True
    End With

    Unload Me
    frmRegularTransactions.Show

End Sub

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