Get this lesson’s code from my GitHub.

This one’s pretty easy or self-explanatory, but it still deserved its own section. You can do a lot of cool things with strings if you understand the different operators and how you can use them in combination with if-then-else statements and comparing strings.

It is a good idea to keep short lines when you code. To continue onto the next line in VBA, you type a space followed by an underscore, then enter to go to the next line. You may indent after that however you like for looks, but those three characters need to be typed 1, 2, 3; space, underscore, enter; just as I described.

Math operators include all the arithmetic symbols, such as:

'       > < + - * / <> =
' greater than, less than, plus, minus, 
         'times, divide, not equal, equal

Here are some examples of how we use them. I’ll be using if-then-else statements, which we have not covered yet, to demonstrate:

Function fFunction()
dim x as integer
x = 3
If x < 4 and x >= 0 Then 
     Debug.Print x
Elseif x > 5 Then 
     Debug.Print "x is too big!"
Else
End if
End Function

The following makes use of some of the operators as well as showing how to extend a piece of code to the next line.

Function fFunction()
'declare variables
dim sValue1 as string
dim sValue2 as string
'assign value to them
sValue1 = "Hello "
sValue2 = "World"
'use of logical operators:
If sValue1 = "Hello " and sValue2 = "World" Then 
     Debug.Print sValue1 & sValue2
Elseif sValue1 = "Hello " or sValue2 = "World" Then
     Debug.Print sValue1 & sValue2
Else
     Debug.Print sValue1 & "!"
End If 
If not sValue1 = "Hello" Then
    Debug.Print "I didn't say hello.  i said, " &  _
         sValue1 & "!"
End Function

Concatenate strings with ‘ &’ like in this example. Also shown in this example is “Chr(32)”, which is called an ASCII code, which represents a single character. We will be using these moderately throughout, so I have demonstrated it here. You can find a table listing all characters and their ASCII numbers here.

Function fFunction()
'declare variables
dim sValue1 as string
dim sValue2 as string
dim sValue3 as string
'assign value to them
sValue1 = "Hello"
sValue2 = "World"
sValue3 = sValue1 & Chr(32) & sValue2  _
     & Chr(33)
     'hit Ctrl+G to see debug window.  
     'Prints in debug window 
          '"Hello World!" without the quotes.
Debug.Print sValue3
End Function

We’ll discuss these in more detail as they come up in the functions I’ll be showing you how to write. Next up, we’ll be discussing DAO recordsets.

**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.

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 Function

Or 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.