Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home





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!


Check out our Excel VBA Resources

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

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

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


Thanks, but I dont want to use global variables!

Posted by John A. McGraw on January 08, 2002 10:59 PM
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


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

Posted by Ivan F Moala on January 09, 2002 12:44 AM
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


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

Posted by Juan Pablo G. on January 09, 2002 5:53 AM
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




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.