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


deltaquattro wrote:

On 5 Feb, 18:00, Dave Peterson wrote:
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 wrote:

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

--

Dave Peterson

--

Dave Peterson

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

On 8 Feb, 15:47, Dave Peterson wrote:
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

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



deltaquattro wrote:

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

On 8 Feb, 15:47, Dave Peterson wrote:
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

--

Dave Peterson