![]() ![]() ‘Attempt to coerce variants into currency So, for those functions where the intention is to be used as an expression, it is highly desirable to define the function like this: You also cannot debug in those cases because the problems start with attempting to call the function itself, thus errors are at the expression service layer, rather than VBA layer. How do you handle nulls? How do you handle bad values that are being sent to those functions? You may end up with errors such as #Type! or something else in those cases. ![]() It might be very reasonable to define your function as this:īut in practice, this actually causes problems. If you’ve programmed with VBA long enough, you might want to use specific data types. However, if I’m starting with a new custom VBA function, I always house it in form/report’s module first. Thus, the only time such functions should appear in a standard VBA module is when you actually intend it to be used across several forms. Essentially, a VBA code function that is used as an expression in a standard module won’t tell you where it is being used. Those sorts of references can’t be back-tracked and found in VBA code. This is especially true when the functions themselves appear as expressions within controls. You certainly don’t want to end up with a general VBA module that houses several VBA functions that will be used throughout the projects. This essentially makes the custom function “private” to the form/report and can generally be a good thing for keeping your forms and your VBA code cohesive and loosely coupled. What people might not realize is that you can define custom functions directly on the form’s or report’s module, and you can refer to those VBA functions from any controls of the same form/report. If you think the answer is standard VBA module, yes, that is one possible place, but is it ideal? Personally, I prefer to follow the same principle we apply toward our variables - declare it with the smallest scope needed for its job. Seems more enticing doesn’t it? Let’s think about design patterns we can adopt for those custom functions. Not to mention, the fact that you can leave comments in your VBA functions allows you to revisit it months later and see why it’s doing the calculations that way. the “Sum()” this can be done once then analyzed in the If/Then block. There is no need to repeat the expression (e.g. VarPercentage = curAmountPaid / curTotalAmount Next, you don’t have to worry about balancing parentheses and square brackets as VBA allows you to write the same expression above but simply, like this: For one, you can easily debug VBA code to aid validating the logic or the result. Creating custom access formulas and functions in VBA offer several advantages over using expressions. Unfortunately, sometimes the expressions are much messier than the tidy examples I showed so far.Īs of late, I’ve come to appreciate using custom functions rather than expressions. I don’t know about you but whenever I inherit a database that is heavy with expressions like those, it can be quite a chore to go through the expressions and think about the path it takes to arrive at the result, which slows down my troubleshooting or understanding of what this is going on within the form. But it’s no longer straightforward to read. Several people work around this problem by doing this instead: For example, what happens if the InvoiceAmount adds up to $0? We would, of course, get a division by zero error which is then shown as #Div/0!. This will work but not exactly without hitches. We might need to calculate a percentage, which would be expressed as: Now, sometimes, we don’t have simple requirements. For example, showing a sum of some column can be as simple as putting down: MS Access has a very powerful support for using expressions in your forms/reports’ controls. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |