VBA code doesn't work: Is this from a bad install?

[ChrisOswald]

This is a pretty general question: the code below actually resides in an Access 2003 db. (in case you're wondering why I'm passing a worksheet as an Excel.worksheet)

Sub TestIt()
    Dim wb As Excel.Workbook
    Dim WS As Excel.Worksheet
    Dim appXL As Excel.Application
    Dim LR As Long
 
    Set appXL = New Excel.Application
    Set wb = appXL.Workbooks.Open("C:\Temp.xls")
    Set WS = wb.Worksheets("Corporate_Request_tbl")
 
    LR = GetLastRow(WS)
 
    MsgBox (LR)
 
    wb.Close
    Set WS = Nothing
    Set wb = Nothing
    appXL.Quit
    Set appXL = Nothing
 
 
End Sub
Function GetLastRow(WS As Excel.Worksheet) As Long
    Dim cloc As Excel.Range
    Dim LastRow As Long
    Set cloc = WS.Cells.Find(what:="*", After:=WS.Cells(1, 1), searchorder:=xlByRows, SearchDirection:=xlPrevious)
    If cloc Is Nothing Then
        LastRow = 0
    Else
        LastRow = cloc.Row
    End If
    Set cloc = Nothing
    GetLastRow = LastRow
End Function

On only one persons computer, the code is breaking on the Set cloc = ws.cells.find ... line with an error 13 'Type Mismatch'. Everyone else using this (or similar routines) isn't having a problem.

Some background: A while back (a few weeks ago), the person had some problems with their email. The IT help desk decided the "fix" was to upgrade their Outlook to 2007 (leaving MS office 2003 for the rest of the office suite), found out it didn't work, and then reinstalled Outlook 2003.

Playing around in the immediate window in debug mode, ?ws.name returns correctly, ?ws.cells(2,2) returns the value in cell B2, etc, but ?ws.usedrange.rows.count (for example) gives another error 13.

So, do you think that it's worth getting IT to do a full reinstall, or is it something silly on my part?

(BTW, the C:\Temp.xls file does exist)

 

[Norie]

Have you tried not using the named arguments for the find?

 

[ChrisOswald]

No, not yet. I do know (from the immediate window) that the integer values for the xlConstants are being passed. I checked that out after reading SHG's post at 2:12.

It just seems like that parts of the range object aren't there and excel is throwing random errors. 'Type mismatch' from ws.usedrange.rows.count? (found via debug.print in the immediate window), but no error for ?ws.name

The user is gone for the day, and I'm just about to get out of here. If you could help me mock up some sort of checklist to look at tomorrow, I'll walk through it. I can't think of anything other than a bad install, but I really don't want to have office reinstalled unless it's a fix.

 

[Norie]

That was just off the top of my head, I think I had trouble in the past using Excel VBA outwith Excel.

Can't remember where but the answer, well it worked anyway, was to not use named arguments.

Of course that made life difficult but it worked, just needed to watch those commas.

 

[shg]

That would be curious indeed.

 

[xenou]

Personally I would late bind any code run from Access. Though it clearly works (I tested your code too - no problem here), I think the simplest way to get down to bare essentials is to list the arguments without parameter names and using integer values rather than named constants.