0

Problem

Our office PC's all run Office 365 ProPlus and are progressively getting updated to version 1908 (Build 11929.20562). As PC's get updated, many of our VBA macros are failing with various Object errors. We are getting the errors in two products so far, Outlook and WRQ Reflections (terminal emulator).

Examples

Sub master_dailyWhispirSMS_P2(myExcel As Excel.Application, myWorkbook As Excel.Workbook, worksheetName As String, testingMode As Boolean)

While Trim(myWorkbook.Worksheets(worksheetName).Cells(rowNum, 1).Value) <> ""

The above code resulted in a Run-time error '91' Object variable or With block variable not set

Dim myExcel As Excel.Application    
Dim myWorkbook As Excel.Workbook
Set myExcel = GetObject(, "Excel.Application")
myExcel.Workbooks.Open (folderPath & "\" & fileName)
Set myWorkbook = myExcel.Workbooks(fileName)
myWorkbook.Activate

The above code resulted in the error Compile error: Method or data member not found. The compile error was on MyWorkbook.Activate.

Findings

The common theme so far is that we are using early binding and using objects from the Microsoft Excel Object Library.

I tried changing to using late binding and it got me past one issue, but there are way too many routines that might need updating.

From within the VBA Editor, on the Tools menu, under References, the Microsoft Excel 16.0 Object Library is showing as selected. I tried removing the reference and then adding it back again and this seems to have solved the issues. This worked on my colleagues PC as well.

Ideas/Solution Sought

This code used to work fine prior to the Office update. Other than getting each individual user to perform the remove/add, I'm trying to understand what has gone wrong and if there are any other alternatives to fixing this please.

Please note I am still very 'green' with my understanding of early and late binding, but I think somehow the references to the object libraries are either corrupt or just not pointing to the correct DLL file after the update. This would explain why the various methods are not working.

ACCtionMan
  • 511
  • 1
  • 3
  • 12
  • `Dim myExcel As Excel.Application ` is early binding. If you want to use late binding so that it works in all versions, use this instead - `Dim myExcel As Object` - You will want to remove the reference in your project as well. – braX Feb 05 '20 at 05:50
  • On which line did you get the compile error? – Zev Spitz Feb 05 '20 at 05:55
  • 2
    Going from a subscription to a MSI install? Office 365 has probably updated beyond what's available in Office 2019. Office 2016 and 2019 are "shapshots", while Office 365 evolves continually. O2019 will *not* have the same content as Office 365. Going "backwards" rather than "forwards" has always been a problem/issue with VBA. References to newer libraries don't run on older installations. The rule has always been: develop on the oldest version the code should run on. I don't think there will be a simple solution. Code to remove and re-attach the reference might work. – Cindy Meister Feb 05 '20 at 06:14
  • Thanks for the responses. Made a couple of edits. Sorry, I wasn't sure what the latest version of Office 365 is referred to. I have put the actual version number/build number. Also added which line got the compile error. Just trying to find out how I might implement @Cindy Meister's suggestion about creating code to remove/re-attach. Just trying to find out what Methods and Properties are available within the References object. – ACCtionMan Feb 05 '20 at 21:00
  • 1
    See https://stackoverflow.com/a/51313215/9439330 and Related section (right side of page, after jobs, before Hot Network Questions). Office365 version 1908 may be equivalent Office 2019. All office libs changed version. – ComputerVersteher Feb 05 '20 at 23:10

1 Answers1

1

Whilst I was not able to figure out what caused the issue, I was able to resolve the issue by removing each Reference and then adding it back again. Luckily I found out that I was able to change a master file and have our IT guys push it out to all staff as I was unable to get VBA to remove and add the references within the correct Project.

I was able to run the macro in each Project individually and compared the Reference.Name, Reference.Description, Reference.fullpath and Reference.GUID. All properties were the same so don't know why the references 'broke'.

Anyway, posting this answer just to confirm that removing and adding back the references resolved the issue for me.

This was the code I used to compare the References between Projects (before and after adding/removing References).

Sub checkLibraryReference()

Dim vbProj As Object
Dim refChk As Object
Set vbProj = Session.VBProject
Set refChk = vbProj.References
Dim printLine As String

For Each refCheck In vbProj.References
printLine = refCheck.Name & "," & _
     refCheck.Description & "," & _
     refCheck.major & "," & _
     refCheck.minor & "," & _
     refCheck.fullpath & "," & _
     refCheck.GUID & Chr(13)
Debug.Print printLine

Next refCheck

End Sub

I grabbed the output from the Immediate Window, pasted into Excel and used Text-to-Columns. I then just did a simple compare.

I struggled to find the documentation on the References Object, but eventually found it in the Access VBA Reference here:

Access VBA Reference Library

ACCtionMan
  • 511
  • 1
  • 3
  • 12