Dim Elements of Array

MikeDBMan

Well-known Member
Joined
Nov 10, 2010
Messages
608
If I dimension an array, DataArray(50,5) as variant, I wish to have each of the 5 elements of the second dimension have certain types other than Variant. For example, the 1st element should be a string. The second should be Double, the third long, the fourth String and the 5th long.
Someone gave me a suggestion a while back on a method for doing this but I can't find it now. Suggestions?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi Mike

Define a user type with those elements and then define an array of that type.
 
Upvote 0
Define a user type? Would you kindly give me a short example? Thanks!!!!
The Type declaration is placed in the (General)(Declarations) section of the code window... that is, at the top of the code window, but not inside any Sub or Function procedures. You can have more than one Type declaration if needed, just give them different names. Here is an example...

Type MyType
First As String
Second As Double
Third As Long
Fourth As String
Fifth As Long
End Type

MyType is an example name for the Type block... you can give it any name you want (follow the naming rules for variables). The First, Second, Third, Fourth and Fifth are example variable names... you can call your Type elements by any (legal variable) name you want. Remember, the above declaration is done outside of any procedures. Inside a procedure, you would declare your variable or array to be of the name you gave your Type. So, if you wanted to declare an array of 3 elements of this Type, your declaration could look like this...

Dim MyArray(1 To 3) As MyType

Then, to assign values to the Type elements, you could do something like this...

MyArray(1).First = "Hello"
MyArray(1).Second = 1.23
MyArray(1).Third = 12345678
MyArray(1).Fourth = "Good-bye"
MyArray(1).Fifth = 987654
MyArray(2).First = "Aloha"
MyArray(3).Second = 9.8765
etc.

You might also find the VB help files useful. Type the word "type" into the Immediate Window and, with the text cursor in or next to the word, press F1 and select the VBA entry from the popup menu that appears.
 
Upvote 0
I think that is similar to what someone else had shown me a while back. That will certainly do the trick. Thanks to both of you for sharing!
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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 MrExcel.com.
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 "mrexcel.com".
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
Back
Top