dynamic array as public variable

[alkarkar]

Hi everyone,
How can I make a dynamic array public?
I have to Dim it so it stays valid only inside the sub.
The below code does not work

Public MyVar()

Sub test1()

Dim MyVar()
ReDim MyVar(1 To 4)
For x = 1 To 4
MyVar(x) = "ffffff"
Next x
End Sub

Sub test2()

For x = 1 To 4
Range("A" & x) = MyVar(x)
Next x
End Sub

Error I get is 'MyVar(x) = Subscript out of range'

 

[pgc01]

Help says that "Objects passed as arguments to the macro are converted to values (by applying the Value property to the object). This means that you cannot pass objects to macros by using the Run method."

That's not strictly true. Wrapping an array around the object will allow it to be passed.

I think your code is not passing the object, it's passing a copy of the object.

Ex., comparing changes to the object in the procedure in the external workbook and in the same procedure, but local:

' in workbook1
 
Sub callOtherBooksRoutine()
    Dim myArray(1 To 3) As Variant
 
    Set myArray(3) = ThisWorkbook.Worksheets(1).Range("a1")
 
    Application.Run "Workbook2.xlsm!foo", myArray
    MsgBox myArray(3).Address(, , , True)
 
    LocalFoo myArray
    MsgBox myArray(3).Address(, , , True)
End Sub
 
Sub LocalFoo(arrArgument As Variant)
 
    MsgBox arrArgument(3).Address(, , , True)
    Set arrArgument(3) = ThisWorkbook.Worksheets(2).Range("B1")
    MsgBox arrArgument(3).Address(, , , True)
End Sub
 
 
' in workbook2
Sub Foo(arrArgument As Variant)
 
MsgBox arrArgument(3).Address(, , , True)
Set arrArgument(3) = ThisWorkbook.Worksheets(2).Range("B1")
MsgBox arrArgument(3).Address(, , , True)
End Sub

 

[mikerickson]

The range object is being passed as the argument, but arguments passed by Run are always ByVal.

' workbook1 as above

' in workbook2
Sub Foo(arrArgument As Variant)
 
MsgBox arrArgument(3).Address(, , , True)
arrArgument(3).Value = "xxx"

Set arrArgument(3) = ThisWorkbook.Worksheets(2).Range("B1")

MsgBox arrArgument(3).Address(, , , True)
arrArgument(3).Value = "yyy"
End Sub

 

[Domenic]

Mike, pgc01,

Interesting discussion guys...

pgc01,

Oh, I see, you're talking about setting a reference to a workbook so that a variable can be passed to a procedure in that referenced workbook. I was actually talking about referring to a variable from another workbook, as per the example provided in the original post.

 

[GTO]

Mike, pgc01,

Interesting discussion guys...

...

Simply in agreeance with Domenic. Apologies to Mike, no chance to test (yet).

If one or both of you would care to expound upon ByVal, and an example or two of passing between workbooks (ie - actually global), that would be great!

Time permitting of course.

Apologies for not having handy, but what I have read was from Tushar; appeared very correct.

Mark

 

[GTO]

ACK!

...and ByRef...

 

[tusharm]

For as far as I can remember the documentation for the Run method has not reflected the actual implementation. Objects are *not* converted to values. Instead, they are passed ByVal.

When an object is passed ByVal (whether it is via the Run method or a call to a sub or a function) the code makes a copy of what's on the stack, i.e., the pointer (or handle) to the object. So, one can easily pass an object using the Run method w/o embedding the object in an array element.

I've used this technique for many years to pass arguments *by reference* using the Run method.

Function ABC(ByVal Y)
    Y.X = 1
    Set Y = Nothing
    End Function
Sub byRefCallee(ByRef Y)
    Set Y = Nothing
    End Sub
Sub Caller()
    Dim X As Class2: Set X = New Class2
    
    X.X = 2
    ABC X
    MsgBox X.X
    
    X.X = 10
    Application.Run "ABC", X
    MsgBox X.X
    
    X.X = 100
    Application.Run "byRefCallee", X
    MsgBox X Is Nothing

    byRefCallee X
    MsgBox X Is Nothing
    End Sub

Class2 has a single public element of type integer.

The range object is being passed as the argument, but arguments passed by Run are always ByVal.

' workbook1 as above

' in workbook2
Sub Foo(arrArgument As Variant)
 
MsgBox arrArgument(3).Address(, , , True)
arrArgument(3).Value = "xxx"

Set arrArgument(3) = ThisWorkbook.Worksheets(2).Range("B1")

MsgBox arrArgument(3).Address(, , , True)
arrArgument(3).Value = "yyy"
End Sub

 

[GTO]

@tusharm:

Thank you very much

Mark

 

[Domenic]

As a relative beginner when it comes to VBA, I'm trying to understand the example provided by Tushar in which an object is passed by reference to another procedure. When I try to run the code, "X As Class2" is highlighted in blue and I get the following error message...

Compile Error:

User-defined type not defined

What am I missing? Also, how would the following code change if I'm simply interested in passing a regular variable by reference to another procedure using application.run?

Sub Caller()
Dim x As Integer
x = 10
Application.Run "Book5.xlsx!ByRefCallee", x
MsgBox x
End Sub

Sub ByRefCallee(ByRef y)
y = y * 100
End Sub

 

[tusharm]

Hi Domenic,

Add a new class module and make sure it is called Class2. In it, add a public x as integer

You cannot pass a scalar variable (such as an integer) as a byval argument and have the changed value returned to the caller. It works when the variable is part of an object because now the code makes a copy, not of the variable, but of the handle to the object.

As a relative beginner when it comes to VBA, I'm trying to understand the example provided by Tushar in which an object is passed by reference to another procedure. When I try to run the code, "X As Class2" is highlighted in blue and I get the following error message...

Compile Error:

User-defined type not defined

What am I missing? Also, how would the following code change if I'm simply interested in passing a regular variable by reference to another procedure using application.run?

Sub Caller()
Dim x As Integer
x = 10
Application.Run "Book5.xlsx!ByRefCallee", x
MsgBox x
End Sub

Sub ByRefCallee(ByRef y)
y = y * 100
End Sub

 

[Domenic]

Hi Tushar,

An interesting technique. Works like a charm. Thank you very much for your help.

Cheers!