VBA中使用Application.InputBox获取乘数嵌入公式失败的问题

原始代码功能 #

以下代码通过IF公式结合固定乘数10，利用前列数据计算填充H列和I列的数值：

Range("H1").Select
ActiveCell.FormulaR1C1 = "Range Total"

Range("I1").Select
ActiveCell.FormulaR1C1 = "Range Real"

Range("I2").Select
ActiveCell.FormulaR1C1 = _
    "=IF(RC[-5]>=RC[-2],(RC[-5]-RC[-2])*10,(RC[-2]-RC[-5])*10)"

Range("H2").Select
ActiveCell.FormulaR1C1 = "=(RC[-3]-RC[-2])*10"

Range("H3").Select

Do Until ActiveCell.Offset(0, -1).Value = ""            
    ActiveCell.FormulaR1C1 = "=(RC[-3]-RC[-2])*10"            
    ActiveCell.Offset(0, 1).Activate            
    ActiveCell.FormulaR1C1 = "=IF(RC[-5]>=RC[-2],(RC[-5]-RC[-2])*10,(RC[-2]-RC[-5])*10)"            
    ActiveCell.Offset(1, -1).Activate    
Loop

需求与错误代码 #

需要通过Application.InputBox获取用户输入的数字作为动态乘数嵌入公式，但编写的代码无法正常运行：

Dim factor As Integer
factor = Application.InputBox("What is the multiplier value :", Type:=1)

Range("H1").Select
ActiveCell.FormulaR1C1 = "Range Total"

Range("I1").Select
ActiveCell.FormulaR1C1 = "Range Real"

Range("I2").Select
ActiveCell.FormulaR1C1 = _
    "=IF(RC[-5]>=RC[-2],(RC[-5]-RC[-2])*10,(RC[-2]-RC[-5])*factor)"

Range("H2").Select
ActiveCell.FormulaR1C1 = "=(RC[-3]-RC[-2])*factor"

Range("H3").Select  
Do Until ActiveCell.Offset(0, -1).Value = ""            
    ActiveCell.FormulaR1C1 = "=(RC[-3]-RC[-2])*factor"            
    ActiveCell.Offset(0, 1).Activate            
    ActiveCell.FormulaR1C1 = "=IF(RC[-5]>=RC[-2],(RC[-5]-RC[-2])*10,(RC[-2]-RC[-5])*factor)"            
    ActiveCell.Offset(1, -1).Activate    
Loop

问题原因 #

直接在字符串形式的公式中写入VBA变量factor时，Excel会将其视为公式内的单元格引用或未知名称，而非读取VBA变量的实际数值，导致公式报错。

修正后的代码 #

Dim factor As Integer
' 获取用户输入的乘数，Type:=1限定输入数字类型
factor = Application.InputBox("请输入乘数：", Type:=1)

' 直接写入表头，避免Select/Activate操作
Range("H1").Value = "Range Total"
Range("I1").Value = "Range Real"

' 将factor变量值拼接进公式字符串中
Range("H2").FormulaR1C1 = "=(RC[-3]-RC[-2])*" & factor
Range("I2").FormulaR1C1 = "=IF(RC[-5]>=RC[-2],(RC[-5]-RC[-2])*10,(RC[-2]-RC[-5])*" & factor & ")"

' 用行号变量循环，替代Activate操作，提升效率
Dim currentRow As Long
currentRow = 3
' 循环终止条件改为直接判断前列单元格值，逻辑更清晰
Do Until Cells(currentRow, "G").Value = ""
    Cells(currentRow, "H").FormulaR1C1 = "=(RC[-3]-RC[-2])*" & factor
    Cells(currentRow, "I").FormulaR1C1 = "=IF(RC[-5]>=RC[-2],(RC[-5]-RC[-2])*10,(RC[-2]-RC[-5])*" & factor & ")"
    currentRow = currentRow + 1
Loop

修正要点 #

1. 变量值拼接：通过& factor将VBA变量的数值直接嵌入公式字符串，让Excel识别为具体数字
2. 移除冗余操作：删除不必要的Select和Activate，直接通过单元格对象赋值，减少运行开销
3. 优化循环逻辑：改用行号变量控制循环，终止条件直接判断对应单元格值，代码可读性更强

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