Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Understanding Variables in VBA Macros.
Written by Allen Wyatt (last updated April 9, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
Excel allows you to write macros in a language called Visual Basic for Applications (VBA). This is a specialized version of the BASIC programming language, and as such, allows you to use variables. Variables are nothing but names which represent other data. During the course of your macro you can even change the data to which the name applies.
VBA allows you to use quite a few different types of variables. There are eleven types of variables you can use in your macros. These are known as data types, and you should use the data type that most closely matches the characteristics of the information you are storing in the variable. VBA supports the following data types:
An additional data type (Decimal) is also specified in the VBA documentation, but is not currently supported by the language. As in other versions of BASIC, VBA also allows you to define variable arrays and you can also create user-defined data types. The full range of variable specifications is much too complex for a simple ExcelTip, however. If you need specific information about how to work with variables, refer to a good Visual Basic or VBA programming book. You can also look in the VBA on-line help under the Dim statement.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11800) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Understanding Variables in VBA Macros.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Excel allows you to create a special type of macro called a user-defined function (UDF). These can let you add to the ...
Discover MoreMacros depend on the use of variables to do their work. This tip examines how variables are declared in a macro, using ...
Discover MoreWant a quick way to speed up your macros? All you need to do is to stop Excel from updating the screen while the macro is ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments