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

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!

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

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

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

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.

Archive of Mr Excel Message Board

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

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

Thanks, but I dont want to use global variables!

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

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

Check out our Excel VBA Resources