Click here to Skip to main content
14,878,278 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Hi all,
I would like to have a function which returns the signed maximum available number in Excel in case or overflow (i.e. + or - 9.99999999999999E+307) in case of overflow and zero in case of underflow.
IFERROR let me only set a single value in case of error.

I came out with some code but it doesn't work

The main reason, I think is that the argument, which could be an expression, is evaluated by Excel before passing it to the function, so I cannot control what happens

Any clue ?

Thanks

What I have tried:

VB
Option Explicit
Function Test(ByVal x As Variant) As Variant
Application.Volatile
Dim retval As Variant
Application.ScreenUpdating = False
On Error GoTo ErrorHandling
retval = Evaluate(x)
If VarType(retval) = vbError Then
Select Case retval
 Case CVErr(xlErrDiv0)
 MsgBox "#DIV/0! error"
 Case CVErr(xlErrValue)
 MsgBox "#VALUE! error"
Case Else
         MsgBox "UNKNOWN ERROR  - Error# " & Err.Number & " : " & Err.Description
         End Select
End If

Test = retval
Application.ScreenUpdating = True
Exit Function
ErrorHandling:
Select Case Err.Number
Case 10 ' Divide by 0
MsgBox "Division by 0"
 Case Else
         MsgBox "UNKNOWN ERROR  - Error# " & Err.Number & " : " & Err.Description
         End Select
End Function
Posted
Updated 29-Jul-20 21:54pm

1 solution

I got this to work with only one change - the declaration needs to be Public
[__b__]Public Function Test(ByVal x As Variant) As Variant
Make sure you have this function in a module - not in Sheet1 or ThisWorkBook.

If you attempt to examine the argument x while debugging you do only see e.g. "Error 2007" for 1/0 but you handle that with the Evaluate(x)
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900