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)

 

[JackDanIce]

I'm not well versed with functions (prefer to code), but you do not appear to have an On Error Resume Next before your Set Cloc part in the function

I don't know if this is possible, but to me it makes sense the Set Cloc part fails IF it can't find your search item, as it interprets this as an error and thus throws up the error message and stopping the rest of the code executing so the If cloc Is Nothing Then would never be tested?

Ignore me if totally wrong!

 

[shg]

I think you need to explain where your constants come from, e.g., Excel.xlByRows, and expect if you used Option Explicit you would get a compile error.

EDIT: You don't need On Error Resume Next for a Find; if not found, the range will be set to Nothing.

 

[JackDanIce]

I'm sure I've had a problem before when a find couldn't find an item and didn't set to Nothing but made my code was set up some other way. Thanks anyway shg!

 

[shg]

If the right-side expression errors, no assignment is made:

    Dim r As Range
    
    Set r = Range("B1")
    
    With Range("A1:A10")
        .Value = "Bob"
        On Error Resume Next
        Set r = .SpecialCells(xlCellTypeBlanks)
    End With
    
    MsgBox r.Address

But Find explicitly returns Nothing if no match is found, and that's a valid assignment for a range.

 

[ChrisOswald]

shg;

Thanks for the response. However, your suggestion misses a couple of things (some that I only implied in my original post, some that I just found out after being prompted by your comment regarding XLConstants).

1) The declaring of where constants come from makes sense: I've done stuff with Attachmate's version of VB where I've had to use the numerical value of the constant. Unfortunately, on testing, the XLconstant returns the correct value with a reference set up, at least in Access (I'm assuming this is true for all Microsoft supported VBA).

2) Reducing the find method to include only what:="*", After:=ws.cells(1,1) still gives the error. A bit more evidence that it isn't caused by implicit referencing?

2) the function works perfectly well on many other people's computers (30+) for over a year's time. The function below is part of my "ModStdRoutines" code module for access projects. I guess I really should start late binding REAL SOON NOW^tm, but before today I'd probably have been thrown for a loop regarding the XLConstants.

3) a Debug.print of WS.usedrange.rows.count gives a '13' error also, but, so far as I can tell, the worksheet object is being passed (ws.name returns, ws.cells(23,4) returns correctly, etc.). I'll admit that the locals window info for a worksheet object gives be a headache whenever I try to read it, though...

4) The posted code runs fine from an Excel module on my computer (and presumably most peoples), but throws the error on the users computer. Ok, it's kinda goofy to instantiate an invisible Excel from a visible Excel, but...

5) Believe it or not, I've been burned before by not using Option Explicit. It's there in the production and test code both. I just generally leave it out of posts.

6) (possible coincidence). It stopped working after there was some goofy install OL2007, reinstall OL2003 type of thing. I could swear that I read somewhere that it's a bad idea to have different versions of office suite software installed on the same computer. (ie, running OL2007 and XL2003 is not a good idea)

7) Not that this came up, but I'm fairly confident that there isn't another instance of excel running that could cause a problem with a hanging object, although this would be very strange as to why it only doesn't work on the one users computer.
In case you're wondering why I brought this up, using
after:=[a1]
(implicit reference) as part of the find methods input can cause a hanging object. I found that one out the hard way. (IIRC, it'll cause this problem the second time that the function is called)

This problem is occuring on someone else's computer, so it's not like I'm getting a lot of time to find out exactly what's going on...

 

[shg]

Maybe check what references they have set.

And late binding would be good if people are running different versions of Office.

(ie, running OL2007 and XL2003 is not a good idea)

I've had that combination for three years without difficulty.

 

[ChrisOswald]

First thing I did was check references. They're set correctly.

As for late binding, the excel macros I've got out there are all .xla's, which (in testing) seems to handle 2003 --> 2007 conversions quite well. At least that's my excuse to keep putting it off. On the other hand, the Access stuff I've done...

The MS office upgrade my company is due for is supposed to be done a whole department at a time, so I'm not going to have too much of a headache. Or, at least, not one strung out over too long of a time period. At worst, it's job security, right? Right?

Quote:
<TABLE border=0 cellSpacing=0 cellPadding=6 width="100%"><TBODY><TR><TD style="BORDER-BOTTOM: 1px inset; BORDER-LEFT: 1px inset; BORDER-TOP: 1px inset; BORDER-RIGHT: 1px inset" class=alt2>(ie, running OL2007 and XL2003 is not a good idea) </TD></TR></TBODY></TABLE>
I've had that combination for three years without difficulty.

Well, there's that hope for good luck (i.e., I can blame this on somebody else)

 

[Norie]

Did you check for any references marked MISSING?

By the way, I don't see UsedRange anywhere in the code you posted.

Are you only using that for some sort of debugging?

Also have you tried some other method that doesn't involve Find to get the last row?

Perhaps the simple End(xlUp).Row?

 

[ChrisOswald]

Yes, there are no missing references. If there were, I don't think the code could even get into the function call. I'm pretty sure it wouldn't compile in the first place.

The posted code has the same user specific results if ran from an excel workbook (with an excel 11.0 reference selection verified) -- I can't see how early binding Excel within Excel can be a good practice, but it works on my computer and not the users computer.

No, the posted code doesn't have UsedRange anywhere within it. I was goofing around in the immediate window to see what works. I'm pretty sure the user's gone for the day now. Now that I think of it, I should probably check out tomorrow that Excel VBA is or isn't completely broken and test out something like the below without all of the Excel.* type stuff in it.

While I could, and have considered, using just End(XLup).row, when I started developing Excel reports from Access I made the wonderful mistake of choosing a column without required values to key up on (Solution: use a key field). This was my original impetus to find a more robust GetLastRow function.

What I'm really concerned about are: "what other things are going to break like this" and "is this a 'contagious' problem", so I'd really like to figure out the root cause.