My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

You are here: Home / Excel VBA / Custom Data Types in VBA
custom data types in vba
by

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

DIM Variables

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

Define Custom Data 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

Intellisense recognises new custom data type

To access the variables inside the new emp

Intellisense recognises variables in custom data type

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.

By submitting your email address you agree that we can email you our Excel newsletter.

custom data types in vba

More Excel VBA Posts

Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts
Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables
PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel
Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list
Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


Category:

Reader Interactions

Comments

    • 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 Sub

      Regards

      Phil

      Reply

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

    Reply

    • 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

      Reply

  3. Sandeep Kothari

    Great stuff! Looks to me like class modules. How using these custom data types is different from using class modules?

    Reply

    • 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

      Reply

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

    Reply

      • Julian

        I’m so sorry for late reply. I found you assigned more than on employee in TestTypes() and TestTypeArr() respectively.

        Reply

        • 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

          Reply

  6. Robert

    Could you please elaborate more on the Type function and give a detailed example? I would really appreciate it. Thank you

    Reply

    • 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

      Reply

      • 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

        Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *