On Error not working as I thought

[RobN]

I have a procedure, shown below (in part), where I want to jump to another
part of the code when the Directory is invalid. ie I want to be able to run
the code on 3 different computers without having to amend the code each
time. Maybe there's a better way to do this, but I would have thought that
what I have done should work! ie in the part of the code that's headed
'work, if there is no such dir, then the code jumps to the line OFFICE:
without a hitch, BUT even though there is no valid dir in the OFFICE: part
of the code, I get a run-time error 76 "Path not Found" at the line "ChDir
dir", and it doesn't jump to the next part of the code (ie HOME.

Why doesn't the On Error command work at that point?
If there is a better way to do this please advise.

Rob

Dim drive As String
Dim dir As String

'work
WORK:
On Error GoTo OFFICE
dir = "H:\SupporterStats Construction\"
drive = "H"
ChDir dir
GoTo Proceed

'Office
OFFICE:
On Error GoTo HOME
dir = "K:\2007\common\Supporter Stats\"
drive = "K"
ChDir dir
GoTo Proceed

'home
HOME:
dir = "C:\Documents and Settings\USER\My
Documents\Fola\UnderConstruction\"
drive = "C"
ChDir dir

Proceed:

 

[Dave Peterson]

With only two folders to check, I'd use:

Option Explicit
Sub testme01()

Dim TestStr As String
Dim myDir As String
Dim myHomeFolder As String
Dim myWorkFolder As String
Dim UseThisFolder As String

'needs the final \
myHomeFolder = "K:\2007\common\Supporter Stats\"
myWorkFolder = "H:\SupporterStats Construction\"

UseThisFolder = ""
TestStr = ""
On Error Resume Next
TestStr = dir(myWorkFolder & "nul")
On Error GoTo 0

If TestStr <> "" Then
UseThisFolder = myWorkFolder
Else
'not found, check for home
On Error Resume Next
TestStr = dir(myHomeFolder & "nul")
On Error GoTo 0

If TestStr <> "" Then
UseThisFolder = myHomeFolder
End If
End If

If UseThisFolder = "" Then
MsgBox "both locations not available"
Else
ChDrive UseThisFolder
ChDir UseThisFolder
End If

End Sub

=====
If you start having to add more locations, then I'd build an array of those
folder names and loop until I found a good location.

 

[RobN]

Thanks Dave,

Very helpful!

Any idea why my code wouldn't work?

Rob

 

[Dave Peterson]

You have to be more careful when you're in the middle of the error handling.

Check Chip Pearson's site:
http://www.cpearson.com/excel/ErrorHandling.htm

As a personal preference, if I know that a line I use may cause an error, I'll
use:

on error resume next
line that may cause an error
'check the error
if err.number <> 0 then
'there was an error
err.clear
end if
on error goto 0

Or if I can, I'll use a object:

set testwks = nothing
on error resume next
set testwks = worksheets("name that may not exist")
on error goto 0

if testwks is nothing then
'not there
else
'it is there
end if

Or use a string (like in the other code).

But these are with specific lines that may cause errors--it isn't for generic
error handling. (See Chip's site for that.)

ps. Without the "on error goto ..." line, using Dir as a variable would have
been seen as an error, too.

It's usually (always!) best not to use VBA's keywords for your variable names.

 

[RobN]

A very helpful comprehensive answer.....Thanks!

Rob