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.
Excel Calendar Template Date Formulas Explained
Paul Byers
Can custom data types be nested?
Philip Treacy
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
Sandeep Kothari
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.
Philip Treacy
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
Sandeep Kothari
Great stuff! Looks to me like class modules. How using these custom data types is different from using class modules?
Philip Treacy
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
JL
Great article! Can you create user defined types with xlm macros?
Philip Treacy
Hi JL,
I’ve never done it. Why not just use the modern custom data types?
Phil
Julian
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
Catalin Bombea
Hi Julian,
What array scenario you have in mind?
Julian
I’m so sorry for late reply. I found you assigned more than on employee in TestTypes() and TestTypeArr() respectively.
Catalin Bombea
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
Julian
Thank you very much. I’ll try it out accordingly.
Robert
Could you please elaborate more on the Type function and give a detailed example? I would really appreciate it. Thank you
Philip Treacy
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
Tim Rutherford
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
Catalin Bombea
Hi Tim,
The code is working, so not much to tell, good job!
Peter
The link won’t open
‘Sorry, the page you are looking for can’t be found.’
Catalin Bombea
Which link?