Split out a too large sub into several? (1 Viewer)

mdnuts

Registered User.
Local time
Today, 15:37
Joined
May 28, 2014
Messages
128
Hey folks.

I'm a bit tired this morning and excuse me for this but the question seems so familiar - I just can't think how to frame the question properly to search for it, and I'm pretty much out of time to work on this project.

I've got a sub that wouldn't compile due to it being 85kb vs 64kb. I don't have the time to go through it and see what I could reduce to get it under. So I'm trying to break it up into three sub's and I'm running into a continuity problem.

Code:
Private Sub Label194_Click()
  Dim oDoc As Word.Document
  Dim oWord As Word.Application
  Dim oTable As Word.table
  
  Set oWord = CreateObject("Word.Application")
  oWord.Visible = True
   Set oDoc = oWord.Documents.Add
  With oWord
    .Activate
    
  Call Proc1
  Call Proc2
 
  
  End With  'end with oword
  
  With oWord.ActiveDocument.PageSetup
    .TopMargin = oWord.InchesToPoints(0.88)
    .LeftMargin = oWord.InchesToPoints(1)
    .RightMargin = oWord.InchesToPoints(1)
    .BottomMargin = oWord.InchesToPoints(1)
  End With
   oDoc.SaveAs2 "temp.doc"
   Set oDoc = Nothing
  
  Set oWord = Nothing
 End Sub
Proc1 and Proc2 take the word document and adds on tables as needed. The problem is proc1 or 2 doesn't recognize the oDoc/oWord/whatever was already opened in Sub Label194.

How do I get it to recognize that without creating a new document in each sub?

Thank you.
 

sneuberg

AWF VIP
Local time
Today, 12:37
Joined
Oct 17, 2014
Messages
3,506
You would need to pass references to these objects to the called procedure. I believe that Access passes arguments by value by default so I suggest using the keyword ByRef to make sure they are references. See this page for more info.
 
Last edited:

mdnuts

Registered User.
Local time
Today, 15:37
Joined
May 28, 2014
Messages
128
Thank you,

I actually got a loop to work properly so I didn't need it in the end.

Fridays. bleh.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:37
Joined
Feb 28, 2001
Messages
27,001
The advice of Julius Caesar is appropriate: Divide and Conquer!

Look to making a separate library of actions for Word calls, where the objects are passed in as objects by reference. Pardon the mixed programming metaphor here, but ... put most of your variables in the "main sub" and then do things with them in the "sub subs."

For instance, I had a subroutine that looked at my Word App object to see if it was Nothing; if it was, I instantiated a Word App based on that object. Another sub would open a named document given the name and the App Object. Another sub would CLOSE the document in the App object without closing the object itself. Another would close the object and reset it to Nothing. It sounds like your subs could be coded to do the .AddTable and .AddRow using appropriate Word objects.

Now, what this does is that every time you take a basic operation and code that in a separate "Word Support" module, you end up replacing maybe 10 or so lines in the "main sub" with 1 line. Not only that... you now have a Word App Object toolbox for your next project. This concept - making a tool out of a task - is the basis of the fine art of "tool-smithing" and contributes to current ease of debugging and future ease of building new projects. The latter is true because once the tool is working, you would debug it a LOT less often the next time you use it.

Now as to your explicit question "How do I get it to recognize that without creating a new document in each sub?" The answer is that by passing in the object to a sub by reference, you do the work from the sub-sub but you do it on a variable in the main-sub.
 

mdnuts

Registered User.
Local time
Today, 15:37
Joined
May 28, 2014
Messages
128
that is fantastic advice Doc and I had originally intended it to be like that of sorts.

end of the day I wound up with a rats nest that works. I'm damn near ashamed at how terrible it is but I'm just out of time.
 

Users who are viewing this thread

Top Bottom