|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
ByRef v ByVal By Richard Rost 
6 years ago
ByRef v ByVal in VBA. Creating an Increment Operator. If you're familiar with languages like C there is an increment operator. To increase the value of X all you have to do is write X++ whereas in VB you have to say X=X+1. That doesn't seem like a lot of extra work, unless you have a long variable name like "CustomerStatementCounter" or something like that. So, wouldn't it be nice to create our own increment operator so all we have to say in our code is Inc X and that's it. That's what this lesson covers, and to do it, I'll teach you the different between ByRef and ByVal variable calls in our subs and functions. Alex from England (Admin) asks, "After watching yesterday’s Extended Cut video, you mentioned that Access VBA doesn’t have an Increment Operator like C does which is ++. Does it support +=" MembersI'll show you how to use optional parameters, use our increment sub with a form field instead of just a variable, determine if a value is numeric, and create a public global subroutine or function.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today! Prerequisites
Links
IntroIn this video, we will talk about ByRef and ByVal in Microsoft Access VBA and discuss whether Access supports increment operators like ++ or += found in other programming languages. I'll show you how to create your own custom increment procedure and explain how ByRef and ByVal affect variables when passed to subs and functions. We will also touch on tips for working with variables and how understanding references and values can help prevent common coding errors in Access VBA.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com.My name is Richard Rost. In today's lesson, we are going to talk about ByRef and ByVal in Access VBA programming. Specifically, we are going to address the question: does Access VBA have an increment operator like some other programming languages do? Today's question comes from Alex. Alex is actually one of my admins. He is my right-hand man. I bounce a lot of stuff off of him, and he helps me out a lot. When we got to talking about yesterday's extended cut video, he said, after watching yesterday's extended cut video, you mentioned that Access VBA doesn't have an increment operator like C does, which is ++. Does it support +=? He says he spends so much time working in C# that he just assumed this stuff existed in VB. If you look at the little picture I put up here, you can see customerCount = customerCount + 1 is how you have to do things in Visual Basic because it does not support an increment operator. This little guy here is called an increment operator. In a language like C, if you want to add one to customerCount, you can just say customerCount++. Or in some other languages, you can say customerCount += 1. Well, Visual Basic doesn't support any of that. So what I am going to show you today is how to make your own little subroutine that we are going to call ink, which is just say, ink customerCount. It is basically the same as doing that. Once you get used to it, there you go. Let's see how this works. Here I have got a real simple database with just a form in it. The form has a button on it. I am going to put some stuff in this button. Right click, build event. If you get asked what kind of builder you want, pick the code builder. If you have never done Visual Basic programming before, this is not the lesson for you. I have got other lessons on VB basics. I will put links below the video here. This is for people who know a little bit of VB. If I have a variable, let's call it X. Let's actually dim that as Long. If I want to say X = 9, and I want to increment X, I have to say X = X + 1. That doesn't seem like a lot of typing, just to look at that. Of course, it is X. But if you have got a variable like customerInterestValue or whatever, you have to either copy and paste, or you have to type that in twice. So if you are dealing with big long variable names, it can be a lot of typing. It would be nice to be able to say ++ there to add one to it, which you can do in C, or even += 1. A lot of other languages support that, but not Visual Basic. Now if I say MsgBox X, I should get a 10. Let me switch this over to FormView and hit the button. Boom, there is a 10. Perfect. Now, I can make an increment function, which would look like this. We could say, let's just make this private for now: Private Function increment (ink) - we will call it ink. We will take in X As Long and we will return a Long. Here I will say X = X + 1. Or you could even say, in this case, to make your code short, ink = X + 1. That is how Access returns values. X will come into here as a Long, add one to it, and return that as ink. Now, down here, I can say X = ink(X). Now, that is almost as much typing as we had before, but it works. I would like to shorten this even more. I want to make this so easy, literally all I have to do is type ink X like that. And we can, if we know how ByRef and ByVal work. Let's get rid of this. We are going to switch this over to Private Sub, not a function. We are not going to return a value. Private Sub ink (capitalize the ink) and we are going to say ByRef X As Long. We do not have to return a value. Here say X = X + 1. I will explain what this does in a minute. Let me show you that it works. Look at that, we can increment it again. Ink X a second time, boom, and now it is 11. How does this work, and what is the difference between ByRef and ByVal? The other option is ByVal. If I switch this to ByVal, watch what happens. It is still 9. You need ByRef in here. ByRef is technically the default. If you do not put anything in here, you get ByRef. If I do not put anything in there, watch what happens. It actually increments it. But do not rely on that because it is not always the case in certain circumstances and with certain languages. So I like to specify ByRef up here if you are going to rely on it for your code. What is the difference? ByRef sends a pointer, a reference to the original variable. When I dim this X as Long, that reserves a little tiny bit of memory in the computer's RAM that it makes a pointer to. It says this address, this location, kind of like your location on your block, your house address. This is where I am storing the value X and that is a pointer to it. So when I pass that up to a function, this function actually receives a pointer to X. That is the reference. So if I make any changes to it, it changes it down here. Sometimes people email me and they cannot understand why their code is misbehaving. That is because they are accidentally changing it up here and they do not mean to, because the default is ByRef. If you switch this to ByVal, it does not send that pointer. It sends the actual value. Then you can do stuff up here and make changes, and you do not have to worry about it affecting the rest of your code. But if we do ByRef, now we are actually sending a reference to X. So X goes up here, if it gets changed, it gets returned down here. This can be a different variable, by the way. This could be PPP. When it comes in here as PPP, watch this. We just go PPP and then PPP. The name of it inside this function is meaningless, and it will still work. It is basically saying, send this address up here, now call it PPP, do some stuff to it, and then when you return, it is getting stored back in X. Let me undo that. Now that you know this, you can make yourself your own little function called ink and it will increment X, because we are going to send up a memory address containing X, make some changes to it, and then return it down here. See how we got 10? That is ByRef versus ByVal. That is how you can make your own increment operator. You can do the same thing with the decrementer if you want to. Same exact thing. Call this one DEC for decrement or decrement, however you want to pronounce it. X = X - 1. We are going to do the increment X and then let's just decrement it, DEC X. Actually, DEC might be a reserved word. Let me see here. DEC could be decimal in some languages, so you want to be careful with this stuff. That is all for today's free TechHelp. There is an extended cut members edition. In that video, I show you how to work with optional parameters so you can have it where you can specify how many to increment it by: 3, 5, 10, whatever. You do not have to just increment by one. The default will be one, and we can specify that with an optional parameter. I will show you how to use form controls in addition to just X, because right now X is just a dim variable. But you can do the same thing with controls on a form. Let me pull it up for you. I can hit the button here and look at that, it can increment the little field on a form. In our code, we will determine if that is a field or if I am just sending it a number. There is a way you can tell. Then, we will create a global module where we can make this a public subroutine. Subs and functions work pretty much the same way, so that everybody can use it. We will change it from Private to Public. That way, you can call your ink subroutine from anywhere in your database. How do you become a member? Click on that Join button down below the video. You will get access to all my extended cut TechHelp videos, live video sessions, chats, and other kinds of perks as well. Hit the Join button. It will tell you what all the different membership levels are and what the costs are. But do not worry, these TechHelp videos are going to be free for the foreseeable future. I am going to keep making them for free as long as you guys keep watching them. So if you like it, make sure you subscribe, comment, share, and do all that good stuff. Subscribing is free. Of course, it will give you updated notifications whenever I release a new video. Make sure you click the little bell. Also, you can stop by my website and check out my Access forum. If you want to learn more, make sure you click on that little Show More link below the video description. YouTube does a pretty good job of hiding that. It is not very visible, and when you click on it, you will see links to all kinds of extra stuff. Today we talked about ByRef and ByVal and dimming variables. I have more lessons available on my website and here on YouTube that talk about these things in more detail. Of course, there is always the Access Index on my website that will pretty much show you where everything is. If you have not yet watched my free Access Level 1 class, it is three hours long. It is absolutely free. There is the address. If you like Level 1, Level 2 is just a dollar. Now, you might be thinking that you are a VBA person and have been doing some programming. Obviously, you watched this lesson. But I have a lot of people who have told me they have been using Access for many years, and they watched my beginner Level 1 class and still picked up a few things. It is free. Just give it a look. Level 2 is a dollar if you decide to continue. If you become a member of my Learning Connection, you will get a 50 percent discount if you want to get any of the more advanced lessons. Level 2 is also free for members of my YouTube channel, so just let me know. Want to get your question answered? There is the page. Drop it there. I have also got a full directory of all my TechHelp videos on there. You can send me an email, but I prefer the TechHelp page because I do get a lot of email and I check my TechHelp page more often. There is my blog, Facebook, Twitter, and of course, my YouTube page. Special thanks again to Alex, my right-hand man who asked me this question. We were chatting this morning and he asked me. He was watching last night's TechHelp video and he was like, does Access have an increment operator? I am like, no, not that I am aware of. So let's make one. Thanks for watching. I hope you learned something. We will see you next time. Keep learning. QuizQ1. What does the "increment operator" (like ++ or +=) do in programming languages such as C?A. It subtracts one from a variable B. It multiplies a variable by two C. It adds one or a specified value to a variable D. It resets a variable to zero Q2. Does Visual Basic (Access VBA) natively support increment operators like ++ or +=? A. Yes, you can use ++ and += directly B. Only += is supported, not ++ C. No, you must use explicit assignment like X = X + 1 D. Only for certain data types Q3. In Visual Basic, what is the standard way to increment a variable named customerCount by one? A. customerCount++ B. customerCount += 1 C. customerCount = customerCount + 1 D. increment(customerCount) Q4. What is the purpose of creating a subroutine like ink in Access VBA as shown in the lesson? A. To perform multiplication for a variable B. To increment a variable by directly altering its value C. To sort variables in order D. To convert a variable to string Q5. What does the ByRef keyword do when used in a VBA subroutine parameter? A. Passes only the value of the variable, not affecting the original B. Passes a reference to the variable, allowing changes in the subroutine to affect the original C. Converts all variables to integers D. Prevents the variable from being modified Q6. What happens if you use ByVal in the ink subroutine instead of ByRef? A. The original variable will be incremented as usual B. The subroutine will not compile C. Changes to the variable inside the subroutine will not affect the original variable D. You must return a value from the subroutine Q7. If you leave out ByRef and ByVal, which is the default in Access VBA? A. ByVal B. ByRef C. No default; you must specify D. It depends on the data type Q8. Why is it recommended to explicitly specify ByRef or ByVal in your subroutine or function parameters? A. For better performance B. To avoid unexpected behavior and ensure clarity of how variables are passed C. Because the compiler will give an error if you do not D. It makes your code look more professional Q9. What is the key difference between ByRef and ByVal? A. ByVal is used only for numbers, ByRef for strings B. ByRef sends a pointer to the variable, allowing changes to affect the original; ByVal sends just a copy C. They are functionally identical D. ByRef reserves more memory than ByVal Q10. What would be an appropriate way to create a decrement function as discussed in the video? A. Sub dec(ByRef X As Long) X = X / 2 End Sub B. Sub dec(ByRef X As Long) X = X + 1 End Sub C. Sub dec(ByRef X As Long) X = X - 1 End Sub D. Sub dec(ByVal X As Long) X = X * 2 End Sub Q11. Which of the following is TRUE regarding the name of the parameter received inside a subroutine in VBA? A. It must match the variable passed in the call B. It can be any valid identifier—it does not need to match C. It must always be named X D. It must begin with the same letter as the calling variable Q12. What additional feature was discussed as available in the extended cut members edition of the lesson? A. Incrementing only by 1 B. Working strictly with global variables C. Using optional parameters to increment by different values D. Using only form controls, never variables Q13. What is one example of when you might not want to use ByRef? A. When you need to change the value in the original variable B. When you want the function to alter the actual data C. When you do not want the original variable to be affected by changes in the subroutine D. When incrementing is essential Q14. According to the video, what is a potential issue with naming your decrement procedure "DEC"? A. DEC is a reserved word or could conflict in some languages B. DEC dynamically erases content C. VBA does not support user-defined procedures D. DEC is only used for database connections Answers: 1-C; 2-C; 3-C; 4-B; 5-B; 6-C; 7-B; 8-B; 9-B; 10-C; 11-B; 12-C; 13-C; 14-A 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 ByRef and ByVal in Access VBA, along with a look at increment operators and how to create one in VBA, since this feature is not built in.I am Richard Rost. This lesson was prompted by a question from Alex, who works closely with me and often helps out with technical challenges. After watching a previous video, Alex wondered if Access VBA supports increment operators similar to C, like ++ or +=, which many programmers become accustomed to in other languages. In languages such as C, if you need to add one to a variable, you can simply use the ++ operator, or += to add any value. Visual Basic, including Access VBA, does not support these operators. Instead, you have to write out the full statement, for example: customerCount = customerCount + 1. This is known as incrementing a variable. To illustrate this, I created a basic database form with a button. Inside the button's event procedure, I demonstrated how, if you declare a variable called X and set it to 9, you need to write X = X + 1 to increase its value. While this is not particularly tedious with a short variable name, it can become cumbersome if the variable name is lengthy. Unfortunately, Visual Basic does not allow you to use ++ or +=, so typing out the full increment statement is necessary. To simplify this, I show how you can create your own subroutine, which I call 'ink,' to mimic an increment operator. First, I create a function that accepts a variable, adds one to it, and returns the new value. While this works, calling it still requires almost as much typing as the original statement. To improve this, I use a subroutine with the ByRef keyword. ByRef allows you to pass a reference to the original variable, rather than a copy. This means if the subroutine changes the variable, the changes reflect in your main code. The result is that you can call ink X, and X gets incremented directly, just as if you had done X = X + 1. If you use ByVal instead of ByRef, a copy of the variable is passed, so any changes within the subroutine do not affect the original variable. ByRef is the default in VBA, but it is best to specify it explicitly to avoid confusion. The variable name within the subroutine is irrelevant; what matters is that the correct reference or value is being passed and modified. With this knowledge, you can create your own increment subroutine. Similarly, you can create a decrement routine to subtract one by changing X = X + 1 to X = X - 1. Be mindful of reserved words; for example, DEC might already be reserved for 'decimal' in some languages. In the extended cut of today's lesson, I show how to add optional parameters to your increment subroutine so you can specify how much to increment by, not just one. I also demonstrate how to apply these techniques to form controls, allowing you to increment values directly in form fields. Additionally, I cover how to place the subroutine in a global module and make it public, so it can be used anywhere in your database. You can learn more about becoming a member and accessing extended cut videos, live sessions, and more on my website. Even if you choose not to become a member, all my regular TechHelp videos are freely available, and subscribing ensures you receive updates when new lessons post. There is also a forum and more tutorials on my website for deeper exploration. In summary, today I addressed how to simulate increment operators in VBA using ByRef versus ByVal. There are additional lessons available on my website and YouTube channel for those interested in learning more about variable handling and VBA fundamentals. If you have questions, you can submit them via my TechHelp page, which I check regularly. Special thanks to Alex for the question that inspired today's lesson. 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 ListIncrementing variables in Access VBALimitations of increment operators in VBA Creating a custom increment subroutine in VBA Using ByRef versus ByVal in VBA procedures How passing variables ByRef allows modification Demonstrating effects of ByVal in subroutines Writing and calling a decrement subroutine Renaming parameters inside subroutines Explanation of reference pointers in VBA Applying custom increment subroutine to variables |
||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access Increment Operator Pass by Reference Pass by Value ++ += PermaLink ByRef v ByVal in Microsoft Access |
