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
Adrian Mole
49.5k155 gold badges49 silver badges79 bronze badges
asked Jun 17, 2014 at 10:34
3
Your error is caused by these:
Dim oTable As Table, oRow As Row,
These types, Table and Row are not variable types native to Excel. You can resolve this in one of two ways:
- 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
Objecttype: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.
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
answered Jun 17, 2014 at 12:09
David ZemensDavid Zemens
52.9k11 gold badges80 silver badges129 bronze badges
0
I am late for the party. Try replacing as below, mine worked perfectly-
«DOMDocument» to «MSXML2.DOMDocument60»
«XMLHTTP» to «MSXML2.XMLHTTP60»
answered May 2, 2019 at 15:38
Sub DeleteEmptyRows()
Worksheets("YourSheetName").Activate
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
The following code will delete all rows on a sheet(YourSheetName) where the content of Column A is blank.
EDIT: User Defined Type Not Defined is caused by «oTable As Table» and «oRow As Row». Replace Table and Row with Object to resolve the error and make it compile.
answered Jun 17, 2014 at 10:51
Olle89Olle89
6687 silver badges22 bronze badges
1
Are you sitting there staring at this error on your VBA screen and getting frustrated? No worries, we shall fix it.
But before deep diving into the root cause and solution to fix this error, let’s understand the correct procedure for using an object in our code. It will ease the debugging process.
For this example, let’s look at a Dictionary object.
DICTIONARY in VBA:
A DICTIONARY is an object similar to the VBA COLLECTION object with the following differences:
- The values of the keys can be updated or changed later and
- The key / item value can easily be checked for existence without completely iterating through all the items. This also helps to retrieve values easily.
If you’re a beginner, just imagine that this object is a real time dictionary where the keys are the words and items are the respective definitions. As in a dictionary, in the VBA object we do not need to iterate through all the keys to find the value of one specific key.
And just like any other object in VBA, we can use a dictionary object by adding the corresponding reference through Tools menu. Declaration and definition of objects can be done through early or late binding methods per the developer’s convenience.
Resolving the Error
The error in the title is a compile time error that is encountered when you compile the code.
Analyze the meaning and “ROOT CAUSE” of the error:
Let us split and read the error to understand it better.
User-defined type | not defined
First, let’s try to understand we have encountered the error because something is
“not defined”.
A possible reason for the error to occur is that you are utilizing the early binding method to declare and define the object, but the required reference has not been added.
Refer to the sample code below to understand the difference between early and late binding.
Late binding:
' Create a dictionary object using the late binding method.
Dim obdict As Object
Set obdict = CreateObject("Scripting.Dictionary")
Early binding:
' Create a dictionary object using the early binding method.
Dim obdict As New Scripting.Dictionary
Solution:
Try one of the following steps to resolve the error:
Method 1
Maybe VBA doesn’t understand that you have defined the object. In VBA, you need to add the respective reference for the object to let the language know that you have properly defined it.
- Goto the menu Tools-> References
- Select the library “Microsoft Scripting Runtime.” (This varies depending on the object used. Here the same dictionary object is considered for explanation purposes
- Click on the “OK” button and close the dialog
- Now you can compile the code and see that the error doesn’t appear anymore
Note: All this is NOT mandatory if you are following “late binding” method.
Method 2
Use the late binding method where you declare a generic object first, then define its type. This does not require any reference.
Syntax:
Dim <variable> As Object
Set <variable> = CreateObject("Scripting.Dictionary")
Example for an Excel sheet object:
Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")
Example for a dictionary object:
'Example of creating a dictionary object
Dim odict As Object
Set odict = CreateObject("Scripting.Dictionary")
Video Example
The video below shows how to resolve the error using each of the two methods above.
|
user-defined type not defined vba |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
User-defined type error message usually occurs when the VBA compiler cannot recognize a custom data type or class that you’ve used in your code. This can be caused by various reasons, including missing references, incorrect syntax, or a simple typo.
Neck and Shoulder Relaxer
To solve this error, the first thing you need to do is to identify the source of the problem. Check your code carefully and make sure that all the custom data types or classes are properly defined and referenced. If you’re using external libraries or APIs, make sure that you’ve added the appropriate references to your VBA project.
If you’ve confirmed that all your data types and classes are defined and referenced correctly, you can try resetting the VBA environment. Close all instances of Excel and then reopen the workbook to clear any cached or corrupted data.
Another solution is to explicitly declare the custom data type or class in your code. This can be done by using the “Option Explicit” statement at the beginning of your module and then declaring the custom type/class using the “Type” or “Class” keyword.
Selecting the right References from the available reference list will solve this isssue. In this example, we show how to fix “User-defined type not defined” compile error when “Outlook” called in a statement.
- Go to Tools Menu -> Click References
- Scroll down and select (tick) “Microsoft Outlook 16.0 Object Library” from the Available References list.
- Click “Ok”
Now you will be able to run the code without
Permalink
Cannot retrieve contributors at this time
| title | keywords | f1_keywords | ms.prod | ms.assetid | ms.date | ms.localizationpriority |
|---|---|---|---|---|---|---|
|
User-defined type not defined (VBA) |
vblr6.chm1011292 |
vblr6.chm1011292 |
office |
60e0da5e-c498-7a2f-46c6-c09d59fc607a |
12/27/2018 |
high |
You can create your own data types in Visual Basic, but they must be defined first in a Type…End Type statement or in a properly registered object library or type library. This error has the following causes and solutions:
-
You tried to declare a variable or argument with an undefined data type or you specified an unknown class or object.
Use the Type statement in a module to define a new data type. If you are trying to create a reference to a class, the class must be visible to the project. If you are referring to a class in your program, you must have a class module of the specified name in your project. Check the spelling of the type name or name of the object.
-
The type you want to declare is in another module but has been declared Private. Move the definition of the type to a standard module where it can be Public.
-
The type is a valid type, but the object library or type library in which it is defined isn’t registered in Visual Basic. Display the References dialog box, and then select the appropriate object library or type library. For example, if you don’t check the Data Access Object in the References dialog box, types like Database, Recordset, and TableDef aren’t recognized and references to them in code cause this error.
For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).
See also
- Visual Basic how-to topics
[!includeSupport and feedback]