By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,479 Members | 2,239 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,479 IT Pros & Developers. It's quick & easy.

Code design Theory

P: 20
Okay this may be a bit trivial, it is a concept Im having difficulty understanding. When creating VB code I like to break up job steps/functions into seperate modules for example;

01Globals
02Cleardatatables
03 CreateDateString
04 etc..etc

The module 01Globals list all of the variables used by every module and the code there calls each module in sequence;
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. 'Global Variables
  4.     Global gstrFileName As String
  5.     Global gstrFNDate As String
  6.     Global gstrDate As String
  7.     Global gstrFilePath As String
  8.     'Global gstrDate As String
  9.  
  10.  
  11. Public Sub Start()
  12.  
  13.     Dim dbs As Database
  14.  
  15.     Set dbs = CurrentDb
  16.  
  17.  
  18.     ClearDataTables '02ClearDataTables
  19.  
  20.  
  21.     DateString '03CreateDateString
  22.  
  23.  
  24.  
  25.  
  26.     gstrFilePath  "T:\Automation\01PkWood\KaladaData\AutomatedAdjustments_" & gstrDate & ".xls"
  27.     gstrFileName = "AdjClaimIn_" & gstrDate
  28.  
  29.     Debug.Print "file pathe = " & gstrFilePath
  30.     Debug.Print "file Name = " & gstrFileName
  31.  
  32.     ImportdataPull '04ImpotDataPull
  33.  
  34.     CreatePtrackinput '05CreatePtrackInput
  35.  
  36.     ExportTables '06ExportTables
  37.  
  38. End Sub
This is my question: Do I have to include the following in each additional module;

Pseudo code
Expand|Select|Wrap|Line Numbers
  1. Public Sub "Sub Name"()
  2.  
  3.     Dim dbs As Database
  4.  
  5.     Set dbs = CurrentDb
Once the workspace is created buy the initial module is it available to all of the other modules? What I've read to date isnt very clear and the few chance I've had to experiment have only added to my confusion.
Jan 2 '08 #1
Share this Question
Share on Google+
11 Replies


FishVal
Expert 2.5K+
P: 2,653

Pseudo code

Public Sub "Sub Name"()

Dim dbs As Database

Set dbs = CurrentDb

Once the workspace is created buy the initial module is it available to all of the other modules? What I've read to date isnt very clear and the few chance I've had to experiment have only added to my confusion.
Hi, there.

What is this code supposed to do? And what it does in the posted subroutine?

Regards,
Fish
Jan 2 '08 #2

NeoPa
Expert Mod 15k+
P: 31,186
In short - No.
You need to define dbs as a global variable (the recommended method is to use Public rather than the older Global) outside of a particular procedure.
With it defined that way you will only need to set it once for it to be available to your whole project.
Jan 3 '08 #3

P: 20
Thanks NeoPa I appreciate you taking the time to answer such a simple question.

B
Jan 7 '08 #4

NeoPa
Expert Mod 15k+
P: 31,186
Hey, no worries.
It's what I do after all :)
Jan 7 '08 #5

ADezii
Expert 5K+
P: 8,597
Okay this may be a bit trivial, it is a concept Im having difficulty understanding. When creating VB code I like to break up job steps/functions into seperate modules for example;

01Globals
02Cleardatatables
03 CreateDateString
04 etc..etc

The module 01Globals list all of the variables used by every module and the code there calls each module in sequence;
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. 'Global Variables
  4.     Global gstrFileName As String
  5.     Global gstrFNDate As String
  6.     Global gstrDate As String
  7.     Global gstrFilePath As String
  8.     'Global gstrDate As String
  9.  
  10.  
  11. Public Sub Start()
  12.  
  13.     Dim dbs As Database
  14.  
  15.     Set dbs = CurrentDb
  16.  
  17.  
  18.     ClearDataTables '02ClearDataTables
  19.  
  20.  
  21.     DateString '03CreateDateString
  22.  
  23.  
  24.  
  25.  
  26.     gstrFilePath  "T:\Automation\01PkWood\KaladaData\AutomatedAdjustments_" & gstrDate & ".xls"
  27.     gstrFileName = "AdjClaimIn_" & gstrDate
  28.  
  29.     Debug.Print "file pathe = " & gstrFilePath
  30.     Debug.Print "file Name = " & gstrFileName
  31.  
  32.     ImportdataPull '04ImpotDataPull
  33.  
  34.     CreatePtrackinput '05CreatePtrackInput
  35.  
  36.     ExportTables '06ExportTables
  37.  
  38. End Sub
This is my question: Do I have to include the following in each additional module;

Pseudo code
Expand|Select|Wrap|Line Numbers
  1. Public Sub "Sub Name"()
  2.  
  3.     Dim dbs As Database
  4.  
  5.     Set dbs = CurrentDb
