VBA Macro Code Skips Call Statement for Subroutine

[Mavila]

I have a macro with several userforms. In one of the userforms, there is a subroutine that loops through each item in an array. Each item is the path/name of a document. The loop Calls a subroutine that opens the file, copies its contents and does some other stuff. The loop continues for each file name item in the array like so.

I'm experiencing some baffling behavior. The macro project is inside a Word document. When I first open the Word document, the macro runs just as intended. If I immediately run it again after completion, I run into a strange behavior where the code, when it runs through this loop, it Calls the subroutine on the first pass through the code, but it ignores the Call on each subsequent pass through the loop. It treats the Call statement as just another line of code - it doesn't send the code into the subroutine. It's like it skips that line of code or something. If I step through it, the Call statement line is highlighted yellow and then it goes to the very next line of code immediately below the Call statement line (rather than going to the subroutine).

If I press the "Reset" button in the Visual Basic Project Editor window, then it will run perfect again (or, if I close the document, reopen it and run the macro). It's as if there is some persisting condition or state that causes this, but I cannot imagine what that might be.

I'm going to post the code in my next post.

[Mavila]

This the Sub that loops through the items in the array and calls the other subroutine:

Private Sub CompileElements()
'Loop through each Program Element in the array
For Each lArr In ElementsArray
If lArr = "" Then
Exit Sub
End If

'This sub just opens the file, copies it and dervices the chapter name variable from the document name minues the extension
Call Open_and_Copy_The_File

