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]

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.

Hi again

But you are right, that's what I was referring to!

I think this thread is interesting but I think it's getting very confusing.

I think there are 2 different syntax questions being mixed up:

Question 1:
- how to access a public variable in another workbook (directly, like you access them when in the same workbook)

Question 2:
- how to pass a variable as a parameter to a routine in another workbook

I understood the original problem here was Question 1. This does not mean that Question 2 is not a solution to the problem, it's just a different approach.

So, coming back to Question 1, this is an example of what I meant.

1 - I created 2 workbooks and save them, Book1.xlsm and Book2.xlsm

2 - In Book1.xlsm I inserted a module and pasted:

Option Explicit
 
Sub Book1_Init()
 
s = "Hi!"
Set r = ThisWorkbook.Worksheets(1).Range("A1")
MsgBox "I'm in Book1" & vbLf & _
    "s: " & s & vbLf & _
    "r: " & r.Address(external:=True)
 
' calls a public procedure in Book2.xlm
Book2_Init
 
MsgBox "I'm in Book1 again" & vbLf & _
    "s: " & s & vbLf & _
    "r: " & r.Address(external:=True)
End Sub

Notice that the code uses 2 variables ( s and r) and 1 routine (Book2_Init) that are not declared. That's because they will be declared as public in the other workbook.

3 - In Book2.xlsm, I inserted a module and pasted:

Option Explicit
 
Public s As String
Public r As Range
 
Public Sub Book2_Init()
 
MsgBox "I'm in Book2" & vbLf & _
    "s: " & s & vbLf & _
    "r: " & r.Address(external:=True)
 
' changes the public variables
s = "Hi again!"
Set r = ThisWorkbook.Worksheets(1).Range("B2")
End Sub

4 - While a module of Book2.xlsm was active, I changed the project name (in Tools->VBAProject Properties) to VBAProject_Book2. This will allow me to identify the project easily, when I have several projects open.

5 - I activated a module in Book1.xlsm. In Tools->References I enabled the reference to VBAProject_Book2. This makes its variables directly accessable.

6 - I ran Book1_Init.



2 points to notice

- no syntax errors, the code knew that s, r and Book2_Init existed.

- check the values in the msgboxes. The variables are manipulated as if they were in the same workbook. That's how I think a public variable should behave.


Please test.

 

[Domenic]

Thanks pgc01, works like a charm. Thanks for having another look at this and taking the time to provide a step-by-step example. I really appreciate it. While I understand that this is not something that you and others would recommend, it's an important point to understand, nonetheless.

Cheers!

 

[pgc01]

I'm glad I was able to help.

Cheers!