Does VBA Lose Variable Definitions and/or Values between Subs?

[RonMcK]

I'm working on project and in the process I'm attempting to build separate Subs for discrete tasks. I'm noticing two behaviors that appear strange and unexpected. I have Option Explicit at the head of my code.

Sometimes, in a 'called' Sub, VBA tells me that a Variable is undefined. However it is in my DIM list in the "parent" Sub. Aren't variable defined in one sub visible to all other subs in a project?

The other problem is that sometimes, as I go from the main sub to another Sub, the value assigned to a variable disappears. debug.print executed before I invoke the sub displays the value, however, another debug.print executed as the first instruction within the new Sub displays a null. Nothing is being DIMed in the new sub.

Is there an explanation for this behavior? Do you need more information from me?

So, far, I've been solving my problems by converting problem Subs to Functions as a work-around but I remain perplexed by the behaviors.

Thanks for your assistance!

[Kenneth Hobs]

Variables are private unless you make the Public outside of a Sub or Dim outside of Sub.

Undefined variables are sometimes mistaken due to spelling errors. Other times, if not set as another part expects, it will show a problem.

e.g.
[VBA]
Public s as String
Sub t
s="Hello World!"
tt
End Sub

Sub tt
MsgBox s
End Sub
[/VBA]

[RonMcK]

Variables are private unless you make the Public outside of a Sub or Dim outside of Sub.

Undefined variables are sometimes mistaken due to spelling errors. Other times, if not set as another part expects, it will show a problem.

DOH! I've either lost too many brain cells or had a brain fart. Thanks, Kenneth for pointing that out. Instead of DIMing, I need to be PUBLICing the variables that are shared.

This makes sense. Is a public variable visible inside a Function or just in Subs?

Thanks,

[mdmackillop]

Hi Ron,
A variable dimmed in a sub is used only within that sub. The variable value can be passed to another sub, but may not have the same name.
[VBA]Sub Trial1()
Dim x, y
x = 1
y = 3
Call Trial2(x, y)
End Sub


Sub Trial2(a, b)
MsgBox a & "," & b
End Sub
[/VBA]

Alternatively, you can declare variables at Module Level, then set values and use these in any sub.

[VBA]Option Explicit
Dim x, y

Sub test1()
x = 2
y = 4
Call Test2
End Sub

Sub Test2()
MsgBox x & "," & y
End Sub
[/VBA]

Finally, Dim variables as Public, and they can be used in different modules

[VBA]'in Module1
Option Explicit
Public x, y

'in Module2
Option Explicit
Sub MySub()
x = 5
y = 8
MySub1
End Sub

Sub MySub1()
MsgBox x & "," & y
End Sub
[/VBA]

[RonMcK]

Thank you, Malcolm, for a very clear tutorial that extends what Kenneth laid out. So since I have only the one module, at the moment, in my project, I can just DIM the variables outside of any of the Subs and things will be good.

Thanks,

[Paul_Hossler]

Personal opinion: I try to avoid using global variables as much as possible since it can become difficult to figure out how and where they might have gotten changed

You can use ByRef to pass a parameter from a calling sub (Top) to a called sub (Lower1), and make the result available to the calling sub.

Lower2 doesn't update the parameter at the calling level


[vba]
Option Explicit
Sub Top()
Dim i As Long, j As Long, k As Long
i = 2
j = 3
k = -1
'Call Lower1(2, 3, k)
MsgBox "k before Lower1 = " & k
Call Lower1(i, j, k)
MsgBox "k after Lower1 = " & k
k = -1
MsgBox "k before Lower2 = " & k
Call Lower2(5, 7, k)
MsgBox "k after Lower2 = " & k

End Sub

'ByRef updates c
Sub Lower1(a As Long, b As Long, ByRef c As Long)
c = a * b
MsgBox "Inside Lower1 " & c
End Sub
'ByVal passes c but does not update c
Sub Lower2(a As Long, b As Long, ByVal c As Long)
c = a * b
MsgBox "Inside Lower2 " & c
End Sub
[/vba]

Paul

[RonMcK]

Paul,

Thanks for the examples. Tell me if I'm understanding variables i and j, they are passed into the called sub, however, any changes of a and b within the called sub do not affect i or j? What is the difference between ByVal and i/a & j/b in what VBA is doing in the Subs? Do i&j/a&b represent an implicit ByVal ?

Thanks,

[Paul_Hossler]

From Help ...

ByValOptional. Indicates that the argument is passed by value.ByRefOptional. Indicates that the argument is passed by reference.ByRef is the default in Visual Basic.

Basically, ByRef passes the address of a variable, and ByVal passes a (one way) copy of the variable

Since it's the address that's passed with ByRef, the called sub can update the upper level element.

If a copy is passed (ByVal), the copied value is used inside the called sub, but it gets thrown away when the sub exits

Paul

[RonMcK]

In your example when you have:

[VBA]
Sub Lower1(a As Long, b As Long, ByRef c As Long)

[/VBA]

If ByRef is the VBA default, does this mean that 'a' and 'b' are also ByRef? Or, are they something else, entirely?

Thanks,

[Paul_Hossler]

They are also passed ByRef -- I only used the keyword explicitly on C to show the difference between ByRef and ByVal

Paul

[RonMcK]

Paul,

Thanks, so, to summarize, I can use the ByRef keyword to remind myself that is what I'm doing with the variable(s) I'm passing, however, I must use ByVal when I do not want to alter my variable in the calling program.

Thanks. I appreciate your point about avoiding global variables, it makes more sense to control them by specifically passing the ones so I know that the value is what it expect it to be.

Thanks,

[mdmackillop]

Hi Ron,
I would not go so far as "avoid", but use only when you need them. They can be useful when working between UserForms and between a Userform and other code modules.

[RonMcK]

Malcolm,

Thanks for the caution. Your example of UserForms reminded me that I needed and effectively used global variables a couple of years ago in using a UserForm to automate a process. So, let's modify my statement to use them with care. Will that work?

Cheers,

[Paul_Hossler]

Works for me

You do need to use them sometimes since there's no other way.

I was suggesting that debugging can become more complicated if you're not careful. If there are multiple subs on multiple modules all reading and setting a global variable, it would get hard to track down a problem

Paul

[RonMcK]

It's smart, as you point out, to reduce complexity rather than increase it unnecessarily. One can create a lot of self-inflicted wounds and grief that way.

Thanks for the reminder, Paul.