Macro aborting without error

[Asator]

I'm working on supporting a macro I wrote (in excel 2003) for another group. On all users computers (excel 2010) it works, except one. The macros (more than one) seem to stop execution without any errors.

For example, I have a command button that brings up application.GetOpenFilename, the user selects a file, and a For ... Next loop parses out the filename and path and puts them in separate cells.

strFilePath = Application.GetOpenFilename("Excel files (*.xls; *.xlsx),*.xls;*.xlsx") '("Excel files (*.xls, *.xlsx),*.xls;*.xlsx")
For i = Len(strFilePath) To 1 Step -1
    Debug.Print Mid(strFilePath, i, 1)
    If Mid(strFilePath, i, 1) = "\" Then
        Sheets("user form").Range("locrfi").Value = Left(strFilePath, i)
        Sheets("user form").Range("fnrfi").Value = Right(strFilePath, Len(strFilePath) - i)
        
        Exit For
    End If
Next i

I noticed that when he runs the macro, it populates the first cell, but not the 2nd. I added breaks in front of both lines. It paused after the first line, but when I try and go to the next, it doesn't pause on the next line. Its as though it completely quit the subroutine.

Another macro does a bunch of random stuff, opens the file that is referenced above, and is supposed to do a bunch more random stuff. It seems to quit right after the file opens.

'Open RFI, verify all required fields are filled out
Workbooks.Open (RFI)
Set wkbRFI = ActiveWorkbook
Set wsRFI = wkbRFI.Worksheets("RFI")

Again, I set pause breaks before both of those lines. The RFI file opens, but it doesn't break on the next line, the macro just seems to stop.

Like I said, there are other users with excel 2010 using the exact same file. What could be going wrong here? Should I have IT reinstall Office on thier workstation?

Halp!

 

[Norie]

I know it's probably not connected with the problem, but or the first part you could try using InStrRev to find the last occurence of '\'.

Then you can get the path and filename without looping.

strfilepath = Application.GetOpenFilename("Excel files (*.xls; *.xlsx),*.xls;*.xlsx")    '("Excel files (*.xls, *.xlsx),*.xls;*.xlsx")
 
    pos = InStrRev(strfilepath, "\")
 
    If pos > 0 Then
        Sheets("user form").Range("locrfi").Value = Left(strfilepath, pos)
        Sheets("user form").Range("fnrfi").Value = Mid(strfilepath, pos + 1)
    End If

For the 2nd part of code where the problem seems to be I think we need more information.

Is that all the code?

What's the value of RFI when it doesn't work?

Do you have On Error... statements?

 

[Asator]

Yeah, I had an error handler (which never triggered), and also tried on error goto 0. No errors popped up. The macro just stops in both cases.

On this particular user's computer, the macro never works, regardless of the value of RFI. The file opens, but it stops executing immediately after.

 

[Norie]

You had On Error...?

Did you remove it and this the problem is still happening without any error?

What code isn't being executed?

Again I don't know if it will help but you could try creating a reference to the workbook when you open it.

Set wkbRFI = Workbooks.Open(RFI)

Actually when I think about it that is probably a better idea than using ActiveWorkbook to get the workbook reference.

With ActiveWorkbook there is a possibility that you are referencing the wrong workbook.

It's probaby not a very big possibilty but it could happen.

 

[Asator]

No errors with On Error Goto 0.

And like I said, it happens with the other snippet of code that parses out the filename, too.

And if it was referencing the wrong workbook, it'd generate a subscript out of range error when it tried to set the worksheet object, right?

 

[Norie]

Sorry I'm confused.

Do you or don't you have On Error statements in the code?

If you do remove them and try the code again.

I also don't understand what you mean by it 'happens', I though the problem was nothing was happening?

As for subscript out of range, that error would be likely but there is a remote possibility it won't - there happens to be another workbook with the worksheet RFI.

Like I said very remore.

 

[Asator]

I have tried with both

On Error Goto errhandle

and

On Error Goto 0

The only workbook with a sheet named RFI is the file referenced by workbooks.open(RFI).

The code stops executing after the green lines above, the red lines never run. There are no errors, it just stops... i.e. in the first code snippet, it fills the first field, but not the 2nd, and in the second snippet, it opens the correct RFI file, but then ends.

 

[Norie]

Remove or comment out anything starting On Error.

If you still get no errors the only other thing I can think of is that the workbook getting opened has a Workbook Open event.

If the problem is only with one machine that's unlikely though

 

[Asator]

I'm gonna see about having IT reinstall office on his system. Thanks for the help, though.

 

[Norie]

Before you do that you should try removing all the On Error stuff..

Using it can be useful but it can also hide errors.