Once the workspace is created buy the initial module is it available to all of the other modules? What I've read to date isnt very clear and the few chance I've had to experiment have only added to my confusion.
Here are a couple somewhat related pointers that I think will help you out:
  1. VBA loads code only when it is needed, at execution time.
  2. VBA loads only the particular Module it needs at any given moment to run your code.
  3. VBA always loads an entire Module if it needs any portion of the Module or if it must use any Procedure in a Module.
  4. A variable in a Module reacts the same way. If your code attempts to set or retrieve the value of a Public Variable in a Module, VBA must load the entire Module.
  5. If for some strange reason you want to pre-load all Modules, you can actually force this kind of behavior. All you need to do is to add a Public Variable to each Module and attempt to retrieve the value of each in your Application's Start Up code.
  6. Hoped this information helped a little.
Jan 8 '08 #6

P: 20
ADezii:

Follow up question then (I hope this make sense):

A variable in a Module reacts the same way. If your code attempts to set or retrieve the value of a Public Variable in a Module, VBA must load the entire Module.
It would be considered good practice then to define as many of your public variable in the first module and then call them as needed rather then to define them in a separate module, for example:
Expand|Select|Wrap|Line Numbers
  1. Public Sub Module1()
  2.  
  3. DimVariable1 as Integer (count)
  4.  
  5. Other Necessary code
  6. Call Module2()
  7.  
  8. End sub
  9.  
  10. Public Sub Module2()
  11.  
  12. Variable1 = 0
  13. For Variable1 = 0 to 10
  14. next
  15. return value Variable1
  16. End sub
OR........
Expand|Select|Wrap|Line Numbers
  1. Public Sub Module1()
  2.  
  3. Dim Variables for Module 1
  4.  
  5. Other Necessary code
  6. Call Module2() get variable1
  7.  
  8. End sub
  9.  
  10. Public Sub Module2()
  11.  
  12. Dim Variable1 as Integer (count)
  13.  
  14. Variable1 = 0
  15. For Variable1 = 0 to 10
  16. next
  17. return value Variable1
  18. End sub
What would the difference be between the two examples? Does one execute faster, or is one easier to maintain etc. etc. I would think it is easier to keep track of the variables in the module that creates/operates on them (the second example rather then the first)

If for some strange reason you want to pre-load all Modules, you can actually force this kind of behavior. All you need to do is to add a Public Variable to each Module and attempt to retrieve the value of each in your Application's Start Up code.
Under what circumstances would it be wise to do this. This implies the VB code is compiled at run time for each module, is that true? Or is the code compiled for all modules once at run time, with the compiled code being loaded as you described
Jan 8 '08 #7

ADezii
Expert 5K+
P: 8,597
Hello beebelbrox, I'm a little confused here but don't worry since that seems to happen a lot these days. (LOL). Your first question relates to Public Variables in a Standard Code Module, but your examples depict Local Variables in Public Routines. A Public (Global) Variable is defined in a Code Module within the context of the General Declarations Section, as in:
Expand|Select|Wrap|Line Numbers
  1. Public dteStartDate As Date
As a general rule of thumb, keep the most frequently accessed Public Variables in the same Module in order to minimize Module Loading.

The only instances of which I would think it may be beneficial to pre-load Modules is when you are accessing External Data Sources or possibly Automation code, where you will sacrifice Load Time on the front end for enhanced performance during the life of the Application. This is analagous to Visual Basic, where many Programmers will pre-load all Forms in memory, while a Splash Screen is displayed, so they they may be instantly displayed further down the line.
Jan 8 '08 #8

P: 20
ADezii

I think I see now, the confusion is on my end, as well as the bad example. Simply Puting the variable in a Public Sub doesnt give it scope for the Whole Project.


Public Sub Mod1()
Dim Variable1

is very diferent then from

Public Variable1
Public Variable2
Public VariableN
Public Sub Mod1()
Jan 8 '08 #9

ADezii
Expert 5K+
P: 8,597
ADezii

I think I see now, the confusion is on my end, as well as the bad example. Simply Puting the variable in a Public Sub doesnt give it scope for the Whole Project.


Public Sub Mod1()
Dim Variable1

is very diferent then from

Public Variable1
Public Variable2
Public VariableN
Public Sub Mod1()
Bingo, for instance:
Expand|Select|Wrap|Line Numbers
  1. Public Sub Mod1()
  2.   Dim Variable1 As String
  3.  
  4.   Variable1 = "Code Example"
  5.   Msgbox Variable1     'will Print Code Example
  6. End Sub 
  1. Mod1() is a Public Sub-Routine Procedure and can be called from anywhere within the entire Application, and the result 'Code Example' will be displayed in a Message Box.
  2. Variable1 is a Variable Local to Mod1() and has an existence (Scope) only within this Sub-Routine. Once you exit the Sub-Routine, Variable1 has no meaning. After you execute Mod1(), you can never reference Variable1 again, unless of course, you Declare it again.
  3. It's a very confusing area (Scope) and I hope that I made it a little clearer.
Jan 8 '08 #10

NeoPa
Expert Mod 15k+
P: 31,186
...
  1. ...
  2. It's a very confusing area (Scope) and I hope that I made it a little clearer.
I think you did :)
Jan 9 '08 #11

ADezii
Expert 5K+
P: 8,597
I think you did :)
Just because you're not confused, don't think for 1 minute that I'm not! (LOL).
Jan 9 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.