XERROR

XERROR(error_type)
error_type
Required. Specifies the type of output error and takes integers from 0 to 14
XERROR allows for conveniently generating most of the Excel errors as output to functions
R

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
528
Office Version
  1. 365
Platform
  1. Windows
XERROR allows for conveniently generating most of the Excel errors as output to functions

With XERROR, it is very easy to generate all but four of the Excel errors as output to functions. Error types 0, 1, 2, 3, 4, 5, 6, 7, 8, 13, and 14 (i.e. #EXTERNAL!, #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A, #GETTING_DATA, #FIELD!, and #CALC!, respectively) are currently supported. For error types 9, 10, 11, and 12 (i.e. #SPILL!, #CONNECT!, #BLOCKED!, and #UNKNOWN!, respectively), XERROR returns the "Error not supported!" message. For any other invalid error number, XERROR returns the "Invalid error number!" message.

Excel Formula: 
=LAMBDA(error_type,
   LET(e,error_type+1,
      IF(OR(e<1,e>15,MOD(e,1)<>0),
         "Invalid error number!",
         IF(ISERROR(MATCH(e,{1,2,3,4,5,6,7,8,9,14,15},0)),
            "Error not supported!",
            CHOOSE(e,#EXTERNAL!,#NULL!,#DIV/0!,#VALUE!,#REF!,#NAME?,#NUM!,#N/A,#GETTING_DATA,,,,,INDIRECT("").a,LAMBDA(""))
         )
      )
   )
)

Blank power workbook1
ABCDE
1
2#CALC!
3error #type
40#EXTERNAL!16
51#NULL!16
62#DIV/0!16
73#VALUE!16
84#REF!16
95#NAME?16
106#NUM!16
117#N/A16
128#GETTING_DATA16
139Error not supported!2
1410Error not supported!2
1511Error not supported!2
1612Error not supported!2
1713#FIELD!16
1814#CALC!16
19-1Invalid error number!2
202.2Invalid error number!2
2117Invalid error number!2
2217.3Invalid error number!2
23
Sheet1
Cell Formulas
RangeFormula
C2C2=LAMBDA(error_type,LET(e,error_type+1,IF(OR(e<1,e>15,MOD(e,1)<>0),"Invalid error number!",IF(ISERROR(MATCH(e,{1,2,3,4,5,6,7,8,9,14,15},0)),"Error not supported!",CHOOSE(e,#EXTERNAL!,#NULL!,#DIV/0!,#VALUE!,Sheet1!#REF!,#NAME?,#NUM!,#N/A,#GETTING_DATA,,,,,INDIRECT("").a,LAMBDA(""))))))
C4C4=XERROR(0)
D4:D22D4=TYPE(C4)
C5C5=XERROR(1)
C6C6=XERROR(2)
C7C7=XERROR(3)
C8C8=XERROR(4)
C9C9=XERROR(5)
C10C10=XERROR(6)
C11C11=XERROR(7)
C12C12=XERROR(8)
C13C13=XERROR(9)
C14C14=XERROR(10)
C15C15=XERROR(11)
C16C16=XERROR(12)
C17C17=XERROR(13)
C18C18=XERROR(14)
C19C19=XERROR(-1)
C20C20=XERROR(2.2)
C21C21=XERROR(17)
C22C22=XERROR(17.3)



If anyone can come up with tricks to add support for the remaining four errors, please feel free to post an update here.

This work is based on the discussion at the following thread:
www.mrexcel.com

How to generate #VALUE! error as output for a LAMBDA function?

Hello, Is there a way to generate #VALUE! error for the output of a LAMBDA function? For built-in functions, if the wrong values are entered for the parameters, the functions return the #VALUE! error, and I want to mimic that behavior for my LAMBDAs. (Essentially, I need something like the...
www.mrexcel.com www.mrexcel.com
 
Upvote 0
Updated code:
1) fixes the issue where selecting a blank cell for input led to displaying the #EXTERNAL! error
2) fixes the #REF! error issue where the code would change to include the sheet name (such as Sheet1!#REF!)
3) enables spill support

XERROR
VBA Code: 
=LAMBDA(error_type,
   IF(
      MAP(IF(ISNUMBER(error_type),error_type),LAMBDA(a,ISERROR(MATCH(a,ErrorNs,0)))),
      "Invalid error number!",
      IF(
         ISERROR(MATCH(error_type,{9,10,11,12},0)),
         LET(e,error_type+1,CHOOSE(e,#EXTERNAL!,#NULL!,#DIV/0!,#VALUE!,INDEX("",2),#NAME?,#NUM!,#N/A,#GETTING_DATA,,,,,INDIRECT("").a,LAMBDA(""))),
         "Error not supported!"
         )
      )
)

ErrorNs
VBA Code: 
={0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}

XERROR.xlsx
ABCDEFGHIJKLM
1
2#CALC!
3
4parameteroutput
5no parameter#VALUE!
6blank parameterInvalid error number!
7blank cell parameterInvalid error number!
8
9error numbers from cellserror numbers input directlyerror numbers input directly as array
10error numberoutputdata typespill outputdata typeoutputdata typeoutputdata type
110#EXTERNAL!16#EXTERNAL!16#EXTERNAL!16#EXTERNAL!16
121#NULL!16#NULL!16#NULL!16#NULL!16
132#DIV/0!16#DIV/0!16#DIV/0!16#DIV/0!16
143#VALUE!16#VALUE!16#VALUE!16#VALUE!16
154#REF!16#REF!16#REF!16#REF!16
165#NAME?16#NAME?16#NAME?16#NAME?16
176#NUM!16#NUM!16#NUM!16#NUM!16
187#N/A16#N/A16#N/A16#N/A16
198#GETTING_DATA16#GETTING_DATA16#GETTING_DATA16#GETTING_DATA16
209Error not supported!2Error not supported!2Error not supported!2Error not supported!2
2110Error not supported!2Error not supported!2Error not supported!2Error not supported!2
2211Error not supported!2Error not supported!2Error not supported!2Error not supported!2
2312Error not supported!2Error not supported!2Error not supported!2Error not supported!2
2413#FIELD!16#FIELD!16#FIELD!16#FIELD!16
2514#CALC!16#CALC!16#CALC!16#CALC!16
2615Invalid error number!2Invalid error number!2Invalid error number!2Invalid error number!2
2717.5Invalid error number!2Invalid error number!2Invalid error number!2Invalid error number!2
28-32Invalid error number!2Invalid error number!2Invalid error number!2Invalid error number!2
29TRUEInvalid error number!2Invalid error number!2Invalid error number!2Invalid error number!2
30FALSEInvalid error number!2Invalid error number!2Invalid error number!2Invalid error number!2
31#DIV/0!Invalid error number!2Invalid error number!2Invalid error number!2Invalid error number!2
32
Sheet1
Cell Formulas
RangeFormula
B2B2=LAMBDA(error_type,IF(MAP(IF(ISNUMBER(error_type),error_type),LAMBDA(a,ISERROR(MATCH(a,ErrorNs,0)))),"Invalid error number!",IF(ISERROR(MATCH(error_type,{9,10,11,12},0)),LET(e,error_type+1,CHOOSE(e,#EXTERNAL!,#NULL!,#DIV/0!,#VALUE!,INDEX("",2),#NAME?,#NUM!,#N/A,#GETTING_DATA,,,,,INDIRECT("").a,LAMBDA(""))),"Error not supported!")))
C5C5=XERROR()
C6C6=XERROR("")
C7,C11:C31C7=XERROR(B7)
L11:L31,F11:F31,D11:D31,I11:I31D11=TYPE(C11)
E11:E31E11=XERROR(B11:B31)
H11H11=XERROR(0)
H12H12=XERROR(1)
H13H13=XERROR(2)
H14H14=XERROR(3)
H15H15=XERROR(4)
H16H16=XERROR(5)
H17H17=XERROR(6)
H18H18=XERROR(7)
H19H19=XERROR(8)
H20H20=XERROR(9)
H21H21=XERROR(10)
H22H22=XERROR(11)
H23H23=XERROR(12)
H24H24=XERROR(13)
H25H25=XERROR(14)
H26H26=XERROR(15)
H27H27=XERROR(17.5)
H28H28=XERROR(-32)
H29H29=XERROR(TRUE)
H30H30=XERROR(FALSE)
H31H31=XERROR(#DIV/0!)
K11:K31K11=XERROR({0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;17.5;-32;TRUE;FALSE;#DIV/0!})
Dynamic array formulas.
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top