How to use User Define Data Type in Excel VBA Easily (2 Examples)

Welcome To Excel

What is User Define Data Type

This article will provide you an outline of VBA data types. You may have defined a variety of variables during the VBA journey. Here the data type of a variable is something like, which tells your system about the type of data you are storing with the help of some variables.  This is User Defined Data type Variable Which will be Defined Before any Procedures, top of Module. So, let’s understand how to use User Data Defined.

How To Use User Define Data Type

DOWNLOAD USED EXCEL FILE FROM HERE>>

We told you in the previous article that how you can enter in VBA by going to the developer tab and clicking on the visual basic option.

When we enter in VBE and go to Insert tab we will insert the model.

First let’s define the address data type

Type Address

End Type

Then we will Define StreetAddress as string

Type Address
StreetAddress As String
End Type

Then we will define city as string

Type Address
StreetAddress As String
City As String
End Type

Then we will define State as string

Type Address
StreetAddress As String
City As String
State As String
End Type

Then we will define ZIPCODE as String

Type Address
StreetAddress As String
City As String
State As String
ZIPCode As String
End Type

Then we will define Country as String

Type Address
StreetAddress As String
City As String
State As String
ZIPCode As String
Counrty As String
End Type

Then we have to define the address data type

Sub UserDefinedDataType()

End Sub

Address Data Type

Then we will define uAddress with address

Sub UserDefinedDataType()
Dim uaddress as Address
End Sub

Here we will write the address with the range then we will write the address

Sub UserDefinedDataType()
Dim uaddress as Address

uaddress.StreetAddress ="012 Street"
End Sub

Then we would write City with range(uAddress) then we would write city “City Name”.

Sub UserDefinedDataType()
Dim uaddress as Address

uaddress.StreetAddress ="012 Street"
uaddress.City ="City Name"
End Sub

Then I will write State with range(uAddress) then we would write State “State Name”.

Sub UserDefinedDataType()
Dim uaddress as Address

uaddress.StreetAddress ="012 Street"
uaddress.City="City Name"
uaddress.State ="State Name"
End Sub

Now I will write Country with range(uAddress) then we would write Country “USA”.

Sub UserDefinedDataType()
Dim uaddress as Address

uaddress.StreetAddress ="012 Street"
uaddress.City="City Name"
uaddress.State ="State Name"
uaddress.Country ="USA"
End Sub

Let us now define the Userinfo data type

Type UserInfo()

End Sub

Then we will Define FirstName as string

Type UserInfo()
FirstName As String
End Sub

Then we will Define LastName as string

Type UserInfo()
FirstName As String
LastName As String
End Sub

We will Define Contact as string

Type UserInfo()
FirstName As String
LastName As String
Contact As String
End Sub

We will Define Email as string

Type UserInfo()

FirstName As String
LastName As String
Contact As String
Email As String
End Sub

Here we will Define type AddressData with address, here we can put the address data type Also. 

Type UserInfo()

FirstName As String
LastName As String
Contact As String
Email As String
AddressData As address
End Sub

Now we will define uInfo as userInfo

Sub UserDefinedDataType()
Dim uaddress as Address
Dim uInfo as userInfo

uaddress.StreetAddress ="012 Street"
uaddress.City="City Name"
uaddress.State ="State Name"
uaddress.State ="USA"
End Sub

Now we will define uInfo as userInfo and then write first name and then we will write new name (James).

Sub UserDefinedDataType()
Dim uaddress as Address
Dim uInfo as userInfo

uaddress.StreetAddress ="012 Street"
uaddress.City="City Name"
uaddress.State ="State Name"
uaddress.State ="USA"

uInfo.FirstName = "james'
End Sub

and then to write last name we will write uInfo.lastName and last name will be Watson

Sub UserDefinedDataType()
Dim uaddress as Address
Dim uInfo as userInfo

uaddress.StreetAddress ="012 Street"
uaddress.City="City Name"
uaddress.State ="State Name"
uaddress.State ="USA"

uInfo.FirstName = "james"
uInfo.LastName = "Watson"
End Sub

And when we write range for contact then we will write uinfo.contact contact will take any random like (00002111)

Sub UserDefinedDataType()
Dim uaddress as Address
Dim uInfo as userInfo

