A problem with user defined data type

[SMC]

I'm creating certain user-form for a data input.
For that purpose i defined a data type.
If i put its definition in the public area:

Public area:
Type DataX     'defining data type which has to contain everything about a product
    PriceX As Integer
    ColorX() As Variant
    SizeX() As Variant
End Type
.
.
.
Private Sub Fill()
Dim Products() As DataX
                    
Products(0).PriceX = 25
Products(0).ColorX = Array("red", "blue")
Products(0).SizeX = Array("L", "XL", "XXL")

i get:

Compile Error:
Cannot define a public user-defined type within an object module

But if i move it (Type definition) to the Private Sub i get:

Compile Error:
Invalid inside procedure

How do i fill my array of arrays ?


Many thanks in advance

[lucas]

Could you post a sample of your workbook with the form so we can figure out what your trying to populate, etc. Please give a little more of an explaination of what your trying to do exactly as I think your going about this the hard way so far......

[JimmyTheHand]

Hi SMC, wlcome to VBAX

Guess you should move the type definition to a standard code module (e.g. Module1). Also I would try using Public Type statement, just to be sure.

Jimmy

[SMC]

Huh, where to start from?

What i have done so far is not enough to you to conclude what i wanna do.

So, i'm creating a user-form for populating orders.
Each product has its code, price, avilable colors(array) and available sizes(array).

Some array should contain all these data (1or2 ints and 2 arrays).
I should populate this array to prepare it for a later use by the user.

I think i will manage all this somehow, but i need to resolve the problem with the custom data type.

Hope this was of some help.

As for JimmyTheHand's suggestion:
that's one of the things which are not clear enough to me.
I'm writing all of code in the Forms section (on the My-form), so how can i bind it with certain module (e.g. Module1)?
/Adding 'Public' did not resolve the problem - i get the same message as in first mentioned case/


Thanks a lot for the replies

[lucas]

Ok then......
try what Jimmy suggested. Define it publicly. Put your definitions at the very top of the userform module, outside of any modules:
[vba]
Option Explicit
Dim PriceX As Integer
Dim ColorX() As Variant
Dim SizeX() As Variant
[/vba]
I really don't have enough info to tackle this. Maybe someone will come along that understands what your trying to do.

[JimmyTheHand]

I'm not 100% on the terms, but my picture is this:
There several kinds of modules in VBA. One is Object Module, which belong essentially to userforms and sheets. If you rightclick on any userform or sheet or thisworkbook object, and then select view code, you will get to an Object Module. These are, primarily, used to store object-related code, e.g. event handlers.

If you select from menu Insert -> Module, the you get a new "Folder" in the Project Explorer window, called Modules, and in there you see a newly created Standard Code Module, with the default name of Module1. Or Module2, 3, etc. Such modules are automatically created whenever you record a macro in excel, and are used to store all kinds of code, except those that belong to an Object Module.

So I was suggesting to move the Type declaration to a Standard Code Module. In case you don't have one, use Insret / Module command from menu.

HTH

Jimmy

[JimmyTheHand]

Something happened to my browser and I sent the same post twice... I'm sorry.

[Bob Phillips]

Ditch the UDT, they are severely flawed and n ot worth bothering with. Create a simple class instead.

[JonPeltier]

Not much helpful help yet.

Here is the complete text of a module using your UDT which successfully populated the properties of the first Products member. I suspect you didn't redim the Products array.

[vba]Option Explicit

Type DataX
PriceX As Integer
ColorX() As Variant
SizeX() As Variant
End Type

Sub TestUDT()
Dim Products() As DataX

ReDim Products(0 To 1)

Products(0).PriceX = 25
Products(0).ColorX = Array("red", "blue")
Products(0).SizeX = Array("L", "XL", "XXL")

End Sub
[/vba]

[SMC]

My apologize for the delay.

