How to return an error code from a function

[deltaquattro]

Hi,

after some discussions on the ng I decided to keep input data checking
inside my functions. This prompts the problem of how to return an
error code from the function: for example

Function MySqrt(x as Double) As Double
Dim err As Boolean
If x <0 Ihen
err=True
Exit Function
Else
err=False
x = Application.Worksheetfunction.sqrt(x)
End If
End Function

However, err cannot be passed back to the caller! I've read about
different workarounds, and I would like to know your opinion on them,
or just which is your approach:

1. convert the Function to a Sub (easiest, but maybe slower?)
2. pass err ByRef (best?)
3. declare Function As Variant. Variant variables, however, cause a
slowdown of the code, so would this be any faster than 1. ?
4. use global variables (I'd rather not).

Thanks in advance,

Best Regards,

Sergio Rossi

 

[Peter T]

Not sure why you say 'err' can't be passed back to the caller, simply

MySqrt = err
or as an additional argument

If you declare err As Long rather than as a boolean would enable you to pass
more in the way of information, eg the error number itself

In passing, although worksheet functions are extremely efficient in cells
the overhead of calling them in VBA makes them relatively slow, where viable
better to roll your own, eg

result = X ^ (1 / nRoot)

maybe include the root as an argument,
(args, Optional nRoot as Long = 2)

BTW, your 'Exit Function' is redundant

Regards,
Peter T

 

[Dave Peterson]

What do you mean by an error code?

Do you mean the err.number? If yes, then you have to do something special since
err.numbers would be the sqareroot of a number.

Maybe you could do something like:

Option Explicit
Function mySqrt(x As Double) As Double

Dim myVal As Variant
On Error Resume Next
myVal = Sqr(x)
If Err.Number <> 0 Then
'are all the possible errors positive???
'I'm not sure
mySqrt = -Abs(Err.Number)
Err.Clear
Else
mySqrt = myVal
End If
On Error GoTo 0

End Function
Sub testme()

Dim res As Double

res = mySqrt(-123)

If res < 0 Then
On Error Resume Next
Err.Raise Number:=-res
With Err
MsgBox .Number & vbLf & .Description
End With
Else
MsgBox res
End If

End Sub


Or you could pass the error object, too:

Option Explicit
Function mySqrt(x As Double, myError As ErrObject) As Double

Dim myVal As Variant
On Error Resume Next
myVal = Sqr(x)
If Err.Number <> 0 Then
Set myError = Err
mySqrt = -1
Else
mySqrt = myVal
End If

End Function
Sub testme()

Dim res As Double
Dim myRetError As ErrObject

res = mySqrt(-1, myRetError)

If res = -1 Then
With myRetError
MsgBox .Number & vbLf & .Description
End With
Else
MsgBox res
End If

End Sub

 

[Joe User]

Function MySqrt(x as Double) As Double
Dim err As Boolean
If x <0 Ihen
err=True
Exit Function [....]
However, err cannot be passed back to the caller!

I suspect the following is what you want:

Function mySqrt(x As Double)
If x < 0 Then
mySqrt = CVErr(xlErrNum)
Else
mySqrt = Sqr(x)
End If
End Function

The key is: the UDF must be a variant type (implicit).

See Help for "cell error values" for other Excel error constants.


----- original message -----

 

[Joe User]

mySqrt = CVErr(xlErrNum)

But I would do that only if you might be passing the error back to Excel --
that is, the caller might a UDF call in an Excel formula.


----- original message -----

Function MySqrt(x as Double) As Double
Dim err As Boolean
If x <0 Ihen
err=True
Exit Function [....]
However, err cannot be passed back to the caller!

I suspect the following is what you want:

Function mySqrt(x As Double)
If x < 0 Then
mySqrt = CVErr(xlErrNum)
Else
mySqrt = Sqr(x)
End If
End Function

The key is: the UDF must be a variant type (implicit).

See Help for "cell error values" for other Excel error constants.


----- original message -----

Hi,

after some discussions on the ng I decided to keep input data checking
inside my functions. This prompts the problem of how to return an
error code from the function: for example

Function MySqrt(x as Double) As Double
Dim err As Boolean
If x <0 Ihen
err=True
Exit Function
Else
err=False
x = Application.Worksheetfunction.sqrt(x)
End If
End Function

However, err cannot be passed back to the caller! I've read about
different workarounds, and I would like to know your opinion on them,
or just which is your approach:

1. convert the Function to a Sub (easiest, but maybe slower?)
2. pass err ByRef (best?)
3. declare Function As Variant. Variant variables, however, cause a
slowdown of the code, so would this be any faster than 1. ?
4. use global variables (I'd rather not).

Thanks in advance,

Best Regards,

Sergio Rossi

 

[deltaquattro]

Not sure why you say 'err' can't be passed back to the caller, simply

MySqrt = err
or as an additional argument

If you declare err As Long rather than as a boolean would enable you to pass
more in the way of information, eg the error number itself

Hi, Peter,

thanks for the reply. The problem is that my actual code is more
complex than this. MySqrt cannot have negative values, so assigning a
negative value to err and passing that back would allow the caller to
understand if MySqrt found an error or not. However, my real function
(let's call it MyFun) can assume any real value, from negative to
positive. So I cannot just assign a numerical value to err and pass it
back.

In passing, although worksheet functions are extremely efficient in cells
the overhead of calling them in VBA makes them relatively slow, where viable
better to roll your own, eg

result = X ^ (1 / nRoot)

maybe include the root as an argument,
(args, Optional nRoot as Long = 2)

Very interesting, I didn't know that.

BTW, your  'Exit Function' is redundant

You're right, I just wrote the code as an example of a possible
function so I didn't check for redundancies.

Regards,
Peter T

Thanks,

Best Regards,

deltaquattro

 

[deltaquattro]

What do you mean by an error code?  

Do you mean the err.number?  If yes, then you have to do something special since
err.numbers would be the sqareroot of a number.

Maybe you could do something like:

[..]
Hi, Dave,

thanks for the suggestion. However, my actual function MyFun is more
complex than MySqrt, which was just an example, and can assume
negative values. So the caller cannot understand if something went
wrong, by looking at the sign of the result.

Or you could pass the error object, too:

Option Explicit
Function mySqrt(x As Double, myError As ErrObject) As Double

    Dim myVal As Variant
    On Error Resume Next
    myVal = Sqr(x)
    If Err.Number <> 0 Then
        Set myError = Err
        mySqrt = -1
    Else
        mySqrt = myVal
    End If

End Function
Sub testme()

    Dim res As Double
    Dim myRetError As ErrObject

    res = mySqrt(-1, myRetError)

    If res = -1 Then
        With myRetError
            MsgBox .Number & vbLf & .Description
        End With
    Else
        MsgBox res
    End If

End Sub

Again, this doesn't work because it relies on checking the sign of
MySqrt. Can you suggest a workaround? Maybe I could substitute

    If res = -1 Then

with

    If Not myRetError Is Nothing Then

BTW, your example is very interesting because it shows that I didn't
understand something about VBA functions. I thought that VBA functions
couldn't pass back any argument apart from the function value, and, as
a matter of fact, when I tried doing the same declaring myRetError As
Boolean, I got a compile time error. So I thought that wouldn't work
even when using Object variables. This is false, though, because your
code works perfectly. Is there some special rule about Object
variables which make them behave differently than other variables,
when passed to/from functions? Thanks,

Best Regards

Sergio Rossi (deltaquattro)

 

[deltaquattro]

Hi, Joe,

the caller is another subroutine, so I'd rather not do that, unless
that's the only way. It seems that Dave's suggestion with my small
modification did the trick. At least, for now it's working fine
Thanks,

Best Regards

deltaquattro

  mySqrt = CVErr(xlErrNum)

But I would do that only if you might be passing the error back to Excel --  
that is, the caller might a UDF call in an Excel formula.

----- original message -----

"Joe User" <joeu2004> wrote in message

Function MySqrt(x as Double) As Double
Dim err As Boolean
If x <0 Ihen
 err=True
 Exit Function [....]
However, err cannot be passed back to the caller!

I suspect the following is what you want:

Function mySqrt(x As Double)
If x < 0 Then
  mySqrt = CVErr(xlErrNum)
Else
  mySqrt = Sqr(x)
End If
End Function

The key is:  the UDF must be a variant type (implicit).

See Help for "cell error values" for other Excel error constants.

----- original message -----

 

[deltaquattro]

Hi, Dave,

I implemented your code with my modification (check on "Is Nothing")
and it seems to be working great. Thanks!!!

Best Regards,

deltaquattro

 

[Dave Peterson]

Yep, you could just pass a boolean so you could check that or you could check
the err.number to see if it's different from 0.

Just to show that it's ok to pass booleans:

Option Explicit
Function mySqrt(x As Double, WorkedOk As Boolean, _
myError As ErrObject) As Double

Dim myVal As Variant

On Error Resume Next
myVal = Sqr(x)
If Err.Number <> 0 Then
Set myError = Err
mySqrt = -1 'anything you want
WorkedOk = False
Else
mySqrt = myVal
WorkedOk = True
End If

End Function
Sub testme()

Dim res As Double
Dim myRetError As ErrObject
Dim myRetOk As Boolean

res = mySqrt(-1, myRetOk, myRetError)

If myRetOk = False Then
With myRetError
MsgBox .Number & vbLf & .Description
End With
Else
MsgBox res
End If

End Sub

What do you mean by an error code?

Do you mean the err.number? If yes, then you have to do something special since
err.numbers would be the sqareroot of a number.

Maybe you could do something like:

[..]
Hi, Dave,

thanks for the suggestion. However, my actual function MyFun is more
complex than MySqrt, which was just an example, and can assume
negative values. So the caller cannot understand if something went
wrong, by looking at the sign of the result.

Or you could pass the error object, too:

Option Explicit
Function mySqrt(x As Double, myError As ErrObject) As Double

Dim myVal As Variant
On Error Resume Next
myVal = Sqr(x)
If Err.Number <> 0 Then
Set myError = Err
mySqrt = -1
Else
mySqrt = myVal
End If

End Function
Sub testme()

Dim res As Double
Dim myRetError As ErrObject

res = mySqrt(-1, myRetError)

If res = -1 Then
With myRetError
MsgBox .Number & vbLf & .Description
End With
Else
MsgBox res
End If

End Sub

Again, this doesn't work because it relies on checking the sign of
MySqrt. Can you suggest a workaround? Maybe I could substitute

If res = -1 Then

with

If Not myRetError Is Nothing Then

BTW, your example is very interesting because it shows that I didn't
understand something about VBA functions. I thought that VBA functions
couldn't pass back any argument apart from the function value, and, as
a matter of fact, when I tried doing the same declaring myRetError As
Boolean, I got a compile time error. So I thought that wouldn't work
even when using Object variables. This is false, though, because your
code works perfectly. Is there some special rule about Object
variables which make them behave differently than other variables,
when passed to/from functions? Thanks,

Best Regards

Sergio Rossi (deltaquattro)

 

[deltaquattro]

You're right, Dave. I made a mistake when I tested the the function
which passed boolean, because in testme I kept calling the function
which passed myRetError as an Object. So it's not true that functions
can return just a single variable: they can actually return any number
of variables (I tried that also with doubles, longs, etc.). Good to
know,

Best Regards

deltaquattro

 

[Dave Peterson]

Yep. And if you want to return an object, you can use something like:

Option Explicit
Function myFunct() As Range
Set myFunct = ActiveSheet.Range("A1")
End Function
Sub testme()
Dim myRng As Range
Set myRng = myFunct()
MsgBox myRng.Address(external:=True)
End Sub