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

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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..
Dale FyeCommented:
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
upobDaPlayaAuthor Commented:
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.
Dale FyeCommented:
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")
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.