VBA Excel 将工作表分配给变量

Question

尝试将工作表分配给变量时出现“运行时错误‘13’类型不匹配”。它适用于所有其他工作表，但似乎只发生在这个工作表上。

这是我的代码:

Sub TS()
Dim RD, Dep, QC, MM, Pro As Workbook

With ThisWorkbook
Set RD = .Sheets("RawData")
Set Dep = .Sheets("Departments")
Set QC = .Sheets("QC")
Set MM = .Sheets("MM")
Set Pro = .Sheets("Production") 'I have a problem with this line
End With

End Sub

我的工作表标签如下:

Answer 1

该错误与您的错误声明有关:

Pro As Workbook

您应该将其声明为Worksheet，或者只是variant而不指定类型，例如:

Dim RD, Dep, QC, MM, Pro

更好的方法是使用强类型声明:

Dim RD As Worksheet, Dep As Worksheet, QC As Worksheet, MM As Worksheet, Pro As Worksheet

希望这会有所帮助。