PDA

View Full Version : 2D Array of Arrays with User Defined Type



erosergun
05-28-2011, 11:24 AM
Hi guys,

My problem seems to be quite unique as I have failed to find a solution yet after visiting many VBA forums. Let me try my chance here as well.

The following code generates an error: "Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound function"

What is wrong here? It drives me nuts for days already.

Many thanks in advance



Type myType ' for the 2nd index of my 2D array holding different data types
x As Integer
y As Integer
str As String
lng As Long
dbl As Double
End Type

Sub Test()
Dim ArraySub() As myType '2nd index of my 2D array
Dim ArrayMain(1 To 1000) As Variant '1st index of my 2D array

For i = 1 To 1000
ReDim ArraySub(1 To 5) As myType
ArrayMain(i) = ArraySub
Next

'Test if I managed to create a 2D array of different data types

ArrayMain(1)(1).x = 10
ArrayMain(1)(3).y = 20

ArrayMain(1)(5).str = "hello"
ArrayMain(1000)(2).lng = 12345678
ArrayMain(1000)(5).dbl = 123456.123456
MsgBox (CStr(ArrayMain(1)(1).x) & " " & CStr(ArrayMain(1)(3).y) & " " & ArrayMain(1)(5).str & " " & CStr(ArrayMain(1000)(2).lng) & " " & CStr(ArrayMain(1000)(5).dbl))
End Sub

mikerickson
05-28-2011, 11:58 AM
Elements of ArrayMain must be of data type Variant.

A variable of data type variant cannot hold a User Defined Type.

Therefore, no element of ArrayMain can be a value of myType.

Excel's implementation of custom data types is quirky (to be polite).
A custom Class might work better for you.

stanleydgrom
05-28-2011, 12:02 PM
erosergun,

Welcome to the VBA Express forum.


Try:




Option Explicit
Sub Test()
' stanleydgrom, 05/28/2011
' http://www.vbaexpress.com/forum/showthread.php?t=37597
Dim ArrayMain(1 To 1000, 1 To 5) As Variant
ArrayMain(1, 1) = 10
ArrayMain(1, 3) = 20
ArrayMain(1, 5) = "hello"
ArrayMain(1000, 2) = 12345678
ArrayMain(1000, 5) = 123456.123456
MsgBox (CStr(ArrayMain(1, 1)) & " " & CStr(ArrayMain(1, 3)) & " " & ArrayMain(1, 5) & " " & CStr(ArrayMain(1000, 2)) & " " & CStr(ArrayMain(1000, 5)))
End Sub




Have a great day,
Stan

erosergun
05-28-2011, 12:05 PM
Many thanks for your promt return Mike,

I was in front of my pc just think what to do next. I got stuck right here and cannot move for days already.

I wish I could understand what you mean by saying using "custom class". How can I achieve this? Could you have any source you could recommend for me to digest and find a way out.

many thanks again

erosergun
05-28-2011, 12:18 PM
Oh another response! :) How nice after waiting several days in other forums for a reasonable solution.

Stan,

Your code is working. However, me using a custom type is because of vital necessity, whitout it my code does not work out. I have to find a way using my custom type as demonstrated in my previous code.

With your code it seems we have lost IntelliSense. Even though I am not quite sure what it means, i think it is kind of a built-in system to match variable and data types. And I think this is something that we don't omit. Right?

I would love to go with Mike's route using custom class if you guys could give me a hand regarding relevant resourses or a typical code sample.

Many many many thanks again. Your promt response is highly appreciated.

Kind regards,

mikerickson
05-28-2011, 12:41 PM
Custom Classes are achieved through Class modules.
They are designed to allow the user to create custom Objects.

Insert a Class Module.
Name it myType with the Properties window
Put this in the class module

Public x As Integer
Public y As Integer
Public str As String
Public lng As Long
Public dbl As Double

erosergun
05-28-2011, 01:46 PM
Gush! I am almost there! In fact, after several trial and error, the following code actually works! Unbelievable! However, I am not sure if I successfully instantiated ArrayChild and if I had to use a loop which runs 5000 times. I finally need an expert's final consent to make my code shorter and more efficient. :)

Thank you Mike! Your custom class works like a charm. The best of all, variable type control came back. It was not working for variant type 2D array as suggested by Stan. Now I can reach out all the elements the way I dream of.


Sub TestClass()
Dim ArrayParent(1 To 1000) As Variant '1st index of my 2D array
Dim i As Integer, j As Integer
Dim ArrayChild(1 To 5) As myType '2nd index of my 2D array
'Dim obj As myType

For i = 1 To 1000
'ReDim ArrayChild(1 To 5)
For j = 1 To 5
'Set obj = New myType
'Set ArrayChild(j) = obj
Set ArrayChild(j) = New myType
Next j
ArrayParent(i) = ArrayChild
Next i


ArrayParent(1)(1).x = 10
ArrayParent(1)(3).y = 20
ArrayParent(1)(5).str = "hello"
ArrayParent(1000)(2).lng = 12345678
ArrayParent(1000)(5).dbl = 123456.123456

MsgBox (CStr(ArrayParent(1)(1).x) & " " & CStr(ArrayParent(1)(3).y) & " " & ArrayParent(1)(5).str & " " & CStr(ArrayParent(1000)(2).lng) & " " & CStr(ArrayParent(1000)(5).dbl))

erosergun
05-28-2011, 01:48 PM
I cannot thank you enough!!! :) :) :)

Will be waiting for your final consent

Kind regards,

mikerickson
05-29-2011, 02:07 AM
Class modules make objects. If you are going to have 5000 of them, you do have to create 5000 seperate instances of the object.

One difference between a data type and an object is that even if the properties of two objects are the same, they are not the same object.

Consider two identical worksheets are not the same sheet.

Now, with data types, all 2's are the same whether they are 3-1 or 1+1 all 2's are the same. You don't need different instances of 2.

So yes, you need 5000 instances.
I'd consider ditching the custom type or object and go with an indexed array.

erosergun
05-29-2011, 03:54 AM
Mike,

After reading your last message, I still am not sure if I understand you correctly.

You say I should consider using arrays instead of objects. However,

If I use the object approach using custom class module as you suggested before, I can reach out the data elements in a very conventional way, like myArray(10)(5).str which is clearly 10th row, 5th column and the string variable of the type myType. Moreover, I cannot assign accidentally, say a long to a string element, for instance, myArray(10)(5).str=10 generates a compile error which is fine as it provides auto-control during my coding.

On the other hand, if I use the array approach as you have just suggested, the notation for the array elements in my example becomes myArray(10,10,3) which is equivalent to the above string element, being the 3rd item of myType. I have to be very very careful if I assign correct values to correct items. The number 3 does not say it is a string. Even worse, during my code writing I may accidentally assign a number to this string item. For instance, while myArray(10,10,3)="hello", I could accidentally type in my code myArray(10,10,3)=10 and no error is generated by the compiler. Because, in the array approach all items have to be variant type data type and once they are variant, data type control does not work out.

Among these 2 approaches I obviously chose the object approach for my particular needs.

Therefore, I am trying to understand if you are warning me of some other risks which I might come up with if I use the object approach which in fact seems to be working great. What might go wrong as a result of your warning regarding data types and objects? Stack overflow, incorrect data type assignments? I just don't want to disregard an experts warning and jeopardize the health of my code, which might eventually result in untraceable and complicated bugs and impossible to get rid of.

Many thanks again