Results 1 to 3 of 3

Workbook_open not firing due to compile error in class module (missing library reference)

Threaded View

Alex230274 Workbook_open not firing due... 09-22-2021, 12:11 PM
Alex230274 Re: Workbook_open not firing... 09-23-2021, 01:03 PM
Alex230274 Re: Workbook_open not firing... 09-24-2021, 01:24 PM
  1. #1
    Registered User
    Join Date
    03-20-2020
    Location
    Farmington, CT, USA
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    3

    Workbook_open not firing due to compile error in class module (missing library reference)

    Hi all,
    after hours of research in the web I finally need to ask here for your help: Following problem: I try to add a reference (Outlook library) in the Private Sub Workbook_Open() event (yes, it's in ThisWorkbook) since I need early binding for getting Outlook events later in different places. Once I open my workbook I get a compile error "user-defined type not defined" in my class module here --> Private olApp As New Outlook.Application. I understand this since the Outlook reference is not yet set and this is what I exactly try to do in the Workbook_Open event. How does Excel work here? Does it first try to compile the project BEFORE it executes code in the Workbook_Open event (enableEvents is set to True)?
    Once I run the Sub Workbook_open manually, the reference is set without any problems.
    Thanks in advance for your help.

    Trying to add reference for MS Outlook Object Library when Workbook opens:

    Private Sub Workbook_Open()
        Dim strGUID As String, theRef As Variant, i As Long
    'Update the GUID you need below.
        strGUID = "{00062FFF-0000-0000-C000-000000000046}" 'MS Outlook x.x Object Library
    
    'Set to continue in case of error
        On Error Resume Next
    
    'Remove any missing references
        For i = thisWorkbook.VBProject.References.Count To 1 Step -1
            Set theRef = thisWorkbook.VBProject.References.Item(i)
            If theRef.isbroken = True Then
                thisWorkbook.VBProject.References.Remove theRef
            End If
        Next i
    
    'Clear any errors so that error trapping for GUID additions can be evaluated
        Err.Clear
    
    'Add the reference
        thisWorkbook.VBProject.References.AddFromGuid _
        GUID:=strGUID, Major:=1, Minor:=0
    
    'If an error was encountered, inform the user
        Select Case Err.Number
        Case Is = 32813
             'Reference already in use.  No action necessary
             MsgBox "Reference already in use"
        Case Is = vbNullString
             'Reference added without issue
             MsgBox "Reference added"
        Case Else
             'An unknown error was encountered, so alert the user
            MsgBox "A problem was encountered trying to" & vbNewLine _
            & "add or remove a reference in this file" & vbNewLine & "Please check the " _
            & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
        End Select
        On Error GoTo 0
    End Sub
    Here the Class Module "ClsAppoint" in which the compile error occurs once I open the workbook (red line):
    Option Explicit
    Private olApp As New Outlook.Application 
    Public WithEvents Appoint As Outlook.AppointmentItem
    Private invite_sent As Boolean
    
    Private Sub Class_Initialize()
        Set Appoint = olApp.CreateItem(olAppointmentItem)
        invite_sent = False
    End Sub
    
    Private Sub Appoint_Close(Cancel As Boolean)
        Dim CloseAndDiscard As VbMsgBoxResult
        If invite_sent Then Exit Sub
        Appoint.GetInspector.WindowState = olMinimized
        AppActivate Application.Caption
        
        CloseAndDiscard = MsgBox("Close and discard?", vbYesNo + vbQuestion)
        
        If CloseAndDiscard = vbYes Then
            Appoint.Close olDiscard
            Set invite = Nothing
        Else
            Cancel = True
            Appoint.Display
            Exit Sub
        End If
        
    End Sub
    
    Private Sub Appoint_Send(Cancel As Boolean)
        invite_sent = True
        invite.Appoint.SaveAs save_email_folder & invite_name
        Set invite = Nothing
    End Sub
    Last edited by Alex230274; 09-23-2021 at 09:31 AM. Reason: Code added for better understanding

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Compile and Add UDF to a reference Library
    By Steveapa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-01-2015, 06:16 PM
  2. Compile error in hidden module: Module 1 when workbook opened on different machine!?
    By MathUKTeacher in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-06-2015, 04:38 PM
  3. compile error with “let” in class module
    By bsapaka in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-14-2014, 01:34 AM
  4. [SOLVED] Compile Error in Hidden Module and Compile Error: Can't find project or library
    By Taislin in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-10-2013, 07:03 PM
  5. [SOLVED] Make VBA Class Module Library
    By Mark Olsen in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-26-2006, 07:10 PM
  6. [SOLVED] Compile Error: Can't find project or library (missing Add-in)
    By Trefor in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 11-12-2005, 10:55 AM
  7. Compile error on "Right", missing Library
    By Sige in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-30-2005, 01:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1