Changing variable scope lets you control which procedures can see which values. In this video, learn how to manage variable scope.
- When you create a variable in a subroutine or function, it can only be seen by that procedure. The range for a variable's visibility within a code module is called its scope. You can control the scope of your variables either by leaving them within procedures or by declaring global variables. I'll demonstrate how to manage the scope of your variables in this movie. I've opened the course database and I'll press ALT+F11 to move to the Visual Basic editor. Once here, I will open a new code module.
So click insert, module. And there we go. I will create two subroutines to start. The first will be Sub GetSale, and I'm not passing any arguments or values to the subroutine so I will type in (). Then I need to declare a couple of variables, so I'll type Dim and then dblRate as Double.
So that will be the commission rate, then a comma, then curSale as Currency, and that's all I need for here. Next I need to get the value of the sale so I will type curSale = InputBox. Then the prompt will be enter value of sale. Everything's in parenthesis and double quotes. And I will assign the commission rate, so dblRate = 0.05.
And finally I will display a message box that contains the result of another subroutine that I'll call in a moment. So, that would be MsgBox ("$" & and the results of CalcComm, short for calculate commission, based on curSale. There we go. Now I need to create a function below the existing subroutine, and that will be the CalcComm function.
So, I will type Function, and that's the key word, and then CalcComm followed by a left parentheses and we'll pass it the sale value, and I'll call it something different, curValue and that will be a currency value. Right. Looks good. There we go. And what this function will do, is multiply current value, which is the value that's coming in, called current sale. And the other subroutine by double rate.
So we'll have CalComm. Excuse me it should be CalcComm. CalcComm = curValue * dblRate. All right, so I have double rate defined here, I have passed the sale value to the function and we're ready to go. So if I type in a $100 sale, we should get a commission of $100. So, I have clicked inside of the GetSale subroutine. Press F5.
Enter the value of the sale, type a hundred, and if I did this right we should see $5. Click okay. And we get zero. Alright. Something went wrong. Click okay. What you probably suspected, is that the double rate, which I defined here, isn't seen by this procedure. It isn't seen by function. What I can do, however, is define double rate as a global variable and if that's the case then the value should be visible everywhere. So, I will go up to the declarations area, and that's after Option Compare Database, and I will type Dim dblRate as Double.
There we go. And I will delete the declaration within GetSale. So I have it declared here, I assign the value here, and with luck, if I've done everything right I should see it used in the function. Alright, so, I have my values, I've clicked in GetSale. I'll press F5 to run. Value of the sale, make it 100. Click okay. And we get a $5 commission.
So I'll click okay. The reason that worked is that I declared the variable in the declarations area and it can be seen throughout the entire module which means that assigning a value within one subroutine makes it visible here in the other function.
- 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
Skill Level Advanced
What you should know1m 38s
1. Introduction to Visual Basic for Applications
2. Define Variables, Constants, and Calculations
3. Add Logic to Your VBA Code
4. Debug Your VBA Code
5. Manipulate Database Objects Using DoCmd
6. Read and Manipulate Table Data
7. Manipulate a Database Using the Application Object
8. Control Forms and Reports Using the Me Object
9. Automate Processes Using Macros
Next steps1m 7s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.