• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

VBA Data Type

Home » VBA » VBA Coding » VBA Data Type

By Jyoti Singh Leave a Comment

VBA Data Type

VBA Data Type (Table of Contents)

  • VBA Data Type
  • Types of Data Types
  • Example of Data Type in Excel VBA

VBA Data Type

Before we assign any data type in Excel VBA, first we need to declare the variable name. Variable is a name which holds the value through assigned data type. While giving a name to the variable we need to keep in mind a few things.

We can define the variable in two ways. One is “Implicitly” and another one is “Explicitly”.

  • Implicit way of defining a variable is without using the word “DIM” and data type to it. For example, MyValue = 500 is the implicit way of declaring a variable.
  • Explicit way of defining a variable is with the word “DIM” and data type associated with it. For example Dim MyValue as Integer.

Top 2 Types of Data Types in VBA

To assign the data type to the variable first we need to understand the types of data types. To understand better we can categorize in two slabs.

Numeric Data Types & Non-Numeric Data Types.

#1 – Numeric Data Types

These can type can hold only numerical values. The most common numerical data types are Byte, Integer, Long, Single, Double, Currency, and Decimal.

Each of the data types can hold their respective values. Below is a detailed explanation.

  • A byte can hold the values from 0 to 255. It cannot hold anything more than 255.
  • Integer can hold values from -32768 to 32768. It cannot hold any decimal numbers.
  • Long can hold values from -2,147,483,648 to 2,147,483,648.
  • The single data type can hold values only up to 7 digits. For example 1234567, 1.245564.
  • The double data type can hold values up to 15 digits. It is just the expansion of the data type Single.
  • The currency data type can hold 15 digits before the decimal value and holds 4 digits after the decimal value.
  • The decimal data type can hold up to 28 decimal places. If you wish to store anything more than 15 decimal places you can use this data type.

#2 – Non-Numeric Data Types

These data types can hold values of anything other than numerical values. More often used non-numeric data types are Variant, String, Boolean, Date, Object.

Popular Course in this category
Cyber Monday Sale
VBA Training (3 Courses, 12+ Projects) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
4.6 (247 ratings)
Course Price

View Course

Related Courses
  • String: There are two types of string data type. One which can hold numerical data and another one which can hold non-numerical data.
  • String Fixed length can hold characters from 1 to 65400 characters.
  • String Variable length can hold characters from 0 to 2 billion characters.
  • Boolean can hold logical result values i.e. TRUE or FALSE.
  • A date can hold only date values from January 1, 100 to December 31, 9999
  • Object it can hold only objects of Microsoft product. For example Worksheet, Workbook, Range, PowerPoint, Word.
  • The variant also has two data types i.e. Text and Numeric.
  • Variant Text can hold text values the same as the variable String Variable length.
  • Variant Numeric can hold numerical values same as data type Double.

Examples of Data Type in VBA Excel

For examples to assign a data type in VBA Excel first we need to declare a variable by using the word “Dim”.

You can download this VBA Data Type Excel Template here – VBA Data Type Excel Template

Code:

Sub DT_Example1()

  Dim k

End Sub

Now using the word “As” we need to assign the data type in Excel VBA. Now I will assign the data type as “Byte”.

Code:

Sub DT_Example1()

  Dim k As Byte

End Sub

As we know the data type in Excel VBA that can hold the values from 0 to 255. But to test I will assign the value of more than 255.

Code:

Sub DT_Example1()

  Dim k As Byte

  k = 260

End Sub

Now if I run this code we will get the error as “Over Flow”.

Excel VBA Data Type Example 1

Similarly, other data types also can hold values according to their limits.

Now take a look at the below code.

Code:

Sub DT_Example2()

  Dim k As Boolean

  k = 100

  MsgBox k

End Sub

The VBA data type is assigned as “Boolean” which holds either TRUE or FALSE. But I have assigned the values as “100” to the variable “k”.

Run this code and see what happens.

Example 2

We got the result as “True”.

The reason why we got TRUE because excel treats anything more than 0 as TRUE and treats 0 as FALSE.

Now I will assign numerical value and see what happens.

Example 2-1

We got the error because “Boolean” cannot accept text values other than TRUE or FALSE.

Recommended Articles

This has been a guide to VBA Data Type. Here we cover how to assign and declare the top 2 data type in Excel VBA (Numerical and Non-Numerical) along with examples and explanations. You may also have a look at other articles related to Excel VBA –

  • Selection Property in VBA
  • Split Function Examples in VBA
  • VBA Tutorial in Excel
  • VBA End Property Selection
  • Call Sub in VBA Excel
  • ENUM in VBA examples
  • Integers in VBA example
  • Use SubString in VBA
  • ArrayList in VBA
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Filed Under: VBA, VBA Coding

Reader Interactions
Leave a Reply Cancel reply

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

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Download VBA Data Type Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Limited Period Offer - VBA Training Course (6 courses, 35+ hours video) View More