Help with "Resume Next" statement 2

[mscallisto]

I'm trying to identify all Media type files (.avi .mov etc)

from any mapped drive on a file share e.g. (H 1st parameter in call on code line 16
and including its sub folders (True) 3rd parameter in call on code line 16

example: GetFilesInFolders "H:", "*.*", True, True

While cycling thru all the subfolders (code lines 51 - 54)
I get an error that sends me to (code lines 6 - 9)
I expected the Resume Next (on line 9) would bring me back to Next SubFolder (on line 54)
but instead it sends me to Line 16.

Even after reading the "Help on Resume Next" I don't understand why nor can I see a way to make it work.

1 Dim ErrorResult As String, Pathname As String
 2 Sub IsolateFileTypes()
 3 On Error GoTo MyProcedure_Error
 4 GoTo MyProcedure_Exit
 5 MyProcedure_Error:
 6   If Err.Number = 70 Then
 7     MsgBox ("You probably don't have permissions to access this folder ")
 8     ErrorResult = "Access Denied"
 9     Resume Next
10   Else
11     ErrorResult = "Some Other Reason"
12     Resume Next
13   End If
14 MyProcedure_Exit:
15 '                     startpoint, filter, search sub folders, first-time-switch
16     GetFilesInFolders "H:", "*.*", True, True
17     ActiveWorkbook.Save
18     MsgBox ("Done")
19 End Sub
20 Sub GetFilesInFolders(SourceFolderName As String, FileExtensions As String, IncludeSubFolders As Boolean, FirstTime As Boolean)
21 Dim FSO
22 Dim SourceFolder, SubFolder
23 Dim FileItem
24 Dim r As Long
25 Dim strFileName As String, strSearchItem As String, strVlookupResults As Variant
26 Dim intLastRow As Integer, intCounter As Integer
27 Dim strFiletype As Range

28 Set FSO = CreateObject("Scripting.FileSystemObject")
29 Set SourceFolder = FSO.GetFolder(SourceFolderName)

30 intLastRow = Worksheets("Extensions").UsedRange.SpecialCells(xlCellTypeLastCell).Row
31 Set strFiletype = Worksheets("Extensions").Range("A1:B" & intLastRow)
32 If FirstTime Then
33   intCounter = 4
34   FirstTime = False
35 End If

36 r = Range("A65536").End(xlUp).Row + 1
37 i = i + 1

38 For Each FileItem In SourceFolder.Files
39   strSearchItem = Right(FileItem.Name, 3)
40   strVlookupResults = Application.VLookup(strSearchItem, strFiletype, 2, False)
41   If (Not IsError(strVlookupResults)) Then
42     Worksheets("MediaFound").Cells(r, 2) = FileItem.Name
43     Worksheets("MediaFound").Cells(r, 3) = strVlookupResults
44     Cells(r, 1).Formula = FileItem.Path
45     Cells(r, 4).Formula = FileItem.Size
46     r = r + 1 ' next row number
47     intCounter = intCounter + 1
48   End If
49 Next FileItem
50 If IncludeSubFolders Then
51   For Each SubFolder In SourceFolder.SubFolders
52     Application.StatusBar = SubFolder
53     GetFilesInFolders SubFolder.Path, FileExtensions, True, False
54   Next SubFolder
55 End If
56 End Sub

 

[Andrzejek]

Since your error happens in Sub GetFilesInFolders, put your error handler MyProcedure_Error: into this Sub.

Have fun.

---- Andy

 

[fumei]

That would be best. You could do it in IsolateFileTypes by trapping errors more.

Walk it through.

The code lines 51 - 54 are in GetFilesInFolder, but the code starts in IsloateFiles. So start there.

Isolate executes. It starts with an On Error instruction. This, therefore, applies to the Sub Isolate. (I am using short names...) This is key.

Next line in Isolate is a GoTo. So, it goes to. This routes execution to GetFiles. But Isolate is not finished. GetFiles is a sub-routine.

GetFiles executes. It gets an error. As Andy points out, it has no error trapping routine, so it....stops and passes back an error to the calling routine, Isolate.

So. Isloate gets an error. What does it do? It executes its On Error instruction.

What does that do? You have given two choices. Either the error is 70, or Else (not 70). However, it does not matter what the error number is. Whether you have 70, or not, you have a Resume Next.

What exactly is Next? It is the NEXT executable line. Which is technically line 14, but from your point of view, line 16.
9 OR 12 Resume Next
13 End If (no execution)
14 MyProcedure_Exit: (this gets executed)
15 comment line
16 GetFilesInFolders "H:", "*.*", True, True

Either expand your error trapping in Isolate, or put error trapping in GetFiles.

faq219-2884

Gerry
My paintings and sculpture

 

[mscallisto]

Thanks Andy I moved the error handler to the sub where the error occurred and all is well.

Thanks Gerry for the in-depth explain; I did learn some things about error handling. You should contract with MS to upgrade their VBA Help!!

 

[fumei]

Help is not so bad, although yes, it could still be improved. It is MUCH better than it used to be.

Frankly, though while it is true the immediate cause can be stated as insufficient error trapping, the REAL issue is one of Scope.

A full understanding of Scope is not stressed enough.

You were thinking that the Scope of your error routine covered a sub-routine. It does not.

Which is why, if you have very complex error trapping, it can sometimes be a good idea to pass ALL errors to a dedicated error procedure. OR, a layered set of error procedures.

I teach in my Word VBA class that if it is very sophisticated and/or requires very tight error trapping - fully trapping user input text can be a nightmare - writing rock solid error trapping can take as much as 40 - 50% of the work on a project.

They never believe me.

And they always get ticked off when I run through their code and say:

"but what if I type "f@7k you boss", or "asg hkhshdk24" ? "

"hmmm, I left a space at the end of my input, and your userform died."

"hmm, and what exactly is helpful with a client name of "45-123"?

We all complain about bugs. IMO, the majority of bugs stem from insufficient, or erroneous, error trapping. There is a reason for this.

Other than the basics, rock solid error trapping is HARD. Much, much, much harder - IMO - than any original coding. But coding gets the glamour. People love to code. Most people hate to error trap.

I am guilty of this myself.

Me, I am far more impressed by software that handles things graciously when it is given wrong input, bad input, or stupid input. "Input" includes both user input, or input derived programmatically.

faq219-2884

Gerry
My paintings and sculpture

 

[mscallisto]

Thanks again Gerry

I agree with all you've written (very much)

I remember about 30 years ago I took my first Macro Assembly course and our last assignment was to write a 3D Tic Tac Toe game, took us forever!

The instructor asked each of us to start our programs then he visited us one by one to test.

His test method was to "massage" the keyboard with both hands and wait for the blue screen, after which he repeated to each of us...

"Your error handling needs some work"!!!

I've never forgotten that humiliation. LOL

Unfortunately not many people even try to conquer error handling; mine is a work in progress.

Thanks for the SCOPE assistance.

sam

 

[fumei]

His test method was to "massage" the keyboard with both hands "

Yes. When I was a beta tester, I did the same thing, or very similar.

One programmer wailed (when I made his application crash), "but no one will ever go Alt-F3, j, 1, s.

I looked him right in the eye and said: "....I just did."

Assembler....shudder.

faq219-2884

Gerry
My paintings and sculpture