VBA Variables- Complete Guide (Every Secret is Out)

We are Covering below Topics on Variables:

1. What are Variables in VBA?
2. How do we define Variables?
3. What are the various Data Types?
4. Special Case Study on VARIANT Data Types (Why and Why not?)
5. Why Variables are important in VBA?
6. Option Explicit – What is it 
7. Frequently asked Interview Questions

---

 

1. What are Variables?

Variables are like containers. Imagine you have boxes in the kitchen to store sugar, salt and tea powder, and you label each box with a name. So, you keep filling them every month when it is finished. Correct?

In programming, these containers are known as variables. They know what information they are going to store and they can be reused again and again. Like, your Container, if it is emptied, it can be refilled with an item and if it is not emptied, it is still in use to store the item like Salt, sugar etc. In the programming world too, it is the same concept.

---

2. How do we declare/define variables ?

We start with a word “dim” . It means dimension. Every variable name that we choose should have a dim word before it. For e.g.

dim i as integer

dim sheetname as string.

You see , here dim is very important word and in VBA we do write this so VBA can treat the name next to dim as a variable. Here “i” is a variable name that as a user you can choose. There are some names you cannot choose due to the rules that we will discuss little later in this blog. So, once you have given the name to a variable , you go and decide the data type which we are learning in the next paragraph that what are the different types of data types and which one we should choose while working on the projects.

So, “sheetname” is also a variable name and string is a data type. As a good practice, we should choose a name for our variables which describes them best. Here, there is a variable called “i” which is ok but as a good practice , we should not use such names for our variables because in practical world what happens is when we write the long codes where you have let us say 100 lines of code, you cannot remember easily that your variable “i” stands for. But, If i rename it to “myscore” – you see it is so easy to understand that why this variable is there and for what purpose it is created. Here, one can say , it is using a scorecard value. The moment we read the code , we know this variable purpose in the Code. So, we should definitely name our variables which are close to their purposes. Let me give you more example.

Dim Score as integer , Dim football_team as string, dim VotingCount as long, dim myfruit as string. 😊😊😊

---

3. Variables Data Types ?

1. BYTE Data Type:

     You can store whole numbers within the range of 0 to 255. (No negative number).Pls note no decimals or no text data type is acceptable here in BYTE data type

Example: You always need to write Dim word followed by a variable name and then its data type. Dim means dimensional here. That is the way we write in VBA. Don’t think too much right now.Every programming has a way of defining the variables in terms of a syntax.

You see “i” is a name of a variable and you can name anything, There are some rules that we need to follow while naming variables. We will discuss that in a while. Don’t worry. We are not leaving anything here. Byte as i said is a data type which when we write , we write it based on our expectations. If i believe my value that I am going to use will remain within 0 and 255, I can use Byte. This way, VBA also keeps a check , if value is going to be more than 255 or negative , VBA will throw an error. Again, This too we will discuss in detail as to what can be the errors and how do you deal with this. You are reading one of the best blogs on VBA here. 😊😊

---

 

  2. Integer Data Type:

  You can store whole numbers within the range of  -32,768 to 32,767. 

Pls note no decimals or no text data type is acceptable here in integer data type however negative number is acceptable to -32k unlike byte data type.

Example:

---

 

3.Long :

If you need to store larger whole numbers within the range of -2,147,483,648 to   2,147,483,647. Again, Long is used only for whole numbers and no decimals or text values are allowed here.

Example:

---

 

4.Single and Double:

Use When: You need to store decimal numbers or Whole numbers as well, especially large number.

Single data type for smaller precision, Double data type for higher precision.

Frankly speaking, I don’t use double data types because I have never needed such a long decimal number. If I have to use a decimal number , it is generally one or two decimals like 1000.32 or 111.3.You know what I mean. So, for me, a single data type is fine if I am dealing with decimal numbers.

Single data type can deal with up to 5 decimals and Double can go up to 13 decimals in which I have no interest. I am a Data Analyst and the data I deal with every day is always like one or two decimals maximum. If you are wondering why I don’t use long data types? See, Long cannot hold decimals ,if you have seen a lecture. But, if it’s a whole number ,I don’t use a single one then. I use a long data type because it can hold a very big number.(Pls see the long strength in the above paragraph).

Bottom line is if we know fundamentals, we are the boss and we can choose whatever we would like to, however we should be considering the memory usage part too.

---

 

5.String:

Use When: You need to store text or a combination of characters.

Example: (I am not pasting any image here , by now you must have understood how do we use the variables)

Dim greeting As String

greeting = “Hello, VBA!

---

 

6.Boolean:

Use When: You need to store True or False values. Boolean only expects True or False. I dont use this data type much. But, you should remember this.This reminds me of excel functions like isnumber, istext, isodd etc. All these functions are used in excel and you know they return either True or False. Nothing else.

Example:

---

 

7.Variant:

Variant is a data type which VBA decides while reading the code. We use this data type when we are not sure about the nature of a value. For example, our value can be a text or decimal or a whole number so one data type cannot be used. By using variant data type VBA will give the correct data type.

Question can come to your mind then why do we bother about giving the other data types if the variant can work on any value since VBA decides it. Yes, you are right if you declare all variables as variants, technically nothing wrong and code will execute. But, variant is a data type which uses computer memory a lot. It uses the largest memory as compared to any other data type. So, our code speed will be slow. Such things are to be used wisely.

