Round Up Cents 2
By Richard Rost
14 months ago
Automate Penny Rounding in Microsoft Access Part 2
In this Microsoft Access tutorial, we will learn how to create a custom VBA function to automatically round up currency values to the nearest cent, mimicking the Ferengi's obsession with precision. This developer-focused session builds on the previous introduction, providing practical insights into writing clear, human-friendly code. This is part 2.
Members
In the extended cut, we will learn how to handle null values and invalid inputs in your VBA function when users attempt to input non-numeric values. This will ensure your function operates smoothly without errors, even when unexpected data is provided.
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
Recommended Courses
Keywords
TechHelp Access, Microsoft Access VBA, currency rounding, global module function, code efficiency, Access database functions, immediate window testing, error handling in VBA, custom VBA functions, Access query design, Access form functions, Access developer tutorial, intellisense
Intro In this video, we continue with part two of rounding up to the next cent in Microsoft Access by creating a custom VBA function called Roundup Sense. I will show you how to set up this function in a global module, explain the differences between public and private functions, demonstrate the use of the currency data type, and test your code using the immediate window. You'll see how to use this function in queries and forms to round values up efficiently, and I'll discuss the importance of writing clear, readable code versus ultra-efficient code. This is part 2.Transcript Today is part two of my video on rounding up to the next cent. The nearest cent I guess you could call it rounding up to the nearest cent. Not down. Up. In other words, we're always moving right on the number line. I keep saying timeline. Why do I keep saying timeline? Anyways, this is part two. In part one, I show you how to do it without any VBA, so that was the beginner part of it. Now I'm going to show you the developer part.
We're going to make this function here called Roundup Sense. So before we get started, if you've never done any VBA programming before and you want to learn how, go watch this video first. It's about 20 minutes long. It'll teach you everything you need to know to get started. And go watch this function where I teach you how to create your own custom functions in VBA. Which is what we're going to do today.
All right, here I am back in the same database I had last time. Here's the thing. I want to have to remember to put this calculation everywhere you want to use this. We can just make a function called Roundup Sense. So I'm going to come down here to my global module. If you don't have a global module, just go to create and then click on module. It'll create a blank one for you. Don't pick class module. That's something totally different. Pick module. I already have one right here. And so I'm going to use this one. I'm going to put it right here. And I'm going to say public function Roundup Sense C as a currency as a currency. What does that do? Well, public means everybody in the database can use it as opposed to private, which means only other members of this module could call it.
All right, it's a function, which means it's going to return a value as opposed to a subroutine, which does stuff and doesn't return a value. The name of it is Roundup Sense. It takes in a value C as a currency and it's going to return a currency. So, we're going to break it down the same way that we broke it down in the query before step by step. So, we're going to say first C equals C times 100. Take the value, multiply it by 100. C equals C times negative one. We're going to multiply it by negative one. C equals the int of C. Now, we're going to round it down on the number line to the next integer to the left, which will make it bigger. C equals the C times negative one again, flip it. And then C equals C divided by 100 to get rid of that extra 100 that we put in there.
Now, we should be left with the final value. So now we'll say the name of the function roundup sense equals C. And that's the value that will get returned. If you want to test it, you could test it right here using something called the immediate window. So, go to view and then immediate window, that brings up this little pane down here. Watch this. Mark that means you're querying roundup sense and then put a value in here. Let's go 1.011 like that and then press enter and it will run it through that and give you the return value. See? That's pretty cool. Let's try a different value. I'm going to just delete all this stuff here. 9.999 like that. And then 10. And it's 10. See? So now we know that that function works. When you're done with using this, you can close that. I use the immediate window every now and then. But now we have this function. We can just call roundup sense anywhere in our database. So, save this, control S, debug compile once in a while. Make sure that your code compiles.
Let's create another new function, create query design or another query in that function. Bring in that customer table again, customer ID and we'll find credit limit, which will again call C. You don't have to. Now here, all we have to say is some new value. Let's call it x is going to be roundup sense open parentheses. See? Just like that. Roundup sense C. And now when I run this, look at that. All I need to do is call that function. And that's the benefit of using a global function like that with a little VBA. Save this. Roundup 3Q.
Now, a couple little things. Obviously in here, this is fine. But what happens if you're on the customer form? Not live. Design view. And you come down here and let's copy and paste this credit limit. And I'm just going to get rid of the label. We're going to put that rounded value here. So, I'm going to come in here and I'm going to say equals the roundup sense. Notice it shows up in the intellisense here. It doesn't always show from the queries though. And we'll call this roundup credit limit. Save that. Close it. So now we should get the rounded version. And since it's calculated, I think that gray. But we'll come over here and just take the gray. Save it. Close it. Open it. Looks good. If I put the, okay, rounded it nicely. If I go to a new record, that's fine.
Now the default value of zero is getting sent into it. What if I don't have a default value in that credit limit field? Let's say for the customer table, credit limit doesn't have a default value. Which means it's going to be null. If I come in here now, these all look fine. If I go to a new record, I get an error in there. I don't want to see errors in there. How do we handle that? Likewise, what if the user tries sending in something like the letter A? How do we handle that? Well, we'll talk about that in the extended cut for the members. Silver members and up can access all of my extended cut videos. Old members can download these databases. You get code vault. Everybody gets some free training and all kinds of extra stuff.
Now, a few other things before you go away, a few other things I want to point out in this global module. A lot of you were looking at that going, that's not very efficient code. It's not really going to slow the execution down. We're talking microseconds. But is this the most efficient code? No, it's not. In fact, let me copy it here and I'll just paste it in from my notes. Here we go. This is a lot tighter. It's written better. I'm going to call this round up sense too because you can't have two functions with the same name in there. But if you look at this, it's not quite as evident what's going on here. So, I like to call this human friendly. When I write my code, that's kind of how my brain functions. I think in steps like this.
If you want later on, you can take this and tighten it down. Like we did with the queries in the last video. Tighten it down to a much shorter bit of code. But you know what? This is easier three years from now. If I come back to try to figure out what I was doing, this is easier to read for a human. This might be more efficient, and it might look a little better, make you look a little more professional. I don't care. I want something that's easily readable. I want to say, okay, I multiplied it by 100 and negated it, took the int, negated it again, divided it by 100. Okay, I get it now. I look at this and I'm like, okay, I got to start from in here. Like those first and this. So, all those people that are telling you, that's very efficient code. It doesn't have to be. Don't listen to those people.
Sorry, I'm not as professional a code writer as you. I write for clarity. Put comments in here if you want to and all that stuff. But the compiler doesn't care. And unless you're crunching billions of numbers, you're not going to notice a difference in speed. So, that's just my two cents. I've got so much code that 25-year-old Rick wrote 20, what, seven years ago now. And I come back and I look at it and I'm like, what was I thinking? Because I didn't use to write with a lot of comments and I used to try to make my code as tight and efficient as possible. No, no, no. Now I write for 60-year-old Rick to be able to read it later.
And if you like these programming tips from me and you want to learn more, you want to benefit from my decades of experience building databases with Microsoft Access, I got tons of developer lessons available on my website. Come and check them out. Don't forget about the extended cut for the members where I'll teach you how to deal with those null values. And if the user tries to send something into your function, that's not a number. But aside from that, that is going to be your TechHelp video for today. Hope you enjoyed this little two-part series, and I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Creating a Roundup Sense function in VBA
Public and Private function definitions
Currency data type in VBA
Immediate window for testing functions
Using a global module for VBA functions
Creating and using custom VBA functions
Handling calculations in Access forms
Rounding numbers to the nearest cent
Efficient versus clear code writing practices
Global functions usage in queries
Setting default values to avoid errors
Debugging and compiling VBA code
COMMERCIAL:
In today's video, we're continuing with part two of our series on rounding up to the next cent. You will learn how to create a custom VBA function called Roundup Sense to streamline your calculations in Microsoft Access. If you missed part one, where we tackled this without VBA, make sure to check that out first. We'll walk you through crafting this function step-by-step in a global module, making it accessible throughout your database. Plus, you'll see how to test it using the immediate window. And remember, writing user-friendly and understandable code is just as important as efficiency. But wait, there's more! Silver members and up can access the extended cut where I'll cover handling errors, such as null values or non-numeric inputs. Don't miss out on this essential VBA lesson. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What does the function Roundup Sense do?
A. It rounds numbers down to the nearest cent.
B. It rounds numbers up to the nearest cent.
C. It averages numbers to the nearest cent.
D. It truncates numbers at the decimal point.
Q2. In VBA, what does the term 'public' signify when defining a function?
A. The function returns a public error message.
B. The function can only be used within the same module.
C. The function can be used throughout the database.
D. The function has an open-access source code.
Q3. What is the main benefit of creating a global function in the database?
A. It improves database security.
B. It reduces code duplication by using the function anywhere in the database.
C. It automatically creates backups of the database.
D. It simplifies database connectivity settings.
Q4. What is the purpose of using the 'Immediate Window' in VBA?
A. To compile and run the entire program.
B. To quickly test individual functions and return their values.
C. To backup VBA projects automatically.
D. To detect and fix errors in external databases.
Q5. What should you do if you encounter an error with null values in the Roundup Sense function?
A. Remove the function from the module.
B. Restart the database to clear the error.
C. Implement error handling code to manage null values.
D. Ignore the error; it will resolve itself over time.
Q6. Which step is NOT a part of the initial process of rounding up to the nearest cent within the Roundup Sense function?
A. Multiply the value by 100.
B. Multiply the value by negative one.
C. Add a constant value to the number.
D. Divide the value by 100.
Q7. What does the presenter suggest about writing efficient vs. human-friendly code?
A. Always write the most efficient code possible.
B. Write human-friendly code that is easier to read and maintain.
C. Do not include comments in your code to make it clearer.
D. Efficiency is more important than code readability.
Q8. Why should you use comments in your VBA code according to the presenter?
A. To ensure the program runs quickly.
B. To make the code more understandable in future reviews.
C. To reduce the file size of the database.
D. To hide errors from users.
Answers: 1-B; 2-C; 3-B; 4-B; 5-C; 6-C; 7-B; 8-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 TechHelp tutorial from Access Learning Zone focuses on rounding up to the nearest cent. This is part two of a series, and in this section, we'll be taking a more advanced approach using VBA programming. In the first part, I demonstrated how you can accomplish this without using any VBA, which served as an introductory guide. Now, we'll tackle the developer aspect of creating a custom function.
We'll be creating a function named Roundup Sense. If you're not familiar with VBA programming and want to learn, I recommend watching a beginner's video that provides a foundational understanding for creating your own custom functions in VBA, which is what we'll be doing today.
I'll be working in the same database from last time. To avoid repetitive calculations wherever you need to round up values, we'll create a function called Roundup Sense. First, we'll access a global module—create one if you don't have it by selecting 'Create' and then 'Module'. Ensure you're not selecting 'Class Module', as it serves a different purpose. After opening your module, declare your function as a public function so it can be accessed by the entire database. The function name is Roundup Sense, and it accepts one parameter C as currency, which it will also return as currency.
The process involves several steps: multiplying the value by 100, negating it, rounding it to the nearest whole number, negating it again, and dividing by 100. These steps ensure we arrive at the correct rounded value. The function will then return this value.
To test the function, use the immediate window in the VBA editor. Enter a sample value, like 1.011, and press Enter to see the returned value after rounding up. By testing various values, you can see how the function performs. After verifying the function works, remember to compile your code by selecting 'Debug' and 'Compile' regularly to ensure everything runs smoothly.
After creating the function, you can use it within your database by simply calling Roundup Sense wherever needed, such as in a query design. Create a query, bring in a table—say, the customer table—and use the function by writing an expression that rounds up credit limits or any currency field you want.
While using forms, be cautious if a field like credit limit might handle null values or invalid data inputs. Handling such cases would require more advanced adjustments, which we'll discuss in a more detailed session available to members.
Finally, it's important to note that although the code I've demonstrated might not be the most efficient in terms of execution time, it is well structured for readability. Clarity in coding is crucial, especially when revisiting code after several years.
If you're interested in further programming insights and want to leverage my extensive experience with building databases in Microsoft Access, I have a wide array of developer lessons on my website. Members can also access extended content that addresses handling null values and invalid entries. I hope you found this two-part series helpful and informative. For step-by-step instructions, check out the complete video tutorial available on my website. Live long and prosper, my friends.Topic List Creating a Roundup Sense function in VBA
Public and Private function definitions
Currency data type in VBA
Immediate window for testing functions
Using a global module for VBA functions
Creating and using custom VBA functions
Handling calculations in Access forms
Rounding numbers to the nearest cent
Efficient versus clear code writing practices
Global functions usage in queries
Setting default values to avoid errors
Debugging and compiling VBA code |
