Subroutines manipulate your database and its data. In this video, learn how to create a subroutine.
- [Instructor] When you read about VBA programming, you might see writers refer to subroutines and functions. Even though some authors incorrectly use the two terms to mean the same thing, they are actually different. The generic name for both subroutines and functions is a procedure. A subroutine is a type of procedure that operates internally but doesn't return a value. So for example, if you were to do a calculation, a function would return a value you could use in a formula, while a subroutine would not.
In this movie, I will focus on subroutines and how to work with them. I have opened the course database. And I need to switch to the Visual Basic Editor by pressing Alt + F11. Now here in the Visual Basic Editor, I can insert a code module. So I will go to the Insert menu and click Module. And I get a code window. To create a subroutine, you type the keyword Sub, S-U-B. Then you need to give a name for the subroutine.
The subroutine's name must start with a letter and it may not contain any internal spaces. There are also some other characters that you can't use. But if you stick with letters and numbers, you'll be fine. So for example, if I were to create a subroutine called DbaseName, that would be fine. Next, I'll type in open and closed parentheses. I'm not passing any arguments or any data to the subroutine. So I can leave the space between the parentheses blank.
The parentheses do need to be there. And I'll show you in a little bit later in this movie, how to pass arguments from one subroutine to another. All right, I'll press Enter a number of times. That gives me some white space. And when I pressed Enter the first time after the sub line, you see that Access added the End Sub line, indicating the end of the subroutine. All right, now I can start by creating the code within the subroutine. I'll start by declaring, or dimensioning, a variable that I'll call sName as String, then Enter.
I will assign the string Order Tracking to that. So, sName, equal, and then in double quotes, Order Tracking. And now, I will jump out of the subroutine for a moment, and add another one. You'll see why in a second. So I will click below the End Sub line and I'll start creating another subroutine. That will be Sub ShowDbaseName and then I will type a left parentheses, but now I need to identify the data or the argument that is going to be passed in.
That will be the database name or sName variable that I used in the other subroutine. But I'll call it something different so I know that it's in a different subroutine. So I'll say sDbase as String. And Enter a couple of times. So what I've done is to accept data from another subroutine. Within the ShowDbaseName subroutine, that variable is sDbase and it is expecting a string value.
And note for continuity, that up in the first subroutine, we have sName as a String, and assigned it the value Order Tracking. Now we'll have a single line of code that uses a message box to display the variable that was passed to the subroutine. So that will be MsgBox sDbase. And again, this is the name that we're giving it within this subroutine. Then a down arrow, and the code looks good.
All right, now I'll switch back to the first subroutine. And I will call the other subroutine from within it. So I'll do ShowDbaseName, and again that's the name of the other subroutine we're creating. And in parentheses, we will pass it sName, which is the string variable that we are defining within this subroutine. Also note that, because Access can see what other subroutines and functions are available, that when I type ShowDbaseName and then a left parentheses, it tells me what to expect.
So this would be sDbase as a String. And again, that is sName is the string that we are passing. All right, I will make sure that the insertion point is flashing in the top subroutine. Press F5 to run. And we get Order Tracking as the name. Click OK, and we see that our subroutines are working as expected.
- Working with macros
- Running a VBA procedure
- Debugging VBA code
- Defining variables, constants, and calculations
- Displaying a calculated result in a message box
- Repeating tasks with loops
- Manipulating database objects
- Adding, editing, and deleting records
- Looking up values in a table
- Automating processes with macros