为何一段VBA UserForm初始化代码报Run-time error '1004'，另一段正常？

两段对比代码 #

报错代码 #

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Dim rng As Range
Dim LRow As Long
Dim Myarray As Variant

 Set ws = ThisWorkbook.Sheets(Mark1.LEADLISTDROPDOWN.Value)
 
 With ws
 LRow = .Range("BJ" & .Rows.Count).End(xlUp).Row
 
 Set rng = ws.Range("A:KH" & LRow)
 
 PopoutLeadListBox.RowSource = rng.Address(External:=True)
 
 End With
 
End Sub

正常运行代码 #

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim rng As Range
Dim LRow As Long
Dim Myarray As Variant

 Set ws = ThisWorkbook.Sheets(SalesForm.BHSDLEADLISTSCOMBOBOXLF.Value)
 
 With ws
 LRow = .Range("S" & .Rows.Count).End(xlUp).Row
 
 Set rng = ws.Range("H2:Y" & LRow)
 
 PopoutLeadListBox.RowSource = rng.Address(External:=True)
 
 End With
 
End Sub

具体排查步骤 #

• 修正范围语法错误：第一段代码中ws.Range("A:KH" & LRow)是非法格式，A:KH代表整列范围，拼接行号后会变成A:KH123这类无效地址。正确写法应为"A1:KH" & LRow（从第一行开始）或"A2:KH" & LRow（跳过表头），这是最可能触发1004错误的原因。
• 验证工作表名称有效性：在Set ws = ...前添加调试语句MsgBox Mark1.LEADLISTDROPDOWN.Value，确认下拉框返回的工作表名称无拼写错误、空格或特殊字符，且确实存在于当前工作簿中。
• 检查目标列存在性：确认报错代码中用到的BJ、KH列在目标工作表中可正常访问，排除工作表保护、隐藏列限制等特殊情况。
• 处理空列边界情况：如果BJ列全为空，LRow会返回第一行的行号，此时可添加判断逻辑（如If LRow < 2 Then Exit Sub）避免无效范围赋值。
• 确认窗体控件状态：确保Mark1窗体在当前代码运行时已正确加载，LEADLISTDROPDOWN控件有有效选中值，避免因隐式加载窗体引发的异常。

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