Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel VBA Macro: User Defined Type Not Defined

Tags:

ms-word

excel

vba

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
like image 792
holdo1 Avatar asked Jun 17 '14 10:06

holdo1


People also ask

How do you define a user defined type in VBA?

You create a user defined type using the Type statement. This combines multiple data types into a single data type. You define custom data types outside of procedures at the top of your module. Once you have created your type use the Dim statement to declare a variable of that type.

How do I fix variable not defined error in VBA?

This error has the following cause and solution: You used an Option Explicit statement to require the explicit declaration of variables, but you used a variable without declaring it. Explicitly declare the variable, or change the spelling of the variable to match that of the intended variable.

How do I fix type mismatch error in VBA?

Step 1: Write the subprocedure for VBA Type Mismatch. Step 2: Again assign a new variable, let's say “A” as Byte data type. Let's understand the Byte Data type here. Byte can only store the numerical value from 0 to 255.


1 Answers

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:

  1. 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. enter image description here
  2. 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.

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
like image 157
David Zemens Avatar answered Sep 20 '22 04:09

David Zemens