Code design Theory

[beebelbrox]

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;

Option Compare Database
 
'Global Variables
    Global gstrFileName As String
    Global gstrFNDate As String
    Global gstrDate As String
    Global gstrFilePath As String
    'Global gstrDate As String
 
 
Public Sub Start()
 
    Dim dbs As Database
 
    Set dbs = CurrentDb
 
 
    ClearDataTables '02ClearDataTables
 
 
    DateString '03CreateDateString
 
 
 
 
    gstrFilePath  "T:\Automation\01PkWood\KaladaData\AutomatedAdjustments_" & gstrDate & ".xls"
    gstrFileName = "AdjClaimIn_" & gstrDate
 
    Debug.Print "file pathe = " & gstrFilePath
    Debug.Print "file Name = " & gstrFileName
 
    ImportdataPull '04ImpotDataPull
 
    CreatePtrackinput '05CreatePtrackInput
 
    ExportTables '06ExportTables
 
End Sub

This is my question: Do I have to include the following in each additional module;

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.

[FishVal]

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

[NeoPa]

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.

[beebelbrox]

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

B

[NeoPa]

Hey, no worries.
It's what I do after all :)

[ADezii]

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;

Option Compare Database
 
'Global Variables
    Global gstrFileName As String
    Global gstrFNDate As String
    Global gstrDate As String
    Global gstrFilePath As String
    'Global gstrDate As String
 
 
Public Sub Start()
 
    Dim dbs As Database
 
    Set dbs = CurrentDb
 
 
    ClearDataTables '02ClearDataTables
 
 
    DateString '03CreateDateString
 
 
 
 
    gstrFilePath  "T:\Automation\01PkWood\KaladaData\AutomatedAdjustments_" & gstrDate & ".xls"
    gstrFileName = "AdjClaimIn_" & gstrDate
 
    Debug.Print "file pathe = " & gstrFilePath
    Debug.Print "file Name = " & gstrFileName
 
    ImportdataPull '04ImpotDataPull
 
    CreatePtrackinput '05CreatePtrackInput
 
    ExportTables '06ExportTables
 
End Sub

This is my question: Do I have to include the following in each additional module;

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.

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.

[beebelbrox]

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:

Public Sub Module1()
 
DimVariable1 as Integer (count)
 
Other Necessary code
Call Module2()
 
End sub
 
Public Sub Module2()
 
Variable1 = 0
For Variable1 = 0 to 10
next
return value Variable1
End sub

OR........

Public Sub Module1()
 
Dim Variables for Module 1
 
Other Necessary code
Call Module2() get variable1
 
End sub
 
Public Sub Module2()
 
Dim Variable1 as Integer (count)
 
Variable1 = 0
For Variable1 = 0 to 10
next
return value Variable1
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

[ADezii]

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:

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.

[beebelbrox]

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()

[ADezii]

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:

Public Sub Mod1()
  Dim Variable1 As String
 
  Variable1 = "Code Example"
  Msgbox Variable1     'will Print Code Example
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.

[NeoPa]

...

1. ...
2. It's a very confusing area (Scope) and I hope that I made it a little clearer.

I think you did :)

[ADezii]

I think you did :)

Just because you're not confused, don't think for 1 minute that I'm not! (LOL).