A problem with user defined data type

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:

Code:

---

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:

Quote:

---

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:

Quote:

---

Compile Error:
Invalid inside procedure

---

How do i fill my array of arrays ?:help


Many thanks in advance

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......

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

Huh, where to start from? :think:

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 :)

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.

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

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

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

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]

My apologize for the delay.

I have moved (:thumbJimmyTheHand) type definition to Module,
and the code now (basically) works.
Now i have to work it (complicate it :*)) out (using JonPeltier's code):thumb,
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.:doh:
i think it would take more time to refresh 'class' knowledge than to use udt.
thanks for the good advice anyway.

How do i ask if certain cell is empty? :doh:

I need something like this:

Code:

---

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

---

but it doesn't work.

Thanks

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?

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):

Code:

---

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

---

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

Without this MsgBox line i have no error messages.

what is that meaning?

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]

Quote:

---

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

---

..yellow marked MsgBox line !!

I enclose SheetX as an attachment.


Thanks

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]

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

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) (??):think:


Thanks

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

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...


http://home.fuse.net/tstom/Screen Grab1.jpg

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.:thumb

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

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

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.:dunno

Any assistance would be greatly appreciated.:hi:

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.

What to say but: FANTASTIC !!
:clap: :clap: :clap: :clap: :clap: :clap: :clap: :clap: :clap:

tstom, i really cannot thank you enough for your help.

Now i must study this redefined code out, and maybe come back with some questions.


Greetings :friends:

hi tstom,
i can't see where is DataX class declared.
In the class module i see DataX properties with Get & Let methods defined, but there's no DataX class name ?? :wot

As i mentioned before i forgot the most of OO syntax, but still i'm a bit surprised that i don't have to explicitely call Get & Let methods within the OrderForm module. Somehow they are called implicitely - preety nifty (but how?). :think:

Thanks

How to make chosen DataXCollection(Product.text) /from Product_Change() Sub/ public ?

because i need:
dx.ColorX
dx.SizeX
arrays available outside of 'Private Sub Product_Change()' to print
some excel fields based on these 2 arrays.

I tried to simply change 'Private' to 'Public', and 'Dim' to 'Public' dx As DataX, but that doesn't work - undefined variable or so.

Thanks in advance

i've resolved this issue with:

Public DataXCollection As Collection
Public dx As DataX
(and removed Dims dx)

I ran straight into the twilight zone!

I have Private Sub 'Product_Change' (by tstom) that isn't called by any other procedure, but it's working ??
[vba]Option Explicit

Private DataXCollection As Collection

Private Sub Frame1_Click()

End Sub

Private Sub Product_Change()
Dim dx As DataX
'get the correct DataX object reference from your collection
Set dx = DataXCollection(Product.Text)
PriceInput.Text = dx.PriceX
Color.List = dx.ColorX
Color.ListIndex = 0
Size.List = dx.SizeX
Size.ListIndex = 0
End Sub

Private Sub UserForm_Initialize()
Call ArrayFill
End Sub

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

Set DataXCollection = New Collection
Set r = Worksheets("SheetX").Range("A1")

Do Until r = ""
'make sure we create a new instance of your custom type on every iteration
Set dx = New DataX
dx.CodeX = r.Text
dx.PriceX = r.Offset(, 1).Value
dx.ColorX = Application.Transpose(Range(r.Offset(1), r.Offset(1).End(xlDown)))
dx.SizeX = Application.Transpose(Range(r.Offset(1, 1), r.Offset(1, 1).End(xlDown)))
Product.AddItem dx.CodeX
'add this reference of an instance of your custom type to
'to the DataXCollection collection for future use
'use the product code as the key
DataXCollection.Add dx, dx.CodeX
Set r = r.Offset(, 2)
Loop

Product.Text = "Select Product Code"
End Sub[/vba] How comes?

Apparently, there are more things in heaven and earth, than i dreamt of..

Product is a control on the form (a textbox?), and that procedure is invoked whenever a change is made to the textbox.

Thanks xld.

And here is another (really big) one.
Whatever i do, i can?t make this code to put a value in cell (7, 1).

If i change it to (8, 1) or (7, 10) or whatever else, no problem at all, but it is absolutely impossible to put anything in (7, 1) ???
[vba]Private Sub FillTotal()

Dim j As Integer
Dim f As Integer
? siz is Public defined string variable, set something in the code

j = Worksheets("SheetY").Cells(2, 7).Value 'number of previous inputs in the ?TOTAL? sheet

