FV计算在立即窗口可行，常规代码运行却报类型不匹配错误求助

我正在做FV计算的作业，逻辑很简单，但代码运行时一直报类型不匹配错误。已经排查了所有细节，问题还是没解决。奇怪的是，这段代码里的FV函数在立即窗口里能正常运行，而且我的变量声明、参数值和朋友的完全一致，但他们的代码就能正常工作，实在搞不懂，急需帮忙。

代码如下：

Sub CalculateFV()
    Dim PValue As Currency, pmt As Currency, PMT_Type As String, PMT_Freq As Integer
    Dim N_Years As Integer, rate As Double, Cmpd_Freq As Integer
    Dim EffPrdRate As Double, Nper As Integer, FValue As Currency
    

    PValue = PV_FVCal.Value
    rate = Rate_FVCal.Value / 100
    pmt = PMT_FVCal.Value
    
    If PMTFreq_FVCal.Value = "Monthly" Then
        PMT_Freq = 12
    ElseIf PMTFreq_FVCal.Value = "Quarterly" Then
        PMT_Freq = 4
    ElseIf PMTFreq_FVCal.Value = "Semi-Annually" Then
        PMT_Freq = 2
    ElseIf PMTFreq_FVCal.Value = "Yearly" Then
        PMT_Freq = 1
    Else
        MsgBox ("Please select Compounding Frequncy!")
    End If
    
    If OptionButton5.Value = True Then
        PMT_Type = "End"
    ElseIf OptionButton6.Value = True Then
        PMT_Type = "Beginning"
    Else
        MsgBox ("Select End or Beginning of period for PMT!")
    End If

    N_Years = Nyears_FVCal.Value
    
    If CmpdFreq_FVCal.Value = "Monthly" Then
        Cmpd_Freq = 12
    ElseIf CmpdFreq_FVCal.Value = "Quarterly" Then
        Cmpd_Freq = 4
    ElseIf CmpdFreq_FVCal.Value = "Semi-Annually" Then
        Cmpd_Freq = 2
    ElseIf CmpdFreq_FVCal.Value = "Yearly" Then
        Cmpd_Freq = 1
    Else
        MsgBox ("Please select Compounding Frequncy!")
    End If
    
    EffPrdRate = (1 + rate / Cmpd_Freq) ^ (Cmpd_Freq / PMT_Freq) - 1
    Nper = PMT_Freq * N_Years
    If PMT_Type = "End" Then
        
        Debug.Print FV(0.0004758, 120, 100, 10000)
        'FValue = FV(EffPrdRate, Nper, pmt, PValue, 0)
    ElseIf PMT_Type = "Beginning" Then
        FValue = FV(EffPrdRate, Nper, pmt, PValue, 1)
    End If
    
    FV.Text = FValue
       
End Sub

可能的问题及解决方法 #

• 控件值未做类型转换：文本框控件的Value默认是字符串类型，直接赋值给Currency/Double/Integer变量时，若控件为空或输入非数值内容，会触发类型不匹配。给变量赋值时显式转换类型：

  PValue = CCur(PV_FVCal.Value)
  rate = CDbl(Rate_FVCal.Value) / 100
  pmt = CCur(PMT_FVCal.Value)
  N_Years = CInt(Nyears_FVCal.Value)
  

• 控件命名与内置函数冲突：代码最后一行的FV.Text中，控件名FV和Excel内置的FV函数重名，导致VBA混淆类型。把输出控件改名（比如FV_Result），并将Currency类型的FValue转为字符串赋值：

  FV_Result.Text = CStr(FValue)
  

• 未处理控件未选择的异常分支：当用户未选择付款/复利频率时，PMT_Freq或Cmpd_Freq会保留初始值0，计算EffPrdRate时会出现除以0的错误，也可能引发类型问题。弹出提示后直接退出子程序：

  Else
      MsgBox "请选择付款频率！"
      Exit Sub
  End If
  

  （复利频率的Else分支同理添加Exit Sub）
• End分支未给FValue赋值：当前代码中PMT_Type = "End"分支只执行了Debug.Print，未给FValue赋值，后续给控件赋值时会因变量为空触发类型错误，恢复注释的赋值代码：

  FValue = FV(EffPrdRate, Nper, pmt, PValue, 0)
  

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