使用OOP简化代码时解析Userform名称遇‘Object Required’错误求助

问题背景 #

尝试通过OOP模式减少重复代码，解析UserForm字符串名称来填充控件，但运行代码时触发"Object Required"错误。

模块md_AddCountry代码 #

Sub Select_AddDonor_Country()
Dim showsql As New cls_DBConPath

With showsql
    .colname = "country_Name"
    .sqlst = "Select country_Name From ccf_country;"
    .formname = "frmAddDonor.cmbAddDonr_Country"
    .DBConPath

    Set showsql = Nothing
End With
End Sub

类模块cls_DBConPath代码 #

Option Explicit

Private psqlSt As String
Private pcolumnName As String
Private pform As Object
Private pformName As String

Public Property Get colname() As String
colname = pcolumnName
End Property

Public Property Let colname(Value As String)
pcolumnName = Value
End Property

Public Property Get sqlst() As String
sqlst = psqlSt
End Property

Public Property Let sqlst(Value As String)
psqlSt = Value
End Property

Public Property Get formname() As String
formname = pformName
End Property

Public Property Let formname(Value As String)
pformName = Value
End Property

Public Property Get form() As Object
form = pform
End Property

Public Property Let form(Value As Object)
pform = Value
End Property

Sub DBConPath()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim dbPath As String
Dim fName As Object

Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

'On Error GoTo ErrHandler

dbPath = frmCCFDashboard.DBAddress.Caption

Set con = New ADODB.Connection
con.Open dbPath

Set rs = con.Execute(sqlst)

While rs.EOF = False

UserForms.Add(formname).AddItem rs.Fields(colname).Value
UserForms.Add(formname).AddItem rs.Fields(colname).Value
rs.MoveNext

Wend

End Sub

错误提示 #

Object Required

错误原因分析 #

1. UserForms.Add参数非法：formname传入的是带控件的完整路径（frmAddDonor.cmbAddDonr_Country），但UserForms.Add仅支持单独的表单名称，无法识别复合路径，导致对象创建失败。
2. 对象赋值未用Set：类模块中form属性的Get过程直接赋值form = pform，VBA中对象类型赋值必须使用Set关键字，否则触发对象错误。
3. 冗余重复操作：代码中连续两次调用AddItem，会导致每条数据重复添加，属于无效逻辑。

修复方案 #

步骤1：修改类模块cls_DBConPath代码 #

Option Explicit

Private psqlSt As String
Private pcolumnName As String
Private pformName As String
Private pcontrolName As String '新增：单独存储控件名称

'拆分表单名与控件名
Public Property Let formname(Value As String)
    Dim splitArr As Variant
    splitArr = Split(Value, ".")
    If UBound(splitArr) = 1 Then
        pformName = splitArr(0)
        pcontrolName = splitArr(1)
    End If
End Property

Public Property Get colname() As String
colname = pcolumnName
End Property

Public Property Let colname(Value As String)
pcolumnName = Value
End Property

Public Property Get sqlst() As String
sqlst = psqlSt
End Property

Public Property Let sqlst(Value As String)
psqlSt = Value
End Property

'修正对象赋值，添加Set关键字
Public Property Get form() As Object
    Set form = UserForms(pformName)
End Property

Sub DBConPath()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim dbPath As String
Dim targetControl As Control

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

dbPath = frmCCFDashboard.DBAddress.Caption

con.Open dbPath
rs.Open sqlst, con

'获取目标控件并清空原有内容
Set targetControl = form.Controls(pcontrolName)
targetControl.Clear

'遍历记录集填充控件
While Not rs.EOF
    targetControl.AddItem rs.Fields(colname).Value
    rs.MoveNext
Wend

'释放资源
rs.Close
con.Close
Set rs = Nothing
Set con = Nothing
End Sub

步骤2：保持模块md_AddCountry代码不变 #

原模块代码无需修改，类模块已处理formname的拆分逻辑。

关键修复点说明 #

• 拆分路径：通过Split方法将复合路径拆分为表单名和控件名，分别处理后正确获取对象。
• 对象赋值规范：类中form属性的Get方法添加Set关键字，符合VBA对象赋值规则。
• 优化资源管理：添加连接和记录集的关闭、释放代码，避免资源泄漏；填充前清空控件内容，避免数据冗余。
• 去除无效代码：删除重复的AddItem调用，确保每条数据仅添加一次。

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