Office tutorials

VBA data types in Excel

Introduction to data types

Why is it so important to assign a variable to the correct data type? Our program and its variables use the resources of our computer. If we want to ensure that our program works quickly and efficiently, we choose the types of data that we really want to use. For example, we do not write dates and numbers in text form, although this is of course possible. We do not use, for example, the type Double to write numbers, if we want to assign numbers to a variableon a scale from 1 to 10. For this purpose, the Byte type will work perfectly.

 

Numeric VBA data types

List of numerical data types in the Excel VBA and their detailed description is presented in the table below:

 
Data type Description Memory usage in Bytes The default value The range of accepted values
Byte The type used for natural numbers in the range 0-255 1B 0 Natural numbers in the range from 0 to 255
Integer A type used for integers with low values 2B 0 Total numbers in the range -32768 to 32767
Long A type used for integers with high values 4B 0 Total numbers in the range from -2,147,483,648 to 2,147,486,647
Single The type used to store real numbers 4B 0 The numbers range from -3.402823e38 to 1.401298e45
Double The type used to store real numbers 8B 0 Numbers from -1.79769313486232e + 308 to -4.94065645841247E-324 for negative values
Numbers from 4.94065645841247E-324 to 1.79769313486232e + 308 for positive values
currency The type used to store numbers in monetary units 8B 0 Numbers in the range -922,337,203,477.5808 to 922,337,203,685,477,5807
Variant (numeric) A type used both for storing numeric and character data 16 B vbEmpty Numbers from ranges corresponding to the Long and Double types

 

We assign variable assignments to a specific type of data in the manner described in the example below. After defining the variable, we can also directly assign a value to it. You can do this as shown in the example below.

 

Non-numeric VBA data types

 
Data type Description Memory usage in Bytes The default value The range of accepted values
boolean The type used for logical values ​​True / False 2B false True or False
Date The type that stores the date 8B 0 Dates with ranges from 100/01/01 to 9999/12/31
String (without a defined length) The type used to store character data 10B + number of characters A zero-length text string from 0 to 2 billion characters
String (fixed length) The type used to store character data number of characters The number of defined characters shown as spaces From 1 to 6,500 characters
Variant (character) A type used both for storing numeric and character data 22B + number of characters vbEmpty The range corresponds to the String range without a defined length, ie from 0 to 2 billion
Object The type used when operating on objects 4B nothing Any embedded object

 

 

As you can see, the Variant type can be used to save numbers and strings. If we know in advance which target value our variables should adopt, then using this type of data is not recommended. We use it in cases of short, fast-written programs. Types of VBA data in the Office suite are also written on the Microsoft website  at this link .

Tasks (You can enter the solution in a comment)

  1. Create a simple example. Define 4 numeric variables with different data types. Check if you can perform arithmetic operations on them. e.g. a * b + c ^ d. Use the Byte, Single, Integer, Double types.

 

More examples of Excel VBA basics can be found in the Excel VBA Tutorial section and  Examples in VBA  section. If you have any questions about this chapter, you can post them on the forum without logging in.

1 comment

    • anio on June 23, 2018 at 4:56 pm

    Reply

    Your method of telling all in this paragraph is genuinely pleasant, every one can simply be
    aware of it, Thanks a lot.

Leave a Reply

Your email address will not be published.