Hi there,

I have a Sub in a form, which runs when a commandbutton is pressed. In the
Sub an array is dimmed and filled with values.

e.g. Dim NANames(1 To 20, 0 To 2)

I need the values of the Array in another Sub, but the array is only
available in the specific Sub when created. Declaring it Public is not
possible with an array, so I need another way of doing this.

Anyone ?

Thanks !

You could make it a public variable within a Standard Module

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Fred" wrote in message
...
Hi there,

I have a Sub in a form, which runs when a commandbutton is pressed. In the
Sub an array is dimmed and filled with values.

e.g. Dim NANames(1 To 20, 0 To 2)

I need the values of the Array in another Sub, but the array is only
available in the specific Sub when created. Declaring it Public is not
possible with an array, so I need another way of doing this.

Anyone ?

Thanks !

Rob,

thanks for the quick reaction ! But....
I declared it public in the main module which starts (load/show) the form on
which the array is filled. At the end of code execution of the Sub on the
form the Array is alive and filled with the correct values, but when the Sub
ends (the form is still active) the values disappear...


???

"Rob van Gelder" wrote in message
...
You could make it a public variable within a Standard Module

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Fred" wrote in message
...
Hi there,

I have a Sub in a form, which runs when a commandbutton is pressed. In
the
Sub an array is dimmed and filled with values.

e.g. Dim NANames(1 To 20, 0 To 2)

I need the values of the Array in another Sub, but the array is only
available in the specific Sub when created. Declaring it Public is not
possible with an array, so I need another way of doing this.

Anyone ?

Thanks !

Here's an example:

Module Code:

Public arr() As String

Sub test()
Dim i As Long
UserForm1.Show '(assuming your userform is named UserForm1)

For i = LBound(arr) To UBound(arr)
Debug.Print arr(i)
Next
End Sub


UserForm1 Code :

Private Sub UserForm_Initialize()
Dim i As Long

ReDim arr(50)

For i = LBound(arr) To UBound(arr)
arr(i) = "string " & i
Next
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Fred" wrote in message
...
Rob,

thanks for the quick reaction ! But....
I declared it public in the main module which starts (load/show) the form
on
which the array is filled. At the end of code execution of the Sub on the
form the Array is alive and filled with the correct values, but when the
Sub
ends (the form is still active) the values disappear...


???

"Rob van Gelder" wrote in message
...
You could make it a public variable within a Standard Module

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Fred" wrote in message
...
Hi there,

I have a Sub in a form, which runs when a commandbutton is pressed. In
the
Sub an array is dimmed and filled with values.

e.g. Dim NANames(1 To 20, 0 To 2)

I need the values of the Array in another Sub, but the array is only
available in the specific Sub when created. Declaring it Public is not
possible with an array, so I need another way of doing this.

Anyone ?

Thanks !

Rob,

it works ! Thanks.
I had forgotten to take the Dim statement out of the Sub on the form...


"Rob van Gelder" wrote in message
...
Here's an example:

Module Code:

Public arr() As String

Sub test()
Dim i As Long
UserForm1.Show '(assuming your userform is named UserForm1)

For i = LBound(arr) To UBound(arr)
Debug.Print arr(i)
Next
End Sub


UserForm1 Code :

Private Sub UserForm_Initialize()
Dim i As Long

ReDim arr(50)

For i = LBound(arr) To UBound(arr)
arr(i) = "string " & i
Next
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Fred" wrote in message
...
Rob,

thanks for the quick reaction ! But....
I declared it public in the main module which starts (load/show) the
form
on
which the array is filled. At the end of code execution of the Sub on
the
form the Array is alive and filled with the correct values, but when the
Sub
ends (the form is still active) the values disappear...


???

"Rob van Gelder" wrote in
message
...
You could make it a public variable within a Standard Module

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Fred" wrote in message
...
Hi there,

I have a Sub in a form, which runs when a commandbutton is pressed.
In
the
Sub an array is dimmed and filled with values.

e.g. Dim NANames(1 To 20, 0 To 2)

I need the values of the Array in another Sub, but the array is only
available in the specific Sub when created. Declaring it Public is
not
possible with an array, so I need another way of doing this.

Anyone ?

Thanks !

Water it regularly and make sure it gets plenty of sunlight.

(Sorry, I just couldn't resist ;)

Fred wrote:
* Declaring it Public is not
possible with an array*

Why not, but you have to declare it in Module1 (Or whatever the mai
module is).

I have a similar problem, I have a public array which is used for
different userforms so there is no point filling the array twice (Whic
is 70x3) everytime I open a userform I filled it when opening th
workbook but for some reason when you press stop button on debugger an
some other times I have to keep running the procedure to fill it sinc
it is emptied for some reason.

Best Regards

Noo

--
Message posted from http://www.ExcelForum.com