Prices Ending in 9
By Richard Rost
15 hours ago
Automatically Adjust Retail Prices with VBA In this lesson, we will show you two VBA methods for adjusting prices ending in 9 in Microsoft Access without increasing the original value. You will learn how to use a loop that subtracts pennies until the final digit is 9, and how to use a mathematical shortcut with rounding, the Mod operator, and the Int function. We will also cover handling minimum-price edge cases. Mason from Burlington, Vermont (a Gold) asks: I run a small retail store, and every month I get an updated price list from one of my suppliers. I usually mark everything up about 10%, but my boss likes all our selling prices to end in 9, like $19.99 or $8.49. He says customers like those prices better. Right now, I'm going through and adjusting them by hand, which is a pain. Is there an easy way to have Access do this for me automatically? MembersIn the extended cut, we will apply the price-ending-in-9 adjustment to a table of products using queries. I'll show you how to calculate markup from product costs, preview the adjusted retail prices, and update the table. We will first use functions across multiple records, then do the price adjustment directly in SQL for faster processing of large recordsets. 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!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, Access VBA price ending in 9, price rounding to .99, psychological pricing, VBA custom function, VBA Mod operator, VBA Int function, round currency values, SQL price update query, retail price markup, bulk price adjustment
More InformationTranscript I've never noticed how stores love prices that end in 9, 999, 1999, 499, 999, 9999.
Well, what if you got a whole bunch of prices in your database and you want to automatically adjust them so they all end in nine without raising any of them?
Well, unless you want to raise them.
Welcome to another TechHelp video brought to you by Access Learning Zone. I'm your instructor, Richard Rost.
In today's video, I'm going to show you two ways you can do this with VBA and Microsoft Access.
First, we'll use a simple loop that subtracts one penny at a time until we get the price that we want. Then I'll show you a neat little mathematical shortcut that does the same thing without the loop.
And don't worry if you're not a math person. I'll walk you through exactly how it works.
Today's question comes from Mason in Burlington, Vermont, one of my gold members.
Mason says, "I run a small retail store, and every month I get an updated price list from one of my suppliers. I usually mark everything up about 10%, but my boss likes all of our selling prices to end in nine, like 1999 or 849. He says customers like those prices better. Right now, I'm going through adjusting them by hand, which is a pain. Is there an easy way to have Access do this for me automatically?"
Well, yes, of course, Mason. We're going to do it two different ways.
Yeah, I've read lots of studies, too, where there's a lot of friction for buying if the price doesn't end in a nine. If it's $100, they won't spend $100, but they'll spend $99.99.
I don't know what it is. It's psychology.
All right, let's take a look and see how this works.
All right, so in a nutshell, we're going to move down to a price ending in nine unless we're already there.
So $10 will become $9.99. 849 stays put. $9.00 minimum. That's the cover charge. $9.00 coverage.
Now, if it's eight cents, obviously, we're not going to drop it down to negative $9.00, so we'll put that into our code.
Now, the first method we're going to do is a simple loop, and this is the easiest way to do it. You start at your regular price and you just subtract a penny until the last cent ends in nine. That's really easy to do. Very little math involved. Again, minimum price, nine cents.
Now, before we get started, this is a developer-level video. So if you've never done any VBA programming before, go watch this video first. It'll teach you everything you need to know to get started in about 20 minutes.
You should also know how to use the Mod, or modulus, operator. You should know how to perform basic rounding. And you should know how to create your own custom function.
If you don't know any of these things, go watch these videos first. They're all free. They're on my YouTube channel. They're on my website. Go watch those and then come on back.
All right, here I am in my TechHelp free template. This is a free database. You can grab a copy off my website if you'd like to. But you can use any database that you want.
In here, I've already got a nice currency value on my customer form. We got this thing called Credit Limit. I'm just going to use that.
So I'm going to Design View, and we'll make this a little bit bigger. And we'll bring this down. And I'm just going to copy one of these buttons, copy, paste.
And we'll call this, we'll just call this Nine Loop. All right, we're going to use the loop method here.
So I'm going to right-click Build Event. That'll bring up my Code Builder. And yes, my Code Builder is in dark mode, easier on the eyes that way.
This will be the Loop Nine button.
Now, here we're going to say the field that we're working with is Credit Limit. So we're going to say:
```vb
CreditLimit = PriceToNineLoop(CreditLimit)
```
And then we're done.
Just kidding.
What is PriceToNineLoop? Well, we got to write that. That's going to be our custom function.
We're going to send it the current Credit Limit, and it's going to return the new value to stuff back into Credit Limit. That's how that works.
So right here, I'm going to build my function. Now, usually I'll build functions like this right here while I'm building it and testing it. And then when I'm done, I'll move it to a global module so everybody can use it. But for now, we'll just put it right here.
We're going to call this a public function, PriceToNineLoop, and it's going to take in C as a Currency, and it's going to return a Currency as Currency.
All right, get rid of this extra space down here.
Now, the first thing I'm going to do is, the value that comes in, I want to make sure it's rounded to the nearest penny. Just in case some other math that I've got somewhere else gives me half a penny or whatever.
So we're going to start off by rounding it:
```vb
C = Round(C, 2)
```
Take whatever value is sent in and just round it off. So we've got even pennies here.
Next, we're going to check and make sure that it's not less than nine cents. Let's say if C is less than or equal to 0.09, then we're just going to keep it at that. We're going to return this value.
```vb
PriceToNineLoop = C
Exit Function
```
If three cents comes in, guess what? You're sticking at three cents.
You might want to pop up a message box or something else and say, "Hey, you got to adjust this guy." If you're selling goldfish, can you even get goldfish for a penny?
I remember when I was a kid, you used to get, like, you bring a dollar, you get like a hundred goldfish. Remember those? I am old.
I don't know what those things cost nowadays. I used to be able to go to the store with a dollar, and I could play a couple of games of Pac-Man and get a Coke and a candy bar, and I was in heaven.
All right, now here's the top part. Any other value comes in, we're going to say while it's not ending in a nine, then we're going to loop and subtract the penny.
Now, I'm going to write this out first, and then I'm going to explain it.
```vb
While (C * 100) Mod 10 <> 9
C = C - 0.01
Wend
```
What does this do?
We've got a While loop. And the While loop keeps repeating the same instructions as long as the condition is true. Once the condition becomes false, the loop stops.
Now, let's say C, let's say our starting price is $8.52.
So the first thing we're going to do is multiply it by a hundred. Why? Because I want to work with whole pennies instead of dollars and cents.
And if we multiply 8.52 by a hundred, we get 852. Consider that 852 pennies.
Now, next comes the Mod operator. Mod is the remainder after a division. So we're going to mod it by 10.
So basically, we're saying if we divide 852 by 10, it goes in 85 times with two left over. So the answer to that is then two.
And here's the neat thing. Whenever you take a whole number and use Mod 10, you get its last digit. So 852 Mod 10 gives us two. 857 Mod 10 gives us seven.
Here it is in Excel. Let's see it more clearly.
852, here we are multiplying it by a hundred, and here we are taking the Mod of it, Mod 10. We get a two, a seven, a nine.
And that's exactly what we're looking for. We want the last digit of our price in pennies to be a nine.
So this says if that result is not a nine, I want to subtract a penny from it and then do that all over again. As long as it's not a nine, it's going to loop.
So when I get down here after the While loop ends, that penny should be a nine.
And now we can say:
```vb
PriceToNineLoop = C
```
Whatever we got left over there should be our value.
All right, let's Debug Compile.
Once in a while.
I'm going to close it. I'm going to close Excel now. We're done with you, Excel. Go back. Get out of here. Close that.
I always use Excel to test stuff. It's great for testing things. I'm like, well, I see. How do we do that? What do the numbers look like in Excel?
Okay, now we can code it.
All right, here we go. Ready? Hit the Nine Loop button. Boom. 29,999.99.
This computer is yours for 19 payments of 299.99.
Let's try some smaller numbers. Let's go 845, and then I'll hit the loop button. Boom. 839. See that?
And six cents. Hit the button. Boom. Nothing happens. That's a condition we put up top there.
How about $1,000? Boom. 999.99. Beautiful.
All right, so it's working.
Now, for those of you who are math nerds, I'm a little bit of a math nerd. I'm not a super math nerd, but I'm a little bit of a math nerd.
You can do the same thing without having to count backwards.
Here's the trick in a nutshell. First, then you move down to the nearest 10-cent boundary. Then you subtract a penny, and then you got your answer.
So at 852, you add a penny. That gives us 853. You multiply by 10, and then we have 85.3.
It drops the fractional part. So now we got 85.
So we're moving down to the nearest 10-cent boundary. We're at 850. Subtract the penny, and there you go. There's your 849.
Now, why do it this way? Well, when you're only doing it with one record, one value, it's instantaneous.
What if you got 100,000 records that you got to run through? That could slow things down a little bit if you're running a loop on every single one of those.
Now, with modern computers as fast as they are, it's barely noticeable. But it's noticeable.
A single math equation is going to run faster than a loop on every one of 100,000 records. So this is where a little math comes in handy.
To be fair, this little loop that we built isn't going to bring a modern computer to its knees, but it's a good example of something I always try to teach.
If you can replace repetitive operations with a simple calculation, it's usually worth taking a look and seeing if you can figure out how to do it.
So instead of subtracting pennies one at a time until we get the number we want, we can just calculate the answer directly. One calculation and no loop, and we're done.
Let's see how that works.
And I don't know why, when I asked the AI to generate a picture, it gave me something over here with 11 cents. I don't know. I don't get it. But okay, it looks cool.
All right, so make another button. We're going to take a look at the math.
Okay, right-click Build Event. This is going to be the same as we had up top. In fact, let's just copy this, and we're going to paste this here.
And this will be the Math Nine button. And this is going to be PriceToNineMath.
```vb
CreditLimit = PriceToNineMath(CreditLimit)
```
What's that going to be?
```vb
Public Function PriceToNineMath(C As Currency) As Currency
```
Ready?
```vb
PriceToNineMath = Int((C + 0.01) * 10) / 10 - 0.01
```
There you go. There's your math for the math nerds.
Starting from in here, C, we're going to add a penny. Add a penny. Then multiply it by 10. Multiply it by 10. Then we're going to take the Int. There's the Int right there. Then we're going to divide that by 10. There's a divide by 10. And then we're going to subtract that penny back off. And there it is.
Sorry.
And let's, oh, we forgot the Debug Compile. There we go.
All right, let's close it, save it, open it, hit the button.
All right, didn't do anything because we're already at a 999. Let's put it at 100 or 1000. That's fine. Click it. See? It does the same thing.
How about $80? Click it. $99.99.
Penny? Oh, see, that one went down to negative one. So we didn't put our catch-all for being less than nine.
Let's see, this is why you want to test a broad variety of issues.
So maybe we'll put our little check in here. We could take this stuff and stick that right in here.
We still want to round it. We still want to check to see if it's less than nine cents. Then we just change the name in here. And then after all that, then we can do our math.
All right, let's Debug Compile again.
All right, we'll put that back to what we have, $0.07, $0.06. Hit it. And now that's fixed.
You've got to always check your edge cases because your users are going to try and break stuff.
All right, they put in a hundred, negative $100. That shouldn't run now.
Okay, but if they put in a million, let's see what we get.
Wow, there we go. Beautiful.
I see. Isn't it better to understand this stuff and how it works than to just have AI give you the answers all the time?
I love this stuff. I love learning this stuff. I've always been a bit of a computer nerd and a math nerd, but I like to know how this stuff works.
All right, now members, in the extended cut, we're going to apply this on a table level.
We're going to use some queries. We're going to take a bunch of products with their cost. We're going to apply our markup. We're going to adjust to nine like we did. Preview the changes and then update the product price, the retail price, in the table.
We're going to do it first using functions on a bunch of records.
But then we got the same issue. If we've got 100,000 records that need updating, it'll be a whole lot faster if we do it directly in SQL instead of relying on an iterative loop or a VBA function.
So I'll show you how to do it directly in SQL as well. That's going to be covered in the extended cut for the members.
Silver members and up get access to all of my extended cut videos. Not just this one, you get all of them, and you get some free training every month.
All my members get a free class. And it's a big party, and everybody's welcome.
So click that blue Join button to find out more information.
And if you like learning how to code with me and you want to learn how to build more impressive and functional databases, I got a whole developer series of courses on my website.
Come check them out. You don't have to join or be a member or make any kind of commitments. You can check out the full list of courses right there.
But there you go, folks. That is going to be your TechHelp video for today. I hope you learned something.
Live long and prosper, my friends. I'll see you next time, and members, I'll see you in the extended cut.
If you enjoyed this video, hit that thumbs up button right now and give me a like.
Also, be sure to subscribe to my channel, which is completely free. And make sure you click that bell icon and select All to receive notifications whenever I post a new video.
If you're new to Microsoft Access, check out my Access Beginner Level One course. It's over four hours long, and it covers all the basics like tables, queries, forms, and reports.
It's a great place to start, and it's also completely free.
Members of my channel get extended cut videos, sample databases, access to my code vault, and full training classes every month. Click the Join button for details.
Thanks for watching. I'm Richard Rost with accesslearningzone.com.
Live long and prosper, my friends. I'll see you next time. Intro In this lesson, we will show you two VBA methods for adjusting prices ending in 9 in Microsoft Access without increasing the original value. You will learn how to use a loop that subtracts pennies until the final digit is 9, and how to use a mathematical shortcut with rounding, the Mod operator, and the Int function. We will also cover handling minimum-price edge cases. Quiz Q1. What is the main goal of the PriceToNine functions shown in the video?
A. Increase every price by 9 percent
B. Change a price so its final cent digit is 9 without increasing it
C. Round every price to the nearest whole dollar
D. Remove cents from all prices
Q2. What should happen when a price is already $8.49 before it is processed?
A. It should remain $8.49
B. It should become $8.99
C. It should become $8.50
D. It should become $7.99
Q3. Why does the loop method multiply the currency value by 100 before using Mod 10?
A. To convert dollars into whole pennies
B. To add a 10 percent markup
C. To remove the decimal portion permanently
D. To convert the value into a string
Q4. What does this expression determine when C is a price in dollars?
(C * 100) Mod 10
A. The number of dollars in the price
B. The first digit of the price
C. The last digit of the price in pennies
D. The number of decimal places in the price
Q5. In the loop method, what condition keeps the loop running?
A. While the price is less than $9.00
B. While the last penny digit is not 9
C. While the price is greater than $100.00
D. While the price has more than two decimal places
Q6. What action does the loop perform each time the price does not end in 9?
A. It adds $0.09
B. It subtracts $1.00
C. It divides the price by 10
D. It subtracts $0.01
Q7. Why is C = Round(C, 2) used near the start of each function?
A. To ensure the value is rounded to whole dollars
B. To make sure the price uses even penny values before processing
C. To always round the price up
D. To remove negative values
Q8. Why does the function check whether C is less than or equal to $0.09?
A. To prevent small prices from being reduced into negative values
B. To add a minimum markup to low-priced items
C. To force all prices to become at least $9.00
D. To make the loop run faster on large values
Q9. What is the purpose of Exit Function in the low-price check?
A. It restarts the function with a new value
B. It skips rounding and continues with the loop
C. It returns the original low price immediately
D. It displays an error message
Q10. Which expression is used by the mathematical shortcut to calculate a price ending in 9?
A. Int((C + 0.01) * 10) / 10 - 0.01
B. Round(C * 9, 2)
C. (C Mod 10) - 0.01
D. Int(C / 100) + 0.09
Q11. What does the Int function do in the mathematical shortcut?
A. Rounds the number to the nearest integer
B. Drops the fractional portion of a positive number
C. Converts a number into a text string
D. Finds the last digit of a number
Q12. Why might the mathematical shortcut be preferred when updating a very large number of records?
A. It can calculate the result directly without repeatedly subtracting pennies
B. It always raises prices to the next ending in 9
C. It does not require any currency fields
D. It automatically creates an update query
Q13. If the starting price is $8.52, what should either method return?
A. $8.59
B. $8.50
C. $8.49
D. $7.99
Q14. If the starting price is $100.00, what should either method return?
A. $100.09
B. $99.99
C. $99.90
D. $100.99
Q15. What is an important reason to test values such as $0.06, negative prices, and very large prices?
A. To make Access automatically optimize the VBA code
B. To ensure the function handles edge cases correctly
C. To convert all values into integers
D. To prevent users from editing records
Answers: 1-B; 2-A; 3-A; 4-C; 5-B; 6-D; 7-B; 8-A; 9-C; 10-A; 11-B; 12-A; 13-C; 14-B; 15-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 shows how to automatically adjust currency values so they end in 9 without increasing the original price. This is useful when you import supplier prices, apply a markup, and then want selling prices such as $19.99, $8.49, or $999.99.
Many retailers prefer prices ending in 9 because customers often perceive them differently than round numbers. For example, $99.99 can feel less expensive than $100.00 even though the difference is only one cent. Instead of manually adjusting every price, I will show you two VBA methods that can handle the work automatically in Microsoft Access.
The first method is very easy to understand. It uses a loop that subtracts one penny at a time until the price ends in 9. The second method uses a mathematical calculation that produces the same result without repeatedly subtracting pennies.
Before working with these examples, you should have a basic understanding of Access VBA, custom functions, the Mod operator, and rounding values. The Mod operator is particularly important because it lets us examine the final digit of a number.
For this example, I use a Currency field named CreditLimit, although the same method can be applied to product prices, wholesale costs, retail prices, invoices, quotes, or any other currency field in your database.
The goal is to lower a price to the next value ending in 9, unless it already ends in 9. For example, $10.00 becomes $9.99, $8.52 becomes $8.49, and $8.49 remains unchanged. We also need to account for very small values. If a price is less than or equal to $0.09, we do not want to reduce it into a negative amount, so those values are left alone.
For the first method, I create a custom VBA function that accepts a Currency value and returns a Currency value. Before doing anything else, the function rounds the incoming amount to two decimal places. This is a good precaution because calculations elsewhere in the database may occasionally result in values containing fractions of a penny.
Next, the function checks whether the price is less than or equal to nine cents. If it is, the function simply returns the original value. You may decide to handle this situation differently in your own database. For example, you could display a warning, flag the item for review, or enforce a minimum selling price.
For all other values, the function repeatedly checks the final penny digit. To do that, it multiplies the currency value by 100, converting dollars and cents into a whole number of pennies. For example, $8.52 becomes 852 pennies.
The function then uses Mod 10 to determine the final digit. When you apply Mod 10 to a whole number, the result is its last digit. For example, 852 Mod 10 returns 2, 857 Mod 10 returns 7, and 859 Mod 10 returns 9.
As long as that final digit is not 9, the function subtracts one cent and checks again. Starting with $8.52, it moves to $8.51, then $8.50, and finally $8.49. At that point, the final penny digit is 9, so the loop stops and the function returns the adjusted price.
This approach is straightforward and easy to maintain. It is especially appropriate when you are adjusting a single field on a form or processing a relatively small number of records. If someone enters $1,000.00, the result becomes $999.99. If someone enters $0.06, the result remains $0.06 because of the minimum-price check.
The second method performs the same adjustment using a mathematical shortcut. Rather than subtracting pennies one at a time, it calculates the correct result directly.
The basic idea is to move down to the nearest ten-cent boundary and then subtract one penny. For example, start with $8.52. Add one penny to get $8.53. Multiply by 10, which gives 85.3. Then take the integer portion, leaving 85. Divide that by 10 to get $8.50, and finally subtract one penny. The result is $8.49.
This produces the same outcome as the loop method, but without repeating a calculation for every penny that must be removed. For one record, the difference is essentially unnoticeable. However, if you need to process tens of thousands or hundreds of thousands of records, a direct calculation can be more efficient than a loop.
The math-based function should still round the input value to two decimal places and should still check for values that are less than or equal to nine cents. Without that check, an amount such as one cent could be converted into a negative value. This is a good example of why testing edge cases is important. Always test zero, small values, negative values, values already ending in 9, large values, and values with unusual decimal results.
Neither method raises the price. Both methods always move the amount downward to the nearest value ending in 9. If you want a different pricing strategy, such as rounding upward to a price ending in 9 or choosing the closest price ending in 9, the logic can be adjusted accordingly.
Also, in today's Extended Cut, we will apply these techniques to an entire table of product records. We will use queries to calculate marked-up retail prices, preview the proposed changes, and update the appropriate price fields. We will first use VBA functions with multiple records, and then I will show how to perform the calculation directly in SQL for better performance when working with large tables.
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 Creating VBA functions to end prices in 9
Rounding currency values to the nearest cent
Using While loops to adjust prices by pennies
Using Mod 10 to test the final price digit
Setting a minimum price threshold
Using Int math to calculate 9-ending prices
Testing price adjustment edge cases Article Retailers often use prices ending in 9 because values such as $19.99, $84.99, and $999.99 are commonly perceived as more attractive than rounded prices. If you maintain product prices in a Microsoft Access database, you can automate this adjustment rather than changing each price by hand.
The goal is simple: take a price and lower it to the nearest value whose final cent digit is 9. The price should never be increased unless you deliberately choose a different pricing rule.
For example, $10.00 becomes $9.99. A price of $8.52 becomes $8.49. A price already ending in 9, such as $8.49 or $19.99, remains unchanged. This rule applies to the last digit of the price when expressed in pennies, so both $19.99 and $19.09 qualify.
You should also decide how to handle very small values. If a price is only a few cents, lowering it to a value ending in 9 could produce a negative result. A practical rule is to leave prices at or below $0.09 unchanged. You may instead prefer to flag those records for review, depending on how your business handles very low prices.
The first approach is the easiest to understand. Start with the current price, round it to two decimal places, and then repeatedly subtract one penny until the last digit of the cents value is 9.
To determine whether the final digit is 9, treat the price as a number of pennies. For example, $8.52 becomes 852 pennies. If you divide 852 by 10, the remainder is 2, which is the last digit. In programming terms, the Mod operator returns this remainder. A value ending in 9 will have a remainder of 9 when divided by 10.
The loop-based method keeps subtracting one penny as long as the remainder is not 9. Starting at $8.52, the price moves through $8.51 and $8.50 until it reaches $8.49. Starting at $845.00, it eventually reaches $839.99. Although this method may perform up to nine small adjustments for each price, it is easy to write, test, and understand.
The second approach produces the same result with a direct calculation instead of repeatedly subtracting pennies. This is useful when you need to process a large number of records, such as an imported supplier price list containing thousands of products.
The mathematical method works by first adding one penny to the price. It then shifts the decimal point so that each 10-cent interval can be handled as a whole-number boundary. The fractional part is discarded, shifting the value down to the nearest 10-cent amount. Finally, the calculation shifts the decimal point back and subtracts one penny.
For example, begin with $8.52. Add one penny to get $8.53. Move the decimal point one place to get 85.3. Discard the decimal portion, leaving 85. Move the decimal back to get $8.50, then subtract one penny to reach $8.49.
This calculation also works when the price already ends in 9. For example, starting with $8.49, adding a penny produces $8.50. The calculation returns $8.50 before subtracting one penny, so the final result is still $8.49.
Whether you use the loop method or the mathematical method, build the logic as a reusable VBA function that accepts a Currency value and returns a Currency value. Before doing any calculations, round the incoming value to two decimal places. Then check whether it is at or below your minimum allowed price, such as $0.09. If it is, return the original value unchanged. Otherwise, apply either the loop rule or the direct mathematical rule and return the adjusted price.
Using the Currency data type is important for prices because it avoids many of the small floating-point rounding errors that can occur with other numeric types. Even so, rounding to two decimal places at the beginning of the calculation is a good safeguard, especially if the original price was produced by another calculation such as a markup percentage.
Once the function is working, you can call it from a form button to adjust a single price. For example, a user could enter or calculate a selling price, click an Adjust to 9 button, and have the price field replaced with the lower 9-ending value.
You can also apply the same idea to an entire product table. A typical process is to begin with a product cost, calculate a markup, and then pass the resulting selling price through your price-adjustment logic. Before updating records, create a select query that shows the original cost, the calculated markup price, and the final 9-ending price side by side. This lets you review the results before changing any stored values.
After confirming that the calculated prices are correct, use an update query to write the adjusted values into the retail price field. Always back up the database before running a mass update query, especially if it affects a large number of products.
For a small number of records, calling a VBA function from a query is usually perfectly acceptable. For very large tables, direct SQL expressions may be faster because Access can perform the calculation as part of the query operation without calling VBA separately for every record. The underlying rule remains the same: calculate the desired price, move it down to the nearest 10-cent boundary, and subtract one penny.
The loop approach is best when clarity is more important than optimization. The mathematical approach is best when you want a compact calculation that can be applied efficiently to many records. Both methods ensure that prices end in 9 without accidentally raising them. Primary Topics
VBA custom functions, currency price adjustment, prices ending in 9, While Wend loop, Mod operator, Int function, rounding currency values, minimum-price edge cases
Secondary Topics
form button event procedures, Debug Compile, performance comparison of loops versus direct calculations, psychological pricing
|
