Error handling in vba function

[Jaap Versteegh]

In general, what would be the best way to show an error message from a vba
function ?

I've tried a MsgBox call, but this is anoying when the function editor
(shown after clicking the = button) is used. The function editor tries to
evaluate the function before all parameters are entered completely. This
causes an error in my vba function which then interrupts the parameter
selection with the message box.

Is there some way to determine is the function editor (is it called this way
?) is visible ? so I can surpress the error message in that case ?

TIA for any help !

Jaap Versteegh

 

[Frank Kabel]

Hi
use the CVErr function in the VBA help

Regards
Frank

 

[Jaap Versteegh]

Hi

use the CVErr function in the VBA help

Thanks for the response Frank, but how would this help ?

Problem:
A vba (worksheet)function needs 2 ranges as parameters, but they both need
to be of the same size. I would like to notify the user when they aren't
rather than just return "#VALUE" in the cell.

So something like:

function MyFunc (MyRange1 as Range, MyRange2 as Range)
On Error GoTo ErrorHandler
if MyRange1.Cells.Count <> MyRange2.Cells.Count then
Err.Raise 1000, "MyFunc", "Ranges not of same size !"
end if
' Do the stuff the function does here !
Exit Function
ErrorHandler:
' Now here I would like to tell the user of this function what the problem
is ...
MsgBox Err.Description
' This however causes problems when entering the function from the formula
bar after
' clicking the equal sign -> the function is invoked before the second
range has been entered
' completely, so the ranges are not of the same size (yet). The message
box is displayed so
' the user can't complete the entry of the second parameter....

' Re-raise the error so the function will return #VALUE !
Err.Raise 1000
End Function

TIA for any further suggestions...

 

[Charles Williams]

Hi Jaap,

It is not generally considered a good idea to have a Msgbox inside a UDF:
You could make the function return a string containing the error message,
but usually you make it return an error value as Frank suggested using
CVERR(xlerrValue) or CVERR(XLErrNA) etc.

To detect if the function is being called from the function wizard Alex
Koenig has suggested (and it works):
If (Not Application.CommandBars("Standard").Controls(1).Enabled) Then

' function wizard

else

' not function wizard

endif

see http://www.DecisionModels.com/calcsecretsj.htm for more info on UDF
problems.


regards

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Bob Phillips]

You could also use other errors. So your unequal ranges could be

if MyRange1.Cells.Count <> MyRange2.Cells.Count then
myFunc = CvErr(xlErrRef)
End Function
End If

and use #Value for some otherv error. Your user instructions could tell the
user what each error means.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)