Is a public sub called from a private module able to provide any information to the private modu

Posted on 2014-08-22
Last Modified: 2014-08-24
If a private sub from within a Form calls a Public sub are any of the variables in the Public sub available in the private sub ?

For example if I call Public Sub Volunteers and the Public Sub has a variable called strVolunteerName Name will strVolunteerName be available in the Private Sub if strVolunteerName has also been declared in the Private Sub ?  

If no how can strVolunteerName be made available to the private sub.

The goal is from the Form sub to call the public sub several times so I can retrieve Volunteer information.  Currently I have a record-set retrieval for volunteer information being done from within in the private sub, but the same recordset retrieval is repeated within the private sub several times so I believe this is a good instance where the retrieval should be placed in its own sub, but I always get confused on the public/private declaration.
Question by:upobDaPlaya
    LVL 39

    Assisted Solution

    You can declare variable as Global - before any Sub in Module
    Option Compare Database
    Option Explicit
    Global strVolunteerName as String

    Public Sub .....
    strVolunteerName = "Some name"
    End Sub

    In this case yoyu can use this variable everywhere, but be very careful, when you have many calls to this sub.

    There is also alternate way - use TempVars, in this case you don't need global declarations:
    Public Sub...
    TempVars("VolunteerName").Value = "Some Name"
    End Sub

    Private Sub...
    debug.print TempVars("VolunteerName").Value
    end Sub
    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    If a private sub from within a Form calls a Public sub are any of the variables in the Public sub available in the private sub ?
    To answer this directly - no, variables declared in a Sub or Function are not available outside of that Sub or Function, even if you've named them the same This is known as "scope", and a good understanding of the concept is needed to avoid unintended consequences.

    As als has suggested, you can change that scope by using global variables or such. This is a fairly common practice, but has some pitfalls - for example, if you encounter an unhandled error the value of the global variable will be lost.

    Functions generally return a value, so you can always set the value of a Variable in FunctionA with the return value from FunctionB:

    Dim sVar As String
    sVar = FunctionB("SomeInputValue")

    In the above case, if FunctionB "returns" a value, then your local sVar value would be set to that value.

    You can also set local variables from an external process by using the ByRef method. ByRef means, essentially, "By Reference". In this case, you are passing a "modifiable" variable into an external Sub or Function, and allowing that Sub or Function the ability to change it. An example of that is here:

    1. Add this to a Standard Module:

    Sub Test2(ByRef var As String)
    var = "some other value"
    End Sub

    2. Now add this to the Click event of a button on a form:

    Dim sVar As String
    sVar = "first value"
    '/ show the value before calling Test2
    MsgBox sVar
    '/ now call Test2 and pass the var in
    Test2 sVar
    '/ show the value after running test2
    MsgBox sVar

    Here's an article on ByVal and ByRef. It's for .NET, but the distinction is the same:

    In general, you should use the most restrictive "scope" that suits your needs. There's no need to declare Global Variables that are only used in a local function, for example..
    LVL 47

    Accepted Solution

    Alexey has provided two options that involve creating global variables and tempvars.  I like Tempvars for storing values you will use in multiple places throughout your application.

    With a public function, you can return a single value from the function to your calling procedure, regardless of whether the calling procedure is public or private.  Of course, you could also pass an array of values using a function as well.  The example below builds an array in a function.
    Public Function ReturnArray() As Variant
        Dim A(3) As Variant
        A(0) = 1
        A(1) = "Test"
        A(2) = #8/21/2014#
        ReturnArray = A
    End Function

    Open in new window

    When that function is called as follows, you will see that you can actually pass multiple values back to a calling procedure from the function.
    B = ReturnArray
    Debug.Print B(0), B(1), B(2)

    Open in new window

    But another way to retrieve multiple values from a procedure is to declare them in the calling procedure and pass them to the function or subroutine in the argument list.  By default VBA passes these arguments ByRef, so you can actually change these values in the function/subroutine that is being called and they will be returned to the calling procedure with the new value.  The example below shows how you can do this.
    Dim intValue1 as integer, intValue2 as integer
    intValue1 = 1
    intValue2 = 2
    Call TestSub(intValue1, intValue2)
    debug.print intValue1, intValue2
    Public Sub TestSub(Val1 as integer, ByVal Val2 as integer)
    Val1 = Val1 + 1
    Val2 = Val2 + 1
    Exit Sub

    Open in new window

     You will note that when you print out the value of the two variable after calling the subroutine, intValue2 has changed (because it was declared in the subroutine as ByRef - which is the default), while intValue2 has remained the same (because it was declared using ByVal).

    Hope this helps.


    Author Closing Comment

    I used the ByRef option.  Excellent !  I enjoyed learning this option along with the others.  I need to re-read some of the links, but the explanation and options provided were fantastic.  I never knew about ByRef.
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    The thing you need to keep in mind is the ByRef is the default.

    If you pass a variable to a subroutine without explicitly declaring it ByVal, and then you inadvertently change the value, you could screw up a process in the routine that called it.  So, make sure to explicitly declare those arguments that you need but don't want to change in the calling routine as ByVal.

    In Scott's reply, he also touched on the fact that global variables will lose their value when an unhandled error is encountered.  However, with TempVars, this does not occur.  Another advantage of TempVars is that they retain their value even when the application is not running, so you can set a TempVar's value in the Immediate window:

    Tempvar!MyDate = #1/1/2014#
    Tempvar("MyDate") = #1/1/2014#
    Tempvar.Add "MyDate", #1/1/2014#

    and then use that in a query.

    SELECT * FROM myTable WHERE [DateField] > [Tempvar]![MyDate]

    I must admit that I have occasionally encountered problems with this syntax in queries.  I'm not sure why and have not spent a lot of time trying to figure it out, I simply wrote a function that returns the value to my queries:

    Public Function fnTempvars(strVarName as string) as variant
        fnTempvars = Tempvars("MyDate")
    End Function

    SELECT * FROM myTable WHERE [DateField] > fnTempvars("MyDate")

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now