|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Option Explicit By Richard Rost 
4 years ago
Use Option Explicit in Your Microsoft Access VBA In this video, I'm going to teach you why you should use the "Option Explicit" command in your Microsoft Access VBA code. Links
Suggested Course
Keywordsaccess 2016, access 2019, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, option explicit, tools, options, require variable declaration, dim, variables, explicit, declare
IntroIn this video, we will talk about why you should use the Option Explicit command in Microsoft Access VBA. I will demonstrate how Option Explicit helps catch common mistakes such as variable misspellings, how to enable Require Variable Declaration in Access, and why this setting is important for preventing difficult-to-find runtime errors in your code. You'll also see tips for recognizing errors by watching capitalization and learn about best practices for declaring variables in VBA.TranscriptWelcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, I'm going to teach you why you should use the Option Explicit command in your Microsoft Access VBA. Here I am in my database. My TechHelp free template is a free download on my website if you want to grab a copy, by the way. Let's say I'm here in my Customer form, and I want to make a little button that simply increments the Family Size by one every time I click on it. So I'll go into Design View. I'll just copy one of these buttons up here, copy and paste. I'll slide it down here. I'll put a plus one in there for the caption. Open up that button's properties. I'll call this my Plus One button, or whatever you want to call it. Then let's go into the build event for that button. That will bring up my VBA editor. Now by default, in my databases, I've got the Option Explicit command on. What does that mean? This says I have to explicitly declare any variables. If I want to use a custom variable like "Dim x As Integer," I have to tell Access exactly that: "Dim x As Integer." Let's say I don't have it there, so I'm not forcing Access to declare variables. Now the name of my field is Family Size. That's the name of the text box. So if I just type in here: FamilySize = FamilySize + 1 Save that. Come back over here to my form. I'm going to close it and reopen it. Then I click the button. Looks great. It's working. Click the button. It adds one to Family Size each time I click it. Now let's pretend that I accidentally misspelled Family Size. Now this might seem like a simple example, and it is. If you've got hundreds and hundreds of lines of code in here, it's very easy to miss a misspelling. I'm going to say at least 90 percent of the questions that I get, or that I get asked in my forums, involve a misspelling of some kind. I'm not exaggerating that claim. But I just mistyped Family Size. Maybe I've been up all night, not enough coffee, and I didn't catch that. Now I come back to my button and I'm clicking on it, and it's just setting it to one. What's happening? Why is it not working now? Now I've got to go through my thousands of lines of code to try to figure out where the problem is. I'm not getting any error messages. If I come back in here and I try to compile my code (Debug - Compile), nothing. I don't get any warning messages. I don't get any error messages. It just doesn't work. Those are the worst kinds of errors: runtime errors where your code just doesn't work and you have no idea why. One of my favorite memes, by the way: "My code doesn't work. I have no idea why." And then a little bit later, "My code works. I have no idea why." I love this one. But now let's say we were good little programmers and we had Option Explicit up here. That forces you to declare all of your variables. Now, if something is not declared as a variable and you don't have that, Access will just assume you want a variable of type Variant, which means it could be anything. But with this, you have to explicitly declare your variables. And if you don't, and it's not a form field name, you'll get an error message. So now if I try to run this, I get an error message: Variable not defined. I can very easily now see that I misspelled that. So if I come in here now and I fix that, you'll see that it will work. I come out here and click, and now it's working again. Now I know what the problem was. Also, a nice thing about having Option Explicit and having to declare your variables is you can very easily see that you've typed something in wrong if you watch the capitalization. If you noticed when I was typing this before, I typed in "FamilySize =" then typed it in wrong, "FamilySize + 1." Now watch the capitalization. See, this guy auto-capitalized with the capital F and capital S. That's why I like to dim my variables that way or name my tables and my fields that way. This guy, I can see, is still a lowercase, and it just visually cues me that I spelled that wrong. I had a database years ago. I was working on it for a class and one of my fields was Accounts Receivable. I had to type that in a bunch of times. I should have copied and pasted, but I didn't. In one of them, I accidentally flipped the E and the I and I did not have Option Explicit set. I could not find that error, and my code wasn't working and I had no idea why. This was before I knew the joys of Option Explicit. Now the default option is actually not to have that in Access. I don't know why. I don't know if they've changed that recently. I haven't checked, but I know versions ago, the default was not to put that in your code. So you go under Tools - Options and then right here, Require Variable Declaration. Make sure that's checked and then anytime you create a new module or a new form module (code behind a form or report), you'll automatically get that Option Explicit there. Make sure that's checked and know that's a per-system setting. So you have to change that on every machine that you actually develop on. If not, and if you have old code, like old forms, I had to go through my old database and add this to all of my forms because I started building my database in 2002. I was probably using Access 2000 at the time and that wasn't the default case. So I had to go through and put that in, and then it started popping up little errors I didn't even know happened. Runtime errors are hideous. They'll just be in your code and you won't know that something is wrong. If you like this stuff and you want to learn more, check out my free Intro to VBA video. There's the link right there. I'll put a link down in the description below the video. You can go click on that as well. If you want to learn even more, I've got tons and tons, hundreds of hours, of different lessons on my website. I've got 36 different levels of Access Developer classes where we learn all the ins and outs of programming in VBA for Access. I think Developer Level 8, maybe 8 or 18, one of those with an eight in it, I cover the Option Explicit keyword. We go into more detail on it. I hope this helps you out. Go through your code now and add Option Explicit everywhere. Also, make sure you give your variables good types too. Don't just say "Dim x." Tell Access exactly what type of data it is: "Dim x As Long" or "Dim x As String." You don't want to deal with Variants. I have another whole video coming up on Variants and variable declarations soon. If you like this kind of stuff, I cover it in my Developer lessons too, but I have a Fast Tip coming on pretty soon. Thanks. We'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. All of our members 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 finish 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 finish the Expert classes. These are the full-length courses found on my website, not just for Access. 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 are 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. 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 will always be free. QuizQ1. What is the purpose of the Option Explicit command in Microsoft Access VBA?A. To force explicit declaration of all variables used in code B. To allow implicit declaration of variables C. To protect code from being edited D. To speed up form loading times Q2. What happens if Option Explicit is NOT used and you misspell a variable name in your VBA code? A. Access displays an error and highlights the misspelling B. Access ignores the line of code entirely C. Access assumes it is a new Variant variable without error D. Access prevents the code from saving Q3. How can you set up Access to automatically insert Option Explicit in every new module? A. By using Tools - Options and checking Require Variable Declaration B. By installing an add-in from Microsoft C. By creating a macro called OptionExplicit D. By editing the Windows registry Q4. Why are runtime errors without error messages considered especially problematic in VBA? A. They are usually caused by hardware problems B. They can silently cause code to misbehave without obvious signs C. They always stop the program and show a crash message D. They can be ignored and are usually harmless Q5. What is a benefit of explicitly declaring variable types in VBA? A. It prevents all kinds of errors in Access forms B. It allows Access to run faster queries automatically C. It makes code easier to debug and catch spelling errors D. It is required for using built-in Access reports Q6. What type does VBA automatically assign to undeclared variables if Option Explicit is not used? A. Integer B. String C. Object D. Variant Q7. If you accidentally typed "FamlySize" instead of "FamilySize" in your code without Option Explicit, what would happen? A. Access would highlight the error immediately B. The code would create a new Variant variable called FamlySize C. Access would prompt you to correct the spelling D. Access would crash Q8. How can capitalization of variables help when using Option Explicit? A. Capitalization is irrelevant to debugging B. Incorrect capitalization can visually alert you to a misspelling C. Only variables typed in all caps work in VBA D. The compiler auto-corrects capitalization errors Q9. How often should you check the Require Variable Declaration setting in Access if you develop on multiple computers? A. Only once, it carries over to all machines B. Never, as it is not necessary for debugging C. Every time you use a new computer or system D. Only when upgrading to a new version of Access Q10. What other tips did Richard mention for writing better VBA code besides using Option Explicit? A. Only use single-letter variable names B. Always use Variant as the variable type C. Give variables proper types, like Long or String D. Avoid commenting your code Answers: 1-A; 2-C; 3-A; 4-B; 5-C; 6-D; 7-B; 8-B; 9-C; 10-C 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. SummaryToday's video from Access Learning Zone covers why it is important to use the Option Explicit command in Microsoft Access VBA.I start off in my database using the free TechHelp template, which you can download from my website if you want to follow along. In this example, I'm working with a Customer form. Let's say I want to add a simple button to increase the Family Size field by one each time it is clicked. So, I go into Design View, copy an existing button, adjust its caption to show a plus one, and update its properties with an appropriate name. The next step is to open the event handler for this button, which brings up the VBA editor. In my own databases, I always include the Option Explicit command at the top of my code. This means that I must explicitly declare all variables before using them. For example, if I want to use a variable named "x" as an integer, I need to declare it with "Dim x As Integer." Option Explicit ensures that nothing is used by accident without being declared first. If Option Explicit is not enabled, you are not required to declare your variables, so Access will create a Variant variable automatically if you make a typo. For instance, the text box in the form is called FamilySize. If I write code just using "FamilySize = FamilySize + 1," that line will work as long as the name is spelled correctly. When I save, close, and reopen the form, clicking the button increments Family Size as expected. However, suppose I make a minor spelling mistake in the field name when writing the code. This seems like a simple issue, but when you have hundreds or thousands of lines of code, it's easy to overlook something like this. In fact, I would estimate that the vast majority of questions I receive in my forums are caused by simple typos. If a typo slips in, perhaps because you are tired or distracted, your code might just stop working or behave incorrectly, such as resetting the Family Size to one instead of increasing it. The problem is, you will not receive any error messages, and the debugger will not alert you to the issue. These kinds of errors, which do not halt execution and do not provide clear feedback, are some of the hardest to identify and fix. One of my favorite programming jokes sums it up well: "My code doesn't work and I have no idea why. Later, my code works and I have no idea why." This is a familiar experience for many of us. If, instead, you do have Option Explicit at the top of your code, then every variable must be declared. When a typo occurs and you attempt to run the code, Access immediately notifies you that the variable is not defined. This makes it much easier to spot and correct the mistake. After fixing the typo, the button works as intended once again. This simple safety net can save you from endless frustration. Another benefit of declaring your variables is that the VBA editor will automatically adjust the capitalization of names to match their declarations. For example, if you have "FamilySize" declared, the editor will capitalize it for you as you type. If you see your variable remain in lowercase or not match the capitalization of your field names, that's a visual indicator that you made a spelling error. I actually learned this the hard way years ago when building a database for class. One of the fields was named AccountsReceivable. I had to type this out many times, and at one point, I switched around the "e" and "i" by accident. Without Option Explicit, it was almost impossible to find and fix this, and my code just silently failed. Traditionally, the default in Access has been to leave Option Explicit out of the module code, though you can change this by going to Tools, Options, and enabling the "Require Variable Declaration" setting. Make sure this is checked so that Option Explicit is added automatically to all new modules, whether they are standard modules or code-behind forms and reports. Be aware this is a per-system setting, so you need to change it on every computer you might use for development. For existing databases, especially older ones, you may need to manually add Option Explicit to your forms and modules. When I did this in my own older databases, I suddenly discovered errors I didn't even know existed, since runtime errors often remain hidden unless something truly breaks. If you find these tips helpful and want to learn more about VBA, I have a free Intro to VBA video available on my website, and I'll link it below. If you want more in-depth instruction, I have an extensive series of Access Developer classes—dozens of levels with hundreds of hours of lessons covering all aspects of VBA, including more details on Option Explicit. Around Developer Level 8 or 18, I cover the Option Explicit keyword in depth. I strongly encourage you to go through your code and add Option Explicit everywhere. Also, remember to declare your variables with their correct data types, not just as generic variants. For instance, use "Dim x As Long" or "Dim x As String" instead of simply "Dim x." Variants can introduce all kinds of subtle problems. I have another video coming soon that will explain more about variable declaration. If you enjoy these topics, I cover them in greater detail in my Developer courses, and I'll be posting more Fast Tips in the near future. If you're interested in supporting the channel and accessing additional content, you can become a member by using the Join button below the video. Membership comes with various perks, including access to extended cut TechHelp videos, free classes, and more. Higher levels of membership offer benefits like sample database downloads, code vault access, increased question priority, and more classes across Access, Word, Excel, and beyond. And don't worry, these free TechHelp videos will keep coming as long as you keep watching. You can find a complete video tutorial with step-by-step instructions for everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListUsing Option Explicit in Access VBAForcing variable declaration with Option Explicit How Option Explicit helps catch misspelled variables Demonstration of runtime errors from undeclared variables Enabling Require Variable Declaration in Access options Checking capitalization to identify variable name errors Modifying module settings for Option Explicit Explanation of Variant data types vs specific types Benefits of declaring variable types explicitly |
||||||||||||||||||||
|
| |||
| Keywords: FastTips Access option explicit, tools, options, require variable declaration, dim, variables, explicit, declare PermaLink Option Explicit in Your Microsoft Access VBA |
