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

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




"deltaquattro" wrote in 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

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

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

"deltaquattro" wrote:
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" wrote in 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

I wrote:
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
...
"deltaquattro" wrote:
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" wrote in 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

On 5 Feb, 17:05, "Peter T" <peter_t@discussions wrote:
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

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

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


On 5 Feb, 22:48, "Joe User" <joeu2004 wrote:
I wrote:
* 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

...



"deltaquattro" wrote:
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" wrote in 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

Hi, Dave,

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

Best Regards,

deltaquattro

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:

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

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