Scope
By Richard Rost
4 years ago
Variable Scope & Visibility in Microsoft Access VBA
In this Microsoft Access tutorial, I will teach you about variable scope and visibility in VBA. We'll learn about public vs. private, the keywords dim and global, and the different scopes including procedure-level, form-level, and database-level.
Sandra from Norfork, Arkansas (a Gold Member) asks: Can I use Dim X as String on multiple buttons on one form, or does each button require a separate character? I've watched lots of videos, but have not found one yet that answers this question.
Pre-Requisites
Recommended Courses
Links
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, Understanding variable scope and visibility, vba, invalid outside procedure, variable not defined, can't find the field referred to in your expression, run-time error '2465', application-defined or object-defined error, dim, private, public, global, compile invalid attribute in sub or function, procedure-level, form-level, database-level
Subscribe to Scope
Get notifications when this page is updated
Intro In this video, we will talk about variable scope and visibility in Microsoft Access VBA. You'll learn the differences between procedure-level, form-level, and database-level variables, how visibility works with private and public declarations, and how to properly use variables across forms and modules. I'll demonstrate why you can reuse variable names in different button events, how to access public variables from other forms, and offer best practices for managing global variables in your Access projects.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. Today's video is for my VBA students. We're going to learn about variable scope and visibility in Microsoft Access VBA.
Today's question was posted in my forums by Sandra from Norfolk, Arkansas. I almost said Norfolk, like Norfolk, Virginia - Norfolk, Arkansas. One of my Gold members. I always say, can I use Dim X as String on multiple buttons on one form? Or does each button require a separate character? I've watched lots of videos, but I have not found one yet that answers this question. This was posted in my forums under the For Next Loops video.
Adam and Scott, my moderators, both jumped on it right away and gave her an answer. I do cover this in Access Developer Level 4. It's called variable scope. And to answer your question, Sandra, like they said, you can use the same variable name in different buttons because each button has its own local procedure-level scope. Let's talk about the different kinds right now.
In dealing with variables, there are two things to keep in mind: scope and visibility. Scope is where a variable works, and visibility is who can see that variable.
There are three types of scope. There's procedure level, which is inside a sub or a function, which is what you get when you create a button and you've got code under that button. Then you've got form level, which works inside the single form itself (also a report, but we're just going to call it form level because I do 99 percent of my programming in forms, but the same thing applies to reports as well). Then you've got database level, and these are variables that work over the entire database.
In addition to scope, you've also got visibility. Private means that the variable is only visible inside its own scope, so inside that sub or function, inside that form, or inside that module. Public means it's global to the entire database. Everybody can see it, but you still have to call it by its proper name. We'll talk about that in a minute.
First, let's talk about some prerequisites. If you're scratching your head and you're wondering what all this stuff means, if you've never done any VBA programming before, go watch this video. It's free. It's on my website and on my YouTube channel. It's about 20 minutes long and will walk you through all the basics of programming in VBA.
After that, go watch my variables video. It'll teach you all about what variables are and how to use them. I also talk a little bit about public and private variables there, too, but we're going to go over them in more detail today. If you're interested, the video that Sandra was commenting on is my For Next Loops video that teaches you how to do a one-to-ten kind of loop: X equals 1 to 10. This one's optional - watch this one if you want to.
Let's talk about the different variables and their scope. We've got our main menu here, and I've got a couple of different buttons. If you right-click and go to Build Event on any of these buttons, that's going to bring up the code builder. You can see right here, there's a Private Sub CustomerListButton_Click. This is a procedure - a procedure is either a sub or a function. Any variables you declare inside this procedure only have scope, only work inside this procedure.
For example, I could say Dim s as String right here. If I come up here and Dim s as String again, those are two different s's - completely different. This sub doesn't know anything about that sub. It's private to the inside of this subroutine, and you can reuse the name. That's fine.
So let's say in here, I'll say: s = "Rick" and then MsgBox s. Then this one up top here, I'll say s = "Joe" MsgBox s. Go back out here, look at those buttons. There's Rick, and there's Joe, and they don't know anything about each other. They have limited scope.
The next level up of scope is going to be a form-level variable. For example, come up here. I'm inside a form module right now. This database doesn't have any actual database-level modules. But I'm inside of the form module. This is the module behind this form. You can always see it's right up here: Form_MainMenuF. These are all form modules.
I usually turn this thing off in my classes because I seldom use it, but it's the Project Explorer right here. You can turn that on and off.
Now you can declare a variable out here if you want to: Dim s as String. That is now a form-level variable. You can't set the value of a form-level variable up here - variables can only be set inside of procedures. If I try to put in here s = "Alex," it'll be there and you'll think it's working, but it's actually not going to work. In fact, if you try to compile your database right now, you'll get an error message: Invalid outside procedure. You can't do this outside of one of these subs or functions. This is why it's a good idea to compile your database every now and then because it'll catch stuff like this.
You could type something in here and your database will work just fine. You can come back in here and you can click on buttons. But sometimes you'll be working on your database and you won't even know that that's not running, especially if it's in a form that you don't use much. So we'll get rid of that there.
Where can you set that variable's value? Anywhere you want to, but usually I'll do it in the Form Load event. You can go to Design View, open up the form's properties, then there's the On Load event and the On Open event. They basically do the same thing. The only difference is that On Open can be canceled. You could put it right in here and you could say s = "Alex".
But now here's the thing. I've got a couple local variables called s, and I've got this form-level variable called s. Let's see what happens. Come back out here, close this, click on that - that still says Rick. And this one still says Joe. Let's go and let's just change what this button does. Let's say in here I only want to MsgBox s, but I'm not going to declare a local variable in here. I'm not going to declare a procedure-level variable. This one should use the form variable.
Let's try it and see. Close that, and click. There's Alex. This shows you that the priority of scope actually goes backwards. The priority is: procedure-level variables take priority. If something is declared inside that sub, it's going to use that if you've got two variables with the same name. If you declare a form-level variable with the same name, it will work in any procedures that don't have that also as a variable name. Beyond that, there's the database level, which we're going to get to in just a minute.
To recap, we've got local variables in the procedure (procedure-level variable), form-level variable (declared at the top of a form), and then you can have database-level variables. Those have to be put inside of a module.
Let's create a module. Save it right off the bat. We'll call this "GlobalMod." In here, you can make variables too. Let's do a Dim s - actually, we already have s in the form, so let's do a Dim MyName as String. Save it. Debug Compile. Everybody is good so far.
Let's go back to this form here. Go back to this form's code. Now, when we set s as Alex, let's also set MyName = "Sam". Then up here, let's MsgBox s & " " & MyName. Let's see those both.
Let's see what happens here now. Oh, variable not defined. What's not defined? MyName = "Sam". Why is MyName not defined? Back here, Dim is essentially the same as saying this is a private variable (private and public). If you use Dim, by default, that gives you a private variable, so only that form or that module will see it. You have to declare it as Public if you want it to be global, so that anybody can see it. Dim and Private pretty much do the same thing. Generally, you use Dim inside procedures, whereas Private is usually used at the form or database level, but they're pretty much interchangeable.
You might also see the word Global. In a lot of my classes, I use Global a lot because in the old days, the keyword was Global. There was no such thing as a Public variable. They added Public a little while later and they kept Global around for backward compatibility. So if you see these terms, or if you hear me refer to a global variable, Global and Public is the same thing. Dim and Private are pretty much the same thing. Don't get confused by the keywords.
This thing here I got in my global module, if I just use Dim, that means only other subs and functions in this module will see it. It's not visible to the outside world. I have to come in here and change this to either Global or (preferably) Public. Now, the rest of the database can see it. If I compile, everything looks good. If I run my form, there's Rick, and there's - oh, there's Alex and Sam. See that? That's what was set.
The benefit of doing it like this is, you can use this variable anywhere in the database now: in your queries, in your forms, in your reports, whatever. If you've got a value that you want to bounce around, it's good for storing a user name with a currently logged on user or whatever other information you want to persist throughout the entire database without having to actually save it into a table.
Now, there's one interesting thing about form-level variables. Up here, we've got s as String, and this is a form-level, private variable. But you can make form-level variables public, too. Let's say you've got Dim x as Long in here. Now, remember, Dim by itself is private, so only this form can use it. But we can change that to Public. Now, anybody else can get a hold of that variable as long as the form is open. That's the catch. It's got to be open. As soon as you close this form or report, that variable goes away.
Let's set it when the form opens. Right down here, we'll say x = 1701. So it's got a value. Now, let's get it from another form. Let's open up the Customer form. Oh, I got rid of the buttons to open up the Customer form, didn't I? Let's go over here, Customer form. Let's go in here and we'll just change this button here.
We know how to get information from another form - how to get a value from an open form. This video deals with how to get a value from a form's field. For example, if you want the FirstName field off the customer form: Forms!CustomerF!FirstName. But I don't want that; I want a variable. The variable we want is on the MainMenu form and it's called x.
Let's see if we can do that. Let's MsgBox Forms!MainMenuF!x. Let's see what happens here. Debug Compile - hold on, variable - oh, I've got the s over here. Let's get rid of this guy. Remember, I had the form-level s up here. Let's just get rid of that. Come here. OK. Debug Compile - oh, I've got another variable not defined here. Well, there's my s again. Get rid of that. This is why Debug Compile is important - you can find all these little things that have changed in your database.
Now I compile OK. Everything looks good. Let's close that. Go back to the Customer form, and let's click our button. Error: can't find the field x referred to in your expression. It can't find the field x. Let's debug that. That's obviously not a field. So what's wrong here? You can't refer to a variable like you refer to a field, but there's one little change. Instead of referring to it with the bang (!), you just put a period (.) there. That's it. Variables get treated like properties. Technically, Access creates a property with that variable name. It's kind of weird, but that's how you do it.
Now I should be able to get that variable name. There's another thing I'm going to teach you coming up right here. Let's click this now. It's 0. Why is it 0? It's 0 because whenever you get a compile error and the debugger kicks in, it clears all variable memory for all variables everywhere in the system, which is annoying. That's one of the reasons why it's good to use TempVars. The guys have been bugging me to make a video about TempVars for like a year now. It's on the list. I just haven't gotten to it yet. I do cover TempVars in Access Developer 31, but yes, I'm going to be making a TechHelp video on it pretty soon.
Basically, TempVars are another kind of global variable. They persist over the entire database, and they survive a debug crash. What you have to do in this particular case is re-initialize the forms. You've got to close that form. This is one of the reasons why I start teaching people from a very early age: whenever you do some programming work, shut down the form and restart it. It re-initializes all your variables.
Open that up. x should now be set to 1701. If I open this guy up and hit the button, there's 1701. So you can read the public variable from this form, which now has a value. Be careful if you do this because I could come in here and put "xyz" in there. It'll compile, and then when you come in here and try to run it, you get an application-defined or object-defined error because it's not a compile error. That's a good piece of code; it's just that when it runs (a runtime error), it's going to try and find that variable and go, "I don't know what they're talking about." Access doesn't evaluate this kind of stuff at compile time. So be careful of your spelling.
I still stand by my statement that 90 percent of problems come from people spelling things wrong. I get emails all the time from people; they send me an email and in their question, they type in a paragraph explaining everything. In that paragraph alone, I can see two or three typos just from regular words they misspelled. I can tell you right now, you've got a typo somewhere. You're getting an "Enter Parameter Value" because you typed in something wrong. Spelling counts, people. It's not like English class where you just get points off. If you spell stuff wrong in your code, it's just not going to work.
The good part is when you get an error message like "variable not defined." The bad part is when you do a form field wrong and the code still runs - you just don't know it's not working. That's the sinister part.
One thing I also want to point out while I was doing my research: whenever I do one of these videos, I've been doing this stuff for 30 years now, so I know variables and scopes and all that inside and out. But whenever I do a new video, I always take at least a half hour and I Google that topic just to see what everybody else is out there saying, in case there's something new that's come up that I don't even know yet. I found two or three different websites that said you can't access the public variables of a form from another form. Yes, you can. I just did it. You have to be careful with what sources you read. Remember, when people post things in message forums, they don't always know what they're talking about. Be careful who you listen to.
Let me show you an example of where a global variable is useful. For example, let's go to the global module. Let's make a variable: Public LastCustomerID as Long. We're going to store the last customer ID that was accessed in a public variable so that if you're viewing through customers and you want to go back to the last person that you had accessed, you close the customer form and want to go back to that last customer, we'll make a button to open up the last customer.
Now let's go to the Customer form. Let's go to the Customer form OnCurrent event. Right in here, we're going to say LastCustomerID = CustomerID. LastCustomerID is defined in the global module, and this is a field on this form. If you're ever curious, you can right-click and go to Definition and it'll show you where that's defined.
Now as you're going through your customer form, if I go here, here, here, here, stop on Wesley Crusher number seven. Close that. Let's make a button on this form that opens last customer. "Open Last Customer." Right-click, Build Event: DoCmd.OpenForm "CustomerF", , , "CustomerID=" & LastCustomerID. Save it. Compile. Oh, variable not defined. I got rid of MyName, didn't I? Let's get rid of all this stuff we don't need. Get rid of this x. All right, we're good. Now I got a compile error so it's going to clear that memory variable. If I do this now, we go back to Wesley Crusher. It didn't clear it. It doesn't always clear things, but most of the time it does.
If I go through stuff - oh, error, debug, what happened? LastCustomerID = . That's interesting. Can't assign it to null. Let's say: If IsNull(CustomerID) Then Exit Sub. Save it and come back out here. Go there, let's close this, come back into the Customer form, browse around a bit, do this, do this, stop on Tasha Yar this time, then open last customer - there you go.
Microsoft actually has a pretty good page on their website expanding on scope and visibility with some different examples. It's pretty much everything I just told you. Check that out if you want.
As I mentioned earlier, I do cover scope and variables and a lot more in my Access Developer Level Four class on my website. I'll put a link to this and everything else I mentioned in the description down below the video. You can click on those links and come on and visit.
That is your Fast Tips for today. I hope you learned something, and I'll see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you've finished the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you've finished the expert classes. These are the full-length courses found on my website, not just for Access, too. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you're a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website.
But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.Quiz Q1. What does variable scope determine in VBA?
A. Where a variable works within the application
B. The data type of the variable
C. How a variable is displayed on forms
D. Which users can change the variable
Q2. What is the visibility of a variable in VBA?
A. The physical location of the variable in the database
B. The format in which the variable is displayed
C. Who can see and access the variable
D. The security level required to use the variable
Q3. How many common types of variable scope exist in Access VBA as discussed in the video?
A. Two
B. Three
C. Four
D. Five
Q4. Which statement about procedure-level (local) variables is correct?
A. They can be accessed by any form in the database
B. They are visible to all subs in a module
C. They are only accessible within the sub or function where they are declared
D. They are automatically saved to a table
Q5. Where should you declare a form-level variable if you want it to be shared between multiple procedures on the same form?
A. Inside each sub or function
B. At the very top of the form's module, outside any procedure
C. In a separate table
D. In a query
Q6. What must you remember when referring to a form-level variable from another open form?
A. Use the bang (!) notation
B. Use the bracket notation [ ]
C. Use the period (.) notation to refer to the variable as a property
D. Use a colon (:) between the form name and variable
Q7. If you declare a variable as Dim at the top of a module, what is its default visibility?
A. Public to the entire database
B. Public to all forms
C. Private to that module
D. Global to all users
Q8. What keyword should you use in a module to make a variable accessible from anywhere in the database?
A. Dim
B. Private
C. Public
D. Local
Q9. What is the main difference between the keywords Global and Public in VBA?
A. Global is for form-level, and Public is for database-level
B. Global and Public do the same thing in modern VBA
C. Public variables are not accessible in queries
D. Global is used only in queries
Q10. What should you do after making changes to your modules or code to help catch variable scope or declaration errors?
A. Ignore compiling
B. Only run the code
C. Use the Debug Compile option
D. Rename all your variables
Q11. When reading a public variable from another form, what requirements must be met?
A. The form must be closed
B. The form must be open
C. The variable must be private
D. The variable must be accessed using a query only
Q12. What happens to variable memory when a compile error occurs and the debugger starts?
A. Nothing changes
B. Variable values are retained
C. All variable memory is cleared everywhere in the system
D. Only local variables are cleared
Q13. What kind of variable is recommended if you need a value to persist across the database and survive a debug crash?
A. Local variable
B. Form-level variable
C. TempVar
D. Table field
Q14. Why is spelling important when referencing variables or fields in VBA code?
A. The code will automatically correct small errors
B. Misspelled names result in runtime or compile errors
C. It only affects the appearance, not functionality
D. VBA ignores incorrect spellings
Q15. Which of the following is NOT a valid reason to use a global (public) variable according to the video?
A. To store the currently logged-on user
B. To share a value across queries, forms, and reports
C. To automatically save data to a table permanently
D. To retain a value for use throughout the current session
Q16. What is the main caveat when using public variables in a form?
A. They are only available when the form is open
B. They are saved in the database permanently
C. They can be accessed even with the form closed
D. They require user-level security
Q17. According to the video, how should you refer to a public variable named x declared in an open form called MainMenuF from another form?
A. Forms!MainMenuF!x
B. Forms.MainMenuF.x
C. Forms!MainMenuF.x
D. Forms.MainMenuF!x
Q18. Which Access feature is especially useful for storing values that must persist even after debug crashes?
A. Form fields
B. TempVars
C. Private variables
D. Command buttons
Q19. What advice does Richard give regarding online information about Access VBA programming?
A. All forums have correct information
B. Always trust anonymous advice on message boards
C. Be careful who you listen to, as some sources may be incorrect
D. Microsoft documentation is never useful
Q20. Why did the "variable not defined" error occur when trying to use MyName in different parts of the database?
A. MyName was not spelled correctly anywhere
B. Dim only makes a variable private to its module unless Public is used
C. Public variables cannot be used in Access modules
D. The form was not saved
Answers: 1-A; 2-C; 3-B; 4-C; 5-B; 6-C; 7-C; 8-C; 9-B; 10-C; 11-B; 12-C; 13-C; 14-B; 15-C; 16-A; 17-C; 18-B; 19-C; 20-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on the topic of variable scope and visibility in Microsoft Access VBA. This lesson is particularly aimed at those learning VBA, and comes in response to a question from Sandra, one of my students, who wanted to know if it's possible to use the same variable name, such as Dim X as String, on different buttons within the same form or if each button needs its own uniquely named variable.
This question gets right into the heart of variable scope, which I first cover in depth in my Access Developer Level 4 course. When you declare variables in VBA, it is important to understand two aspects: scope and visibility. Scope refers to where a variable can be used, while visibility determines who or what can access the variable.
There are three types of scope that you need to know about. First is procedure-level scope, which applies to variables declared inside a subroutine or function. Each button's code, for example, runs inside its own procedure, so variables declared within those subs are completely distinct and do not interfere with each other. You can safely use the same variable name in different buttons since each one operates only within its own scope.
The next type is form-level scope. This applies to variables declared at the top of the form's module, outside any specific sub or function. These variables are available to all procedures and functions within that particular form. However, you cannot set the value of a form-level variable outside of a procedure; you must set its value inside an event like Form Load or another subroutine. The key takeaway here is that procedure-level variables take precedence. If a variable exists with the same name both at the procedure level and form level, the procedure-level variable will be used within that context.
Finally, there is database-level scope. Variables at this level are declared in a standard module (not a form or report module) and made public using the Public keyword. These variables are accessible from anywhere within the database, making them ideal for storing values you might need throughout different parts of your project, such as keeping track of the current user or a critical record ID that you need globally.
As for visibility, declaring a variable with Private or Dim makes it only visible within its module or procedure, while Public (or the older keyword Global) allows other modules, forms, or reports to see it. The use of Public is standard practice now, though you may encounter Global in older code.
It's worth noting that sometimes you may want to make a form-level variable publicly accessible so other forms or modules can use its value. You can do this by declaring it Public in the form's module, but it will only hold its value as long as the form is open.
A common pitfall with variable usage in VBA occurs when a compile error forces VBA to clear all variable memory, causing previously set values to disappear. This can be particularly frustrating when you're using global or form-level variables. That's why many developers use TempVars, which are global variables designed to persist even through a crash or debugging session. These are covered in detail in Access Developer 31, and I plan to create a dedicated TechHelp video on TempVars soon.
When working with variables, be mindful of how you refer to them. For instance, to access a public variable from another form, use a period instead of an exclamation point. The exclamation point is for controls or fields, while the period is for properties or variables. Also, always be careful with spelling because a typo is one of the most common causes of issues.
Some sources online claim you cannot access public form-level variables from another form, but that is simply not true, as demonstrated in this lesson. You just need to use the correct syntax.
A practical example of useful global variables is tracking the last record accessed. By creating a public variable in a global module, you can keep the ID of the last viewed customer and use it to conveniently reopen that record later.
Microsoft provides extensive documentation on scope and visibility if you want to explore further examples and explanations.
Shortly, I want to mention that my Access Developer Level Four class goes into much more depth on this topic and more. You can find links to these resources and the other videos mentioned here on my website.
Lastly, for those interested in deeper learning and community interaction, becoming a member at different levels unlocks various benefits, such as access to extended video content, downloadable databases, my code vault, and priority support. Higher levels provide even more exclusive content and perks. As always, the core TechHelp video lessons remain free and available to everyone.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Variable scope in Microsoft Access VBA
Procedure-level variables and their behavior
Form-level variables and where to declare them
Setting and initializing form-level variables
Scope priority between procedure and form-level variables
Creating and using database-level (global) variables
Public vs Private visibility for variables
Differences between Dim, Private, Public, and Global keywords
Accessing public form-level variables from other forms
Correct syntax for accessing form variables via the Forms collection
Debug Compile and its impact on variable state
Practical uses for database-level (global) variables
Storing persistent values using public variables
Example: Tracking and recalling the last accessed record with a global variable
Handling errors and best practices for variable names
Cautions regarding variable persistence after errors
Brief mention of TempVars and their persistence benefits
Spelling and naming variables to avoid errors
Verifying public variable access across forms in Access
Assigning values to variables within specific form events
Troubleshooting variable access and compilation errors |
