Microsoft Office Tutorials and References
In Depth Information
Understanding Custom Function Limitations
Understanding Custom Function Limitations
Almost all users who start creating custom worksheet functions by using VBA make a fatal
mistake: They try to get the function to do more than what is possible.
A worksheet function returns a value, and the function must be completely passive: In other
words, the function cannot change anything on the worksheet.
For example, you might write a function like the following. This function takes one argument (a
cell reference) and attempts to change the formatting of the cell. Although the following function
contains perfectly valid VBA code, it does not make the referenced cell bold:
Function MakeBold(cell)
If cell.Value > 100 Then
cell.Font.Bold = True
cell.Font.Bold = False
End If
End Function
In many cases, you can use an event macro rather than a custom function. The following macro,
located in the module for a worksheet, is executed whenever the sheet is calculated. If cell A1
contains a value greater than 100, the cell is made bold; otherwise, it isn’t bold:
Private Sub Worksheet_Calculate()
If Range(“A1”).Value > 100 Then
Range(“A1”).Font.Bold = True
Range(“A1”).Font.Bold = False
End If
End Sub
This simple example is for illustration only. A more efficient solution is to use Excel’s
conditional formatting feature.
Search JabSto ::

Custom Search