using object in an external sub

  • Thread starter Thread starter Jesper F
  • Start date Start date
J

Jesper F

I'm doing some automation to Word from Access.
In the code I have for example:

Sub PrintInWord()
Dim oDoc As Object
Dim oWord As Object

oWord.Selection.....
oWord.Selection.....
oWord.Selection.....
oWord.Selection.....
oWord.Selection.....

End Sub
 
Sorry, put my elbow on the keyboard there. Here's the whole thing:
----------
I'm doing some automation to Word from Access.
In the code I have for example:

Sub PrintInWord()
Dim oDoc As Object
Dim oWord As Object
oWord.Selection..... 'formatting the word document
oWord.Selection..... 'formatting the word document
oWord.Selection..... 'formatting the word document
oWord.Selection..... 'formatting the word document
oWord.Selection..... 'formatting the word document
End Sub

I'd like to take long, repeating sections such as the
"oWord.Selection..."-lines and put them into an external sub, so that I'd
get:

Sub PrintInWord()
Dim oDoc As Object
Dim oWord As Object
call sRunThis
End Sub

Sub sRunThis
oWord.Selection.....
oWord.Selection.....
oWord.Selection.....
oWord.Selection.....
oWord.Selection.....
End Sub

But of course this doesn't work since the object "oWord" isn't available in
the sub "sRunThis".
Is there a way to refactor this kind of code into several subs? Should I
declare something at module level and doesn't this produce overhead?
Thanks for any input.


Jesper Fjølner, Denmark
 
Make the Dims Public and put them in the declarations section of the module.
I don''t know about overhead, but it will do what you want.
 
Making them public is seldom the best approach. Much better would be to pass
the object as a parameter to the function:

Sub PrintInWord()
Dim oDoc As Object
Dim oWord As Object
call sRunThis(oWord)
End Sub

Sub sRunThis(WordObject As Object)
WordObject.Selection.....
WordObject.Selection.....
WordObject.Selection.....
WordObject.Selection.....
WordObject.Selection.....
End Sub

Slightly more efficient would be to use the With...End With construct:

Sub PrintInWord()
Dim oDoc As Object
Dim oWord As Object
call sRunThis(oWord)
End Sub

Sub sRunThis(WordObject As Object)
With WordObject
.Selection.....
.Selection.....
.Selection.....
.Selection.....
.Selection.....
End With
End Sub
 
Making them public is seldom the best approach. Much better would be to
pass
the object as a parameter to the function:

Sub PrintInWord()
Dim oDoc As Object
Dim oWord As Object
call sRunThis(oWord)
End Sub

Sub sRunThis(WordObject As Object)
WordObject.Selection.....
WordObject.Selection.....
WordObject.Selection.....
WordObject.Selection.....
WordObject.Selection.....
End Sub

Very nice. Thanks a lot.
 
Back
Top