Time for a quick Access and VBA calculator quiz. These are the kinds of little details that can make a form calculator work beautifully, or make it display something like 54 when you were expecting 9. Access is helpful right up until it decides your numbers are actually text. Then it gets creative.
See how many of these you can answer before reading the explanations. If you need more than a few seconds, no worries. This is not a timed final exam, and nobody is going to take away the one printer in the office that still works.
Question 1: Why can adding values from two Access text boxes sometimes produce 54 instead of 9?
Answer: The control values are being treated as text and concatenated.
If one text box contains 5 and another contains 4, VBA may see them as the strings "5" and "4" rather than the numeric values 5 and 4. When text is combined, the result is 54. That is concatenation, not arithmetic. Before performing calculations, make sure you are working with numeric values. Functions such as Val can convert appropriate text input into a number, although you should still validate the input first. Garbage in, garbage out, as they say. Or in Access terms, garbage in, mysterious runtime error at 4:57 PM.
Question 2: In VBA, which expression calculates the nth root of a positive number?
Answer: Use an exponent of 1/n. For example, the nth root of a positive value can be calculated by raising that value to the power of 1 divided by n. The caret character (^) is VBA's exponent operator.
A square root is simply a special case of this idea: the second root. So a value raised to the power of 1/2 gives its square root. Just remember that ordinary real-number roots have limitations with negative values, especially when you are dealing with even roots.
Question 3: What does On Error Resume Next do when VBA encounters a runtime error?
Answer: It ignores the error and continues with the next statement.
This can be useful in very limited, carefully controlled situations, but it is not a magic "make my program work" command. If you use it across an entire procedure, VBA can quietly skip over an important error and leave you wondering why your calculation is wrong. That is generally worse than getting an error message, because now the bug is hiding under the couch.
Question 4: What is the best way for a calculated routine to handle an attempted division by zero?
Answer: Test the divisor first, then skip the calculation and show a useful message.
Do not change the zero to one just to avoid the error. That prevents the crash, sure, but it also gives the user a mathematically incorrect answer. Instead, use a simple If Then test to see whether the divisor is zero before performing the division. If it is, explain the problem and let the user correct the input.
Question 5: A square root button uses only one input control. Which validation approach is most appropriate before converting values with Val?
Answer: Check only the input control required by the square root operation for Null.
Validate the controls that matter for the operation the user selected. There is no reason to require every text box on a calculator form to contain a value when the user only clicked the square root button. Checking unrelated controls creates unnecessary errors and frustrates users. Good validation is specific: check the needed value, make sure it is valid, then perform the calculation.
These are small concepts, but they are the foundation of building reliable calculator routines in Access VBA. Handle text-versus-number conversions correctly, validate only what you need, and prevent predictable errors before they happen. Watch the embedded video for the full quiz walkthrough and demonstrations.
Live long and prosper,
RR
No comments:
Post a Comment