In this part of the video series you’ll learn the following:
Overview of Modules and Procedures:
Single lines of programming code that performs a single task.
Words within statements which are part of VBA.
Sets of statements designed to be run together. Procedures exist in modules.
They hold 1 or more procedures. Stand alone modules are called standard modules.
Statements, declarations which can affect all the procedures in a module are called “General Declarations”
Here is a diagram of the previous explanation:
The VBA Code Window:
This is a sample sub procedure in vba:
Sub Greeting() MsgBox "How's it going?" End Sub
…to run it, click in the procedure (above the “Msgbox” text), and press F5 on your keyboard.
Option Compare Database
***The Option Compare Database is there to help with string comparisons.
Basically it allows your code to be case insensitive.
In other languages, a upper case “S” and lowercase “s” are different.
…well, when you use the “Option Compare Database” statement you are telling your code to view the upper and lower case the same.
If you don’t use the “Option Compare Database” statement you are telling your code that the capital “S” is different than the lower case “s”. (and the language is more like PHP, and Python, etc)
>> Here is an Option Compare Database example, so you can input on your own and try it yourself (paste the following code in a module by itself):
Option Compare Database Sub test() Dim str As String Dim x As Integer str = "test" x = InStr(1, str, "s") MsgBox x End Sub
Run the code (press F5 after clicking in the procedure) twice. Run it the first time without commenting out “Option Compare Database”, and for round 2 comment out “Option Compare Database”, and see what you get.
This statement will prohibit you from using a variable when it has not been declared.
You don’t have to have the Option Explicit statement, but it helps prevent variable misspellings
If you do not use it, you may use the variable with the correct spelling on line 5, but you may use the same variable on line 400, but with a different spelling, and then you’ll probably be pulling your hair out trying to find out why your code doesn’t work!
When you do use the Option Explicit statement, you’ll be notified of your variable spelling errors when you compile your code.
You can assign a value to a variable. Variables can mean anything (remember Algebra). This is useful if you want to start your variable out with some value.
Like, if you were doing a counter, and you want to start it out at a certain number:
Or you may want to assign name variable something like this:
…then you can something called “concatentation” to “glue” them together:
strFName & ” ” & strLName
The ampersand is the connector. So you are in effect saying, “append a space at the end of the strFName variable and then glue the strLName variable.
Click here for the database used in most of the modules:
(read this (Controlling Program Flow) before you take the test…)