'Activate the compilation document
Documents(MyPlansPath & "\" & ProjectNm & "\" & ProjectNm & " Compilation.docx").Activate

'Select the last paragraph in the document
ActiveDocument.Paragraphs(ActiveDocument.Paragraphs.Count).Range.Select
Selection.End = Selection.End - 1 'Move the cursor from the para mark to the space immediately to the left of the para mark
Selection.TypeText Chr(13) 'add a new paragraph (because adding section break with only 1 para puts section break on previous page
Selection.InsertBreak (2) 'SECTION BREAK NEXT PAGE type = 2 This creates another page and moves insertion point to this page at left of first para
Selection.Bookmarks.Add ("InsertionPoint")
Selection.PasteAndFormat (wdFormatOriginalFormatting)
'Configure the header on page one of the chapter and the footers
Call Work_With_Headers_and_Footers

Call Turn_Off_Links_To_Previous
Call TableOfContents

ChptrNum = ChptrNum + 1

Next lArr
Set lArr = Nothing
End Sub

This is the called Subroutine:

Private Sub Open_and_Copy_The_File()
'This sub opens the chapter document, copies the contents, determines if it is > one page,
'closes the document and then gets the name of the chapter from the doc name (chopping off the extension)
If lArr <> "" Then
MyFile = lArr & ".doc"
Else
Exit Sub
End If
'In case the file extension is incorrect (doc vs docx)
If FileFolderExists(MyFile) Then
'do nothing cause it's a .docx extension
Else
'Add the x to the extension
MyFile = MyFile & "x"
End If

'Open the file - the "DocOpen" function tests to see if it is already open
'If DocOpen = True Then
'Do nothing since the document is already open
'ElseIf DocOpen = False Then
Documents.Open FileName:=MyFile
Documents(MyFile).Activate
'End If

If ActiveDocument.Name = ProjectNm & " Compilation.docx" Then
MsgBox "Mistake!"
Stop
End If

CurrentDoc = ActiveDocument.Name
Documents(CurrentDoc).Activate
ChptrNm = ActiveDocument.Name

'Work with bookmarks here because they may change (duplicates) after the chapter is copied and pasted
If ActiveDocument.Bookmarks.Exists("UserCoNm") = True Then
ActiveDocument.Bookmarks("UserCoNm").Range = UserCoNm
End If
If ActiveDocument.Bookmarks.Exists("PolicyPolicyNm") Then
ActiveDocument.Bookmarks("PolicySigNm").Range = UserPolicyNm
End If
If ActiveDocument.Bookmarks.Exists("PolicyPolicyTitle") Then
ActiveDocument.Bookmarks("PolicySigTitle").Range = UserPolicyNmTitle
End If

Selection.WholeStory
Selection.Copy
'Determine if the document is more than one page
If ActiveDocument.BuiltInDocumentProperties(wdPropertyPages) = 1 Then
OnePageDoc = True
Else
OnePageDoc = False
End If
If ActiveDocument.Name = ProjectNm & " Compilation.docx" Then
MsgBox "Mistake!"
Stop
End If
Documents(CurrentDoc).Close savechanges:=wdDoNotSaveChanges
'Get Name of Chapter or Document
Do Until ChptrNm Like "*."
ChptrNm = Left(ChptrNm, Len(ChptrNm) - 1)
Loop
ChptrNm = Left(ChptrNm, Len(ChptrNm) - 1)
End Sub

[Mavila]

No takers? lol

I resolved this. Essentially, I had the code in a non-modal userform that contained a progressbar. I moved the code to another userform (where it was originally) and it's working as expected now. For some reason the non-modal userform was the culprit. Well, having this subroutine-calling code in the userform was the culprit.

All's good now. :)

[Frosty]

There are a host of issues with the code above. But there's nothing to indicate why it wouldn't work (your ultimate solution).

A couple of conceptual pointers:
1. You don't need to have all code in User Forms. While everyone has an opinion on what code is appropriate to have in a user form, and what code is not... the only code which *has* to be in a user form is the code which handles identifying the various events (Click, Change, KeyDown, etc) of the various controls.

2. This is also opinion, but you'll find very few people who disagree: you should use Option Explicit. If you don't, you're opening yourself to a lot of mysterious bugs.

3. If you are using Option Explicit, and the code you posted simply refers to private variables to the UserForm (or public variables), then I would encourage you to move away from using dim/private/public declarations outside of individual sub-routines until you understand the concept of scope a bit better.

Instead, dim variables within the procedures which use them, and learn how to pass arguments to these procedures. That will be a good step towards being able to troubleshoot your code (and limit the amount of troubleshooting you have to do).

4. Finally, in terms of modal/modeless forms... there's no reason you couldn't have code which loops through a lot of documents with a modal user form, and refreshes a progress bar on that modal user-form. However, the point of modal is that it limits the end-user interaction with the application until the form is dismissed or hidden. This is basically para-phrasing the help file. But the basic experience is-- the document you opened the modal form with is the only one which can be active and display that form.

So if you have a modal form opening a bunch of other documents (and subsequently closing them), and also using the selection... but you still want to display that form (which will only normally show when the original document is also the active document), you'd have to do some trickery.

[fumei]

Mavila, the essential issue, as Frosty points out, is Scope. I strongly suggest you do some reading up (and possibly testing the concepts). It is a critical piece of understanding.

[Mavila]

Thanks Frosty and fumei. I'm more or less self taught on this. As a result, I'm weak on some of the fundamentals. I've known about "Option Explicit" but I don't really use it as it is one of those fundamentals that kind of got lost in my zeal to get code up and running. I know that is something I should pay some attention to. I will look it up in the help file but if you have another resource, please let me know.

And thanks for your help!

[Frosty]

The help file is really a good place to start. You should do the following:

1. In VBA, go to Tools > Options > Editor
Uncheck Auto Syntax Check
Check Require Variable Declaration
you *might* want to change the tab width from 4 to 2 (this is purely a preference).

2. Add Option Explicit to the top of every code module (forms/modules and classes, if any) in your project.

3. Right-click on the toolbars and choose Customize
Click the Debug category
Drag the top item (Compile Project) on to a toolbar
Get used to clicking that button regularly (it will compile the project, and let you know if you have any errors).

4. Go through your project, and make sure to use Dim statements for any undeclared variables. Make sure to type all your variables... so you should *not* do
Dim OnePageDoc
but you *should* do
Dim OnePageDoc As Boolean

Preferably, you should get in the habit of prefixing your variables with a lower case prefix indicative of the variable type... so you would

Dim bOnePageDoc As Boolean
or
Dim blOnePageDoc As Boolean
or
Dim blnOnePageDoc As Boolean

It doesn't really matter what type of prefixing you do (I personally prefer single letter prefixes for the more common ones, but this is purely opinion), but it does matter that you be consistent. Consistency makes you a better programmer (which really means you spend more time on the fun-part: creating the project and less time on the not-so-fun-part: figuring out what you screwed up).

5. The primary topics to read up on in the help file are: Data Types (Data Type Summary is the name of one topic), Scope/Lifetime ("Understanding Scope and Visibility", "Understanding the Lifetime of Variables"), and then (always a good one) "Working with Range Objects" (this will get you into the habit of converting your recorded macro which uses the Selection object into a macro which uses the Range object). Using ranges gives you a *lot* more flexibility when programming.

6. And lastly-- many programmers are self-taught. Formal training is over-rated. Curiosity and a continuing willingness to learn are the primary requisites to being a good programmer, in my opinion.

Good luck-- just by being open to learning, you're well on your way :)

[Mavila]

Thanks Frosty, I will read up some on that and get a little more disciplined. I have a basic understanding of variable lifetime and types. I'm always very careful with my variables. Sometimes I declare them but most times I don't unless their a global (in which case their always declared, obviously).

Just a quick question for you - do you always set your variables to "nothing" when you're done with them?

[Frosty]

Rarely.

Proper use of scope removes that need in VBA, since VBA variables get removed from memory when they fall out of scope (generally speaking). This falls under the programmatic concept of "garbage collection" (really, that's the term). VBA is pretty good at it, generally speaking.

I do occasionally set object variables to nothing at the end of the routine, because I was taught to do that, but I have my suspicions that it really doesn't do anything in most cases. Exceptions to every rule, and all that... if I'm dealing with funky objects (OLE containers, etc), then I might need to do that to make sure external garbage collection is handled, but for the most part... if you need to do that on a regular basis, there is either a) a lack of understanding or b) a problem in the design or c) both.

[fumei]

My two cents...

I do not set object variables to Nothing (for the reasons Frosty states, garbage collection works well)...EXCEPT Application objects. I always explicitly set application objects to Nothing when I am done.

[Frosty]

Excellent point. If you're using CreateObject or GetObject (or really, any object not handled within the word application object, like FileSystemObject etc) you should do your own garbage cleanup and also use the .Quit method (when appropriate) prior to setting the object to nothing

[fumei]

Yes, using .Quit is important.

The other possible use of explicit = Nothing is in the multple iteration of a Range.Find operation. As you move through, doing .Collapse to move the range Start/End, if you want to RECREATE the whole range object (the whole document most commonly) for the next iteration, I think it is a good idea to destroy the current object and start fresh.