Welcome back. If you missed the last tutorial click here. If would like to start the series from the beginning click here.
Ok, so we know how to interact with Excel. Where do we go from there? Well, there are constructs in programming called variables. Variables are a representation of some data that can be given an arbitrary name so that it can be referred to later.
VBA handles variables two different ways. The first way is called a static or typed variable; whereas the second way is called dynamic.
If we think about any piece of information, there is a basic, underlying trait to it. This text, for example. The words are strung-together characters. Both happen to be data types — strings and characters.
So a data type is a classification of data.
So how does this all work in VBA? Let’s take a look
So here we see the subroutine “VariablesAndDataTypes”, and inside it there is the statement “Dim thisIsAString As String”. Let me break this down.
Before, I mentioned that when a word is highlighted blue, it is a word reserved by VBA. So “Dim” is a reserved word that is actually an acronym for “declare in memory”. So, when we want to make a variable, it’s called a declaration.
But what the heck is going on with “thisIsAString”? Well, programming languages have this constraint that variables cannot have spaces in their names, and they cannot start with a number (some languages prohibit certain characters from being in the name as well). So what do we do? WeMakeAReallyWeirdLookingWordBySmashingAllTheWordsWeWantIntoOne. It may look funny, but this is how actual code is written. Now, there are some guides — or best-practices — that programmers follow to help clean up names and not make them absolutely ridiculous.
- Names should be descriptive
- They shouldn’t be long
- There should be capitalization
The third one is debated on how much and where, but for this tutorial we’re going to be using a techinque called camelCase. And camelCase is written in camelCase. The first letter of every word, other than the first word, is capitalized.
So, back to “thisIsAString”… thisIsAString is the name I decided to give my variable. It’s very descriptive, but it can be said that it’s a little long. However, I like it so it just be like it is.
Both “As” and “String” are blue, so they’re reserved for VBA. “As” is a bit tricky. Even linguistically speaking, there is no real accurate definition for “as”. However, it’s used as a preposition in this context — which is the declaration of a variable.
The last part of the declaration is “String”. Which, I mentioned earlier, is a data type in VBA. So when we declare a variable as a string, we’re saying that the data stored in that variable will have the constraints of the string data type and will only have that kind of data in it.
Below the declaration of my variable I added a statement. “
Ok, now what? Well, now we really start to be able to do stuff with programming. Let’s see what happens when we perform the assignment statement on “A1” with the value of “thisIsAString”.
Like in the last tutorial, we affected what was in “A1”.
Ok, so that entire explanation I just gave was for typed-variables. With Dynamic variables, all of that can be thrown out the door.
Why would anyone want to go through all the extra typing for variables with declared data types? Well, there’s a general consensus that the users of software will always find a way to break it. Sometimes it’s on purpose, sometimes it’s on accident. So when you’re using a variable that really should be a string and a user puts in a 7.29, it could cause an issue with how your program functions.
Strings and characters are just two of the data types that are baked-in to VBA. Each one could have its own tutorial and maybe I’ll make some in the future. Right now, however, I do not have the time. If you’re curious about all the different data types click here. That’s the link to the documentation — the manual — for VBA.