Example:

---

 

4. Special Case Study on VARIANT Data Types (Why and Why not?)

Let me explain this in detail. Something which will make you very curious, smart and knowledgeable.

When you run the code VBA will read the values and it will assign a string data type so the result variable will show you “Smile please” as an output.

Now, Let us change the values. So, num1 = 100.2 and num2 = 100

What do you think will happen now, if we run the code?

Remember data types are still variant for both the variables.

VBA will read these values as a number. So, it will assign the data type as double. Yes, it is going to be double because it can handle both whole numbers as well as decimal numbers. Don’t get confused why not long or integer. If it is a number , VBA chooses the highest data type which is a double, in case of numbers . So, VBA thinks it should be the double data type as it is the best for it. So, you need not to worry about it. If it is a whole number or decimal, you see data type is double – If you have used variant data type.

Variant means let VBA gives the data types.

Now, Tell me What is the final value in the result variable ?

It is 100.2+100 which is going to be 200.2

I am again reiterating myself because when I was learning I got confused.

Ideally, one data type here is a decimal which can be single or double (our mind thinks that way 🙂)  and the second data type should be byte or integer or single. But, VBA puts all numbers (whole,decimals) under double data type only.

Choosing the right data type is crucial for efficiency and prevents unnecessary memory usage. Understanding each data type’s purpose will help you make informed decisions based on your specific programming needs.

---

Now, the most important question arises in our mind. 😊

6. Why Variables are so important in VBA?

1.Storing Information: Variables hold different types of information, like numbers or words or strings. It’s like putting sugar,salt,cookies etc.  in your containers or boxes😊. Correct?

2.Ready to Change: You can change what’s inside a variable at any point in time by simply changing a value weather it is coming from excel cells or you have written it manually.

See below image. I can change “i “variable value manually and can change the value in A1 Cell of my sheet as well which will ultimately go to “i” variable. That is why we said they are always ready to change.

It’s like adjusting your container size in the kitchen based on what you need at different times. For example, I have two containers 3 kg each, one for sugar and second for salt. Let us say I have guests at my home and they will stay for a month so I planned to buy more sugar and also I thought why not to buy a 5 kg container. So, I can change my current container at any time depending upon the situation.

Same way, let us say the doctor advised you to eat brown rice to lose weight and you should stop eating white rice. So, the question is can you not use the same white rice container for brown rice. Of course , yes. You can, if you think it will save your money. Why to buy a new container from the market? Don’t be serious when I say eat brown rice to lose weight. While writing this blog, my family is cooking rice so I gave this  example. Hahahaha 😂😂

Same way , in programming, world speed costs us , data costs us. Choosing a wrong data type for your variables can impact the speed of a code. Don’t worry , if you do not understand this at this stage. I have covered this in this article for you. Let us go slow.

 

3.Easy to Understand: Giving each variable a name makes your program easier to read. Others (or even future you) can quickly know that this variable holds what type of information so one can easily connect the dots. For example , I have data which is bank data and I want to write a loop on that data. Now, instead referring this data by any name like abc, xyz , I will create a variable which has a name mybankData so this will help me in using this variable throughout the code very easily and if i have to read the code later on or make my team understand , these names can make my work very easy. Imagine , if I had created a variable “abc” and after a week I am coming back to work on this code and trying to explain this to my team, even for me this will be difficult to recall. So, giving a name , not only a name but a good name helps programmers to understand what is going on inside the code.

 

4.Calculations and Processing: Why do you store the information? It is obvious that we want to do the calculation and it can be any type of calculation. You have two variables named num1 and num2 – So, we can write result = num1+num2 or whatever we want. Here,the result is another variable. Pls note , we can store variables to have worksheet information , chart information, pivot information as well. In Fact,whatever you see in excel like charts, pivots, cells, sheets, workbooks, ribbons etc can be stored in variables. It is a deeper concept and right now nothing to worry about. We will see all these variables used in coming articles.

 

5.Saving Memory:  By giving a proper data type for variables we save a memory which results in faster execution of the code. If you remember, we have covered this in our video too. You may check the lecture again, if you would like to revise where I showed you why the byte data type can be chosen over integer or long and vice versa.

Let me give you an example. Suppose I want to calculate a total of three subjects like math, english and science. Now, we know marks can never be more than 100 , generally, so we must use the byte data type and not integer or long or single/double. Reason is that byte can efficiently handle these scores as it can hold 0 to 255 numbers. So, why do we choose integer or higher data types? If you remember, they take more memory than byte. Hence, if we are sure our numbers are not exceeding than 100 , it is a good move to choose byte. However, As I mentioned in the video too, if someone will use the higher data types even then code will run without any errors. It is just that it will eat up our memory more. Technically speaking, there are no errors in the execution part but fundamentally, I would like to take care of such things. Less memory used means more resources we can use and faster.

So, to summarize we now know that variables are like labeled containers that help you organize, change, and use information in your program. They make your code clearer, adaptable, and easier to work with.

Now Let’s dive into the used data types in VBA, providing a more accurate and user-friendly explanation with practical examples:

---

 

6. Option Explicit – What is it ?

Option Explicit by default does not show up in modules because VBA thinks it is not so important and it should be on user if he wants to use it or not. I personally always use it. Many people think it is unimportant and many vba developers like me think it is very important and powerful. So, I will put both sides perspective here. See the image below.