VBA - Passing Values by reference; Why doesnt it work??

Posted by John A. McGraw on January 08, 2002 9:43 PM

I am trying to manipulate a variable from a calling procedure in the called procedure. I cant get it to work! MS Help says:

"As a result, the variable's actual value can be changed by the procedure to which it is passed. Unless otherwise specified, arguments are passed by reference."

But for example, in the following simple code, this does not seem to be the case:

-----
Sub sub1()
Dim i As Integer
i = 10
MsgBox (i)
sub2 (i)
MsgBox (i)
End Sub

Sub sub2(ByRef i as Integer)
i = 20
End Sub
-----

If I run "sub1", both message boxes show "10". Why was sub2 not able to change i to 20? I cant figure it out! VBA Help, like I quoted above, said that it should!

Posted by Jacob on January 08, 2002 10:05 PM

Try this:

Dim i As Integer

Sub sub1()

i = 10
MsgBox (i)
sub2
MsgBox (i)
End Sub

Sub sub2()
i = 20
End Sub

Put the Dim i above both subs and you dont call with variables this will retain the values between subs.

HTH

Jacob

Posted by John A. McGraw on January 08, 2002 10:59 PM

Thanks, but I dont want to use global variables!

But that creates a global variable, that is what I DONT want. I want to pass a reference and change the actual value of the variable. According to all documentation I read, this should be possible.

A long time ago when I used to program a little in C, you would "pass a pointer". That is basically what I want to do.

I am a large program, and if I use global variables it will be a horrible mess. i = 10 MsgBox (i) sub2 MsgBox (i) i = 20

Posted by Ivan F Moala on January 09, 2002 12:44 AM

Re: Thanks, but I dont want to use global variables!

The syntax is not correct
1) No need to declare ByRef as this is the default
2) Remove Brackets

Also declaring varibles at the module level as Jacob suggested
allows only the variable to be avail to that module only, unless declared as Public. So it won't
be a horrible mess
You can use the Private statement to declare private module-level variables.
Private variables can be used only by procedures in the same module.
BUT When used at the module level, the Dim statement is equivalent to the Private statement. You might want to use the Private statement to make your code easier to read and interpret

so code reads;

Sub sub1()
Dim i As Integer
i = 10
MsgBox i
sub2 i
MsgBox i
End Sub

Sub sub2(i As Integer)
i = 20
End Sub

HTH

Ivan But that creates a global variable, that is what I DONT want. I want to pass a reference and change the actual value of the variable. According to all documentation I read, this should be possible. A long time ago when I used to program a little in C, you would "pass a pointer". That is basically what I want to do. I am a large program, and if I use global variables it will be a horrible mess. : Hi : Try this

Posted by Juan Pablo G. on January 09, 2002 5:53 AM

Re: Thanks, but I dont want to use global variables!

I agree with Ivan. Here's what worked for me

Sub sub1()
Dim i As Integer
i = 10
MsgBox i
sub2 i
MsgBox i
End Sub

Sub sub2(i As Integer)
i = 20
End Sub

Juan Pablo G. The syntax is not correct