Excel VBA宏:未定义用户定义的类型 [英] Excel VBA Macro: User Defined Type Not Defined
问题描述
尝试执行此宏时出现上述错误.我对宏和编码一般还是很陌生,所以请原谅我的无知.
I'm getting the above error when trying to execute this macros. I'm pretty new to Macros and coding in general so please forgive the ignorance.
Sub DeleteEmptyRows()
Dim oTable As Table, oRow As Row, _
TextInRow As Boolean, i As Long
Application.ScreenUpdating = False
For Each oTable In ActiveDocument.Tables
For Each oRow In oTable.Rows
TextInRow = False
For i = 2 To oRow.Cells.Count
If Len(oRow.Cells(i).Range.Text) > 2 Then
'end of cell marker is actually 2 characters
TextInRow = True
Exit For
End If
Next
If TextInRow = False Then
oRow.Delete
End If
Next
Next
Application.ScreenUpdating = True
End Sub
推荐答案
您的错误是由以下原因引起的:
Your error is caused by these:
Dim oTable As Table, oRow As Row,
这些类型Table
和Row
不是Excel固有的变量类型.您可以通过以下两种方式之一解决此问题:
These types, Table
and Row
are not variable types native to Excel. You can resolve this in one of two ways:
- 包括对Microsoft Word对象模型的引用.通过工具执行此操作|参考,然后添加对MS Word的参考.尽管不是绝对必要,但您可能想要完全限定
Dim oTable as Word.Table, oRow as Word.Row
之类的对象.这称为早期绑定. - 或者,要使用后期绑定方法,必须将对象声明为通用
Object
类型:Dim oTable as Object, oRow as Object
.使用这种方法,您无需将对Word的引用添加到您的计算机上,但同时也会失去VBE中的智能帮助.
- Include a reference to the Microsoft Word object model. Do this from Tools | References, then add reference to MS Word. While not strictly necessary, you may like to fully qualify the objects like
Dim oTable as Word.Table, oRow as Word.Row
. This is called early-binding. - Alternatively, to use late-binding method, you must declare the objects as generic
Object
type:Dim oTable as Object, oRow as Object
. With this method, you do not need to add the reference to Word, but you also lose the intellisense assistance in the VBE.
我尚未测试您的代码,但我怀疑ActiveDocument
在方法2中不能在Excel中工作,除非您将其正确地限定在Word.Application对象的实例范围内.在您提供的代码中,我看不到任何地方.一个例子是:
I have not tested your code but I suspect ActiveDocument
won't work in Excel with method #2, unless you properly scope it to an instance of a Word.Application object. I don't see that anywhere in the code you have provided. An example would be like:
Sub DeleteEmptyRows()
Dim wdApp as Object
Dim oTable As Object, As Object, _
TextInRow As Boolean, i As Long
Set wdApp = GetObject(,"Word.Application")
Application.ScreenUpdating = False
For Each oTable In wdApp.ActiveDocument.Tables
这篇关于Excel VBA宏:未定义用户定义的类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!