{"id":1887,"date":"2018-07-12T14:44:55","date_gmt":"2018-07-12T14:44:55","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=1887"},"modified":"2024-03-14T11:37:18","modified_gmt":"2024-03-14T11:37:18","slug":"vba-data-types","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/","title":{"rendered":"VBA Data Types"},"content":{"rendered":"\n

VBA can take care of data types automatically, but it does it at a cost of speed and memory. When your application grows, sometimes you will need to explicitly define the best data type for the particular variable.<\/p>\n\n\n\n

The following list shows the data types you can use in Excel VBA.<\/p>\n\n\n\n

Boolean<\/h2>\n\n\n\n

It holds only one of two logical values: True or False. It is often used in logical statements.<\/p>\n\n\n\n

Bytes used<\/strong><\/td>Range<\/strong><\/td>Example<\/strong><\/td><\/tr>
2<\/td>True\u00a0<\/strong>or\u00a0False<\/strong><\/td>Dim checkVal As Boolean

checkVal = True<\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Byte<\/h2>\n\n\n\n

You can use this type when you work with small values (e.g. people\u2019s ages).<\/p>\n\n\n\n

Bytes used<\/strong><\/td>Range<\/strong><\/td>Example<\/strong><\/td><\/tr>
1<\/td>0 to 255<\/td>Dim age As Byte

Age = 56<\/span><\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Currency<\/h2>\n\n\n\n

The currency format<\/a> allows you to store numbers as the decimal fraction that has 15 digits to the left and 4 digits to the right.<\/p>\n\n\n\n

Bytes used<\/strong><\/td>Range<\/strong><\/td>Example 1<\/strong><\/td>Example 2<\/strong><\/td><\/tr>
8<\/td>-922,337,203,685,477.5808 to 922,337,203,685,477.5807<\/td>Dim money AS Currency\u00a0 <\/span>

money = 123.456789<\/p>

result<\/strong>: 123.4568<\/em>\u00a0<\/em> <\/span><\/p><\/td>

Dim money Dim value As Double
value = 123.456789
money = CCur(value)

MsgBox (money)
description: Ccur function converts a value to currency<\/a>.<\/em><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Date<\/h2>\n\n\n\n

The date format is useful when you need to perform some kind of operations on dates (e.g. calculate the number of days between two dates). Each date or time needs to be placed between two \u201e#\u201d marks.<\/p>\n\n\n\n

Bytes used<\/strong><\/td>Range<\/strong><\/td>Example<\/strong><\/td><\/tr>
8<\/td>1 January 100 to

31 December 9999<\/span><\/p><\/td>

Dim today As Date

Const myDate As Date<\/p>

myDate = #12\/1\/2014#<\/p>

Dim myTime As Date<\/p>

myTime = #12:45:12#<\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Caution<\/h3>\n\n\n\n

VBA displays your time and date according to your system\u2019s date and time format.<\/p>\n\n\n\n

Caution<\/h3>\n\n\n\n

When you write VBA code you have to remember that you must use the U.S. date formats (e.g. mm\/dd\/yyyy) even if your system\u2019s time format is set differently.<\/p>\n\n\n\n

Decimal<\/h2>\n\n\n\n

The\u00a0Dim value as the Decimal\u00a0notation is not correct. You cannot declare a variable to be a\u00a0Decimal<\/strong>. But you can declare it as\u00a0a Variant<\/strong>\u00a0first and then use the\u00a0CDec<\/strong>\u00a0function to convert it to Decimal.<\/p>\n\n\n\n

Bytes used<\/strong><\/td>Range<\/strong><\/td>Example<\/strong><\/td><\/tr>
12<\/td>Largest possible value:<\/strong>

+\/-79,228,162,514,264,337,593,543,950,335<\/p>

Largest\/smallest value with 28 decimal places: <\/span><\/strong>+\/-7.9228162514264337593543950335<\/p>

Smallest non-zero value:<\/strong> <\/span>+\/-0.0000000000000000000000000001<\/p><\/td>

Dim value

value = 123.456789<\/p>

value = CDec(value)<\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Double<\/h2>\n\n\n\n

The Double (double-precision floating-point) variables<\/a> are used to store large floating-point numbers.<\/p>\n\n\n\n

Bytes used<\/strong><\/td>Range<\/strong><\/td>Example<\/strong><\/td><\/tr>
8<\/td>-1.79769313486231E+308 to\u00a0

-4.94065645841247E-324 <\/span><\/p><\/td>

Dim value As Double

value = 123.456789 <\/span><\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Integer<\/h2>\n\n\n\n

The Integer is used to store whole numbers. Floating point numbers are rounded to the nearest<\/a> integer.<\/p>\n\n\n\n

Bytes used<\/strong><\/td>Range<\/strong><\/td>Example<\/strong><\/td><\/tr>
2<\/td>-32,768 to 32,767<\/td>Dim value As Integer\u00a0

value = 3.5 <\/span>MsgBox(value)<\/p>

result:<\/strong>\u00a04<\/em><\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Long<\/h2>\n\n\n\n

If you need to store larger numbers and the Integer<\/strong> type is not enough, try using Long.<\/strong><\/p>\n\n\n\n

Bytes used<\/strong><\/td>Range<\/strong><\/td>Example<\/strong><\/td><\/tr>
4<\/td>-2,147,483,648 to 2,147,483,647<\/td>Dim value As Long\u00a0

value = 32800 <\/span><\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

LongLong<\/h2>\n\n\n\n

If you need to store even bigger numbers (for example to show the world population), you can use the LongLong<\/strong> format.<\/p>\n\n\n\n

Bytes used<\/strong><\/td>Range<\/strong><\/td>Example<\/strong><\/td><\/tr>
8<\/td>-2,147,483,648 to 2,147,483,647<\/td>Dim value As LongLong\u00a0

value = 7000000000#<\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

NOTICE<\/h3>\n\n\n\n

If you enter a number that is larger than\u00a0Long,<\/strong>\u00a0VBA will add a # mark at the end of the number.<\/p>\n\n\n\n

Single<\/h2>\n\n\n\n

Single (single-precision floating-point) is the smallest data type that you can use to store decimal fractions<\/a>.<\/p>\n\n\n\n

Bytes used<\/strong><\/td>Range<\/strong><\/td>Example<\/strong><\/td><\/tr>
4<\/td>-3.402823E+38 to -1.401298E-45<\/td>Dim value As Single\u00a0

value = 123.456 <\/span><\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

String<\/h2>\n\n\n\n

Use this data type to store text.<\/p>\n\n\n\n

Bytes used<\/strong><\/td>Range<\/strong><\/td>Example 1<\/strong><\/td>Example 2<\/strong><\/td><\/tr>
1 per character<\/td>A variable-length string:<\/strong>

up to 2 billion characters<\/p>

A fixed-length string:<\/strong><\/p>

1 to 65,526 characters <\/span><\/p><\/td>

Dim myText As String

myText = “Some text”<\/p><\/td>

Dim myText As String * 6  myText = “Some text”

MsgBox(myText) 
result:<\/em><\/strong> Some t<\/em><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Variant<\/h2>\n\n\n\n

The Variant type is assumed if you don\u2019t declare the data type explicitly.<\/p>\n\n\n\n

Bytes used<\/strong><\/td>Range<\/strong><\/td>Example<\/strong><\/td><\/tr>
may vary<\/td>For negative values:<\/strong>

-1.797693134862315E308 to -4.94066E-324<\/p>

For positive values:<\/strong><\/p>

4.94066E-324 to 1.797693134862315E308<\/p><\/td>

Dim myVar1 As Variant

myVar = 123.456<\/p>

Dim myVar2<\/p>

myVar2 = “This is simple text”<\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n","protected":false},"excerpt":{"rendered":"

VBA can take care of data types automatically, but it does it at a cost of speed and memory. When your application grows,…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14],"tags":[188],"yoast_head":"\nVBA Data Types<\/title>\n<meta name=\"description\" content=\"Learn about different data types in Excel VBA including Boolean, byte, currency, date, and caution when dealing with time and date formats.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"VBA Data Types\" \/>\n<meta property=\"og:description\" content=\"Learn about different data types in Excel VBA including Boolean, byte, currency, date, and caution when dealing with time and date formats.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-07-12T14:44:55+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-14T11:37:18+00:00\" \/>\n<meta name=\"author\" content=\"Tomasz Decker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Tomasz Decker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"headline\":\"VBA Data Types\",\"datePublished\":\"2018-07-12T14:44:55+00:00\",\"dateModified\":\"2024-03-14T11:37:18+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/\"},\"wordCount\":614,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"keywords\":[\"added\"],\"articleSection\":[\"training\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/\",\"url\":\"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/\",\"name\":\"VBA Data Types\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"datePublished\":\"2018-07-12T14:44:55+00:00\",\"dateModified\":\"2024-03-14T11:37:18+00:00\",\"description\":\"Learn about different data types in Excel VBA including Boolean, byte, currency, date, and caution when dealing with time and date formats.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"VBA Data Types\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"VBA Data Types","description":"Learn about different data types in Excel VBA including Boolean, byte, currency, date, and caution when dealing with time and date formats.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/","og_locale":"en_US","og_type":"article","og_title":"VBA Data Types","og_description":"Learn about different data types in Excel VBA including Boolean, byte, currency, date, and caution when dealing with time and date formats.","og_url":"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/","article_published_time":"2018-07-12T14:44:55+00:00","article_modified_time":"2024-03-14T11:37:18+00:00","author":"Tomasz Decker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Tomasz Decker","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"headline":"VBA Data Types","datePublished":"2018-07-12T14:44:55+00:00","dateModified":"2024-03-14T11:37:18+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/"},"wordCount":614,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"keywords":["added"],"articleSection":["training"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/","url":"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/","name":"VBA Data Types","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"datePublished":"2018-07-12T14:44:55+00:00","dateModified":"2024-03-14T11:37:18+00:00","description":"Learn about different data types in Excel VBA including Boolean, byte, currency, date, and caution when dealing with time and date formats.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/training\/vba-data-types\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/training\/vba-data-types\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"VBA Data Types"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/1887"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=1887"}],"version-history":[{"count":24,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/1887\/revisions"}],"predecessor-version":[{"id":14699,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/1887\/revisions\/14699"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=1887"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=1887"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=1887"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}