PDA

View Full Version : [SOLVED] Types inside a Type



gsmcconville
05-24-2016, 04:50 PM
Can anyone please let me know if there is anyway to group a structure of data or types like so...



Type Data
Sht as Worksheet
Cols as ColumnList
Otherstuff
End Type

Enum ColumnList
Batch = 1
Description = 2
Otherstuff
End Enum

Public Dt as Data

X = Dt.Cols.Batch


I cannot seem to find anyway to group data like this, maybe in a class module but even then when debugging it steps in and out of the class constantly.

Error I get as you cannot have a user defined class in a type

SamT
05-24-2016, 05:38 PM
ColumnList is not a Type. Even User Defined Enumerated Constants are Longs.

Declare Cols as Long in the UDT,

Type Data
Sht As Worksheet
Cols As Long
Otherstuff
End Type

Enum ColumnList
Batch = 1
Description '= previous + 1 (2)
Otherstuff '= Previous + 1 (3)
MyStuff = 999
Urstuff '= Previous + 1 (1000)
End Enum

Public Dt As Data

Sub test()
Set Dt.Sht = Sheets("Sheet1")
Dt.Cols = Batch

With Dt.Sht
X = Application.WorksheetFunction.CountA(.Columns(Dt.Cols))
End With
End Sub

SamT
05-24-2016, 06:01 PM
Easy way to create Enumerated Column Constants:
copy header Row
PasteSpecial: Transpose
Ctrl+H on that to replace " " with "".
Copy that, Paste into VBA Editor.

Add "Enum myColumns" & "End Enum"
Fill in blanks with "EmptyColumn1," "EmptyColumn2," Etc.

Thereafter, in Code:

Dim RowCounter As Long
With Sheets"(Sheet1")
X = .Cells(RowCounter, NoSpacesColumnHeaderName)
End With

SamT
05-24-2016, 06:14 PM
When working with multiple sheets with similar headers, for example Client List, Vendor List and Employee List, prefix Headers with Sheet mnemonic.

CliFirstName, CliLastName, CliPhone
VenFirstName,VenLastName, VenPhone
EmpFirstName, EmpLastName, EmpPhone

gsmcconville
05-24-2016, 07:07 PM
OK, looks like I did not frase this correctly, sorry for that

The Enum should be a Type, doesn't matter what is in the type, just want to find a way to structure data in a way that I can reference it through other types.

eg: Data.Cols.Batch = 1

Paul_Hossler
05-24-2016, 07:49 PM
It is possible to have a Type within a Type




Option Explicit

Type TypeOne
One_A As Long
One_B As String
One_C As Boolean
End Type

Type TypeTwo
Two_A As Long
Two_B As String
Two_C As Boolean
Two_Z As TypeOne
End Type


Sub Demo()
Dim X As TypeTwo

X.Two_Z.One_A = 100

X.Two_Z.One_A = 10 * X.Two_Z.One_A

MsgBox X.Two_Z.One_A
End Sub

gsmcconville
05-24-2016, 08:14 PM
Exactly what I wanted.

When I tried it I got an error, but it must have related to different code that was in the same module.

Another quick question, is there anyway to put constants in there?

GTO
05-24-2016, 09:02 PM
From Help:

[Private | Public] Type varname
elementname [([subscripts])] As
type
[elementname [([subscripts])] As type]
. .
.

End Type

The Type statement syntax has these parts:







Part

Description




Public

