Passing from a from array to a module array


New Member
Jan 6, 2005
I have a small problem I'm sure is easy finger/Brain trouble on my side. I have a variable array DistArray(5, 30) located in a UserForm which works fine, but I wish to pass this data back to an array in a module and I'm having no success.

It's declared as follows in the UserForm

<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> DistArray(5, 30) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> DistArray1(5, 1)
<SPAN style="color:#00007F">Dim</SPAN> DistArray2(5, 1)
<SPAN style="color:#00007F">Dim</SPAN> DistArray3(5, 1)
<SPAN style="color:#00007F">Dim</SPAN> DistArray4(5, 1)
<SPAN style="color:#00007F">Dim</SPAN> DistArray5(5, 1)
<SPAN style="color:#00007F">Dim</SPAN> DistArray6(5, 1)</FONT>

In the Module called Create_UserData I have declared a procedure as below
<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> AR_Store()
<SPAN style="color:#00007F">Dim</SPAN> Ar_Str(5, 30) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN></FONT>

To pass this data I have used the following code, but It doesn't recognise the array name. Does anyone know why??

<font face=Courier New><SPAN style="color:#00007F">For</SPAN> P = 0 <SPAN style="color:#00007F">To</SPAN> 4
<SPAN style="color:#00007F">For</SPAN> M = 1 <SPAN style="color:#00007F">To</SPAN> 30

Create_UserData.AR_Store(P, M) = DistArray(P, M)
<SPAN style="color:#00007F">Next</SPAN> M
<SPAN style="color:#00007F">Next</SPAN> P</FONT>


DaveFish :eek:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
This line of code is wrong
Create_UserData.AR_Store(P, M) = DistArray(P, M)
Create_UserData.AR_Str(P, M) = DistArray(P, M)
Also, If you are passing info from the module to the userform you need to declare distarray as a public dim in the module. The Ar_str array if only used in the module does not need to be public. Hope this helps. Dave
edit: ps Welcome to the Board
Upvote 0
Hi Dave,

I checked the code and made the changes you suggest, but when I run it I get "Method or data member not found" error box. I checked the declaration and I am publicly declared in the module and form. Can you think of any other fixes??

daveFish :)
Upvote 0
Well I'm really not sure what this part of your code is supposed to do
This works
AR_Str(P, M) = DistArray(P, M)
Upvote 0
Hmm.. I wonder. The Create_UserData is the name I've given to a module holding other functions and public subs. I'll try using a dedicated module and get back to you

Davefish :eek:
Upvote 0
Hi Dave,

I created a new module calle Ar_Test with the following code:-

Public Sub TET()
Dim Ar_Str(5, 30) As Variant
Dim P As Integer
Dim M As Integer

For P = 0 To 4
For M = 0 To 30
Ar_Str(P, M) = DistArray(P, M)

Debug.Print Ar_Str(P, M)

Next B
Next A

End Sub

I added the following declartion to the Userform:-

Public Sub Dp_Cmd_Add_Click()
Dim Prot1 As String
Dim Com1 As String
Dim Pole As String
Dim X As Integer
Dim Y As Integer
Dim j As Long
Dim T As Long
Dim P As Integer
Dim M As Integer
Dim Ar_Str(5, 30) As Variant

And left the code within the module as :-

For P = 0 To 4
For M = 1 To 30

Ar_Str(P, M) = DistArray(P, M)

Next M
Next P

This doesn't generate any error's and although my debug.print shows the DistArray working it still isn't passing the data to the module.


DaveFish :wink:

Thanks for your help so far.
Upvote 0
You're going to have to offer a bit more info. What data are you passing from the userform to the module? If the distarray is being passed then it needs to be declared in the module as a public dim and the Ar_str also needs to be declared in the module as a variable. Dave
Upvote 0
Ok. without posting all the code I'll offer an explanation. The UserForm code has a declared main array, DistArray(5, 30) as Variant. It's fed from a number of smaller arrays as below:-

Dim DistArray(5, 30) As Variant
Dim DistArray1(5, 1)
Dim DistArray2(5, 1)
Dim DistArray3(5, 1)
Dim DistArray4(5, 1)
Dim DistArray5(5, 1)

The data in each small array is passed to DistArray and also a textbox to view the data with the five heading as shown

DistArray(0, 0) = "Cct Number"
DistArray(1, 0) = "Rating"
DistArray(2, 0) = "Fuse/CB"
DistArray(3, 0) = "Trip/Ind"
DistArray(4, 0) = "Pole"

For X = 0 To 4 Step 1
DistArray(X, 1) = DistArray1(X, 0)
DistArray(X, 2) = DistArray2(X, 0)
DistArray(X, 3) = DistArray3(X, 0)
DistArray(X, 4) = DistArray4(X, 0)
DistArray(X, 5) = DistArray5(X, 0)
DistArray(X, 6) = DistArray6(X, 0)
DistArray(X, 7) = DistArray7(X, 0)
DistArray(X, 8) = DistArray8(X, 0)

The data passed is both text and numeric and was passed to the each small array from a combination of comboboxes and checkboxes. All of this works OK within the form, and all I wish is to pass this to a module if possible. Does this help? if not please let me know what you need and maybe I can mail the file to you??


Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back