Question about globally declared array not available in function

[ker_01]

I have one module and one userform (I'm asking this as a general question
first, to save the group from having to wade through some ugly code).

I declare a Global array at the top of the module

Global MyArray(0 to 3)

In my sub, I assign values
MyArray(0) = "A"

Then when a cmdbutton on the userform is clicked, some other code is run,
which then triggers a private function behind the userform. One of those
lines needs my variable, so I try to use it, and it is empty.

I set up a watch, and
Expression Value Type Context
MyArray(1) "A" String MyModule.MySubName
MyArray(1) <out of context> variant/empty MyUserForm.MyPrivateFunction

I do not declare the array anywhere other than the top of the module, and I
don't erase the array anywhere.

Why does Excel think these are two different arrays or variables (with two
different simultaneous values) instead of treating them as the same array,
accessed in two different places? What is the appropriate way to create a
truly accessible array in my sub so that the userform's private function can
access the values?

I don't want to pass the values directly, because the private function is
called from 10 different places, and I these values will be static after they
are assigned in the sub (they are assigned from worksheet values, so I can't
hardcode it)

Any advice is greatly appreciated!!
Thank you,
Keith

 

[Jim Thomlinson]

Based on your description there is nothing wrong with your code... here is
some code that I created as a test and it works

In a blank module add
Public MyArray(0 To 3) As String

Sub Populate()
MyArray(0) = "A"
MyArray(1) = "B"
MyArray(2) = "C"
MyArray(3) = "D"
End Sub

In a blank user form add...
Private Sub UserForm_Click()
Call Populate
MsgBox MyArray(0)
End Sub

Run the code in the userform and click on the form. The message box appears
showing A

A couple of possibilities...
Are you using option explicit in your code? If not then you might have
declared MyArray (spelled incorrectly) on the fly. If you have used the stand
alone line of code
End
That clears all global variables when executed.

 

[Joel]

A trick in passing variable into a userform is to make a textbox on the
userform for passing arguments (as text strings) and make the textbox
invisible. Nobody can see the textbox but the macro can read and write to an
invisible textbox.

When putting in arguements use Join() function and seperate the arguments
using commas. Then insie the userform use Split to seperate the CSV
parameters.

 

[Jim Thomlinson]

Why not just read from the globally defined array?

 

[ker_01]

Jim- thank you for your reply. Interestingly, my simplified test cases work
as well, so it must be something about my code.

I added a watch for MyArray(0) with a scope of all procedures/ all modules,
and it shows:

Expression Value Type Context
MyArray(1) Empty variant/empty
MyArray(1) "A" String MyModule.MySubName
MyArray(1) <out of context> variant/empty MyUserForm.MyPrivateFunction

When I get to the Private Function, the value on the third line changes to
"Empty".
The value of "A" remains for the module scope... so I'm thinking that
somehow I'm not correctly declaring the array, but I'm not sure how. I would
think that all three values should show "A" if it is truly acting like a
global/public variable.

One key difference between your test and my process is that you use the
private function to call the sub, whereas my sub populates the array, then
opens the userform, and the userform triggers the private function.

I suppose if I can't figure this out soon, I'll probably have to take the
piece of code that assigns MyArray values and just put it in a separate sub
so it can be called from the original sub and the private function (more
closely resembling your test case)

Thank you!
Keith

 

[ker_01]

Arggghhh.

When I take the code that assigns the array values and put it in a separate
sub:

Public MyArray(0 to 3) as string

Sub MainSub
SecondSub
Userform1.show
End Sub

Sub SecondSub
MyArray(1) = "A"
End Sub

Then when I return to MainSub I get:
Expression Value Type Context
MyArray(1) "A" String
MyArray(1) "" String MyModule.MySubName
MyArray(1) <out of context> Empty MyUserForm.MyPrivateFunction

I also checked with a debug.print of MyArray(1) (resulted in a blank/ no
value)- so the values are not even carrying over from sub to sub within the
same module.

 

[Joel]

I've run into problems multiple userforms send data between the forms. the
invisible text boxes solved the problems I was having.