Change Early Binding to Late Binding issues

[Inovo]

Hi, I have several old macros that worked for years when Excel was installed on my machine.
They are in MS Project 2010 and Open and Excel and move data from Project to Excel sheets.

The company moved me to Office Online, and the macros stopped working because the Reference files no longer exist.
The macro worked great, but code is very messy from years of patches...
So I am looking for help on the types of issues listed below...

It seems changing from Early Binding to Late Binding will fix the problem.
I have changed statements like as follows:

Dim xlApp As Excel.Application   'Early Binding Method

'changed to:

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

This seems to work, the macro opens Excel...

I was using statements like the following to set variable names:

Dim xlRange As Excel.Range 
Dim xlSheet As Excel.Worksheet
Dim xlCols As Excel.Range

These statements don't work now...
Below are examples of how I use these variables:

Using xlRange as an example, I have code like:

Set xlRange = xlSheet.Range("a1:" & "U" & numrows)
With xlRange
    With .Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = vbBlack
    End With
    With .Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = vbBlack
    End With
End With

Using xlSheet as an example, I have code like:

With pxlsheet
    .Rows(1).Font.Size = 11
    .Rows(1).Font.Bold = True
    .Cells(1, 1).Value = "ID"
    .Cells(1, 2).Value = "UID"
    For i = 1 To 21
        .Cells(1, i).Interior.Color = RGB(135, 206, 250)
        .Cells(1, i).HorizontalAlignment = xlCenter
    Next i
End With

Using xlCols as an example, I have code like:

Set xlCols = .Columns(1) 'ID
    With xlCols
        .ColumnWidth = 5
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .Locked = True
    End With

Can you give me some ideas on what I need to fix?
Thanks for any help provided.

 

[Anthony47]

For example

Sub ttest()

'Dim xlRange As Excel.Range
'Dim xlSheet As Excel.Worksheet
'Dim xlCols As Excel.Range

Dim xlRange As Object
Dim xlSheet As Object
Dim xlCols As Object
Dim NumRows As Long
Dim xlApp As Object


Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

xlApp.workbooks.Open "D:\DDocs\MyExcelFile.xlsx", 0, True             'updatelinks=0, ReadOnly=true

Set xlSheet = xlApp.activeworkbook.sheets("SheetName")
NumRows = xlSheet.Cells(xlSheet.Rows.Count, 1).End(-4162).Row               '-4162  is constant xlUp

Set xlRange = xlSheet.Range("A1:U" & NumRows)
With xlRange
'..
'..
'..
End With


xlApp.activeworkbook.Close False
xlApp.Quit
Set xlApp = Nothing

End Sub

You see that without a reference to the XL Object library you lose the names of the parametres and the enumeration values; command parametres have to be inserted in sequence and enumeration symbols have to be replaced by their value (eg: -4162 instead than xlUp)
To get the enumeration value, open the immediate window in Excel vba (Menu /View /Immediate window, or just Contr-g) and type the command

?xlup

that will return the value for the typed attribute

Bye

 

[RoryA]

I’m confused. You can’t run VBA in the online versions of Office.

 

[Anthony47]

I’m confused. You can’t run VBA in the online versions of Office

That's also my understanding; but since Inovo wrote "This seems to work, the macro opens Excel... " I went on with my suggestions about late binding
Let's wait for a feedback...

 

[MARK858]

I’m confused. You can’t run VBA in the online versions of Office

Known some people call 365 "Online" (although it isn't Office Online), maybe that is what the OP means

 

[Anthony47]

the macros stopped working because the Reference files no longer exist

some people call 365 "Online" (although it isn't Office Online), maybe that is what the OP means

Mark's guess makes sense!

If that is the case then a short fix is to modify in the exisisting VbaProjects the library reference from the current Microsoft Excel xy.z Object Library to Microsoft Excel 16.0 Object Library (but in the long run I should recommand migrating to late bind)

Bye

 

[Inovo]

Hi Everyone, thanks for the help.
Sorry for the slow response, (weekend + Monday holiday)...

It appears I have caused some confusion in my attempt to keep the post as simple as possible
I hope this clarifies:

1. I wrote the VBA code years ago in MS Project 2010
I did not post the entire code because it is over a thousand lines of what I call "spaghetti code".
It's really a mess that evolved over years by trial and error.

2. The VBA Macro is launched in MS Project.
The macro then opens Excel and creates customized spreadsheets using the data from a MS Project schedule read task-by-task.
It reads data from the MS Project file, then writes and formats the data into an Excel spreadsheet.

Once Excel was removed from my laptop, I could no longer set the reference to the Object Library to an Excel Object Library.
I now have MS Excel for Microsoft 365 MSO - that's what I was meaning by Microsoft Online.
All of my Office apps that were originally installed on the laptop are now delivered by the Online 365 Office.

Except I still use MS Project 2010 as a stand-alone application on the laptop because all later versions have too many bugs...

Athony47, thanks for the informative reply, especially about the enumeration values...

It appears this is going to be a bit difficult for me to run down all of the changes, so I will take them one-by-one...
Other than the enumeration issue, is there anything else I should watch for?

Kind Regards, Roy.

 

[Anthony47]

And you don't have a Microsoft Excel 16.0 Object Library that you can reference (via Menu /Tools /References), in place of the current xx.z?