Late Binding Problem

[RobV]

I have a Project VBA macro that creates an Excel file. When I use Early
Binding, there’s no problem. (I use Early Binding because the macro is
distributed to PCs with different versions of Project, Excel, etc.) When I
switch to Late Binding, the Sort command raises a run-time error: "Object
required", and it points to "s.Range" as the problem. I use s.Range
elsewhere in the macro with no problem. Is there something special about
using Range with Sort? Here is the relevant code extract:

Dim XL As Object
Set XL = CreateObject("Excel.Application")
<error handling if Excel already open>
XL.Workbooks.Add
BookNam = XL.ActiveWorkbook.Name
Set s = XL.Workbooks(BookNam).Worksheets(1)
<other logic>
s.Range("A" & SelectionStartRow & ":" & "G" & SelectionEndRow).Select
Selection.Sort Key1:=s.Range("C1"), _
Order1:=xlAscending, Key2:=Range("E1"), _
Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
xlSortNormal

PS the xl-parameters have been DIM'd as constants.

 

[Rod Gill]

Your Key2 parameter should be s.Range("E1"). Range("E1") on its own means
nothing to Project without the qualifier s.

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[RobV]

Thanks, Rod. I changed the key2 parameter as you suggested, but I get the
same error message: Run-time error 424 Object Required . Any other
suggestions would be greatly appreciated.

 

[Rod Gill]

The other thing is that Selection is not meaningful in Project (Project uses
ActiveSelection) so you need:

XL.Selection.Sort

Though as the range s already points to a cell in the area use:

s.Range("C1").sort

For safety I always add the Header:= parameter and say whether there is or
isn't a header row. Sometimes Excel may not correctly identity one!
--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[RobV]

Thanks, Rod. I changed the code to XL.Selection.Sort, and it runs cleanly.

PS Your book is great. Every Project VBA developer should own a copy.

 

[Rod Gill]

Ahh, another scholar, gentleman and good judge of whiskey. Thanks for your
feedback!!

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx