语言服务提交报错‘91’：VBA调用SAS执行查询遇问题

Hey, let's dig into that Error 91 ("Object variable or With block variable not set") you're hitting when trying to run SAS queries via VBA. This error almost always means an object reference hasn't been properly initialized or assigned—let's walk through your code, fix the gaps, and cover common pitfalls.

First, Fix the Critical Missing Steps in Your Code #

Your snippet cuts off at Set ..., which is where the core connection to the SAS workspace happens. Here's a complete, corrected version with explanations:

Dim obObjectFactory As New SASObjectManager.ObjectFactory
Dim obObjectKeeper As New SASObjectManager.ObjectKeeper
Dim obServer As New SASObjectManager.ServerDef
Dim obSAS As SAS.Workspace
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

' 1. Correctly define the protocol using SAS's explicit enum (avoids undefined variable errors)
obServer.MachineDNSName = "xxxx@company.com"
obServer.Protocol = SASObjectManager.Protocols.ProtocolBridge
obServer.Port = 8561
obObjectFactory.LogEnabled = True

' 2. THE MISSING STEP: Connect to the SAS server and retrieve a workspace object
' Replace empty strings with your SAS credentials if required
Set obSAS = obObjectFactory.CreateObjectByServer("SASServer", True, obServer, "", "")

' 3. Example: Submit SAS code via the Language Service
obSAS.LanguageService.Submit "proc print data=sashelp.class; run;"

' 4. Optional: Use ADODB to fetch query results (if needed)
cn.Open "Provider=SAS.LocalProvider.1;Data Source=SAS Workspace;"
cn.Properties("SAS Workspace ID") = obSAS.UniqueIdentifier

rs.Open "SELECT * FROM sashelp.class", cn
' Process your recordset here (e.g., loop through rows, populate Excel cells)

' Cleanup to avoid memory leaks
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
Set obSAS = Nothing
Set obServer = Nothing
Set obObjectKeeper = Nothing
Set obObjectFactory = Nothing

Key Troubleshooting Checks for Error 91 #

1. Verify VBA Project References
   Ensure your VBA project has these references enabled (go to Tools > References in the VBA editor):

   • SAS Object Manager 1.1 Type Library
   • SAS Workspace Manager 1.1 Type Library
   • Microsoft ActiveX Data Objects x.x Library (for ADODB connections)
     Missing references will cause unrecognized objects/properties and trigger Error 91.

2. Confirm Server Access & Credentials

   • Double-check the MachineDNSName, port (8561), and protocol match your SAS server's configuration.
   • If your SAS server requires authentication, pass your username and password in the CreateObjectByServer method (replace the empty strings in the code above).

3. Add Error Handling for Better Debugging
   Wrap the connection step in error handling to get more specific details about the failure:

   On Error Resume Next
   Set obSAS = obObjectFactory.CreateObjectByServer("SASServer", True, obServer, "", "")
   If Err.Number <> 0 Then
       MsgBox "SAS connection failed: " & Err.Description & " (Error Code: " & Err.Number & ")"
       Exit Sub
   End If
   On Error GoTo 0
   

4. Check SAS Object Spawner Status
   The SAS Object Spawner must be running on the server you're connecting to (it listens on port 8561 by default). If it's stopped, your VBA code won't be able to establish a connection.

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