排查VBA调用Attachmate Extra时账户资格误判问题

我编写的这段VBA函数接近完成，但需要协助排查问题。该函数在循环中运行，根据BlueZone终端的返回结果判断账户是否符合资格。当前无论返回结果是否为空，函数都返回“Eligible”，请帮忙分析代码问题：

Function AccountEligible(Account) As String

    Dim ws As Worksheet
    Dim LastPageCheck As String
    Dim AccountCheck As String
    Dim AcctNumber As String
    Dim iRow As Integer
    Dim AcctType As String
    Dim FromDate As String
    Dim ToDate As String
    Dim Cusip As String
    Dim Sess0 As Object

    
    Set ws = ThisWorkbook.Sheets("Starting Page") 'use an explicit worksheet reference
    FromDate = ws.Range("I16").Value
    ToDate = ws.Range("I17").Value
    Cusip = ws.Range("I14").Value
 
    ' ******** set session to bluezone **********

    Set Sess0 = CreateObject("BZWhll.WhllObj")
    Sess0.Connect ("")         'Connect to bluezone session 1 - internally called A
    
    HostSettleTime = 200        'set 200 milliseconds to wait for session to respond

    Sess0.WaitReady 10, HostSettleTime

            ' ******** Navigate to LTXN and enter account number **********
            
            Sess0.SendKeys ("<clear>")
            Sess0.WaitReady 10, HostSettleTime
            Sess0.SendKeys ("LTXN ") & Account
            Sess0.SendKeys ("<Enter>")
            Sess0.WaitReady 10, HostSettleTime
            
            '*********This is the search filter for CUSIP *************
            
            Sess0.SendKeys ("<Tab>")
            Sess0.SendKeys ("<Tab>")
            Sess0.SendKeys ("CUS")
            Sess0.SendKeys Cusip
            Sess0.SendKeys ("<Enter>")
            Sess0.WaitReady 10, HostSettleTime
            
            
            ' ******** Add the date filter to LTXN **********
            Sess0.SendKeys ("<F12>")
            Sess0.WaitReady 10, HostSettleTime
            Sess0.SendKeys ("<Tab>")
            Sess0.WaitReady 10, HostSettleTime
            Sess0.SendKeys ("<Tab>")
            Sess0.WaitReady 10, HostSettleTime
            Sess0.SendKeys ("<Tab>")
            Sess0.WaitReady 10, HostSettleTime
            Sess0.SendKeys ("<Tab>")
            Sess0.WaitReady 10, HostSettleTime
            Sess0.SendKeys ("<Tab>")
            Sess0.SendKeys FromDate
            Sess0.WaitReady 10, HostSettleTime
            Sess0.SendKeys ToDate
            Sess0.WaitReady 10, HostSettleTime
            Sess0.SendKeys ("<Enter>")
            Sess0.WaitReady 10, HostSettleTime
    
    
    ' ******** Check for Valid Class Action Data **********
    Sess0.ReadScreen NoDataChk, 1, iRow, 4          'check for no data in at all
    If NoDataChk = " " Then GoTo 1000 Else GoTo 1001 'return the result
    
    
1000
    AccountEligible = "Not Eligible"
    Sess0.WaitReady 10, HostSettleTime
1001
    AccountEligible = "Eligible"
    Sess0.WaitReady 10, HostSettleTime
    
End Function

问题分析 #

1. 标签跳转逻辑错误：这是核心问题。当代码跳转到1000标签执行AccountEligible = "Not Eligible"后，没有任何退出机制，会继续往下执行1001标签的代码，将结果重新赋值为"Eligible"，导致无论什么情况最终返回的都是Eligible。

2. 变量未声明与初始化问题：

   • NoDataChk变量没有显式声明，属于隐式变量，可能导致ReadScreen读取的结果无法正确存储。
   • iRow变量未初始化，默认值为0，传入ReadScreen方法可能导致读取了错误的屏幕位置，无法准确判断是否有数据。

3. 会话资源泄漏：每次调用函数都会新建一个BlueZone会话对象，但没有在函数结束时释放，长期运行可能导致资源占用过高。

修复方案 #

修复后的代码（改用If-Else结构，替代GoTo） #

Option Explicit ' 强制变量声明，避免隐式变量错误

Function AccountEligible(Account As String) As String ' 指定参数类型
    Dim ws As Worksheet
    Dim NoDataChk As String ' 显式声明变量
    Dim FromDate As String
    Dim ToDate As String
    Dim Cusip As String
    Dim Sess0 As Object
    Dim HostSettleTime As Long ' 显式声明变量
    Dim tabCount As Integer

    Set ws = ThisWorkbook.Sheets("Starting Page")
    FromDate = ws.Range("I16").Value
    ToDate = ws.Range("I17").Value
    Cusip = ws.Range("I14").Value

    ' 初始化会话
    Set Sess0 = CreateObject("BZWhll.WhllObj")
    Sess0.Connect ("")
    HostSettleTime = 200

    Sess0.WaitReady 10, HostSettleTime

    ' 导航到LTXN并输入账户号
    Sess0.SendKeys ("<clear>")
    Sess0.WaitReady 10, HostSettleTime
    Sess0.SendKeys "LTXN " & Account ' 简化写法
    Sess0.SendKeys ("<Enter>")
    Sess0.WaitReady 10, HostSettleTime

    ' CUSIP筛选
    Sess0.SendKeys ("<Tab>")
    Sess0.SendKeys ("<Tab>")
    Sess0.SendKeys "CUS" & Cusip ' 合并发送
    Sess0.SendKeys ("<Enter>")
    Sess0.WaitReady 10, HostSettleTime

    ' 添加日期筛选
    Sess0.SendKeys ("<F12>")
    Sess0.WaitReady 10, HostSettleTime
    ' 用循环简化连续Tab操作
    For tabCount = 1 To 5
        Sess0.SendKeys ("<Tab>")
        Sess0.WaitReady 10, HostSettleTime
    Next tabCount
    Sess0.SendKeys FromDate
    Sess0.WaitReady 10, HostSettleTime
    Sess0.SendKeys ToDate
    Sess0.WaitReady 10, HostSettleTime
    Sess0.SendKeys ("<Enter>")
    Sess0.WaitReady 10, HostSettleTime

    ' 检查是否有数据（请根据实际屏幕无数据提示的位置，调整ReadScreen的行号参数）
    Sess0.ReadScreen NoDataChk, 1, 5, 4 

    ' 用If-Else替代GoTo，逻辑更清晰
    If Trim(NoDataChk) = "" Then ' Trim去掉空格，避免空白字符干扰判断
        AccountEligible = "Not Eligible"
    Else
        AccountEligible = "Eligible"
    End If

    ' 释放会话资源
    Sess0.Disconnect ' 根据BlueZone API确认是否需要断开连接
    Set Sess0 = Nothing
End Function

关键修复点说明 #

• 添加Option Explicit强制变量声明，提前发现未声明的变量错误。
• 用If-Else结构替代GoTo，避免代码执行流混乱，彻底解决结果被覆盖的问题。
• 显式声明所有变量，指定ReadScreen的正确行号（需根据终端实际无数据提示位置调整），确保读取准确。
• 添加会话资源释放代码，避免资源泄漏。
• 简化重复的SendKeys和WaitReady代码，提升可维护性。

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