Subscript out of range - what subscript?

[mintjulep]

I have the following function in an Autocad module.

Public Function LastRow(ByVal f As Object) As Long
  LastRow = f.activesheet.cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
End Function

The Object f is an Excel Workbook.

In the Autocad VBA immediate window I can create a Excel application and open a workbook.

Then

? LastRow(myxlbook)

gives a "Subscript out of range" error.

What subscript?

While trying to figure this out, I did:

Public Function LastRow(ByVal f As Object) As Long
Debug.Print f.activesheet.Name
  'LastRow = f.activesheet.cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
End Function

In the immediate window:

? LastRow(myxlbook)
Sheet1
0

So somehow this is returning two answers, although I don't understand where the 0 is coming from or what it is.

 

[PHV]

where the 0 is coming from
This is the value returned by the function

Subscript out of range
Seems like the function doesn't know the excel constants ...
LastRow = f.activesheet.cells.Find(What:="*", SearchDirection:=[!]2[/!], SearchOrder:=[!]1[/!]).Row

Tip: use the Option Explicit instruction.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[emartinho]

Just to throw some light on why VBA doesn't recognize the Excel constants.

mintjulep, you're probably using late binding (i.e. set myxlbook = create.object("excel.application")), right?
When you use this type of binding, VB doesn't have access to the Excel built-in constants.

Also, DEFINITELY follow PHV's advice and use Option Explicit, you would have gotten an immediate error in the IDE on using xlPrevious and xlByRows.

Cheers,
-Elio

 

[SkipVought]

Also I would NOT use ActiveSheet or Active anything, that ASSUMES a specific object. Make it Explicit.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[emartinho]

The more Explicit, the better!

(Sorry, couldn't resist!)