How to pass Values from one function to another in same Module

[nspader]

Hello all,

Due to the size restriction of VBA code length I am forced to seperate my code accross Functions and call to them.

The setup is simple I have a private function that runs...Within that function, based on a recordcount # it then calls a separate function.

The problem I have is that it uses values detemined in the private function to run code is seperate sub.I dont want to reset the values but instead carry them over to the sub it calls.

For instances:
Private Sub ()
vDate = now()
Employee = current employee
Call ShiftRequest

(now as the code runs these values change based on certain criteria)

I would like to have something like this happen:
Public Sub ShiftRequest()
vDate = (private sub value of vDate when called)
Employee = (private sub value of Employee when called)

Can anyone help with how this is done. Or for that matter if it can be done.

Please let me know is this is not clear.

Thank you in advance.

Nick

[Stewart Ross]

Hi Nick. There are several ways to carry over values from one procedure call to the next, and which of these you use will depend on the scope in which you want to be able to access the values.

If you wish to carry forward the last value of a variable within a specific Sub or Function you can do so by declaring that variable as Static (instead of using the usual DIM form). Using somevar to stand for the name of a variable, and sometype to stand for its type, a static declaration is just:

Static somevar as sometype

You will not be able to access the values outside of the scope of the Sub or Function concerned, but on each call you will be able to refer to the previous value of the variable without passing its value as a parameter - the Static keyword means that its last value is retained and not destroyed on exit from the procedure.

Alternatively, if you need to retain values and refer to them outside of the scope of individual procedures you can define global variables in a public code module and refer to these from any other code module in your project. Such variables are declared in the header of a public code module as follows:

Public somevar as sometype

If you need to encapsulate a number of variables along with procedures to set and reset those properties you could create a class module to do so, but it sounds like this would be overkill for what you need at present.

Finally, if you need to retain data values between user sessions of your database you can use a dedicated table to do so. Access itself frequently uses system tables (not directly visible to users) to capture not just table and query design-related data but state and other data which has to be retained from one session to the next.

-Stewart

[nspader]

That was it. I had tried that. However, I was in a procedure when I named it public. I didnt think about it having to be in the module but not in a procedure. Thank you for your help as always.

Much appreciated

Nick

[missinglinq]

I'm curious about your statement " Due to the size restriction of VBA code length." What exactly are you referring to here?

Linq ;0)>

[ADezii]

In my opinion, the most efficient mechanism for passing Variables between Routines, whether they be Function or Sub, are through the receiving Routine's Arguments. The following code will illustrate this point:

1. Initial Sub-Routine
   

   Private Sub TestSub()
   'Declare Variables to be passed to ShiftRequest()
   Dim dteDate As Date
   Dim strEmployee As String
    
   dteDate = Now()
   strEmployee = CurrentUser()
    
   'Pass the Calculated Vales in TestSub() to ShiftRequest
   'via its Arguments
   Call ShiftRequest(dteDate, strEmployee)
   End Sub

2. Receiving Routine, accepts and processes values passed to it as Arguments from TestSub()
   

   Public Sub ShiftRequest(dteTheDate As Date, strCurrentEmployee As String)
     Debug.Print "Date passed to ShiftRequest: " & dteTheDate & vbCrLf & _
                 "Current Employee Name passed to ShiftRequest(): " & strCurrentEmployee
   End Sub

3. Sample OUTPUT
   

   Date passed to ShiftRequest: 6/13/2008 9:02:06 AM
   Current Employee Name passed to ShiftRequest(): Admin

4. Any questions, please feel free to ask.

[Stewart Ross]

Adezii is right, Nick - best practice is to ensure that all values passed between routines are explicitly passed as arguments (parameters) of the procedure or function involved. The use of global variables can introduce side effects that can cause failures as a result of interactions which are not clear to the users.

It is good software engineering practice to treat modular code as 'black boxes' which interact solely through their interfaces - the calling statement and the arguments passed. The workings of the inner code should make no difference to anything except what is passed through the arguments or specified to be the output of the function concerned.

In designing an object class globals are used for the object and its state variables - but in designing the class itself these internal variables are not normally made available to the 'outside world' except through interface routines (property set and get, and the methods for the class).

-Stewart

[ADezii]

nspader, one significant point to mention here is Load on Demand, a VBA concept whereby Modules are dynamically loaded into memory only when a component of them is referenced, i.e. Function, Sub-Routine, Variable, etc. If you have a situation in which Function1 calls Function2 and Function2 references a Global Variable, contain both Functions and the Global Declaration within a single Module so only one Module is loaded at Run Time. If you have cascading Function/Sub Calls, and these Routines are in different Modules, all those Modules will be loaded into memory. Bottom line, keep all related code/logic contained within single Modules!

[nspader]