If j = 0 Then ' 1st input
Worksheets("TOTAL").Cells(7, 1).Value = ?whatever!!!!!!? ?! ! ! ! ! ! !
Worksheets("TOTAL").Cells(7, 2).Value = Amount.Text

Worksheets("TOTAL").Cells(7, 2 + kk * 2 - 1).Value = siz
Worksheets("TOTAL").Cells(7, 2 + kk * 2).Value = Amount.Text

Exit Sub
End If

For f = 1 To j

If (Worksheets("TOTAL").Cells(6 + f, 1).Value = Product.Text & Color.Text) Then
Worksheets("TOTAL").Cells(6 + f, 2).Value = Worksheets("TOTAL").Cells(6 + f, 2).Value + Amount.Text

Worksheets("TOTAL").Cells(6 + f, 2 + kk * 2 - 1).Value = siz
Worksheets("TOTAL").Cells(6 + f, 2 + kk * 2).Value = Worksheets("TOTAL").Cells(6 + f, 2 + kk * 2).Value + Amount.Text
Exit Sub
End If
Next f

'And if that combination (Product/Color) doesn?t exist, then populate first empty cell in the column

Worksheets("TOTAL").Cells(7 + j, 1).Value = Product.Text & Color.Text ?entry input for that product
Worksheets("TOTAL").Cells(7 + j, 2).Value = Amount.Text
Worksheets("TOTAL").Cells(7 + j, 2 + kk * 2 - 1).Value = siz
Worksheets("TOTAL").Cells(7 + j, 2 + kk * 2).Value = Amount.Text

kk = 0
siz = ""

End Sub[/vba]:banghead:

Hi SMC,
I can't offer a solution, but re your code, you are better to make use of With statements rather than repeating the sheet name.
[VBA]
Private Sub FillTotal()
Dim j As Integer
Dim f As Integer
'siz Is Public defined String variable, Set something In the code
j = Worksheets("SheetY").Cells(2, 7).Value 'number of previous inputs in the ‘TOTAL’ sheet
With Worksheets("TOTAL")
If j = 0 Then ' 1st input
.Cells(7, 1).Value = 10 '“whatever!!!!!!” ‘! ! ! ! ! ! !
.Cells(7, 2).Value = Amount.Text
.Cells(7, 2 + kk * 2 - 1).Value = siz
.Cells(7, 2 + kk * 2).Value = Amount.Text
Exit Sub
End If
For f = 1 To j
If (.Cells(6 + f, 1).Value = Product.Text & Color.Text) Then
.Cells(6 + f, 2).Value = .Cells(6 + f, 2).Value + Amount.Text
.Cells(6 + f, 2 + kk * 2 - 1).Value = siz
.Cells(6 + f, 2 + kk * 2).Value = .Cells(6 + f, 2 + kk * 2).Value + Amount.Text
Exit Sub
End If
Next f
'And if that combination (Product/Color) doesn’t exist, then populate first empty cell in the column
.Cells(7 + j, 1).Value = Product.Text & Color.Text 'entry Input For that product
.Cells(7 + j, 2).Value = Amount.Text
.Cells(7 + j, 2 + kk * 2 - 1).Value = siz
.Cells(7 + j, 2 + kk * 2).Value = Amount.Text
End With
kk = 0
siz = ""
End Sub

[/VBA]

Thanks mdmackillop for the useful suggestion.

I found (as usual stupid) mistake in my code.

I have no doubt that i'll come again soon, with a help request.

:giggle

Quote:

---

Originally Posted by xld

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

---

Bob,

I'm just curious to know how severe is "severely flawed"? Do you have an article about this on your site? I didn't see one.

My only concern about this is a project I developed for my customer where I implemented a UDT. I'm going to switch it to a simple class as you suggested (as it's still at its tail end of the development stage), but I'm wondering what flaws it has? And if it's too many to mention, do you have a reference article of some kind?

Thanks

I need a command to copy an entire sheet into some variable, in order that variable to paste in the same sheet later, if necessary (classical undo functionality).

Something like:

[vba]Public w As Worksheet

Private Sub CopySheet()

w = Worksheets("test")
...
...
Worksheets("test") = w 'if necessary

End Sub[/vba]

Thanks

I decided to copy Range("A1:S200") instead of entire Sheet, because of memory consumption.
But the following code copies just data without the formatting.
[vba]Public vx as Variant

Private Sub CopySheet()
vx = Worksheets("test-1").Range("A1:200")
Worksheets("test-2").Range("A1:200") = vx
End Sub[/vba]
and i need the formatting.

Thanks