点击计算按钮无响应：VBA物料需求计算代码故障求助

问题描述 #

• 点击Calculate1按钮后无任何反应，无法完成物料计算
• 已尝试的排查操作：删除重建按钮及输出字段、仅执行首行代码输出到文本框、复用其他表单正常按钮、更换输出文本框，均未解决问题

提供的VBA代码 #

Private Sub Calculate1_Click()
    
    Dim Wall1 As Integer, Wall2 As Integer, AStuds1 As Integer, AStuds2 As Integer, Roof1 As Integer, Roof2 As Integer
    Dim Frames1 As Integer, Frames2 As Integer, Ptch As Integer, SqFt1 As Integer, LinSF As Integer
   
        Wall1 = ((Nz(H1LX) * 0.75) * 1.15) * Nz(H1Hx)
        Wall2 = ((Nz(H2LX) * 0.75) * 1.15) * Nz(H2Hx)
        AStuds1 = Nz(H1ASX)
        AStuds2 = Nz(H2ASX)
        Frames1 = ((Wall1 * 0.05) + Wall1) + AStuds1
        Frames2 = ((Wall2 * 0.05) + Wall2) + AStuds2
        Ptch = Eval(Pitch)
        SqFt1 = SFX
        Roof1 = Round((SQ((Ptch) / 12) + 1) * SqFt1)
        Roof2 = Roof1 / 2
        LinSF = Round((Frames1 + Frames2) + Roof2 + (Wall1 / 2))
        LSqFt1 = Wall1

End Sub

问题排查与修复步骤 #

1. 修正变量类型
   所有变量声明为Integer，但计算涉及小数运算，会导致精度丢失或溢出错误，建议改为Double类型：

   Dim Wall1 As Double, Wall2 As Double, AStuds1 As Double, AStuds2 As Double, Roof1 As Double, Roof2 As Double
   Dim Frames1 As Double, Frames2 As Double, Ptch As Double, SqFt1 As Double, LinSF As Double
   

2. 替换错误的平方根函数
   VBA中平方根函数是Sqr()而非SQ()，拼写错误会直接中断代码：

   Roof1 = Round((Sqr(Ptch / 12) + 1) * SqFt1)
   

3. 安全引用控件值
   直接使用控件名称可能存在作用域问题，明确引用表单控件并处理空值：

   Wall1 = ((Nz(Me.H1LX.Value, 0) * 0.75) * 1.15) * Nz(Me.H1Hx.Value, 0)
   Wall2 = ((Nz(Me.H2LX.Value, 0) * 0.75) * 1.15) * Nz(Me.H2Hx.Value, 0)
   AStuds1 = Nz(Me.H1ASX.Value, 0)
   AStuds2 = Nz(Me.H2ASX.Value, 0)
   SqFt1 = Nz(Me.SFX.Value, 0)
   

4. 替换Eval函数
   Eval(Pitch)易引发错误，直接读取控件值更可靠：

   Ptch = Nz(Me.Pitch.Value, 0)
   

5. 正确赋值给输出控件
   确保LSqFt1是表单文本框，明确赋值控件的Value属性：

   Me.LSqFt1.Value = Wall1
   

6. 添加错误捕获
   加入错误捕获代码，快速定位具体报错：

   Private Sub Calculate1_Click()
       On Error GoTo ErrHandler
       
       ' 修正后的变量声明及计算代码...
       
   ExitHandler:
       Exit Sub
   ErrHandler:
       MsgBox "错误代码：" & Err.Number & vbCrLf & "错误描述：" & Err.Description
       Resume ExitHandler
   End Sub
   

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