Optional. Used to declare user-defined types (http://www.vbaexpress.com/forum/HV10383569.htm)
that are available to all procedures (http://www.vbaexpress.com/forum/HV10383569.htm) in all modules (http://www.vbaexpress.com/forum/HV10383569.htm) in all projects (http://www.vbaexpress.com/forum/HV10383569.htm).




Private

Optional. Used to declare user-defined types that are available only within
the module where the declaration (http://www.vbaexpress.com/forum/HV10383569.htm) is made.




varname

Required. Name of the user-defined type; follows standard variable (http://www.vbaexpress.com/forum/HV10383569.htm) naming conventions.




elementname

Required. Name of an element of the user-defined type. Element names also
follow standard variable naming conventions, except that keyword (http://www.vbaexpress.com/forum/HV10383569.htm)s can be used.




subscripts

When not explicitly stated in lower, the lower bound of an array is
controlled by the Option Base statement.
The lower bound is zero if no Option Base
statement is present.




type

Required. Data type of the element; may be Byte (http://www.vbaexpress.com/forum/HV10383569.htm), Boolean (http://www.vbaexpress.com/forum/HV10383569.htm), Integer (http://www.vbaexpress.com/forum/HV10383569.htm), Long (http://www.vbaexpress.com/forum/HV10383569.htm), Currency (http://www.vbaexpress.com/forum/HV10383569.htm), Single (http://www.vbaexpress.com/forum/HV10383569.htm), Double (http://www.vbaexpress.com/forum/HV10383569.htm), Decimal (http://www.vbaexpress.com/forum/HV10383569.htm) (not
currently supported), Date (http://www.vbaexpress.com/forum/HV10383569.htm), String (http://www.vbaexpress.com/forum/HV10383569.htm) (for variable-length strings), String * length (for fixed-length strings), Object (http://www.vbaexpress.com/forum/HV10383569.htm), Variant (http://www.vbaexpress.com/forum/HV10383569.htm), another
user-defined type, or an object type (http://www.vbaexpress.com/forum/HV10383569.htm).





In short, no; Type is meant to define a data structure, not to hold values.

Hope that helps,

Mark

gsmcconville
05-24-2016, 09:47 PM
Thank you both Mark and Paul. Your help is greatly appreciated

snb
05-25-2016, 02:43 AM
Can you please shed some light on in what situations you want to apply this ? and what the benefit of this approach could be compared to other approaches ?

(I ask because I have no idea when you could/should use this technique).

gsmcconville
05-25-2016, 05:45 AM
Can you please shed some light on in what situations you want to apply this ? and what the benefit of this approach could be compared to other approaches ?

(I ask because I have no idea when you could/should use this technique).

It's not so much as a situation I have, more just wanted to get an understanding of how I can structure my data for any future projects.

snb
05-25-2016, 06:14 AM
Isn't the use of 'Type' replaced by the availability to introduce class modules ?

Paul_Hossler
05-25-2016, 06:15 AM
Type is meant to define a data structure, not to hold values.

Yes, and once the data structure is Type-ed, you can Dim a variable of the Type which will hold the values

In 'normal' VBA, a Long is already defined as a (built in) Type, so you just need to



Dim X as Long




Simple example of using it. Doesn't lend itself to a random access 'database' but is easy and simple for some things




Option Explicit

Public Enum eNewCustomer
NewCustomer = 0
ExistingCustomer = 1
PotentialCustomer = 2
End Enum

Public Type tCustomer
cName As String
cAddr As String
cType As eNewCustomer
cTotalSales As Double
End Type

Sub Demo()
Dim arrCustomers(1 To 100) As tCustomer
Dim Customer As tCustomer
Dim arrType As Variant
Dim I As Long

arrType = Array("New", "Old", "Maybe")

With Customer
.cName = "Acme"
.cAddr = "1234 5th Street"
.cTotalSales = 12345.67
.cType = ExistingCustomer
End With
arrCustomers(1) = Customer

With Customer
.cName = "Fred"
.cAddr = "54321 10th Avenue"
.cTotalSales = 0
.cType = PotentialCustomer
End With
arrCustomers(2) = Customer

For I = LBound(arrCustomers) To UBound(arrCustomers)
Customer = arrCustomers(I)
If Len(Customer.cName) > 0 Then
MsgBox Customer.cName & " - " & Customer.cAddr & " - " & Customer.cTotalSales & " - " & arrType(Customer.cType)
End If
Next I
End Sub

SamT
05-25-2016, 07:06 AM
OK, looks like I did not frase this correctly, sorry for that
You're welcome.

Paul_Hossler
05-25-2016, 11:12 AM
Isn't the use of 'Type' replaced by the availability to introduce class modules ?

I'd say 'Yes' for you more advanced users, esp since you can't .Add a variable of a user defined type to a catalog or dictionary (AFAIK)

However, I've found it works well for reading and writing a structured records to a file with Put/Get



Option Explicit
Public Enum eNewCustomer
NewCustomer = 0
ExistingCustomer = 1
PotentialCustomer = 2
End Enum

Public Type tCustomer
cName As String * 40 ' must be fixed len strings
cAddr As String * 40
cType As Long
cTotalSales As Double
End Type

Sub Demo()
Dim Customer As tCustomer, Customer1 As tCustomer, Customer2 As tCustomer
Dim arrType As Variant
Dim I As Long
Dim iFileNum As Long

arrType = Array("New", "Old", "Maybe")

iFileNum = FreeFile

'must be Random for Put/Get
Open Environ("TEMP") & "testing.dat" For Random As #iFileNum Len = Len(Customer)

With Customer
.cName = "Acme"
.cAddr = "1234 5th Street"
.cTotalSales = 12345.67
.cType = ExistingCustomer
End With
Put #iFileNum, , Customer

With Customer
.cName = "Fred"
.cAddr = "54321 10th Avenue"
.cTotalSales = 0
.cType = PotentialCustomer
End With
Put #iFileNum, , Customer

Close #iFileNum


Open Environ("TEMP") & "testing.dat" For Random As #iFileNum Len = Len(Customer)
Get #iFileNum, , Customer1
Get #iFileNum, , Customer2
Close #iFileNum

MsgBox Customer1.cName & " - " & Customer1.cAddr & " - " & Customer1.cTotalSales & " - " & arrType(Customer1.cType)
MsgBox Customer2.cName & " - " & Customer2.cAddr & " - " & Customer2.cTotalSales & " - " & arrType(Customer2.cType)


Open Environ("TEMP") & "testing.dat" For Random As #iFileNum Len = Len(Customer)
Get #iFileNum, 2, Customer2
Close #iFileNum

MsgBox Customer2.cName & " - " & Customer2.cAddr & " - " & Customer2.cTotalSales & " - " & arrType(Customer2.cType)
Close #iFileNum

End Sub