ThisWoorkbook运行时错误438 [英] ThisWoorkbook runtime Error 438
问题描述
我的VBA看起来像这样：
Sub UniqueList（）
Application.ScreenUpdating = False
Dim lastrow As Long
Dim i As Long
Dim dictionary As Object
Set dictionary = CreateObject（scripting.dictionary）
ThisWorkbook。 Sheet1.Activate
lastrow = Sheet1.Cells（Rows.Count，M）。End（xlUp）.Row
On Error Resume Next
For i = 1 To lastrow
如果Len（Cells（i，M））< 0然后
dictionary.Add Cells（i，M）。Value，1
End If
Next
Sheet3.Range（a2）。调整大小（dictionary.Count）.Value = _
Application.Transpose（dictionary.keys）
Application.ScreenUpdating = True
MsgBox dictionary.Count& 找到并复制了独特的单元格。
End Sub
获取错误的行是：
ThisWorkbook.Sheet1.Activate
我使用Sheet3中的按钮运行VBA。但我也尝试使用AltF8和AltF11与sheet1打开，没有任何作用。
我不知道为什么我得到这个错误，所以我希望有一个人可以帮助解决方案
Sheet1 不是 ThisWorkbook 的成员。 ThisWorkbook 是一个工作簿实例，而工作簿在 Worksheets 集合中为每个工作表公开动态成员。因此，错误438，对象不支持属性或方法。
Sheet1 我推测] ThisWorkbook 中的工作表的 CodeName ：它是一个全局范围的工作表对象VBA方便地创建，以文档模块的（Name）属性命名。
Sheet1 对象有一个父属性;就像每个工作表对象一样，它已经知道它所属的工作簿实例： p>
Debug.Print Sheet1.Parent是ThisWorkbook
IntelliSense一直在试图告诉你（由不是列出一个 Sheet1 成员） - 听听它说，
说，修改这样的说明：
Sheet1.Activate
...不解决另一个问题：您正在使用仅激活，以便不合格的单元格调用可以请参考具体的工作表：
For i = 1 To lastrow
如果Len细胞（i，M）） 0然后
dictionary.Add Cells（i，M）。Value，1
End If
Next
相反，限定他们：
code> For i = 1 To lastrow
如果Len（Sheet1.Cells（i，M））<> 0然后
dictionary.Add Sheet1.Cells（i，M）。Value，1
End If
Next
然后，激活调用变得完全无用。
这些隐含的 ActiveSheet 引用可以很容易地引入，很难发现。 Rubberduck （我管理的一个开放源码的VBE加载项目）可以帮助您找到它们（和其他内容）：
I have a VBA to copy and paste unique values from Sheet1 onto Sheet3. However i get the runtime error 438 when i run the VBA. My VBA looks like this:
Sub UniqueList()
Application.ScreenUpdating = False
Dim lastrow As Long
Dim i As Long
Dim dictionary As Object
Set dictionary = CreateObject("scripting.dictionary")
ThisWorkbook.Sheet1.Activate
lastrow = Sheet1.Cells(Rows.Count, "M").End(xlUp).Row
On Error Resume Next
For i = 1 To lastrow
If Len(Cells(i, "M")) <> 0 Then
dictionary.Add Cells(i, "M").Value, 1
End If
Next
Sheet3.Range("a2").Resize(dictionary.Count).Value = _
Application.Transpose(dictionary.keys)
Application.ScreenUpdating = True
MsgBox dictionary.Count & " unique cell(s) were found and copied."
End Sub
The line that gets the error is:
ThisWorkbook.Sheet1.Activate
I run the VBA using a button from Sheet3. But i also tried running it using AltF8 and AltF11 with sheet1 open, nothing works.
Im not really sure why i get that error so i hope that there is a person who can help with a solution
Sheet1 isn't a member of ThisWorkbook. ThisWorkbook is a Workbook instance, and Workbook objects don't expose "dynamic members" for every worksheet in their Worksheets collection. Hence error 438, Object does not support property or method.
Sheet1 is [I presume] the CodeName of a worksheet in ThisWorkbook: it's a global-scope Worksheet object VBA conveniently creates, named after the (Name) property of the document module.
That Sheet1 object has a Parent property; like every Worksheet object, it already knows what Workbook instance it belongs to:
Debug.Print Sheet1.Parent Is ThisWorkbook
IntelliSense has been trying to tell you that (by not listing a Sheet1 member) - listen to what it says!
That said, fixing the instruction like this:
Sheet1.Activate
...doesn't solve the other problem: you're using Activate only so that the unqualified Cells calls can refer to that specific worksheet:
For i = 1 To lastrow If Len(Cells(i, "M")) <> 0 Then dictionary.Add Cells(i, "M").Value, 1 End If Next
Instead, qualify them:
For i = 1 To lastrow
If Len(Sheet1.Cells(i, "M")) <> 0 Then
dictionary.Add Sheet1.Cells(i, "M").Value, 1
End If
Next
And then the Activate call becomes completely useless.
These implicit ActiveSheet references can be easy to introduce, and hard to spot. Rubberduck (an open-source VBE add-in project I manage) can help you locate them (and other things):
这篇关于ThisWoorkbook运行时错误438的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
