Get this lesson’s code from my GitHub.
This is post number one. In the event I forget something, my plan is to teach it when it comes up, then come back and add it to the appropriate more in-depth post. I am going to take a lot of parts of what I’ve learned from writing my own database and teach how to do it here. At the end, you will be able to take the parts I teach you and make your own document generation database that does tons of cool stuff like interact with Wunderlist or PayPal, or calculate your work availability, or tell you how your business is doing, or give you a case history, or generate tons of documents, or produce a word index.
So to start off, we are going to learn how to put certain building blocks together to build a custom solution (yours). The first step is learning what the building blocks are.
A variable is a name that stores some data. For example:
Function fHelloWorld() Dim sValue as string sValue = "Hello, world." Msgbox sValue End Function
So in this example, sValue is a string variable. We declare it on the first line, assign a value to it on the second line, and use it in the third line. As long as this function is open, you may use this variable in place of the value you assigned to it, until you assign it another value. Once the function is done, it will no longer remain in memory.
You can’t have symbols or spaces in your variable name or start it with a number. You can use underscores if you like, although I use something called camelCase instead of underscores. It is a good habit to use a naming system. There are various ones out there. I start all variables with a letter that represents what type they are (s for string, o for object, dr for DAO.recordset, i for integer, etc.). All variables must start with a letter. They can be between 1-255 characters.
Try to use unique names that describe what it’s doing, or storing, or being used for so that, if some random person has to go back through your code later, you’ve made it easier for them to read. Another reason to use unique names is that some variable names are pre-defined by VBA and so can’t be used, such as the word ‘date’.
Constants
A constant is a variable that has a (wait for it) constant value throughout the function. VBA has some built in and you may declare your own. Constants should be values that aren’t likely to change, like possibly the name of a SQL statement or the price of an item such as a page rate for transcription.
Explicit Versus Implicit
We are going to use explicit declaration throughout this blog, all the time every day. Explicit declaration is where you declare your variables at the beginning of your function. You declare it with the dim command like from our earlier example:
Function fHelloWorld() Dim sValue as string sValue = "Hello, world." Msgbox sValue End Function
I am not going to say much about this except that there are certain errors you cannot detect without explicit declaration and troubleshooting code is a lot easier with explicit declaration, so that’s how we’re doing it.
option explicit
“Option Explicit” is how you explicitly declare variables. At the top of every module or function, you should put this phrase.
Types
This is not an exhaustive list; i am covering the ones we will be using a majority of the time.| Variable | Memory (bytes) | Definition |
|---|---|---|
| Boolean | 2 | true or false |
| Byte | 1 | number 0-255 |
| Currency | 8 | money, up to 4 decimal places and 15 to the left. |
| Date | 8 | left = date | right = time |
| Integer | 2 | number -32,768 to +32,767 |
| Long | 4 | number -2,147,483,648 to +2,147,483,648 |
| Object | 4 | a container that allows you to access certain code commands |
| Variable String | 10 plus space for string | letters |
| Fixed String | size of string length | letters, static length |
| Variant | 16 if just numbers 22 plus space for contained characters |
sort of a wild-card variable type in that it can be used to represent any other type |
How do we declare these different variables?
Function fVariablesOnly()
'This is a comment. Declare your variables.
Dim x as Integer
Dim sSQL as String
Dim lValue as Long
Dim oWordApp as Object
x = 1
sSQL = "SELECT * FROM Table"
lValue = 3.4561
set oWordApp = CreateObject("Word.Application")
'never end a function without cleaning up all your objects.
oWordApp.Quit
set oWordApp = Nothing
End Function
Not Declaring a Type
Function fNotDeclared() Dim vNotDeclared End Function
If you don't declare a type, VBA assumes it is a variant. This means it will try to figure out what kind of type it is and use that, but it can't always figure it out and, as you can see from the table above, it costs more memory to not declare a type. Using more memory means your code runs more slowly, so just don't do it!
Public Versus Private
Global variables are public and private variables are concluded at the end of the function. Global variables remain in memory unless you clear them. Do not use global variables unless absolutely necessary and you really should only for information that isn't sensitive.
To declare a global variable:
Public sSQL as String
In my database, I gave my global variables their own module and created a function to clear them all immediately after each use. Clearing them is pretty self-explanatory:
sSQL = "" set oSQL = Nothing
Arrays
To declare a variable as an array, type it like this:
Function fArrayExample()
Dim sNames() as string
sNames = Array("Jim", "John", "Jacob", "Jingleheimer")
End FunctionOr you could do it like this:
Function fArrayExample() Dim sArray () as string sArray = Split(sExportTopic, "\") sFirstTopic = sArray(1) End Function
The above splits the contents of the variable sExportTopic at the "\"s in it into pieces that look like the next to last line.
So that's it for now. Next up, we'll cover operators and arithmetic.
Resources
If you want to read an in-depth reference book on this sort of stuff, I highly recommend Mastering VBA for Office 2013 by Richard Mansfield. I do not receive anything for saying that nor did anyone ask me to say that; I just found it a highly useful reference book while I was developing the Access database I use for my company. It really rounded out what i had learned up to that point. If you have a King County library account, you can check out an e-book version of it for free. He apparently has a newer version out for Office 2016, but I haven't read it, so I can't speak to it.
**While we have made every effort to provide accurate information, A Quo Co. and Erica L. Ingram assume no responsibility or liability for any errors or omissions in the content of this blog. The information contained in this blog is provided on an “as is” basis with no guarantees of completeness, accuracy, usefulness or timeliness.