From the course: Access: VBA

Manage variable scope

From the course: Access: VBA

Start my 1-month free trial

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.

Contents