A ____ Data Type Can Hold 14 or 15 Significant Digits of Accuracy.
Have yous always thought about the main purpose of Visual Basic for Applications?
In Excel VBA Programming for Dummies, Excel potency John Walkenbach provides the following interesting reply to this question:
VBA'south main purpose is to manipulate data.
For purposes of this VBA tutorial, the key term inside the to a higher place statement is "data". If the main purpose of Visual Basic for Applications is to manipulate data, having a skilful understanding of what are the dissimilar VBA data types is essential for mastering VBA.
My purpose with this post is to help y'all understand VBA data types and how to work with them. In this guide, I cover and thoroughly explicate the almost relevant aspects of the dissimilar VBA data types, including the following:
In this item mail service, I don't cover the topic of variable announcement in detail. This is covered in this web log mail service about variable declaration.
How Information Is Stored And Why VBA Information Types Are Important
Data is generally stored in the computer's retentiveness. If you have a basic understanding of computers, you're probably familiar with how this works.
All the same, for VBA purposes, there is an important distinction to exist made between objects and variables. As Walkenbach explains in Excel VBA Programming for Dummies:
- Some data is stored in objects.
- Other data is stored in variables.
I explain what "objects" and "variables" are in this VBA Tutorial for Beginners. To summarize:
- An object is what is manipulated by Visual Basic for Applications.
Some examples of objects are Excel workbooks, worksheets, cell ranges and cells.
- A variable is a storage location paired with a name. Y'all generally use variables to represent a particular value. In other words, variables deed as placeholders for values.
You lot can create variables in Visual Basic for Applications past declaring them. You decide the name and characteristics of whatever variable you create. One of the characteristics that yous tin determine is the VBA data type.
The ability to determine the characteristics of the variables you create in VBA is probably the main reason why understanding VBA data types is important. Let's meet why this is the case:
VBA data types determine the way in which data is stored in the memory.
There are several VBA information types. These data types accept different nominal allocation requirements. This means that different types use a different amount of bytes.
The general rule that yous should apply when determining which data blazon to use is set forth by John Walkenbach in Excel VBA Programming for Dummies:
In general, choose the data blazon that uses the smallest number of bytes but can yet handle all the information you desire to shop (…).
The reason for this is that in that location is an changed relation betwixt execution speed and the amount of bytes used past the relevant data. In other words, the more than bytes your data uses, the slower your VBA application runs.
As this makes it clear, in order to exist able to make advisable decisions about the VBA data types that you lot want to utilise, you must empathize the basic characteristics of each of the chief types. Mastering this topic is very important, particularly as you start creating and developing more than complex VBA applications. The master reason for this is that inadequate choices of VBA data types can event in slower execution of those applications and, in general, an inefficient use of the retention.
Introduction To Why You Shouldn't Ever Allow VBA To Handle Data Types Automatically
Programming languages are ordinarily classified as strongly typed or weakly typed.
Strongly typed languages ordinarily require that you lot ascertain the data type for every variable that you use. Visual Basic for Applications isn't ane of these languages. Therefore, VBA is able to handle the details related to data types automatically.
More precisely, if you don't declare the VBA information blazon for a detail variable, Visual Basic for Applications uses the default blazon. The default VBA data type is chosen Variant, and I explicate it below.
The question is:
Should yous allow Visual Basic for Applications to automatically set the VBA information types for the variables you lot use?
Mostly, no. You should go used to declaring the VBA data types of the variables you lot create ever.
Even so, the answer to the question isn't equally straightforward as the statement to a higher place may go far seem.
Allowing Visual Basic for Application to automatically deal with VBA data types for variables has advantages. The chief advantage is, patently, that by assuasive VBA to handle data types, y'all don't have to practise it yourself. This may sound more convenient, but await…
As I mentioned to a higher place, inappropriate choices of VBA data types can result in problems downwardly the road, including slower application execution. More precisely, if y'all always rely on the default Variant data type, you lot'll likely commencement noticing that your VBA applications run relatively ho-hum and crave more memory than necessary. I explain the reason for this below.
Therefore, if you're committed to becoming a powerful VBA user, you lot must have a practiced grasp of VBA information types and, when required, be able to choose the virtually appropriate one.
So, should you always deal with VBA data types yourself?
Generally, declaring the variables that you use in lodge to determine their VBA information type explicitly is a good practice. Withal, if you don't want to exercise this, you can carry out your own cost-benefit analysis because the following criteria:
- As explained in Excel VBA Programming for Dummies, "letting VBA handle your information typing results in slower execution and inefficient memory use."
- Despite the in a higher place, Microsoft explains how using the Variant information type allows you to handle data more than flexibly.
- How noticeable are the issues of slower execution and inefficient retention use generally depends on the size and complication of the relevant VBA application. Therefore, in small applications or applications that don't use many variables, allowing Visual Bones for Applications to handle the VBA data types direct shouldn't generate large issues.
- Even so, when you're working with large and/or complex applications, letting VBA deal with data types automatically isn't very advisable. In these cases, y'all want to conserve equally much memory every bit possible. A good knowledge of VBA data types is essential in achieving an efficient use of memory when developing VBA applications.
In the words of John Walkenbach:
Letting VBA handle information types may seem like an easy fashion out – but remember that you cede speed and retentiveness.
In addition to the in a higher place, as explained in Mastering VBA for Microsoft Office 2013, non declaring the variables makes your VBA code harder to read and debug.
At that place are additional reasons to e'er declare your variables when working with Visual Bones for Applications. Permit's take a look at 2 further reasons, explained by Walkenbach in Excel 2013 Power Programming with VBA and Excel VBA Programming for Dummies.
Reason #i: Possible Misinterpretation Of Intended VBA Data Type When Using Variant VBA Information Type
According to Mastering VBA for Microsoft Role 2013, i of the disadvantages of using the Variant VBA data blazon is that VBA tin misinterpret the data sub-type that you intended. This can atomic number 82 to "rather obscure bugs".
Let's have a expect at a very uncomplicated example, involving strings and numbers, to illustrate this point.
Accept a look at the post-obit piece of very simple VBA code.
This Excel VBA Data Types Tutorial is accompanied by an Excel workbook containing the data and macros I employ (including the Variant_Concatenation macro higher up). Yous tin become immediate free access to this instance workbook by subscribing to the Power Spreadsheets Newsletter.
The 3 chief statements of the Variant_Concatenation macro in a higher place carry out the post-obit actions:
- "ExVar = "1"" assigns i to the variable ExVar. Since ExVar hasn't been declared, it is of the Variant VBA data type.
- "ExVar = ExVar + ExVar + ExVar" performs an functioning. The + operator is applied to the variable ExVar.
- "MsgBox ExVar" displays a dialog box with the expression that is currently stored by the variable ExVar.
The question is, what is the value displayed in the message that appears within the dialog box at the terminate of the Variant_Concatenation macro?
At first glance, information technology may appear that the dialog box should display the number three. Afterwards all, the second line seems to exist adding ExVar iii times, and ExVar has previously been assigned 1. 1 + 1 + 1 = 3, correct?
Well…
Fifty-fifty though the reasoning in a higher place makes sense, the rules that employ in Visual Basic for Applications tin can be slightly different to what we're used to. Permit's take a wait at the actual dialog box that is displayed when I run the Variant_Concatenation macro:
111? Where'due south the 3 that we were expecting?
If you lot're familiar with VBA, yous may have noticed some of the problems that the piece of VBA code that appears to a higher place has. Annotation how, when assigning one to the variable ExVar, the number 1 is enclosed in quotation marks ("").
This means that the number 1 is really a string instead of a number. When you're working with variables that contain strings and you use the + operator, Visual Bones for Applications concatenates all the strings.
Therefore, if "1" is a string, "1 + ane + i" is not equal to 3. The correct answer, in that instance, is the ane that appears in the dialog box to a higher place: 111.
Microsoft explains this particular characteristic of the Variant VBA information type by stating that, when a Variant variable contains digits, the interpretation depends on the context. Therefore, depending on such context, those digits may be interpreted equally either a string (equally in the example above) or the actual value.
This particular problem is not caused exclusively by the utilise of the Variant VBA information type. The assignment of the variable could've probably been better structured. Nonetheless, this is a further reason to avoid relying on the Variant data type e'er.
If you still want to rely on the Variant VBA data type, there are ways to determine the VBA data sub-type of whatsoever Variant variable. I show how you can apply these methods to the Variant_Concatenation macro in a section below.
Reason #2: Shortcut For Inserting Variables In VBA Code When Working With Declared Variables
Let's take a await at the following piece of VBA code. The purpose of this macro is to delete blank rows in Excel. Notice how the variable iCounter is properly declared.
Imagine that you lot're creating the offset version of this macro. And you're typing at the following point:
Theoretically, you must type the whole name of the variable: "iCounter". However, since the variable has been declared at the beginning of the Sub procedure, you lot can employ the keyboard shortcut "Ctrl + Spacebar" once yous've written the first couple of characters (usually two or three are enough). When you lot do this, the Visual Basic Editor does one of the following:
- If it knows exactly to what particular discussion (this applies to variables, reserved words or functions) you're referring to, it completes the entry.
This is what happens in the case above. The following image shows what happens one time I use the "Ctrl + Spacebar" shortcut:
- If it isn't sure near what word you lot're referring to, it displays a list of possible words from which you can select.
For example, let's assume for a second that the Delete_Empty_Rows macro that appears above has two declared variables: iCounter and iCounter1. In that instance, the Visual Basic Editor looks as follows:
How To Remind Yourself To Declare VBA Data Types
You lot may be convinced that information technology is a expert idea to always declare VBA data types when creating variables. Even so…
Nosotros all forget things sometimes.
Therefore, it's non surprising that, from time to time and despite our best efforts to the reverse, we forget to determine the VBA information blazon of a variable. Additionally, since Visual Bones for Applications is able to handle VBA information types automatically, yous may not fifty-fifty notice this happening.
Fortunately, yous can strength yourself to declare all the variables that yous use. To practise this, you simply need to apply the Option Explicit statement.
When enabled, the Option Explicit statement requires that you explicitly define all the variables in the VBA module where that particular statement appears.
How do you enable the Choice Explicit statement?
Piece of cake, simply type "Pick Explicit" at the tiptop (before whatsoever other code) of the relevant VBA module. Y'all only need to practise this one time per module. Withal, y'all'll demand to include the argument in any separate modules to which you desire the Choice Explicit choice to apply.
If you want to take information technology a step farther, you can have the Visual Basic Editor automatically insert the Choice Explicit statement at the beginning of any future VBA modules. Yous tin do this by customizing the code settings of the VBE in society to enable the Crave Variable Declaration choice. I explain how to do this in this VBE tutorial.
If the Selection Explicit statement is enabled, you'll just not be able to run VBA code that contains undeclared variables.
Enabling the Choice Explicit argument has an additional advantage: information technology helps y'all to spot typos or misspellings that, otherwise, might accept been hard to encounter. Let's see how this works in practice by checking a macro that deletes empty rows.
For this example, I'll use the macro whose VBA lawmaking appears in the image below, named Delete_Empty_Rows_2. I explain this macro in particular (and show how it works in practice) in this post.
In this particular macro, 2 variables are declared at the beginning: aRow and BlankRows. Both variables are of the Range VBA information type.
Now, allow's focus on the BlankRows variable. The prototype beneath shows how this particular variable is used several times in this particular Sub procedure.
Imagine that, while typing the VBA code, a typo somehow manages to squeeze in (just i). Instead of typing "BlankRows" (the appropriate name), you blazon "BlamkRows" (similar, only not the same).
As you lot may imagine, such typos may be difficult to spot in some situations. If you lot don't observe the mistake, the macro may non work as originally intended.
However, if you have enabled the Option Explicit statement, Visual Bones for Applications displays a prominent alarm. Accept a look at the screenshot below and find how like shooting fish in a barrel is to notice that "BlankRows" is misspelled.
Variant VBA Information Type
Variant is the default VBA data blazon. In other words, this is the information type that Visual Basic for Applications uses when y'all don't determine the type yourself at the moment of declaring a variable. You can, still, also declare a Variant variable explicitly.
In Excel VBA Programming for Dummies, John Walkenbach likens the Variant VBA data type with a chameleon. In other words, data that is of the Variant type "changes type depending on what yous practice with it." This allows you to work with information more flexibly.
This sounds not bad! Who wouldn't want this?
Well…
Not having to worry about VBA information types sounds neat in theory but isn't as simple in practice. Variant is, equally a general rule, not the well-nigh efficient data blazon. This inefficiency is caused, funnily enough, by its chameleon-like characteristics.
More precisely, in order for Variant to acquit like a chameleon and change of blazon depending on the context, Visual Basic for Applications must carry out sure checks for determining like which blazon should Variant behave. Equally yous can imagine, carrying out these checks repeatedly requires more time and retentivity.
As explained in Mastering VBA for Microsoft Role 2013, the Variant VBA information type requires more retentivity than any of the other data types, excluding very long strings. More precisely:
- Variant variables with numbers require 16 bytes of retentivity.
- Variables of the Variant data blazon with characters normally require 22 bytes of memory plus the memory required by the string.
And this is one of the reasons why I generally advise that you lot get used to declaring the VBA information type of your variables and utilise other VBA data types different from Variant. When you do this, Visual Basic for Applications doesn't need to be constantly carrying checks (as information technology does with Variant). This usually results in faster, snappier and better VBA applications, and more than efficient memory direction.
Let's take a wait at a few additional characteristics of the Variant VBA data type:
- When it comes to numeric data, you tin can store numbers within the following ranges:
For negative numbers: -1.797693134862315E308 to -4.94066E-324.
For positive numbers: 4.94066E-324 to 1.797693134862315E308.
- Variant's ability to handle different types of VBA data isn't absolute. In particular, Variant variables can't contain stock-still-length string data.
I explain what stock-still-length string information is below. In that aforementioned section, I likewise explicate the Cord data type, which is the one you'll take to use in such cases.
- Variables of the Variant VBA data type can comprise 4 boosted special values:
Value #1: Empty, meaning that no value has been assigned to the variable. You can see an example of this beneath.
Value #ii: Mistake, which is used for purposes of indicating errors in the relevant process.
Value #three: Nothing, which you lot can use when disassociating a variable from an object it was previously associated to.
Value #4: Null, which indicates that the variable contains no valid data.
Byte VBA Data Type
The Byte VBA data type is the one that requires less memory: but ane byte.
Variables whose VBA data type is Byte can be used to store numbers betwixt 0 and 255.
Boolean VBA Data Type
If y'all've always studied Boolean algebra, yous may call back that united states of america of the variables are Truthful and FALSE.
The Boolean VBA Data Type follows this logic. In other words, variables whose VBA data type is Boolean can but be set to one of the following 2 values:
- TRUE.
- FALSE.
In general Boolean algebra, TRUE and Imitation are usually denoted past i and 0 respectively. Even so, in Visual Basic for Applications, conversions betwixt Booleans and numeric VBA information types work slightly different:
- When converting a Boolean variable to a numeric VBA data type, True becomes –ane and FALSE becomes 0.
- When converting a numeric VBA data type into a Boolean, 0 becomes FALSE and all other values (regardless of whether they're negative or positive) become Truthful.
Boolean variables require 2 bytes of retention.
Currency VBA Information Type
As you'd expect, the Currency VBA data type is generally used in connection with monetary matters. However, every bit I explain below, yous tin use information technology for other purposes every bit well.
The Currency VBA data blazon is exact. The Single and Double information types that I explain below are rounded.
Currency variables can exist used to shop both positive and negative numbers. They're stored every bit numbers in an integer format that is scaled by 10,000. As a consequence, these variables give fixed-signal numbers whose length is up to:
- 15 digits to the left side of the decimal point.
- 4 digits to the right side of the decimal point.
As a consequence of the higher up, this data type allows for a range of values between -922,337,203,685,477.5808 and 922,337,203,685,477.5807.
Due to the fact that the Currency VBA data type is exact and these variables give fixed-indicate numbers, Currency is particularly useful for monetary calculations or fixed-signal calculations where accuracy is very of import.
The Currency data blazon takes up eight bytes of retentiveness.
Date VBA Information Blazon
This is another VBA information type whose proper noun is self-explanatory.
Indeed, you won't be surprised to confirm that Date variables can exist used to store values that stand for dates, times or both.
Nevertheless, if you lot've been working with Excel for a while, you may accept noticed that the style Excel and Visual Basic for Applications piece of work with numbers isn't necessarily the easiest to understand. Therefore, I provide a brusk introduction to this topic below.
As explained in Mastering VBA for Microsoft Office 2013, "VBA works with dates and times as floating-point numbers". Allow's accept a quick look at what this means:
Introduction To Floating-Signal And Fixed-Bespeak Numbers
Floating-signal is a way of representing numbers. Nether this formula, a number is represented in an guess style to a sure number of significant digits. This is so scaled using an exponent.
All the same, the most disquisitional point to understand is that the reason why this formula is called "floating-point" is due to the fact that the decimal point "floats".
Computers tin can likewise shop numbers as stock-still-point numbers. Yous can come across an example of this in the Currency VBA data blazon explained to a higher place. In the case of fixed-betoken numbers, the decimal points doesn't "bladder". Information technology remains fixed in a certain location and, therefore, the number has always a fixed number of digits later or before the decimal point.
Working with floating-point numbers, mostly, demands more computational resource. Therefore, the suggestion made in Mastering VBA for Microsoft Office 2013 is to utilize fixed-point numbers "whenever practical".
Date Variables
At present that you have a basic understanding of floating-point and fixed-point numbers, let's take a closer await at what the Date VBA data type can exercise.
In general terms, Appointment variables tin can store values representing:
- Dates between January i, 100 and December 31, 9999.
- Times between midnight (00:00:00) and 23:59:59.
These values are stored every bit floating-bespeak numbers, as follows:
- The appointment appears to the left of the decimal betoken.
- The fourth dimension appears to the right of the decimal point.
The post-obit are examples of how variables of numeric VBA data types are converted to the Date type:
- 0 represents midnight.
- 0.5 is midday.
- Negative integers are dates before December 30, 1899.
Date VBA variables require 8 bytes of retentiveness.
Decimal VBA Data Type
Decimal is one of VBA's numeric data types.
More precisely, the Decimal VBA information type can be used to store integers scaled by a power of 10. This scaling cistron varies depending on how many digits in that location are to the right side of the decimal point. The maximum number of these digits that a Decimal variable can concur is 28.
Because the above, the post-obit are the largest and smallest values for a Decimal variable:
- If there are no decimal places at all: +/-79,228,162,514,264,337,593,543,950,335.
- With the maximum number of decimal places (28), the largest value is +/-7.9228162514264337593543950335 and the smallest (excluding 0) is +/-0.0000000000000000000000000001.
In other words, the Decimal VBA data type gives you the largest corporeality of digits in social club to represent a particular number. Therefore, it'southward more appropriate for cases where you lot are performing calculations with large numbers that need to be very precise and can't be bailiwick to rounding errors.
The precision of the Decimal data type, comes at a cost in the grade of a large memory requirement. The Decimal VBA data type requires 12 bytes, which is larger than the other numeric data types.
As explained by Microsoft, y'all can't declare the Decimal VBA data type directly (merely as you practise with the other data types). Strictly speaking, Decimal is a sub-type of Variant. Therefore, in order to use Decimal, you must utilise the CDec conversion part.
Double VBA Data Type
The Double VBA information blazon is ane of the non-integer numeric data types. This means that, just as decimal, it tin be used to concord both integers and fractions.
More precisely, you can employ Double to store floating-signal numbers inside the following ranges:
- For negative numbers: -1.79769313486231E308 to -four.94065645841247E-324.
- For positive numbers: 4.94065645841247E-324 to 1.79769313486232E308.
I provide a brief introduction to floating-betoken numbers above. You may nevertheless exist wondering, why is this type of VBA information called double?
"Double" stands for "double-precision floating-signal". This makes reference to the number format which determines how the computer handles the number. There is likewise a Single VBA data type (explained below).
Double VBA variables require viii bytes of memory.
Integer VBA Data Type
You tin can use the Integer VBA data type for storing integers betwixt -32,768 to 32,767.
Integer variables just require two bytes. Due to its low memory requirements, the Integer VBA data blazon is normally the most efficient and better performing option for purposes of storing integers that fall within its range.
Every bit explained by Microsoft, you tin as well utilise the Integer data type for purposes of representing enumerated values. Enumerated values:
- Unremarkably contain a finite set of unique natural numbers. Each of these numbers has a item meaning.
- Are commonly used for purposes of selecting amidst different options.
Long VBA Data Blazon
"Long" makes reference to "Long Integer". Every bit implied by its name, y'all can utilize the Long VBA information blazon for storing integer values that are within a "longer" range than the range of the Integer data type.
More precisely, by using the Long VBA information type you can shop numbers between -2,147,483,648 and 2,147,483,647. If this range is still not enough for your purposes, you may want to employ the Double VBA data type.
Long variables require 4 bytes of memory. As you'd expect, this is more than than Integer variables (two), just less than Double variables (8).
Object VBA Data Type
You can apply the Object VBA data type for purposes of storing addresses that refer to objects. In general, Object variables are a convenient fashion to refer to objects.
Variables of the Object type take upward 4 bytes of retentivity.
Single VBA Data Type
I anticipated the beingness of the Single VBA data type when introducing the Double blazon above. "Single" refers to "single-precision floating-point", the number format that determines how the computer handles the number.
You can use the Single VBA data type for storing numbers within the following ranges:
- For negative values: -three.402823E38 to -i.401298E-45.
- For positive values: 1.401298E-45 to 3.402823E38.
Single variables crave iv bytes, half of those required past the Double VBA data type (8).
String VBA Data Type
For purposes of programming, a cord is generally defined equally a sequence of characters that represents the characters themselves (non a numeric value or other similar affair). Within Visual Basic for Applications, the String VBA data type is generally used to store text. However, this doesn't mean that you should only utilize letters within String variables. In addition to messages, String variables can incorporate numbers, spaces, punctuation and special characters.
In that location are 2 different kinds of Strings. The amount of characters and memory required varies depending on the type.
Type #1: Variable-Length Strings
Variable-length String variables can incorporate anything from 0 up to approximately 2 billion characters. They take up 10 bytes of memory plus the retention that is required for the string itself.
Type #2: Fixed-Length Strings
Stock-still-length String variables tin comprise between i and approximately 64,000 characters. These particular String variables require the amount of memory required by the string.
According to Mastering VBA for Microsoft Office 2013, fixed-length Cord variables "are rarely used in nearly programming". The main exception is when managing databases where a dominion specifying a maximum string length applies.
Yous may wonder what happens if the data that is assigned to a fixed-length String variable is dissimilar from the stock-still length. Visual Basic for Applications handles this affair differently depending on whether the data assigned is shorter or longer than the fixed length:
- If the information assigned is shorter, VBA adds trailing spaces in order to achieve the fixed length.
- If the assigned data is longer, Visual Basic for Applications truncates the data afterward the fixed length is reached. For these purposes, characters are counted from left to correct.
For example, if you assign the string "Tutorial" to a stock-still-length String variable with a length of three characters, Visual Basic for Applications only stores "Tut".
Other VBA Information Types
The to a higher place sections explain the most common VBA information types. From time to fourth dimension, you may meet some other data types, such as the ones I briefly innovate below.
LongLong VBA Information Type
The LongLong VBA data type is just valid on 64-bit platforms. This makes reference to how your computer is handling data.
Co-ordinate to tech website TweakTown, 92.eight% of new Windows computers use 64-chip operating systems. Y'all can find more information nigh the 32-fleck and 64-fleck versions of Windows hither, including how to check which version your computer is running.
You can use the LongLong VBA data blazon to store numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.
LongLong variables require 8 bytes of memory.
LongPtr VBA Data Type
LongPtr isn't (strictly speaking) a VBA data blazon. More precisely, the way LongPtr behaves depends on which platform is the relevant procedure running.
- In 32-scrap platforms, LongPtr becomes Long.
- In 64-flake platforms, LongPtr becomes LongLong.
Both the range of values that can exist stored using a LongPtr variable and the memory requirements vary accordingly.
Equally explained by Microsoft, 1 of the main uses of LongPtr is for pointers and handlers.
User-Defined VBA Data Types
Visual Basic for Applications allows you to create your own VBA data types. Every bit you'd expect, these data types are known as user-defined information types.
In social club to create a user-defined data blazon, you use the Type argument. These data types can contain 1 or more elements from the following:
- Data types.
- Arrays.
- User-defined data types that have been defined previously.
User-defined VBA data types are highly dependent on their unlike components. For example:
- The number of bytes required by a item user-defined VBA data type depends on its elements.
- The range of each of those elements is the ane that applies to the relevant information type.
I may explain further details of user-defined VBA information types in time to come VBA tutorials. Please brand sure to enter your electronic mail accost below if you want to join the Power Spreadsheets Newsletter and exist notified whenever I publish new material.
How To Decide Which VBA Data Blazon To Employ
Now that yous know about the primary different VBA data types, you may be wondering…
How exercise I make up one's mind which VBA information type to use for a particular variable?
You already know the bones rule for choosing data types proposed past John Walkenbach. Nether this general rule, your choice of data type is determined by the answers to the post-obit 2 questions:
- Which VBA data type(s) is(are) able to handle the information that y'all want to store?
- Out of the VBA data type(s) that tin can handle the data you lot desire to store, which data type uses the smallest number of bytes?
These guidelines may all the same experience a little flake full general. Therefore, beneath are some general suggestions provided by Richard Mansfield in Mastering VBA for Microsoft Office 2013:
- If the relevant variable will only contain logical values (True and False), utilize the Boolean VBA information type.
- If the variable volition always contain a cord, use the Cord VBA data type.
- If the variable will contain an integer use either the Integer or the Long VBA data type. The option between Integer and Long depends on how large the relevant numbers are.
Remember that neither Integer nor Long variables can exist used to shop fractions. Which takes us to the following point…
- If the variable you're declaring may contain fractions, utilize the Single or Double VBA data types. The decision to choose ane or the other depends on the range of values the variable may store.
- If you'll be using the variable in operations where yous can't have rounding errors (you crave no-rounding fractions), use the Currency or Decimal VBA information types. Every bit in the previous cases, the exact selection of variable depends on the size of the verbal numbers yous'll be storing.
You'll notice that, in several of the above cases, which information type you should utilise depends on the range of values that you desire the variable to exist able to handle. Learning how to cull the appropriate VBA information type (considering factors such every bit the range) is critical and, as computers get more than powerful and VBA develops, there may exist further changes to the available information types.
For example, loop counters used to be declared using the Integer VBA data type, which can't be larger than 32,767. Contempo versions of Microsoft Excel can have 1,048,576 rows. This clearly exceeds the range that Integer variables can handle. Therefore, a more than appropriate selection is using the Long VBA data blazon.
As you practice with Visual Basic for Applications, you'll showtime condign improve and more comfortable deciding which VBA data type to utilise.
Yet, if y'all're still non certain how these rules employ to a particular situation, this is a case where the Variant VBA data type may be of help. Let's take a expect at…
How To Find Out The VBA Data Sub-Type Of A Variant Variable
Remember that the Variant VBA data type behaves like a chameleon. Variant changes its sub-type depending on what particular activity you're carrying out with it.
Despite the downsides of relying on the Variant VBA data type that I have explained throughout this VBA tutorial, Variant can exist quite helpful for purposes of testing a particular variable and determining what is the most appropriate data type to utilize.
Let'due south have a wait at 3 ways yous tin practice this. In all examples, I apply the Variant_Concatenation macro that I used above to illustrate how the Variant data type may result in possible misinterpretations of your intended VBA information type.
This Excel VBA Data Types Tutorial is accompanied by an Excel workbook containing the information and macros I apply. You can become immediate gratis access to this case workbook past subscribing to the Ability Spreadsheets Newsletter.
Testing A Variable Using The Variant VBA Data Blazon
I may embrace the topic of debugging in future VBA tutorials. In this section I simply show you how to use the Locals Window and the Stride Into command for purposes of determining which particular data type to employ.
You tin can exercise this in the following 6 elementary steps, equally explained in Mastering VBA for Microsoft Role 2013.
Footstep #one: Display The Locals Window
For purposes of this process, the Locals Window of the Visual Bones Editor must be visible. This window displays all the variables in the relevant procedure, besides as their values.
You tin normally see the Locals Window at the bottom of the VBE.
If yous can't see the Locals Window in your VBE, yous can ask the Visual Basic Editor to display information technology by clicking on "Locals Window" inside the View menu.
Step #2: Step Into The Relevant VBA Sub Process
Yous can step into a process by using the "F8" keyboard shortcut or by choosing "Footstep Into" in the Debug carte du jour of the Visual Bones Editor.
Once y'all've stepped into the procedure, the Visual Basic Editor takes you to the first line of code in the macro.
Step #iii: Step Through Procedure And Detect Out Variant Sub-type of Variable
Every time you click the Footstep into command or the "F8" keyboard shortcut, the Visual Basic Editor executes a line of code and steps into the next. Practise this for purposes of stepping through the VBA Sub procedure and finding out what is the Variant VBA sub-type that Visual Bones for Applications automatically assigns to the relevant variable.
The following 2 screenshots show how this works in the example of the Variant_Concatenation macro that's been used as example:
- Notice how, initially, the ExVar variable contains the special value Empty. This means that the variable hasn't been initialized.
- In the following screenshot yous can see how the ExVar variable is of the String sub-blazon just i step later.
Bear in heed that simply because a variable of the Variant VBA data type is behaving in a certain fashion, it doesn't mean that this is the most appropriate information type to use. Think that i of the possible downsides of using this item data blazon is that the Visual Bones Editor may misinterpret your intended VBA data type.
Step #four: Exam The Procedure
Footstep a couple of times into the procedure following the steps I described above. Your purpose is to determine that the variable is existence assigned the same VBA data sub-type in a consistent manner. Once y'all've adamant that this is the case, you can proceed to the step #5.
Step #v: Declare Variable With VBA Information Type
Once y'all've determined that the relevant variable is behaving appropriately and that information technology is existence assigned the same information sub-type in a consistent mode, declare the variable using the VBA data type you've institute.
Pace #6: Test VBA Code
Once you've determined the VBA data type that you lot consider appropriate for the variable, test your VBA lawmaking in order to brand sure that the information type is the right one.
Finding Out The VBA Data Sub-Type Of A Variant Variable Using The TypeName Role
Visual Basic for Applications allows yous to determine the VBA information type of a variable by using the TypeName part. When applied, the TypeName role returns a cord displaying the VBA information sub-type information of a item Variant variable.
Let'due south see how you tin can apply the TypeName function to the Variant_Concatenation macro.
For these purposes, I showtime with the VBA code of the original macro that I utilize in the previous examples:
And I add several bulletin boxes that display the VBA data type of the ExVar variable at different stages of execution of the macro. The new macro is called "Variant_Concatenation_TypeName". The relevant statements (which are highlighted in the prototype below) are of the grade "MsgBox TypeName (varname)", where:
- "MsgBox" makes reference to the MsgBox function.
- "varname" is the Variant variable whose VBA information sub-type you want to find out (in this case ExVar).
You already know that, since the ExVar variable isn't declared, its VBA data type is Variant. Additionally, based on the previous department where we used the Step Into command for purposes of testing the variable, you know that the ExVar variable is not initialized at first (is Empty) and, after the assignment is made, information technology behaves like a string variable.
The expectations described above match what the message boxes display once the Variant_Concatenation_TypeName variable is executed. More precisely:
- The first message box to appear returns Empty, as expected.
- The second and third message boxes confirm that (at present) the ExVar variable is of the String VBA data sub-type.
Finding Out The VBA Data Sub-Type Of A Variant Variable Using The VarType Function
Yous can too use the VarType function for purposes of finding out the VBA data type of a particular variable.
The way VarType works is very like to the way the TypeName part works. More than precisely, the VarType function returns an integer that indicates what is the VBA sub-type of a Variant variable. The following are the values that can be returned by VarType:
- 0: Empty.
- i: Zippo.
- 2: Integer.
- 3: Long.
- 4: Single.
- v: Double.
- half dozen: Currency.
- 7: Date.
- 8: String.
- 9: Object.
- x: Error.
- 11: Boolean.
- 12: Variant. This detail value is just used with arrays.
- 13: Data access object.
- fourteen: Decimal.
- 17: Byte.
- 36: Variant containing user-divers types.
- 8192: Array.
The syntax of VarType is also very similar to that of TypeName. More precisely, the syntax of VarType is "VarType (varname)", where "varname" is the Variant variable whose VBA information sub-type you want to discover.
Therefore, I just create a new macro called "Variant_Concatenation_VarType". The VBA lawmaking is nigh the same as that in the previous example (Variant_Concatenation_TypeName). The simply difference is that, instead of using TypeName, I utilise VarType.
You probably expect that, when the Variant_Concatenation_VarType macro is executed, the first message box returns the value that corresponds to Empty, while the second and third message boxes display the value corresponding to String.
This is indeed what happens:
- The first bulletin box to be displayed returns the number 0. This corresponds to Empty.
- The 2nd and 3rd message boxes display the number eight. This number makes reference to strings.
Conclusion
The main purpose of Visual Bones for Applications is to dispense data. Therefore, in guild to master VBA and macros, you lot must accept a good understanding of what are the different VBA data types.
Subsequently reading this VBA tutorial you probably accept a good understanding of the different VBA data types that you can use, and how to determine which type to use in each situation. Choosing which information type to utilize may seem bad-mannered or a fiddling bit hard at first, only don't worry…
As with most aspects of Visual Basic for Applications, the more yous exercise, the better you lot'll become. Therefore, brand certain you beginning using the knowledge about VBA information types that you've gained past reading this post in your day-to-day activities.
Books Referenced In This Excel Tutorial
- Mansfield, Richard (2013). Mastering VBA for Microsoft Office 2013. Indianapolis, IN: John Wiley & Sons Inc.
- Walkenbach, John (2013). Excel VBA Programming for Dummies. Hoboken, NJ: John Wiley & Sons Inc.
- Walkenbach, John (2013). Excel 2013 Power Programming with VBA. Hoboken, NJ: John Wiley & Sons Inc.
Source: https://powerspreadsheets.com/vba-data-types/
0 Response to "A ____ Data Type Can Hold 14 or 15 Significant Digits of Accuracy."
Post a Comment