As a programmer who spends a fair amount of time with Excel, I’ve come to appreciate the little-known gems of VBA, the programming language behind the scenes. One handy feature I often use is the VBA CVERR function. It’s a part of the error handling toolkit in VBA, which might not be glamorous but is crucial for robust code. When I’m writing a script and I need to return an error data type deliberately – maybe to signal that something’s gone awry with an input or a process – CVERR is my go-to.
I like to think of the CVERR function as my first line of defense. When inputs fall outside an expected range or when I’m dealing with special cases, invoking an error through CVERR lets me handle those scenarios gracefully. I’ve found a repository of error codes that I can reference, which ensures I’m returning the correct error types for different situations. This allows me to keep my code clean and my users informed about what’s happening without getting bogged down by unexpected crashes or unhelpful messages.
Syntax
CVErr(Expression)
Arguments
When I call a function, I need to pass in input arguments. In VBA, expression is one such argument I’d use to get the error data type, typically when I’m handling errors in code. I’ll ensure the expression is within an acceptable range, avoiding arguments that would produce errors. For instance, the errornumber argument represents the subtype of an error: it’s a specific integer, like zero or any valid Long data type value.
When handling custom errors with VBA’s CVErr function, I might create a variant for subtype error. If the argument falls outside the acceptable range, it means my argument isn’t a valid variant of subtype error. I have to keep in mind that the argument must respect the expected data type range; providing a null or inappropriate string won’t work and will trigger an error itself.
| Argument | Type | Description |
|---|---|---|
| expression | Variant (required) | Error number to get corresponding error data type |
| errornumber | Integer or Long | Numeric value specifying the error subtype |
Arguments should be named correctly to ensure they’re recognized properly. If I mistakenly insert text where variant data is expected, I’m asking for trouble.
Arguments are the bread and butter here; they’re the way I tell my function what I need, be it a specific error check or any other task. I always check the range and subtype to make sure they behave just as I want them to.
Example
Public Function MyFunc(ByVal value As Double) As Variant
If value = 0 Then
MyFunc = CVErr(11) ' Dividing by zero error
Else
MyFunc = 1 / value ' Calculate inverse
End If
End Function
Private Sub MacroExample()
Debug.Print MyFunc(ActiveSheet.Range("A2").Value) ' Outputs result or error
End Sub
In my example, I’ve got a simple function MyFunc that takes a number and calculates its inverse unless it’s zero. If someone tries to divide by zero, I use CVErr with error code 11 to signal this issue. To see it in action, the MacroExample sub uses Debug.Print to output the result or error straight to the Immediate Window in the VBA editor. If you need to flag other types of errors in your VBA macros, just change the error code in the CVErr function to whatever fits.
Notes
- When I toss a number into VBA that’s not an error code, it slaps me with a run-time 5 error. Clearly, it’s not a fan of impostors.
- Try sneaking in something other than a number as an error code, VBA snaps back with a run-time 13 error, like a bouncer shutting down a fake ID.
- Always use
IsNumericbefore callingCVErrto avoid these mishaps. It’s like a quick background check. - Remember, VBA only likes its own list of application-defined error numbers. Don’t try to make up new ones on the fly.
