{"id":4863,"date":"2016-12-09T06:23:15","date_gmt":"2016-12-09T06:23:15","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/wp\/?p=4863"},"modified":"2022-08-12T11:14:03","modified_gmt":"2022-08-12T11:14:03","slug":"get-type-of-data-stored-in-a-variable","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/","title":{"rendered":"get Data Type of Variable in VBA – VarType() VBA function"},"content":{"rendered":"

Dear Friends,
\nSome time during the VBA programming you may need to perform different action based on “what is the type of the variable” you are dealing with. Or in other words what kind of data is stored in a variable. In VBA, if you have not defined type of a variable while declaring it using “Dim statement”, then whatever value you assign to that variable, data type of the value is inherited by the variable.
\n

\"How<\/a>

How to find the Data Type of a Variable<\/p><\/div><\/p>\n

Get the data type of a Variable in VBA<\/h3>\n

VarTpe(YourVariable) is a VBA function which<\/p>\n

1. Takes your variable name as input parameter
\n2. Returns the data type of the variable or type of the data stored in this variable.<\/p>\n

How to use VarType() VBA function<\/h2>\n

In the below example, code VBA.VarType(varString)<\/i><\/strong> will return 8<\/strong>. This is the return value for String Type variable. Refer the below table for return value for all the data types.<\/p>\n

\r\nSub GetTypeOfAllVariables()\r\n    Dim varString As String\r\n    MsgBox "Data Type of the Variable varString is : " &amp; VBA.VarType(varString)\r\nEnd Sub\r\n<\/code><\/pre>\n
Data Types and their Return Value<\/h2>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Variable Type<\/th>\nReturn Value<\/th>\n<\/tr>\n
Variant<\/td>\n0<\/td>\n<\/tr>\n
Integer<\/td>\n2<\/td>\n<\/tr>\n
Long<\/td>\n3<\/td>\n<\/tr>\n
Single<\/td>\n4<\/td>\n<\/tr>\n
Double<\/td>\n5<\/td>\n<\/tr>\n
Currency<\/td>\n6<\/td>\n<\/tr>\n
Date<\/td>\n7<\/td>\n<\/tr>\n
String<\/td>\n8<\/td>\n<\/tr>\n
Boolean<\/td>\n11<\/td>\n<\/tr>\n
Byte<\/td>\n17<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
As I mentioned above, this function does not return the variable type what is defined but also based on what kind of value it has stored in it. This is quite possible that a variable defined as Variant is holding a NULL value or Nothing or Empty etc. These are special cases where VBA returns different values based on the data which this Variant variable is holding.<\/p>\n
Where should I use this?<\/h2>\n
In VBA, it is possible to define a variable without any type (default type = Variant). This variable will keep changing its type based on the value assigned to it during run time. Then sometime, during programming it will become important to know what kind of data it has got at this moment before I perform any operation on it. <\/p>\n
More about VarType Function<\/h3>\n
As I believe that Microsoft Excel VBA help<\/strong> is really rich. You should always start with the help provided there. It is available offline as well which is the best part of it. Therefore make use of it in learning VBA.
\nTo know more about these return values which I have not specified here you can get them here:
\n
\"varType<\/a>
varType – VBA Function<\/p><\/div><\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"
Dear Friends, Some time during the VBA programming you may need to perform different action based on “what is the type of the variable” you are dealing with. Or in other words what kind of data is stored in a variable. In VBA, if you have not defined type of a variable while declaring it […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[5204],"tags":[],"class_list":["post-4863","post","type-post","status-publish","format-standard","hentry","category-vba-programming"],"yoast_head":"\nGet the Data type of Data stored in a Variable - VarType () VBA Function<\/title>\n<meta name=\"description\" content=\"Function to get the Data Type of a variable in VBA. Get the data type of value stored in a variable. to know the data type of data stored in Variant Data type variable\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"get Data Type of Variable in VBA - VarType() VBA function\" \/>\n<meta property=\"og:description\" content=\"Function to get the Data Type of a variable in VBA. Get the data type of value stored in a variable. to know the data type of data stored in Variant Data type variable\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2016-12-09T06:23:15+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-12T11:14:03+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2016\/12\/YourDataTypePlease.jpg\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"get Data Type of Variable in VBA – VarType() VBA function\",\"datePublished\":\"2016-12-09T06:23:15+00:00\",\"dateModified\":\"2022-08-12T11:14:03+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/\"},\"wordCount\":404,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2016\/12\/YourDataTypePlease.jpg\",\"articleSection\":[\"VBA Programming\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/\",\"name\":\"Get the Data type of Data stored in a Variable - VarType () VBA Function\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2016\/12\/YourDataTypePlease.jpg\",\"datePublished\":\"2016-12-09T06:23:15+00:00\",\"dateModified\":\"2022-08-12T11:14:03+00:00\",\"description\":\"Function to get the Data Type of a variable in VBA. Get the data type of value stored in a variable. to know the data type of data stored in Variant Data type variable\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/#primaryimage\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2016\/12\/YourDataTypePlease.jpg\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2016\/12\/YourDataTypePlease.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"VBA Programming\",\"item\":\"https:\/\/vmlogger.com\/excel\/vba-programming\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"get Data Type of Variable in VBA – VarType() VBA function\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Get the Data type of Data stored in a Variable - VarType () VBA Function","description":"Function to get the Data Type of a variable in VBA. Get the data type of value stored in a variable. to know the data type of data stored in Variant Data type variable","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:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/","og_locale":"en_US","og_type":"article","og_title":"get Data Type of Variable in VBA - VarType() VBA function","og_description":"Function to get the Data Type of a variable in VBA. Get the data type of value stored in a variable. to know the data type of data stored in Variant Data type variable","og_url":"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2016-12-09T06:23:15+00:00","article_modified_time":"2022-08-12T11:14:03+00:00","og_image":[{"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2016\/12\/YourDataTypePlease.jpg"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"get Data Type of Variable in VBA – VarType() VBA function","datePublished":"2016-12-09T06:23:15+00:00","dateModified":"2022-08-12T11:14:03+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/"},"wordCount":404,"commentCount":0,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2016\/12\/YourDataTypePlease.jpg","articleSection":["VBA Programming"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/","url":"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/","name":"Get the Data type of Data stored in a Variable - VarType () VBA Function","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/#primaryimage"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2016\/12\/YourDataTypePlease.jpg","datePublished":"2016-12-09T06:23:15+00:00","dateModified":"2022-08-12T11:14:03+00:00","description":"Function to get the Data Type of a variable in VBA. Get the data type of value stored in a variable. to know the data type of data stored in Variant Data type variable","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/#primaryimage","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2016\/12\/YourDataTypePlease.jpg","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2016\/12\/YourDataTypePlease.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2016\/12\/get-type-of-data-stored-in-a-variable\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"VBA Programming","item":"https:\/\/vmlogger.com\/excel\/vba-programming\/"},{"@type":"ListItem","position":3,"name":"get Data Type of Variable in VBA – VarType() VBA function"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/4863"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=4863"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/4863\/revisions"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=4863"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=4863"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=4863"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}