XL2000: Visual Basic Programming Errors Are Not Reported with User-Defined Functions (215185)


The information in this article applies to:

  • Microsoft Excel 2000
This article was previously published under Q215185

SYMPTOMS

In Microsoft Excel, when you create a user-defined function in Microsoft Visual Basic for Applications, the function may not run, in which case, there are no errors reported in the Visual Basic Editor.

For example, in the following user-defined function 
Option Explicit
Function Test(r As Range) As Boolean
    Dim MyRange As Range
    MyRange = r
    MsgBox "Whatever"
End Function
no syntax error is reported for line four. Line four should read as follows: 
Set MyRange = r
When the the user-defined function is used in the following Excel formula

=Test(B2)

there is no run-time error reported, but the Excel error #VALUE! appears in the cell containing the formula.

CAUSE

This behavior can occur because the Visual Basic Editor does not recognize some syntax errors in user-defined functions.

WORKAROUND

To work around this issue, when a user-defined function does not return the expected result and there are no coding errors reported, recheck the Visual Basic code for common programming errors.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbbug kbpending KB215185
©2004 Microsoft Corporation. All rights reserved.