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

[tanim_84]

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

[SamT]

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(...

[tanim_84]

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!

[tanim_84]

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

[Paul_Hossler]

Might be easier if you just posted a workbook with the code and any data needed to show the issue

[tanim_84]

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!