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.