VBA宏SAP赋值逻辑异常：输入单个MRP值触发错误分支

问题场景 #

需求：实现VBA宏从输入框获取多个逗号分隔的MRP Controller值，输入2个值时将其填入SAP的两个独立位置。
异常：输入单个值"007"运行宏时，本该执行If j = 1分支，却出现逻辑判断失效的报错。

原代码片段 #

Public MRP As String
Public dNext_Monday As Date
Public WK As String
Public LR As Long
Public iWindowState As Integer
Public strEntries As String, inputArray() As String, j As Variant

strEntries = Application.InputBox("Enter multiple comma separated MRP Controllers. Ex: 007,009,016 ", "MRP Entries", Type:=2)
    If strEntries = "False" Then Exit Sub   'User canceled
    inputArray = Split(strEntries, ",")
    j = UBound(inputArray) - LBound(inputArray) + 1    

For Each j In inputArray
If j = 1 Then
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,0]").Text = inputArray(0)
End If
If j = 2 Then
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,0]").Text = inputArray(0)
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,1]").Text = inputArray(1)
End If

问题根源 #

变量j被重复使用导致语义混淆：

• 先将j赋值为数组元素总数（输入单个值时j=1）
• 后续用For Each j In inputArray遍历数组，此时j变成了数组的元素值（比如输入"007"时，j是字符串"007"）
• 原本判断元素个数的If j=1/If j=2条件完全失效，因为此时j是元素值而非数量，类型和数值都不匹配，自然不会执行对应分支，甚至触发类型不匹配报错。

修复方案 #

将统计元素数量的变量和遍历变量拆分，直接根据元素数量执行对应逻辑（无需遍历数组）：

Public MRP As String
Public dNext_Monday As Date
Public WK As String
Public LR As Long
Public iWindowState As Integer
Public strEntries As String, inputArray() As String, countMRP As Integer

strEntries = Application.InputBox("Enter multiple comma separated MRP Controllers. Ex: 007,009,016 ", "MRP Entries", Type:=2)
If strEntries = "False" Then Exit Sub   'User canceled
inputArray = Split(strEntries, ",")
countMRP = UBound(inputArray) - LBound(inputArray) + 1

' 根据MRP数量执行对应填充逻辑
If countMRP = 1 Then
    session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,0]").Text = inputArray(0)
ElseIf countMRP = 2 Then
    session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,0]").Text = inputArray(0)
    session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,1]").Text = inputArray(1)
End If

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