I have moved (JimmyTheHand) type definition to Module,
and the code now (basically) works.
Now i have to work it (complicate it ) out (using JonPeltier's code),
and coming back soon for sure with more Q's.

Thanks a lot to all who replied!!

xld:
i would apply a class instead of udt if i only knew it.
actually, i was working a bit on OO programming (c++), but i forgot the most.
i think it would take more time to refresh 'class' knowledge than to use udt.
thanks for the good advice anyway.

[SMC]

How do i ask if certain cell is empty?

I need something like this:

Do Until (Worksheets("SheetX").Cells(i, 1 + step).Value = "")

but it doesn't work.

Thanks

[JonPeltier]

Does it work without the parens?

[vba]

Do Until Worksheets("SheetX").Cells(i, 1 + step).Value = ""
[/vba]

Also, does that sheet exist, and are i and step valid in that context?

[SMC]

OK thanks, i have solved that already, with the IsEmpty function:
Do Until IsEmpty(Worksheets("SheetX").Cells(i, 1 + step))
/'i' and 'step' are defined/

But the next problem is:
although i have populated my array successfuly (i think), if i include the following msgbox at the end of the Private Sub (for testing):

MsgBox (Products(2).ColorX(1))

i get:
Run-time error '9':
Subscript out of range

Without this MsgBox line i have no error messages.

what is that meaning?

[SMC]

OK here is entire code:
[vba]Module1:
Type DataX
CodeX As Variant
PriceX As Variant
ColorX() As Variant
SizeX() As Variant
End Type[/vba][vba]
MyForm:

Private Sub Input_Click()
Call ArrayFill
End Sub

Private Sub ArrayFill()
Dim cod As Variant
Dim pric As Variant
Dim colors() As Variant
Dim sizes() As Variant
Dim i As Integer
Dim j As Integer
Dim z As Integer
Dim stepx As Integer
stepx = 0
i = 2
j = 2
z = 0
Dim Products() As DataX

Do Until IsEmpty(Worksheets("SheetX").Cells(i, 1 + stepx))

cod = Worksheets("SheetX").Cells(1, 1 + stepx).Value 'stepx must be 2
pric = Worksheets("SheetX").Cells(1, 2 + stepx).Value

'jump over the first row (which contains code & price)
Do Until IsEmpty(Worksheets("SheetX").Cells(i, 1 + stepx)) 'odd columns in 'SheetX'
ReDim Preserve colors(i - 1) '1 elem for start
colors(i - 2) = Worksheets("SheetX").Cells(i, 1 + stepx).Value
i = i + 1
Loop

Do Until IsEmpty(Worksheets("SheetX").Cells(j, 2 + stepx)) 'even columns in 'SheetX''
ReDim Preserve sizes(j - 1) '1 elem for start
sizes(j - 2) = Worksheets("SheetX").Cells(i, 1 + stepx).Value
j = j + 1
Loop

ReDim Preserve Products(z + 1) 'populating the main array
Products(z).CodeX = cod
Products(z).PriceX = pric
Products(z).ColorX = colors
Products(z).SizeX = sizes

stepx = stepx + 2
z = z + 1

Loop

MsgBox (Products(2).ColorX(1))

End Sub[/vba]

Run-time error '9':
Subscript out of range

..yellow marked MsgBox line !!

I enclose SheetX as an attachment.


Thanks

[tstom]

You should separate your logic. Note the function to return individual elements of your Products array. If your UDT is only needed within your userform, then just keep it there as private. Note the Option Base 1. You appear to be set on using base 1 arrays.

[vba]
Option Explicit
Option Base 1


Private Type DataX
CodeX As String
PriceX As Currency
ColorX() As Variant
SizeX() As Variant
End Type


Private Sub Input_Click()
Call ArrayFill
End Sub


Private Sub ArrayFill()
Dim r As Range, Products() As DataX

'initial dim
ReDim Products(1) As DataX

'set to the range of your first product code
Set r = [a1]

Do Until r = ""
Products(UBound(Products)) = ReturnDataX(r)
ReDim Preserve Products(UBound(Products) + 1) As DataX
Set r = r.Offset(, 2)
Loop

ReDim Preserve Products(UBound(Products) - 1) As DataX

Call TestUDTArray(Products)

End Sub


Private Function ReturnDataX(Code As Range) As DataX
Dim rdx As DataX
'you should validate your data here
rdx.CodeX = Code.Text
rdx.PriceX = Code.Offset(, 1).Value
rdx.ColorX = Application.Transpose(Range(Code.Offset(1), Code.Offset(1).End(xlDown)))
rdx.SizeX = Application.Transpose(Range(Code.Offset(1, 1), Code.Offset(1, 1).End(xlDown)))
ReturnDataX = rdx
End Function


'temp procedure to test
Private Sub TestUDTArray(Products() As DataX)
Dim x, y, s, ss

For x = LBound(Products) To UBound(Products)
Debug.Print "----------------------------------------------------"
Debug.Print Products(x).CodeX, Products(x).PriceX
On Error Resume Next
y = 1
Do
s = "": ss = ""
s = CStr(Products(x).ColorX(y))
ss = CStr(Products(x).SizeX(y))
Debug.Print s, ss
y = y + 1
Loop Until (s & ss) = ""
Next
End Sub

[/vba]

[SMC]

tstom,thanks a lot for your time and this fantastic solution.

I have applied it, but Debug.Print command doesn't work to me (get no popup window).
I suspected my firewall and turned it off, but with no avail.
Therefore i've put a MsgBox in TestUDTArray Sub:
[vba].
.
s = "": ss = ""
s = CStr(Products(x).ColorX(y))
ss = CStr(Products(x).SizeX(y))
Debug.Print s, ss
y = y + 1
MsgBox (Products(x).ColorX(y))
.
.[/vba]
and i've noticed that the first elements of ColorX and SizeX are omited (although the Option Base is set to 1) (??)


Thanks

p.s.
Instead of 'Set r = [a1]' i've put:
Set r = [Worksheets("SheetX").Range("A1")]

[tstom]

Debug.Print prints to the immediates window. While in VBA, press CTRL>G to bring this window up. The code was tested on the example spreadsheet pic you posted earlier with the correct results...

[SMC]

Ok my bad, i've found it (postion of MsgBox).
And i didn't know that debug.print shows up only in immediate window.

All in all it works perfect.

Many thanks again tstom , i'm coming back soon with more Q's about this Project.

[SMC]

I've tried to develop this project further, but i'm kinda stuck with it.

I've attached xls with the 'OrderForm'.

What i need is to forward all available product codes to the 'Product' Combo.
I'd do this either by populating some sheet area with the row of code values (from Products() array), or by forwarding the CodeX elements from my Products() array (somehow) directly to Combo.

*Don't pay attention to 'Print New Header' button, because i resolved that already*

And now comes the best:
Depending on chosen product code, corresponding:
1) price (on price TextBox)
2) color array (on color combo)
3) size array (on size combo)

should be available to user.

I hope it?s not too impudent to ask so much help.

Any assistance would be greatly appreciated.

[tstom]

I changed your UDT into a custom class. There is really no good reason to use a UDT in VBA unless you are working with random access files. Open the attached to see the immediate benefits such as encapsulated validation and easier code on the working end in your userform.