Passing definitions into subroutines?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ed - 29 Sep 2003 16:17 GMT

I have a macro which calls up other macros as part of a loop. In the main
routine, I create a document, name it, save it. It is Dimmed and Defined.
But the subroutine macros don't recognize it by name, even though it's
needed in each of the 8,000+ loops.

The only solution I've come up with so far is to close the document just
before executing the loop, then have the subroutine open it, use it, and
close it again. This means this document is opening, saving, and closing
over 8,000 times. I can't help but think it would be faster to leave it
open and available to the subroutine by passing off the definitions and such
for the subroutine to reference, but I don't know how.

Any help is appreciated.

Ed

Reply to this Message

Jonathan West - 29 Sep 2003 18:22 GMT

Hi Ed

You need to do something like this

Sub MainRoutine()
Dim oDoc as Document

Set oDoc = Documents.Open("C:\My Documents\My test document.doc")

For i = 1 to 8000
MySubroutine oDoc
Next i

End Sub

Sub MySubroutine(DocToUse as Document)
MsgBox DocToUse.Fullname
End Sub

That is a fairly trivial ocde example, but it demonstrates the concept of
passing a document as a parameter to a subroutine.

This concept can be applied to just about *any* kind of object.

Signature

Regards
Jonathan West - Word MVP
MultiLinker - Automated generation of hyperlinks in Word
Conversion to PDF & HTML
http://www.multilinker.com

> I have a macro which calls up other macros as part of a loop. In the main
> routine, I create a document, name it, save it. It is Dimmed and Defined.
[quoted text clipped - 11 lines]
>
> Ed

Reply to this Message

Ed - 29 Sep 2003 18:57 GMT

Thanks for replying, Jonathan. I'm not sure, though, how to use what you've
given me in my code. The document in question will end up being a list of
all the file paths of the documents created and saved during all the
looping. What I have is something like:

*** In the main routine:

Const SavePath = 'filepath for saved documents

Sub MainRoutine()
Dim this and that
Dim PathDoc As Document

'Other code

'Create PathDoc
Set PathDoc = Documents.Add
PathDoc.SaveAs FileName:=SavePath & "File Paths.doc"
PathDoc.Close savechanges:=wdDoNotSaveChanges

'Eventually get to the loop
Application.Run MacroName:="ParseMe"

End Sub

*** Then the subroutine:

Const SavePath = "C:\Documents and Settings\emillis\Desktop\Stryker
Reports\Narratives\"
Const PathDoc = SavePath & "File Paths.doc"

Sub ParseMe()

Dim PathDoc As Document

'Some code

'Use PathDoc
Documents.Open FileName:=PathDoc
Set PathDoc = ActiveDocument

'Finish loop code

End sub

*******

The whole routine slices a longer document into smaller ones, so the actual
number of loops depends on the length and attributes of the longer doc. All
that is built into the code and works okay. It just feels real clumsy the
way I have to handle the passing of this one document through the
subroutines.

Ed

> Hi Ed
>
[quoted text clipped - 36 lines]
> >
> > Ed

Reply to this Message

Jay Freedman - 29 Sep 2003 21:24 GMT

Hi, Ed,

First, change your subroutine's first line to

Sub ParseMe(PathDoc As Document)

That tells VBA that *inside the subroutine* the variable PathDoc refers to
the value passed in from the main program. In particular, it isn't enough
just to have two variables with the same name, one in each routine but
separately declared -- VBA considers them to be completely separate
variables. In fact, I prefer to use *different* names in each place, just to
make it clear that the value has to be passed through the subroutine's
parameter.

Now *delete* these lines from the subroutine:

> Const SavePath = "C:\Documents and Settings\emillis\Desktop\Stryker

Reports\Narratives\"

> Const PathDoc = SavePath & "File Paths.doc"

> 'Use PathDoc
> Documents.Open FileName:=PathDoc
> Set PathDoc = ActiveDocument

(The Const PathDoc in particular should have caused an error when you have a
later Dim PathDoc statement -- VBA should complain about attempted
redefinition.)

In the main program, move the PathDoc.Close line to the end, just before the
End Sub statement, and change the constant to wdSaveChanges (else you'd be
discarding all the info you put into the file, so why bother?).

Finally, change the Application.Run statement by adding the parameter to it:

Application.Run MacroName:="ParseMe", varg1:=PathDoc

or simplify that line to just

ParseDoc PathDoc:=PathDoc

(Have a look at the VBA Help topic about the Run method, which explains that
the Application.Run syntax is usually unnecessary.) Placing the parameter
assignment after the subroutine name tells VBA to copy the value of PathDoc
in the main program into the parameter (coincidentally but not necessarily
also named PathDoc) of the subroutine.

Signature

Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://www.mvps.org/word

> Thanks for replying, Jonathan. I'm not sure, though, how to use what
> you've given me in my code. The document in question will end up
[quoted text clipped - 98 lines]
>>>
>>> Ed

Reply to this Message

Jay Freedman - 29 Sep 2003 21:32 GMT

Oops, one more line to delete -- get rid of the Dim PathDoc in the
subroutine (the parameter in the Sub ParseMe line serves as the declaration
of the variable in the subroutine). The only occurrence of Dim PathDoc
should be in the main program.

Signature

Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://www.mvps.org/word

> Hi, Ed,
>
[quoted text clipped - 146 lines]
>>>>
>>>> Ed

Reply to this Message

Ed - 30 Sep 2003 18:09 GMT

Jay - once again, I owe you big time! I printed all that out so I can
follow it. If I get bugs I can't kill, I'll holler back.

Ed

> Oops, one more line to delete -- get rid of the Dim PathDoc in the
> subroutine (the parameter in the Sub ParseMe line serves as the declaration
[quoted text clipped - 151 lines]
> >>>>
> >>>> Ed

Reply to this Message