• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

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

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Custom Data Types in VBA

You are here: Home / Excel VBA / Custom Data Types in VBA
custom data types in vba
July 2, 2019 by Philip Treacy

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.
Please enter a valid email address.

Download the workbook. Note: This is a .xlsm file. Please ensure your browser doesn't change the file extension on download.


custom data types in vba

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

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

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

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

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

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

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

Multi-Language Excel Calendar (Date Picker) for 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

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

Searching for Data With a User Form

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

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: Excel VBA
Previous Post:calendar_template_formulas_thumbExcel Calendar Template Date Formulas Explained
Next Post:Referencing the Next Row in Power QueryPower Query reference next row

Reader Interactions

Comments

  1. Paul Byers

    October 2, 2020 at 5:24 am

    Can custom data types be nested?

    Reply
    • Philip Treacy

      October 2, 2020 at 11:22 am

      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

    April 21, 2020 at 10:56 pm

    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

      April 22, 2020 at 12:46 pm

      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

    April 21, 2020 at 9:48 pm

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

    Reply
    • Philip Treacy

      April 22, 2020 at 12:42 pm

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

    March 7, 2020 at 7:25 pm

    Great article! Can you create user defined types with xlm macros?

    Reply
    • Philip Treacy

      March 9, 2020 at 5:25 pm

      Hi JL,

      I’ve never done it. Why not just use the modern custom data types?

      Phil

      Reply
  5. Julian

    July 5, 2019 at 9:31 pm

    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
    • Catalin Bombea

      July 10, 2019 at 1:39 pm

      Hi Julian,
      What array scenario you have in mind?

      Reply
      • Julian

        July 12, 2019 at 1:04 pm

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

        Reply
        • Catalin Bombea

          July 12, 2019 at 1:46 pm

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

            July 13, 2019 at 7:12 pm

            Thank you very much. I’ll try it out accordingly.

  6. Robert

    July 5, 2019 at 8:12 am

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

    Reply
    • Philip Treacy

      July 7, 2019 at 3:10 am

      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

        July 9, 2019 at 12:14 am

        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
        • Catalin Bombea

          July 10, 2019 at 1:38 pm

          Hi Tim,
          The code is working, so not much to tell, good job!

          Reply
  7. Peter

    July 3, 2019 at 6:52 am

    The link won’t open

    ‘Sorry, the page you are looking for can’t be found.’

    Reply
    • Catalin Bombea

      July 3, 2019 at 3:33 pm

      Which link?

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.