Consulting

Results 1 to 6 of 6

Thread: 'Compile Error: User-defined type not defined' after adding word object library>code

  1. #1
    VBAX Newbie
    Joined
    Oct 2017
    Posts
    4
    Location

    'Compile Error: User-defined type not defined' after adding word object library>code

    I am a newbie at VBA programming and this is my first post on a forum so I would like to apologise beforehand in case i make any mistakes.

    I am automating an Excel workbook which opens and works with a new Word file from a template. I am using 'WithEvents' to track application events in Word. I am also using code to remove word object library references when closing the workbook and then adding them again at 'Workbook_Open' to make sure this workbook will work on other machines with different versions of word.

    Everything works as expected except for a 'Compile Error: User-defined type not defined' error every time I open the workbook but consequent compilations work fine without a hitch. I know what is causing it - there is no reference to word object library during the first compile trial hence the compiler does not know what 'Word.Application' is but from the second instance onwards it does and hence produces no errors.

    I just cannot ge my head around how to fix this. I have looked into 'LateBinding' but from research I found out 'WithEvents' is not compatible with LateBinding. Any help will be greatly appreciated.

    Thank you in advance for your time.
    Option Explicit
    
    
    Private Sub Workbook_Open()
    
    
        ThisWorkbook.VBProject.References.AddFromGuid GUID:="{00020905-0000-0000-C000-000000000046}", Major:=0, Minor:=0
        ThisWorkbook.VBProject.References.AddFromGuid GUID:="{00062FFF-0000-0000-C000-000000000046}", Major:=0, Minor:=0
    
    
    End Sub
    
    
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    
        If IsEmpty(ThisWorkbook.VBProject.References.Item("Word")) = False Then
            ThisWorkbook.VBProject.References.Remove ThisWorkbook.VBProject.References.Item("Word")
        End If
    
    
        If IsEmpty(ThisWorkbook.VBProject.References.Item("Outlook")) = False Then
            ThisWorkbook.VBProject.References.Remove ThisWorkbook.VBProject.References.Item("Outlook")
        End If
    
    
        ActiveWorkbook.Save
    
    
        Set wdAppClass = Nothing
        Set wdAppClass.wdApp = Nothing
        'Set wdApp = Nothing
        Set wdDoc = Nothing
        Set button = Nothing
    
    
    End Sub
    Option Explicit
    
    
    Public WithEvents wdApp As Word.Application
    
    
    Private Sub wdApp_DocumentBeforeClose(ByVal Doc As Document, Cancel As Boolean)
    
    
        Dim datecheck As Boolean
    
    
        ThisWorkbook.ActiveSheet.Range("F" & row & "").Value = wdDoc.Shapes(1).TextFrame.TextRange.Text
        ThisWorkbook.ActiveSheet.Range("K" & row & "").Value = wdDoc.Shapes(2).TextFrame.TextRange.Text
    
    
        datecheck = IsDate(wdDoc.Shapes(3).TextFrame.TextRange.Text)
    
    
        If datecheck = True Then
            ThisWorkbook.ActiveSheet.Range("M" & row & "").Value = wdDoc.Shapes(3).TextFrame.TextRange.Text
            Set_Reminder
        End If
    
    
        wdAppClass.wdApp.Quit
        wdApp.Quit
        wdDoc.Close
    
    
        Set wdAppClass = Nothing
        Set wdAppClass.wdApp = Nothing
        Set wdApp = Nothing
        Set wdDoc = Nothing
        Set button = Nothing
    
    
    End Sub
    Last edited by tanim_84; 10-25-2017 at 06:53 AM.

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,849
    Location
    I think
    Sub Workbook_Open
    Set wdApp = create new(Object...)
    My notes say:
     
    Private Sub Workbook_Open()
        Set ThisApp = Me.Application 'for a With Events Class
    End Sub
    ThisApp_BeforeClose(...
    Please take the time to read the Forum FAQ

  3. #3
    VBAX Newbie
    Joined
    Oct 2017
    Posts
    4
    Location
    Hello and thank you SamT for your quick response! I will try your suggestion a bit later today and will post the outcome here. Fingers crossed!

  4. #4
    VBAX Newbie
    Joined
    Oct 2017
    Posts
    4
    Location
    I have now tried your suggestion but it still gives the same error just on the second line of the class module (sub withevents wdapp declaration) with same error type. I have also tried other variations with the same result. I do not know what else I can try.

    I posting my full project code below, if you can spare the time could you try running it please? I would be very grateful.

    Thank you again.

    'ThisWorkbook'
    '------------'
    Option Explicit
    
    
    Private Sub Workbook_Open()
        
        ThisWorkbook.VBProject.References.AddFromGuid GUID:="{00020905-0000-0000-C000-000000000046}", Major:=0, Minor:=0
        ThisWorkbook.VBProject.References.AddFromGuid GUID:="{00062FFF-0000-0000-C000-000000000046}", Major:=0, Minor:=0
            
    End Sub
    
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
            
        If IsEmpty(ThisWorkbook.VBProject.References.Item("Word")) = False Then
            ThisWorkbook.VBProject.References.Remove ThisWorkbook.VBProject.References.Item("Word")
        End If
        
        If IsEmpty(ThisWorkbook.VBProject.References.Item("Outlook")) = False Then
            ThisWorkbook.VBProject.References.Remove ThisWorkbook.VBProject.References.Item("Outlook")
        End If
                
        ActiveWorkbook.Save
        
        Set wdAppClass = Nothing
        Set wdAppClass.wdApp = Nothing
        'Set wdApp = Nothing
        Set wdDoc = Nothing
        Set button = Nothing
        
    End Sub
    'Module1'
    '-------'
    Option Explicit
    
    
    Public wdAppClass As New wdAppClass
    Public wdDoc As Word.Document
    Public button As Object
    Public row As Integer
    Public column As Integer
    
    
    Public Sub AutoOpen()
    
    
        Set wdAppClass.wdApp = Word.Application
        
    End Sub
    
    
    Sub Button_Click()
        
        Set wdAppClass.wdApp = Word.Application
        Set button = ActiveSheet.Buttons(Application.Caller)
        
        With button.TopLeftCell
            row = .row
            column = .column
        End With
        
        Set wdAppClass.wdApp = CreateObject("Word.Application")
        Set wdDoc = wdAppClass.wdApp.Documents.Add(ThisWorkbook.Path & "\Sales Call Report.dotm")
            
        With wdDoc
            
            .Fields(3).Code.Text = " Quote " & """" & ActiveSheet.Range("A" & row & "").Text & """" & " "
            .Fields(4).Code.Text = " Quote " & """" & ActiveSheet.Range("B" & row & "").Text & """" & " "
            .Fields(5).Code.Text = " Quote " & """" & ActiveSheet.Range("C" & row & "").Text & """" & " "
            .Fields(6).Code.Text = " Quote " & """" & ActiveSheet.Range("D" & row & "").Text & """" & " "
            .Fields(7).Code.Text = " Quote " & """" & ActiveSheet.Range("E" & row & "").Text & """" & " "
            .Fields(8).Code.Text = " Quote " & """" & ActiveSheet.Range("H" & row & "").Text & """" & " "
            .Fields(9).Code.Text = " Quote " & """" & ActiveSheet.Range("J" & row & "").Text & """" & " "
            
            .Shapes(1).TextFrame.TextRange.Text = ActiveSheet.Range("F" & row & "").Text
            .Shapes(2).TextFrame.TextRange.Text = ActiveSheet.Range("K" & row & "").Text
            '.Shapes(3).TextFrame.TextRange.Text = ActiveSheet.Range("M" & row & "").Text
            
        End With
         
        wdAppClass.wdApp.Selection.WholeStory
        wdAppClass.wdApp.Selection.Fields.Update
        wdAppClass.wdApp.Selection.Collapse
        
        wdAppClass.wdApp.Visible = True
        wdAppClass.wdApp.ActiveWindow.WindowState = wdWindowStateMaximize
        wdAppClass.wdApp.ActiveWindow.SetFocus
        wdAppClass.wdApp.Activate
                
    End Sub
    
    
    Sub Set_Reminder()
        
        Dim olApp As Outlook.Application
        Dim olAppt As Outlook.AppointmentItem
                        
        If button Is Nothing Then
            Set button = ActiveSheet.Buttons(Application.Caller)
        End If
        
        With button.TopLeftCell
            row = .row
            column = .column
        End With
        
        On Error Resume Next
        
        Set olApp = GetObject("", "Outlook.Application")
        On Error GoTo 0
        If olApp Is Nothing Then
            On Error Resume Next
            Set olApp = CreateObject("Outlook.Application")
            On Error GoTo 0
            If olApp Is Nothing Then
                MsgBox "Outlook is not available!"
                Exit Sub
            End If
        End If
                  
        Set olAppt = olApp.CreateItem(olAppointmentItem)
            
        With olAppt
            .Start = ThisWorkbook.ActiveSheet.Range("M" & row & "").Value & Chr(32) & Time()
            .Duration = 15
            .Subject = "Call " & ThisWorkbook.ActiveSheet.Range("D" & row & "").Value
            .Location = ThisWorkbook.ActiveSheet.Range("A" & row & "").Value & Chr(44) & Chr(32) & ThisWorkbook.ActiveSheet.Range("C" & row & "").Value
            .Save
            .Display
        End With
       
        Set olApp = Nothing
        Set olAppt = Nothing
        Set button = Nothing
        
    End Sub
    'wdAppClass'
    '----------'
    Option Explicit
    
    
    Public WithEvents wdApp As Word.Application
    
    
    Private Sub wdApp_DocumentBeforeClose(ByVal Doc As Document, Cancel As Boolean)
        
        Dim datecheck As Boolean
            
        ThisWorkbook.ActiveSheet.Range("F" & row & "").Value = wdDoc.Shapes(1).TextFrame.TextRange.Text
        ThisWorkbook.ActiveSheet.Range("K" & row & "").Value = wdDoc.Shapes(2).TextFrame.TextRange.Text
        
        datecheck = IsDate(wdDoc.Shapes(3).TextFrame.TextRange.Text)
        
        If datecheck = True Then
            ThisWorkbook.ActiveSheet.Range("M" & row & "").Value = wdDoc.Shapes(3).TextFrame.TextRange.Text
            Set_Reminder
        End If
        
        wdAppClass.wdApp.Quit
        wdApp.Quit
        wdDoc.Close
        
        Set wdAppClass = Nothing
        Set wdAppClass.wdApp = Nothing
        Set wdApp = Nothing
        Set wdDoc = Nothing
        Set button = Nothing
        
    End Sub
    Last edited by tanim_84; 10-26-2017 at 02:11 AM.

  5. #5
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,662
    Location
    Might be easier if you just posted a workbook with the code and any data needed to show the issue
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    VBAX Newbie
    Joined
    Oct 2017
    Posts
    4
    Location
    Thank you samT and Paul_Hossler for taking the time to respond to my question. I greatly appreciate it. I have been able to solve my problem using a solution by a user on another forum.

    His ingenious solution was getting rid of the classmodule completely and hiding the code from it in a hidden worksheet which worked an absolute charm! This was due to the fact that modules and classmodules are compiled first which was the root of the problem. I also learned from this exercise that worksheets and even the workbook itself cannot 'see' each others code including variables and subs even when public declarations are made.

    Thank you again guys!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •