为何VBA中(L \ 12)计算为0？Wrap面积计算异常求助

你的问题核心是VBA里误用了整除运算符，导致除法结果被强制取整，具体分析和修复如下：

错误原因 #

VBA中\是整除运算符——无论变量类型是否为Double，它都会自动舍去小数部分取整。你在控件源里用的是普通除法/，所以能得到正确的2/12=0.1666...，但VBA里用2\12直接得到0，最终导致整个计算结果为0。

修复步骤 #

1. 替换所有整除运算符\为普通除法/ #

把代码中所有用\做除法的地方改成/：

• 长度转换：Length = (L \ 12) → Length = L / 12
• 件数计算：Me.Txt_Pcs_JobTicket = Abs(Int(Me.Quantity_Ordered \ Length)) → Me.Txt_Pcs_JobTicket = Abs(Int(Me.Quantity_Ordered / Length))
• 包装slit转换：(frm("Wrap_Slit" & W) \ 12) → (frm("Wrap_Slit" & W) / 12)

2. 修正Scrap Rate的错误写法 #

Else分支里的frm(RIP_Scrap_Rate + 1)是语法错误，正确写法应该是frm("RIP_Scrap_Rate") + 1——原写法会把控件名当成变量进行数值运算，导致错误结果。

3. 对齐VBA计算和控件源的逻辑 #

控件源里用了Abs(Int(...))处理最终结果，VBA代码里也应该加上这两个函数，确保计算结果和手动输入表达式的结果完全一致。

修正后的完整代码 #

Private Sub FTG_Calculations()

    'Declare Variable
    Dim L As Double
    Dim Length As Double
    Dim OrderFTG As Double
    Dim UoM As String
    Dim W As Integer '循环计数用Integer更合适
    Dim frm As Access.Form
    Dim wrapSlit As Double '存储slit值，减少重复控件引用
    Dim scrapRate As Double '存储废品率，提升效率
    
    Set frm = Forms!Frm_JobTicket
    
    'Set L equal to Length from Tbl_JobTicketMould
    L = DLookup("Length", "Tbl_JobTicketMould", "Access_ID =" & Forms!Frm_JobTicket!Part_Number)
    
    'Convert Length to Feet - 替换为普通除法
    Length = L / 12
    
    'Find Unit of Measure for this part
    UoM = DLookup("Unit_of_Measure", "Tbl_JobTicketUoM", "Access_ID =" & Forms!Frm_JobTicket!Part_Number)
    
    'Multiply Length times Quantity to get Order Footage
    OrderFTG = Int((Length * Me.Txt_Pcs_JobTicket))
    
    'If UoM is PCS then insert that number. Otherwise set equal to Quantity Ordered divided by Length of piece(in FT)
    If UoM = "PCS" Then 
        Me.Txt_Pcs_JobTicket = Me.Quantity_Ordered 
    Else 
        '替换为普通除法
        Me.Txt_Pcs_JobTicket = Abs(Int(Me.Quantity_Ordered / Length))
    End If
    
    'Define limits of the loop. Then runs through all Wrap SQ FTG fields and inputs calculation
    scrapRate = Round(frm("RIP_Scrap_Rate"), 3) '提前获取废品率，避免重复计算
    For W = 1 To 3
        wrapSlit = frm("Wrap_Slit" & W) '获取当前slit值
        
        'If UoM is PCS then calculate Order Footage to find Wrap Square Footage. Otherwise take slit size in FT and multiply by Order Quantity and Scrap Rate
        If UoM = "PCS" Then
            '添加Abs(Int())对齐控件源逻辑，替换为普通除法
            frm("Txt_Wrap" & W & "SQFTG_JobTicket") = Abs(Int( ((wrapSlit / 12) * OrderFTG) * (scrapRate + 1) ))
        Else 
            '修正废品率写法，添加Abs(Int())，替换为普通除法
            frm("Txt_Wrap" & W & "SQFTG_JobTicket") = Abs(Int( ((wrapSlit / 12) * frm("Quantity_Ordered")) * (frm("RIP_Scrap_Rate") + 1) ))
        End If
    Next W
End Sub

额外优化建议 #

• 循环变量W用Integer类型更合适，因为它是整数计数，节省内存且逻辑更清晰
• 提前把控件值存入变量，避免多次引用控件，提升代码效率和可读性
• 可添加错误处理（如On Error GoTo），防止DLookup找不到数据时程序崩溃

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