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

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

P: 78
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
Jun 9 '08 #1
Share this Question
Share on Google+
11 Replies


Expert Mod 2.5K+
P: 2,545
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. 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
Jun 9 '08 #2

P: 78
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
Jun 13 '08 #3

missinglinq
Expert 2.5K+
P: 3,532
I'm curious about your statement " Due to the size restriction of VBA code length." What exactly are you referring to here?

Linq ;0)>
Jun 13 '08 #4

ADezii
Expert 5K+
P: 8,616
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
    Expand|Select|Wrap|Line Numbers
    1. Private Sub TestSub()
    2. 'Declare Variables to be passed to ShiftRequest()
    3. Dim dteDate As Date
    4. Dim strEmployee As String
    5.  
    6. dteDate = Now()
    7. strEmployee = CurrentUser()
    8.  
    9. 'Pass the Calculated Vales in TestSub() to ShiftRequest
    10. 'via its Arguments
    11. Call ShiftRequest(dteDate, strEmployee)
    12. End Sub
  2. Receiving Routine, accepts and processes values passed to it as Arguments from TestSub()
    Expand|Select|Wrap|Line Numbers
    1. Public Sub ShiftRequest(dteTheDate As Date, strCurrentEmployee As String)
    2.   Debug.Print "Date passed to ShiftRequest: " & dteTheDate & vbCrLf & _
    3.               "Current Employee Name passed to ShiftRequest(): " & strCurrentEmployee
    4. End Sub
  3. Sample OUTPUT
    Expand|Select|Wrap|Line Numbers
    1. Date passed to ShiftRequest: 6/13/2008 9:02:06 AM
    2. Current Employee Name passed to ShiftRequest(): Admin
  4. Any questions, please feel free to ask.
Jun 13 '08 #5

Expert Mod 2.5K+
P: 2,545
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
Jun 13 '08 #6

ADezii
Expert 5K+
P: 8,616
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!
Jun 13 '08 #7

P: 78
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
Jun 16 '08 #8

ADezii
Expert 5K+
P: 8,616
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.
Jun 16 '08 #9

P: 78
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
Jun 16 '08 #10

P: 78
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
Jun 16 '08 #11

ADezii
Expert 5K+
P: 8,616
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function fIntroFunction()
  2. Dim lngLength As Long
  3. Dim lngWidth As Long
  4. Dim lngHeight As Long
  5.  
  6. lngLength = 150
  7. lngWidth = 75
  8. lngHeight = 50
  9.  
  10. Debug.Print "Volume of space: " & FormatNumber(fCalculateVolume(lngLength, lngWidth, lngHeight), 0) & _
  11.             " Cubic Feet"
  12. End Function
Expand|Select|Wrap|Line Numbers
  1. Private Function fCalculateVolume(lngLen, lngWdth, lngHgt) As Long
  2.   fCalculateVolume = (lngLen * lngWdth * lngHgt)
  3. End Function
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Volume of space: 562,500 Cubic Feet
NOTE: fCalculateVolume() can be declared Privately since it exists in the same Module as fIntroFunction().
Jun 17 '08 #12

Post your reply

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