Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-14-2016, 04:06 AM   #1
mdnuts
Newly Registered User
 
Join Date: May 2014
Posts: 73
Thanks: 14
Thanked 7 Times in 7 Posts
mdnuts is on a distinguished road
Split out a too large sub into several?

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.

mdnuts is offline   Reply With Quote
Old 10-14-2016, 04:55 AM   #2
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 976 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Split out a too large sub into several?

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.
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve

Last edited by sneuberg; 10-14-2016 at 04:58 AM. Reason: Change parameter to argument
sneuberg is offline   Reply With Quote
Old 10-14-2016, 04:57 AM   #3
mdnuts
Newly Registered User
 
Join Date: May 2014
Posts: 73
Thanks: 14
Thanked 7 Times in 7 Posts
mdnuts is on a distinguished road
Re: Split out a too large sub into several?

Thank you,

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

Fridays. bleh.

mdnuts is offline   Reply With Quote
Old 10-14-2016, 05:02 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,575
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Split out a too large sub into several?

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 10-14-2016, 06:26 AM   #5
mdnuts
Newly Registered User
 
Join Date: May 2014
Posts: 73
Thanks: 14
Thanked 7 Times in 7 Posts
mdnuts is on a distinguished road
Re: Split out a too large sub into several?

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.

mdnuts is offline   Reply With Quote
Reply

Tags
vba , word

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
To split or not to split...that is the question :) rede96 Theory and practice of database design 5 08-05-2015 04:51 AM
Split string, execute function, delete split. Dranoweb Modules & VBA 30 11-03-2010 09:56 PM
LArge Table Split villaw Tables 10 03-11-2010 09:37 PM
Large Recordset, Large Search Criteria StevenAFC Modules & VBA 7 12-10-2008 05:15 AM
Large Tables: To split or not to split. PookaTech Tables 8 08-11-2004 11:36 AM




All times are GMT -8. The time now is 09:45 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World