Options for error output in custom Excel functions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jack Hoxley [MVP] - 20 May 2008 14:58 GMT

Afternoon all,

I've been doing some research into reporting errors in custom Excel
functions to the end user today. I've not really come up with any promising
leads so I was hoping someone frequenting this newsgroup might be able to
point me in the right direction :-)

The primary interest is in increasing the usability of our addin. Currently
it just dumps out a textual error message into the cell (repeated accordingly
if the output is an array) but this looks plain ugly to say the least.
Something friendly and ideally self-explanatory would make everyone happy and
stop the phones ringing off the hook whenever a scary error message appears!

What i'm wondering about is any standardised options for error pop-ups,
highlighting, help file linking, auto-complete type hints as to which
parameter is wrong etc...etc...

Maybe something like the standard Excel feature where it draws a small green
triangle in the upper-left corner that then shows a pop-up menu with details
on the formula error/warning.

Any suggestions?

Thanks in advance,
Jack

Reply to this Message

PatrickS - 20 May 2008 15:11 GMT

Not sure if this is of any use, but would adding a comment to the cell where
the error has occurred giving them the details be of any use? I don't think
that comments can have hyperlinks or anything like that though, just plain
text from the looks of things.

I tried recording a macro and it created the following VBA code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 20/05/2008 by PatrickS
'

'
Range("D5").AddComment
Range("D5").Comment.Visible = True
Range("D5").Comment.Text Text:="Please provide the correct parameters."
End Sub

> Afternoon all,
>
[quoted text clipped - 21 lines]
> Thanks in advance,
> Jack

Reply to this Message

Jim Cone - 20 May 2008 15:41 GMT

Validate the arguments before use and keep any
error messages short would be my approach.
'--
Function Sludge(postposition as Double, _
speedrating as Double, _
lastouting as String) as Variant
On Error GoTo Toxic

Select Case True
Case postposition > 21
Sludge = "Check Post Position"
Exit Function
Case speedrating < 0
Sludge = "Verify Speed Rating"
Exit Function
Case Len(lastouting) > 255
Sludge = "One Word Only Please"
Exit Function
End Select

'Do something in the function

Sludge = "Faster Horses Required"
Exit Function
Toxic:
Sludge = "Function Error " & Err.Number
End Function

Signature

Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"Jack Hoxley [MVP]" <Jack Hoxley [MVP]@discussions.microsoft.com>
wrote in message
Afternoon all,

I've been doing some research into reporting errors in custom Excel
functions to the end user today. I've not really come up with any promising
leads so I was hoping someone frequenting this newsgroup might be able to
point me in the right direction :-)

The primary interest is in increasing the usability of our addin. Currently
it just dumps out a textual error message into the cell (repeated accordingly
if the output is an array) but this looks plain ugly to say the least.
Something friendly and ideally self-explanatory would make everyone happy and
stop the phones ringing off the hook whenever a scary error message appears!

What i'm wondering about is any standardised options for error pop-ups,
highlighting, help file linking, auto-complete type hints as to which
parameter is wrong etc...etc...

Maybe something like the standard Excel feature where it draws a small green
triangle in the upper-left corner that then shows a pop-up menu with details
on the formula error/warning.

Any suggestions?

Thanks in advance,
Jack

Reply to this Message