4

What error number could I use with CVErr() to indicate no value would be returned from an Access VBA function? I'm pulling blanks. CVErr(5) could work, but I'm hoping for something less generic. 744 ("Search text not found") is close (-ish), but still no cigar.

I'm creating a special lookup function for use in an Access Report. If the function would return no value, I'd like it to return an ERROR# value instead. Chip Pearson's discussion of this is perfect, however it's geared toward Excel, and must be altered slightly for Access.

I've searched the Error List at FMS with a variety of keywords such as "zero", "not found", "too few", etc. There are thousands of errors in the list, so any help whittling it down without having to actually read the whole list would be appreciated.

Example:

   Function MyLookup(param1) as Variant
        '..............
        '...do stuff...
        '..............
        If SomethingBad = True Then
            MyLookup = VBA.CVErr(N) 'What here for N?
            Exit Function
        Endif
   End Function
Erik A
  • 28,352
  • 10
  • 37
  • 55
spinjector
  • 2,534
  • 1
  • 19
  • 39

1 Answers1

5

If the error that you are going to raise (or in this case pass) does not fall into the category of any of the predefined error messages, you should define your own error number and raise that. The constant vbObjectError exists for this purpose - add it to your internal number and pass that:

Public Const SOMETHING_BAD_ERROR As Long = vbObjectError + 666

Function MyLookup(param1) As Variant
     '..............
     '...do stuff...
     '..............
     If SomethingBad = True Then
         MyLookup = VBA.CVErr(SOMETHING_BAD_ERROR)
         Exit Function
     End If
End Function

Passing or raising an error number that does not convey exactly what really went wrong is a disservice to the caller.

Comintern
  • 20,878
  • 5
  • 30
  • 73
  • Hmm, perfect. I've used vbObjectError in Excel functions, but I didn't stop to think it might work in this case when returning an error back to an Access report or query. Thanks. – spinjector Aug 08 '18 at 13:17
  • I have a further thought/question. By returning `vbObjectError + nnn` this results in a single numeric value. I know it's not possible to create a New ErrObject, but is there any way to return error text with a UD error value? Or is there a way to define it globally, such a a public Enum or Const, or even a CLS..? I could hack something together, but I'm wondering if there's a "industry standard" way of doing it. – spinjector Aug 08 '18 at 15:47
  • @spinjector I would guess that is implementation specific. What `CVError` does is basically just wrap an error number up into a `Variant`. If you need the full error information, you can actually pass the `Err` object itself. – Comintern Aug 08 '18 at 16:29