Passing an Array between Forms?

[Jason Paris]

Hi folks,

I'm using VBA to create a "Wizard" in Excel. The Wizard is simply a
collection of UserForms.

In the first Form, the user is asked to supply the for names for a
group of items. They do this by typing the names in to a Text Box,
one at a time.

For example, the first name is typed, the user presses an ADD button,
and the Text Box's current value is written to an Array element. The
Text Box is cleared, the user types another name, this is added to the
next Array element, and so on.....

When finished, the user clicks a NEXT button to advance to the next
Form in the Wizard. In this Form, I'd like to present (in a combo-
box) a list of all the elements in the Array. This gives the user a
chance to review their entries, and make corrections if necessary.
And here is my question.......

How can I make the Array from Form1 visible to Form2?

Kind regards,

Jason Paris

 

[Bob Phillips]

Declare the array as a public variable in a standard code module.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[NickHK]

Jason,
Whilst I would probably go with Bob's reply in this situation, have you
thought of redesign your UI to either:
- Allow reviewing of entries before the user leaves that form, but maybe you
will still need to pass an array any way..
- Use frames to show/hide the steps of the Wizard on a single userform.

As an alternative to the Public array and if you really want to pass the
array: As forms are really class modules, you can create public
properties/methods for them. e.g.

'<Form 2 code>
Dim Form2Array() As String

Public Property Let SetArrayValues(InValues() As String)
Form2Array = InValues
End Property

Public Property Get GetArrayValue(Index As String)
GetArrayValue = Form2Array(Index)
End Property
'</Form 2 code>

'<Form 1 code>
Private Sub CommandButton2_Click()

With UserForm2
.SetArrayValues = Split(TextBoxInput, vbNewLine)

MsgBox .GetArrayValue(2)
End With
End Sub
'</Form 1 code>

NickHK

 

[Bob Phillips]

But the properties method does require that the form stays in memory Nick,
i,e it is not unloaded (I know that you know, but the OP might not).

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[NickHK]

I did assume (possibly wrong) that the OP was aware of the lifetime issue.
Just emphasising the class nature of the userform.

NickHK