If you are working with several variables that relate to one thing, for example you are working with employee data, and every employee has a first name, last name, location and salary, you could set them up like this
But if you want to work with data relating to several employees then you're going to end up creating a whole bunch of variables and it could get messy.
In a situation like this you might be better off to create a custom data type. A custom data type allows you to gather several variables about one thing into its own type.
You can think of the custom data type as a box into which you place all the data relating to one thing.
To set up a data type for our employees we first create a new code module and then define the type
To use this new type we can use a DIM statement like any other data type.
With the Employee type declared at the top of your module, it will now appear in Intellisense
To access the variables inside the new emp
Again, you can see that Intellisense knows that emp contains four variables and shows them in the drop down as I'm typing.
Your custom type can contain variables that are basic VBA data types, other custom types, classes or objects.
Download the Example Workbook
This workbook contains examples of declaring and using a custom data type.
Enter your email address below to download the workbook.
Can custom data types be nested?
Hi Paul,
Yes they can, here’s an example
Option Explicit Type MyType typename As String typenum As Long End Type Type Employee fname As String lname As String location As String salary As Single dummy As MyType End Type Sub TestType() Dim emp As Employee With emp .fname = "Philip" .lname = "Treacy" .location = "Buderim" .salary = 50000 .dummy.typename = "abc" .dummy.typenum = 16 Debug.Print .fname Debug.Print .lname Debug.Print .location Debug.Print .salary Debug.Print .dummy.typename Debug.Print .dummy.typenum End With End SubRegards
Phil
Dear Phillip
The second macro named Sub TestTypeArr() in the comments, which uses dictionary, is not working for following reasons:
1. Following line of code threw an error:
empDict.Add Key:=emp.fname & “-” & emp.lname, Item:=emp
Pl check if Item:=emp is complete or anything needs to be added, like, emp.location?
2. following line threw an error:
Debug.Print empDict(“Rohan” & “-” & “Kothari”).salary
3. Pl check if placing colon after Object is correct:
Dim empDict As Object:
I shall be grateful for your help.
Hi Sandeep,
Check the double quotes.
VBA uses
" "but often when copying code from a website and then pasting it, the quotes get changed to“ “Regards
Phil
Great stuff! Looks to me like class modules. How using these custom data types is different from using class modules?
Hi Sandeep,
In class modules you can define your own methods and properties that do things with the values in the class. But if all you want to do is store a group of related data, then a custom type is easier to implement.
Class modules also require their own code module, custom types don’t, they can be declared in the code module you use them in.
Regards
Phil
Great article! Can you create user defined types with xlm macros?
Hi JL,
I’ve never done it. Why not just use the modern custom data types?
Phil
Hi Philip,
Could you please provide an user case to demo how to apply the defined custom types especially for an array scenario. Thanks!
Julian
Hi Julian,
What array scenario you have in mind?
I’m so sorry for late reply. I found you assigned more than on employee in TestTypes() and TestTypeArr() respectively.
Yes,
You can load a table of employees into an array, using a code similar to TestTypeArr procedure:
Sub TestTypeArr()
Dim emp As Employee
Dim empArr() As Employee
Dim i As Long, tbl As ListObject
Set tbl = ThisWorkbook.Worksheets("Sheet1").ListObjects(1)
'load data
For i = 1 To tbl.ListRows.Count
emp.fname = tbl.ListRows(i).Range.Cells(1).Value
emp.lname = tbl.ListRows(i).Range.Cells(2).Value
emp.location = tbl.ListRows(i).Range.Cells(3).Value
emp.salary = tbl.ListRows(i).Range.Cells(4).Value
ReDim Preserve empArr(1 To i)
empArr(i) = emp
Next
'use it
For i = 1 To UBound(empArr)
Debug.Print empArr(i).fname, empArr(i).lname
Next
End Sub
Or, you can use a dictionary and check if a spcific employee exists in the data table:
Sub TestTypeArr()
Dim emp As Employee
Dim empDict As Object: Set empDict = CreateObject("scripting.dictionary")
Dim i As Long, tbl As ListObject
Set tbl = ThisWorkbook.Worksheets("Sheet1").ListObjects("Employees")
'load data
For i = 1 To tbl.ListRows.Count
emp.fname = tbl.ListRows(i).Range.Cells(1).Value
emp.lname = tbl.ListRows(i).Range.Cells(2).Value
emp.location = tbl.ListRows(i).Range.Cells(3).Value
emp.salary = tbl.ListRows(i).Range.Cells(4).Value
empDict.Add Key:=emp.fname & "-" & emp.lname, Item:=emp
Next
'use it
'do we have this employee? if yes, print the salary
if empDict.Exists("Mynda" & "-" & "Treacy")=true then
Debug.Print empDict("Mynda" & "-" & "Treacy").salary
end if
Set empDict=Nothing
End Sub
Thank you very much. I’ll try it out accordingly.
Could you please elaborate more on the Type function and give a detailed example? I would really appreciate it. Thank you
Hi Robert,
This isn’t about a Type function. It’s creating your own data type. If you download the workbook from the post there are examples in it.
Regards
Phil
Hi Robert,
I played around with the TestType Sub and added an example. I added another sheet “TableTest” and created a table “EmpData” with first name, Last Name, Loc, Salary, Married as the columns. Then modified the Sub with the following. Corrections or tips would be greatly appreciated.
Tim
Sub TestType()
Dim emp As Employee
Dim EmpTbl As ListObject
Dim i As Long
Set EmpTbl = Worksheets(“TableTest”).ListObjects(“EmpData”)
Dim M_Status As String
For i = 1 To EmpTbl.ListRows.Count
With emp
.fname = EmpTbl.DataBodyRange(i, 1).Value
.lname = EmpTbl.DataBodyRange(i, 2).Value
.location = EmpTbl.DataBodyRange(i, 3).Value
.salary = EmpTbl.DataBodyRange(i, 4).Value
.married = EmpTbl.DataBodyRange(i, 5).Value
End With
‘Debug.Print emp.lname + “, ” + emp.fname
If emp.married = “Y” Then
M_Status = “married.”
Else
M_Status = “not married.”
End If
With Cells(i + 1, 6) ‘Column F
.Value = emp.lname + “, ” + emp.fname + ” of ” + emp.location + ” is earning ” + _
FormatCurrency(emp.salary, 0) + ” and is ” + M_Status
.Font.Color = vbBlue
‘ .Columns.AutoFit
End With
Next
Columns(6).AutoFit ‘ only autofit column F once instead of in the loop.
End Sub
Hi Tim,
The code is working, so not much to tell, good job!
The link won’t open
‘Sorry, the page you are looking for can’t be found.’
Which link?