VBA 中的 Excel 错误"运行时错误'424'：SET 语句中需要对象

Question

我在代码的最后一行收到运行时错误"424"，无法解释原因。请帮忙。

Dim sourceSh As Worksheet
Dim destSh As Worksheet
Dim FileName As String
Dim destWk As Workbook
Dim sourceWk As Workbook
FileName = "Data.xlsx"
Set destWk = Workbooks.Open(FileName)
Set sourceWk = DataEntry
Set sourceSh = sourceWk.Sheets(Data_Entry.Name)  'why here I'm not getting error !!
Set destSh = destWk.Sheets(Employees.Name)      'here I'm getting Run-time error 424. 

我检查了一下，一切都很好。

Answer 1

您正在设置一个工作表对象"destSh"；通过将工作簿中的某个工作表分配给它；destWk"；。

如果"；雇员"；是现有工作表的代码名称，则应该没有错误(BTW，只有

Set destSh = destWk.Employees

在这种情况下就足够了(。

如果"；雇员"；是一个工作表名称(选项卡标题(，那么你应该写

Set destSh = destWk.Sheets("Employees")

既然你没有，情况可能就不是这样了。。。

因此，唯一可能的结论是；雇员"；存在于"；destWk"；。

Answer 2

按代码名称定义工作表

• 我建议您只在ThisWorkbook中使用Data_Entry；当它已经被定义时，不需要将它分配给变量(SourceSh(

代码

Option Explicit
Sub wsByCodename()
Dim sourceSh As Worksheet
Dim destSh As Worksheet
Dim FileName As String
Dim destWk As Workbook

' The following should contain the 'complete' path, e.g. "C:TestData.xlsx".
FileName = "Data.xlsx"
Set destWk = Workbooks.Open(FileName)

Set destSh = defineWorksheetByCodeName(destWk, "Employees")
Set sourceSh = Data_Entry
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      In a specified workbook, NOT containing this code, returns
'               the worksheet (object) which is specified by its code name.
' Remarks:      If it is expected that a worksheet might get renamed
'               or moved to another position, it is best to refer to it
'               by its code name. In 'ThisWorkbook', the workbook containing
'               this code, you will use just e.g. 'Sheet1'.
'               But in another workbook, you could use this function,
'               because you CANNOT use e.g. 'ActiveWorkbook.Sheet1'.
' Example:      Const WorksheetCodeName As String = "Sheet1"
'               Dim wb As Workbook: Set wb = ActiveWorkbook
'               Dim ws As Worksheet
'               Set ws = defineWorksheetByCodeName(wb, WorksheetCodeName)
'               If ws Is Nothing Then Exit Sub ' or whatever.
'               ' Continue with code...
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function defineWorksheetByCodeName(Book As Workbook, _
ByVal WorksheetCodeName As String) _
As Worksheet
Dim ws As Worksheet
For Each ws In Book.Worksheets
If StrComp(ws.CodeName, WorksheetCodeName, vbTextCompare) = 0 Then
Set defineWorksheetByCodeName = ws
Exit For
End If
Next ws
End Function