SOLVED

How to return #Value! or #Name? from VBA?

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3155692%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EHow%20to%20return%20%23Value!%20or%20%23Name!%20from%20VBA%3F%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3155692%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EHow%20can%20I%20return%20the%20standard%20spreadsheet%20errors%20%23Value!%20or%20%23Name!%20from%20a%20VBA-function%20to%20the%20calling%20Cell%3F%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EWith%20the%20following%20little%20function%20%3CEM%3EdoSomething%26lt%3B%5C%2FEM%26gt%3B%20one%20can%20write%20the%20formula%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FEM%3E%3CPRE%20class%3D%22%5C%26quot%3Blia-code-sample%22%20language-excel-formula%3D%22%22%3E%3CEM%3E%3CCODE%3E%3DdoSomething(A1)%26lt%3B%5C%2Fcode%26gt%3B%26lt%3B%5C%2Fpre%26gt%3B%3C%2FCODE%3E%3C%2FEM%3E%3C%2FPRE%3E%3C%2FP%3E%3CP%3Einto%20some%20cell%20and%20it%20produces%20any%20error%20when%20its%20number%20is%20entered%20into%20cell%20A1.%20But%20I%20cannot%20find%20the%20numbers%20for%20those%20standard%20Excel%20errors.%26lt%3B%5C%2FP%26gt%3B%3CPRE%20class%3D%22%5C%26quot%3Blia-code-sample%22%20language-visual-basic%3D%22%22%3E%3CCODE%3EFunction%20doSomething(nbr%20As%20Long)%20As%20Variant%5Cn%20%20%20doSomething%20%3D%20True%5Cn%20%20%20On%20Error%20GoTo%20handleError%5Cn%20%20%20If%20nbr%20%26gt%3B%200%20Then%20Err.Raise%20nbr%2C%20%5C%22doSomething%5C%22%5Cn%20%20%20Exit%20Function%5CnhandleError%3A%5Cn%20%20%20doSomething%20%3D%20Err.Description%5CnEnd%20Function%26lt%3B%5C%2Fcode%26gt%3B%26lt%3B%5C%2Fpre%26gt%3B%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FP%3E%3CP%3E%26nbsp%3BBTW%3A%20I%20cannot%20find%20documentation%20about%20the%20meaning%20of%20all%20those%20error%20numbers.%20Can%20someone%20point%20me%20to%20a%20reference%20list%3F%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3155692%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EMacros%20and%20VBA%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3155692%22%20slang%3D%22en-US%22%3EHow%20to%20return%20%23Value!%20or%20%23Name!%20from%20VBA%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3155692%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20can%20I%20return%20the%20standard%20spreadsheet%20errors%20%23Value!%20or%20%23Name!%20from%20a%20VBA-function%20to%20the%20calling%20Cell%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20the%20following%20little%20function%20%3CEM%3EdoSomething%3C%2FEM%3E%20one%20can%20write%20the%20formula%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DdoSomething(A1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Einto%20some%20cell%20and%20it%20produces%20any%20error%20when%20its%20number%20is%20entered%20into%20cell%20A1.%20But%20I%20cannot%20find%20the%20numbers%20for%20those%20standard%20Excel%20errors.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EFunction%20doSomething(nbr%20As%20Long)%20As%20Variant%0A%20%20%20doSomething%20%3D%20True%0A%20%20%20On%20Error%20GoTo%20handleError%0A%20%20%20If%20nbr%20%26gt%3B%200%20Then%20Err.Raise%20nbr%2C%20%22doSomething%22%0A%20%20%20Exit%20Function%0AhandleError%3A%0A%20%20%20doSomething%20%3D%20Err.Description%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BBTW%3A%20I%20cannot%20find%20documentation%20about%20the%20meaning%20of%20all%20those%20error%20numbers.%20Can%20someone%20point%20me%20to%20a%20reference%20list%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3155692%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

How can I return the standard spreadsheet errors #Value! or #Name? from a VBA-function to the calling Cell?

 

With the following little function doSomething one can write the formula 

 

=doSomething(A1)

 

into some cell and it produces any error when its number is entered into cell A1. But I cannot find the numbers for those standard Excel errors.

 

Function doSomething(nbr As Long) As Variant
   doSomething = True
   On Error GoTo handleError
   If nbr > 0 Then Err.Raise nbr, "doSomething"
   Exit Function
handleError:
   doSomething = Err.Description
End Function

 

 BTW: I cannot find documentation about the meaning of all those error numbers. Can someone point me to a reference list?

4 Replies

@Werner_Geiger 

You can use the CVErr function in combination with the Cell Error Values such as xlErrDiv0 and xlErrNA.

@Hans Vogelaar 

I knew CVErr but do not know how to produce #Name? or #Value! with it.

I changed my doSomething program a bit. For negative numbers it produces CVErr(Abs(nbr)) now. For 0 it produces True.

Function doSomething(nbr As Long) As Variant
   doSomething = True
   If nbr < 0 Then
      doSomething = CVErr(Abs(nbr))
   ElseIf nbr > 0 Then
      On Error GoTo handleError
      Err.Raise nbr, "doSomething"
   End If
   Exit Function
handleError:
   doSomething = Err.Description
End Function

 

best response confirmed by Werner_Geiger (Occasional Contributor)
Solution

@Werner_Geiger 

Now I found it: You have to use

' use nbr = xlErrDiv0, xlErrNA, xlErrName, xlErrNull, xlErrNum , xlErrRef, xlErrValue
doSomething = CVErr(nbr)

If called from a spreadsheet this produces:

#DIV/0!, #NA, #Name!, etc.

@Werner_Geiger 

Yes, that is what the links I posted show...