nspader, one significant point to mention here is Load on Demand, a VBA concept whereby Modules are dynamically loaded into memory only when a component of them is referenced, i.e. Function, Sub-Routine, Variable, etc. If you have a situation in which Function1 calls Function2 and Function2 references a Global Variable, contain both Functions and the Global Declaration within a single Module so only one Module is loaded at Run Time. If you have cascading Function/Sub Calls, and these Routines are in different Modules, all those Modules will be loaded into memory. Bottom line, keep all related code/logic contained within single Modules!

Thank you for your advise. I did know about containing them into one module to keep dynamic memory to a minimum. Which they are.

My comment about being too long is that a single function in Access 2000 (mine atleast) cannot contain more the 1700 lines of code. I was running over this with the single function. It is very long and calls multiple variables. It only made sense to break it up into different functions to call anyway, I just didnt know how to pass the variables. Depending on where it is in the code loop will determine certain values for the variables.

One question I do have is why the different name of passing variable? i.e. Call Shiftrequest1 (acDate, acEmployee)

Private function Shiftrequest1(acTheDate, acCurrentEmployee)<---- Can you explain how to know what to write?

Thank You for all your help

Nick

[ADezii]

Thank you for your advise. I did know about containing them into one module to keep dynamic memory to a minimum. Which they are.

My comment about being too long is that a single function in Access 2000 (mine atleast) cannot contain more the 1700 lines of code. I was running over this with the single function. It is very long and calls multiple variables. It only made sense to break it up into different functions to call anyway, I just didnt know how to pass the variables. Depending on where it is in the code loop will determine certain values for the variables.

One question I do have is why the different name of passing variable? i.e. Call Shiftrequest1 (acDate, acEmployee)

Private function Shiftrequest1(acTheDate, acCurrentEmployee)<---- Can you explain how to know what to write?

Thank You for all your help

Nick

Hello Nick, the names within the Fuction/Sub Procedure are not important since they are just 'placeholders' for the actual Values being passed. What is important is that the values being passed are in the right order (unless you are using Named Arguments) and are of the correct Data Type. If a Function accepts a Date, Integer, and String, be sure to pass a Date, Integer, then String to it. Hope this helps. BTW, a single Function should 'never' contain anywhere near 1,700 lines of code.

[nspader]

Hello Nick, the names within the Fuction/Sub Procedure are not important since they are just 'placeholders' for the actual Values being passed. What is important is that the values being passed are in the right order (unless you are using Named Arguments) and are of the correct Data Type. If a Function accepts a Date, Integer, and String, be sure to pass a Date, Integer, then String to it. Hope this helps. BTW, a single Function should 'never' contain anywhere near 1,700 lines of code.

I know it shouldnt. However with my limited knowledge I have to perform things the way I know how. Unfortunately it is very long winded Tiering If Then statements. I am trying to learn case statements in hopes this will greatly shorten it. Also, The large amount of variables included to run this code makes it extremely long.

Thank you for your help.

Nick

[nspader]

Adezii,

Also, When passing to a called function, When that function ends and it returns to the original function to run will it retain the new value from the called function back to the original function?

I hoep that is clear. Basically it is a loop in values used to make a rotating shift schedule. The vEmployee number changes continually as the code runs. I need to retain last value from any given function to use for the next, until the Original function is completed. Will this still work that way. I know it did in Public code setup. I am adjusting the code to your recommendation as it does appear that it will be more properly written that way.

I just want to make sure that the value will go back and forth the way you stated to run.

Thanks

Nick

[ADezii]

Adezii,

Also, When passing to a called function, When that function ends and it returns to the original function to run will it retain the new value from the called function back to the original function?

I hoep that is clear. Basically it is a loop in values used to make a rotating shift schedule. The vEmployee number changes continually as the code runs. I need to retain last value from any given function to use for the next, until the Original function is completed. Will this still work that way. I know it did in Public code setup. I am adjusting the code to your recommendation as it does appear that it will be more properly written that way.

I just want to make sure that the value will go back and forth the way you stated to run.

Thanks

Nick

I think a little example will make things clearer. Calling fIntroFunction() will yield the following output:

Public Function fIntroFunction()
Dim lngLength As Long
Dim lngWidth As Long
Dim lngHeight As Long
 
lngLength = 150
lngWidth = 75
lngHeight = 50
 
Debug.Print "Volume of space: " & FormatNumber(fCalculateVolume(lngLength, lngWidth, lngHeight), 0) & _
            " Cubic Feet"
End Function

Private Function fCalculateVolume(lngLen, lngWdth, lngHgt) As Long
  fCalculateVolume = (lngLen * lngWdth * lngHgt)
End Function

OUTPUT:

Volume of space: 562,500 Cubic Feet

NOTE: fCalculateVolume() can be declared Privately since it exists in the same Module as fIntroFunction().