Solved: Global Variables and Functions

[aerodoc]

Did some searching around and this should be straightforward, but I am struggling with it (still coming up the learning curve).

I would like to create a function where I call a variable like:

x = Range("NamedRange").Value

Where "NamedRange" is the name of a cell

Then I want to pass "x" to the function, but I would also like to pass "x" to many functions as well.

From what I can see, I need something like:

Public x as Double

but I am not sure in which part of the module to define "x" in (is does not like to be in the Declarations section it seems.

As an additional note, how do I get the function to update when "x" has been updated. I saw a bit of code by Chip Pearson, but was confused by it. The point is that VBA does not know that "x" has been updated unless you specify it with proper code or use the "volatile" switch (but that bogs the code down too much).

Thanks, hope this was not too confusing.

[Bob Phillips]

A public variable in the module declaratives should be fine.

[aerodoc]

In the Declarations section, I have "Public x as Double"

If I put "x = Range("test").Value" inside the function, it works. But if this line is outside of the function I get a value of 0.

Where exactly should the variable line "x = Range("test").Value" be located? It does not get passed to the function as is.

[Bob Phillips]

You cannot have a line of code outside of a procedure. Just can't do it!

[aerodoc]

That would explain it. So I will need to repeat this variables many times? I thought by "global" I could just define them once and then pass them to the functions.

For example, "x" gets used in many functions, but I need to have "x" defined within each function? Seems inefficient and tedious for modifications?

[Bob Phillips]

No, by declaring it as Public you make it available to all procedures.

[aerodoc]

Oh, I think I got it now. In one of the functions, I can create the variable, but then I also define it as a Public variable. This allows it to then be reused by the other functions. In hindsight, it is simple and makes sense, but it was not clicking. Thanks.

[Bob Phillips]

No, you don't create a public variable and another in the function. The one in the function will be different than the public one. Try this to demonstrate



Option Explicit

Public gVariable1 As String
Public gVariable2 As String

Public Sub CallerProc()
gVariable1 = "Initial value of gVariable1"
gVariable2 = "Initial value of gVariable2"

Call CalledProc1
Call CalledProc2
End Sub

Public Sub CalledProc1()

'here you should see the two values you set displayed
MsgBox "CalledProc1" & vbNewLine & _
vbTab & "gVariable1: " & gVariable1 & vbNewLine & _
vbTab & "gVariable2 : " & gVariable2
End Sub

Public Sub CalledProc2()
Dim gVariable2 As String
'here you should see the first value you set displayed and then a blank value
MsgBox "CalledProc2" & vbNewLine & _
vbTab & "gVariable1: " & gVariable1 & vbNewLine & _
vbTab & "gVariable2 : " & gVariable2

End Sub

[aerodoc]

Actually, this is what I was doing, but I think I did not properly describe it. I realize I need to be more careful with how I phrase things when it comes to dealing with code.

[Bob Phillips]

Actually, this is what I was doing, but I think I did not properly describe it. I realize I need to be more careful with how I phrase things when it comes to dealing with code.

:yes you have to be absolutely precise.