Excel VBA：关闭登录表单后如何将凭证传递至模块？

我在Excel启动时创建了登录表单，通过登录凭证验证用户是否能连接Teradata服务器。现有一个执行该服务器SQL查询的模块，希望将登录阶段输入的用户名和密码保存下来供模块使用，但表单登录后会关闭，尝试使用公共变量却无法保留值，请问这是否可行？

登录表单的VBA代码 #

Option Explicit

Private Sub cmdLogin_Click()

    Dim Cn As ADODB.Connection
    Dim Rc As ADODB.Recordset
    Set Cn = New ADODB.Connection
    Dim user As String
    Dim password As String
    Dim sConnect As String
    
    user = Me.txtUserID.Value
    password = Me.txtPassword.Value
    
    sConnect = "Driver={Teradata};DBCname=xxxxx;DatabaseName=xxxxx;Uid=" & user & ";Pwd=" & password & "; Authentication=LDAP;"
    
    Cn.Open sConnect
    
    If Cn.State = 1 Then
        Unload Me
        Application.Visible = True
    Else
        If LoginInstance < 3 Then
            MsgBox "登录凭证无效，请重试。", vbOKOnly + vbCritical, "登录失败"
            LoginInstance = LoginInstance + 1
        Else
            MsgBox "你已超过最大登录尝试次数。", vbOKOnly + vbCritical, "登录失败"
            Unload Me
            ThisWorkbook.Close savechanges:=False
            Application.Visible = True
            LoginInstance = 0
        End If
    End If

End Sub

Private Sub cmdSkip_Click()
    Unload Me
    Application.Visible = True
End Sub

Private Sub UserForm_Initialize()
    Me.txtUserID.Value = ""
    Me.txtPassword.Value = ""
    Me.txtUserID.SetFocus
End Sub

模块中使用的连接代码片段 #

sConnect = "Driver={Teradata};DBCname=xxxxx;DatabaseName=xxxxx;Uid=" & user & ";Pwd=" & password & "; Authentication=LDAP;"
Cn.Open sConnect

预期的模块代码 #

Public LoginInstance As Integer
Public user As String
Public password As String

Sub Extract_SUMMARY()

Dim Cn As ADODB.Connection
Dim Rc As ADODB.Recordset
Set Cn = New ADODB.Connection

table_query = "sql query"
summary_query = "sql query"

sConnect = "Driver={Teradata};DBCname=xxxx;DatabaseName=xxxxx;Uid=" & user & ";Pwd=" & password & "; Authentication=LDAP;"

Cn.Open sConnect

Set table_set = New ADODB.Recordset

table_set.Open table_query, Cn
Worksheets("Higher Level Analysis").Cells(2, 4).CopyFromRecordset table_set
table_set.Close

Set summary_set = New ADODB.Recordset

summary_set.Open summary_query, Cn
Worksheets("Higher Level Analysis").Cells(5, 1).CopyFromRecordset summary_set
summary_set.Close

Cn.Close

Set recset = Nothing

End Sub

解决方案 #

1. 修正公共变量的赋值逻辑 #

你当前的问题核心是：登录表单的cmdLogin_Click过程中声明了局部变量user和password，这些变量仅在该过程内有效，表单关闭后就会被销毁，并没有赋值给模块中声明的公共变量。

修改表单代码，去掉局部变量声明，直接将输入值赋值给模块的公共变量：

Private Sub cmdLogin_Click()

    Dim Cn As ADODB.Connection
    Dim Rc As ADODB.Recordset
    Set Cn = New ADODB.Connection
    Dim sConnect As String
    
    ' 直接赋值给模块级公共变量
    user = Me.txtUserID.Value
    password = Me.txtPassword.Value
    
    sConnect = "Driver={Teradata};DBCname=xxxxx;DatabaseName=xxxxx;Uid=" & user & ";Pwd=" & password & "; Authentication=LDAP;"
    
    Cn.Open sConnect
    
    If Cn.State = 1 Then
        Unload Me
        Application.Visible = True
    Else
        If LoginInstance < 3 Then
            MsgBox "登录凭证无效，请重试。", vbOKOnly + vbCritical, "登录失败"
            LoginInstance = LoginInstance + 1
        Else
            MsgBox "你已超过最大登录尝试次数。", vbOKOnly + vbCritical, "登录失败"
            Unload Me
            ThisWorkbook.Close savechanges:=False
            Application.Visible = True
            LoginInstance = 0
        End If
    End If

End Sub

这样表单关闭后，模块中的公共变量user和password会保留赋值后的内容，后续执行Extract_SUMMARY过程时就能正常调用。

2. 替代方案：使用隐藏存储（更安全） #

如果不想依赖公共变量（避免变量被意外修改），可以将用户名和密码存储到隐藏工作表的单元格中：

步骤1：创建隐藏工作表

在Excel中插入新工作表，命名为HiddenData，然后设置为深度隐藏：

' 可在工作簿打开事件中执行
Sheets("HiddenData").Visible = xlSheetVeryHidden

步骤2：表单中保存凭证

登录验证成功后，将值写入隐藏工作表：

If Cn.State = 1 Then
    Sheets("HiddenData").Range("A1").Value = Me.txtUserID.Value
    Sheets("HiddenData").Range("A2").Value = Me.txtPassword.Value
    Unload Me
    Application.Visible = True
End If

步骤3：模块中读取凭证

Sub Extract_SUMMARY()
    Dim Cn As ADODB.Connection
    Dim Rc As ADODB.Recordset
    Set Cn = New ADODB.Connection
    Dim user As String, password As String, sConnect As String
    
    user = Sheets("HiddenData").Range("A1").Value
    password = Sheets("HiddenData").Range("A2").Value
    
    table_query = "sql query"
    summary_query = "sql query"

    sConnect = "Driver={Teradata};DBCname=xxxx;DatabaseName=xxxxx;Uid=" & user & ";Pwd=" & password & "; Authentication=LDAP;"

    ' 后续执行逻辑不变...
End Sub

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