uaddress.StreetAddress ="012 Street"
uaddress.City="City Name"
uaddress.State ="State Name"
uaddress.State ="USA"

uInfo.FirstName = "james"
uInfo.LastName = "Watson"
uInfo.contact = "+1 00002111"
End Sub

For  email Detail,  Write uInfo.Email and Email is “abcd@example.com” 

Sub UserDefinedDataType()
Dim uaddress as Address
Dim uInfo as userInfo

uaddress.StreetAddress ="012 Street"
uaddress.City="City Name"
uaddress.State ="State Name"
uaddress.State ="USA"

uInfo.FirstName = "james"
uInfo.LastName = "Watson"
uInfo.contact = "+1 00002111"
uinfo.Email= "abcd@example.com
End Sub

Now we can store other data inside one data address.In this, first we will write the code for the address data inside uinfo. And the street address is ‘random street’.

Sub UserDefinedDataType()
Dim uaddress as Address
Dim uInfo as userInfo

uaddress.StreetAddress ="012 Street"
uaddress.City="City Name"
uaddress.State ="State Name"
uaddress.State ="USA"

uInfo.FirstName = "james"
uInfo.LastName = "Watson"
uInfo.contact = "+1 00002111"
uinfo.Email= "abcd@example.com

uInfo.AddressData.StreetAddress = "Random STreet"
End Sub

We will write the code for the address data inside uinfo and then for the city. And the address of the city is ‘City Name’.

Sub UserDefinedDataType()
Dim uaddress as Address
Dim uInfo as userInfo

uaddress.StreetAddress ="012 Street"
uaddress.City="City Name"
uaddress.State ="State Name"
uaddress.State ="USA"

uInfo.FirstName = "james"
uInfo.LastName = "Watson"
uInfo.contact = "+1 00002111"
uinfo.Email= "abcd@example.com

uInfo.AddressData.StreetAddress = "Random Street"
uInfo.AddressData.City = "City Name"
End Sub

We will write the code for the address data inside the uInfo and then for the state. And the state address is ‘NY’.

Sub UserDefinedDataType()
Dim uaddress as Address
Dim uInfo as userInfo

uaddress.StreetAddress ="012 Street"
uaddress.City="City Name"
uaddress.State ="State Name"
uaddress.State ="USA"

uInfo.FirstName = "james"
uInfo.LastName = "Watson"
uInfo.contact = "+1 00002111"
uinfo.Email= "abcd@example.com

uInfo.AddressData.StreetAddress = "Random Street"
uInfo.AddressData.City = "City Name"
uInfo.AddressData.State = "NY"
End Sub

We will write the code for the address data and zipcode inside uInfo. And the zipcode is “00000”.

Sub UserDefinedDataType()
Dim uaddress as Address
Dim uInfo as userInfo

uaddress.StreetAddress ="012 Street"
uaddress.City="City Name"
uaddress.State ="State Name"
uaddress.State ="USA"

uInfo.FirstName = "james"
uInfo.LastName = "Watson"
uInfo.contact = "+1 00002111"
uinfo.Email= "abcd@example.com

uInfo.AddressData.StreetAddress = "Random Street"
uInfo.AddressData.City = "City Name"
uInfo.AddressData.State = "NY"
uInfo.AddressData.ZipCode = "00000"
End Sub

Like everyone else, we will write the address data and country name inside uInfo. And the name of the country is “United State”.

Sub UserDefinedDataType()
Dim uaddress as Address
Dim uInfo as userInfo

uaddress.StreetAddress ="012 Street"
uaddress.City="City Name"
uaddress.State ="State Name"
uaddress.State ="USA"

uInfo.FirstName = "james"
uInfo.LastName = "Watson"
uInfo.contact = "+1 00002111"
uinfo.Email= "abcd@example.com

uInfo.AddressData.StreetAddress = "Random Street"
uInfo.AddressData.City = "City Name"
uInfo.AddressData.State = "NY"
uInfo.AddressData.ZipCode = "00000"
uInfo.AddressData.Country = "United State"
End Sub

Here we have been told how to defend two types of data, So, I hope you have understood How to use User Define Data Type in Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.

DOWNLOAD USED EXCEL FILE FROM HERE>>

Learn More Topic in VBA here

You can also see well-explained video here