Operation is not supported in Excel UDF

[retailcoder]

Let's make an Excel-specific inspection that flags instructions that can't be used in a UDF, causing undesired behavior. Inspection should ignore modules with Option Private Module set, and only look at standard/procedural modules.

• ActiveCell calls that mean to work against the cell being calculated, should be replaced with Application.Caller.
• Application.Calculation, Application.EnableEvents, Application.ScreenUpdating assignments, are illegal.
• Range.Activate calls can't be removed without breaking the code, but need to be flagged as illegal anyway.
• Range.Insert, Range.Delete, Range.BorderAround, assignment to any FormatCondition member (or any FormatConditions method call), assignments to any Border members, or any Font members, are illegal.
• Assignments to Range.Value, Range.Value2, and Range.[_Default] (or to any Range property for that matter) are illegal and should be removed.
• Names.Add calls are illegal and should be removed.
• Assignments to Sheet.Name (Worksheet.Name), Sheets.Add (Worksheets.Add), Sheet.Delete, Sheets.Delete (Worksheet.Delete, Worksheets.Delete), Sheet.Copy (Worksheet.Copy), are all illegal too.
• Charts method calls, and Chart property assignments and method calls are illegal.
• PivotTables method calls, and PivotTable property assignments and method calls are illegal.
• ListObject , ListRow and ListColumn method calls and property assignments are illegal.

Probably forgetting a number of others...

Operation is not supported in a User-Defined Function
A public function in a standard procedural module is exposed to Excel worksheets as a UDF. These functions can produce invalid results when invoked from a worksheet. If the function isn't intended to be exposed as a UDF, consider specifying 'Option Private Module'.

One quick-fix could be to move the function to another standard/procedural module with Option Private Module specified; another option could be to specify Option Private Module at the top of the module. Another quick-fix could be to outright remove the illegal statement. Support for "Fix in procedure" is a must here.

[comintern]

I was considering using some sort of "visibile as UDF" filter for #4488, and found a general description of prohibited calls on an old MS support page:

A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel. This means that such a function cannot do any of the following:

Insert, delete, or format cells on the spreadsheet.
Change another cell's value.
Move, rename, delete, or add sheets to a workbook.
Change any of the environment options, such as calculation mode or screen views.
Add names to a workbook.
Set properties or execute most methods. 

As a "rough estimate" of what is visible, this seems to narrow it down to using property gets and functions that do not instantiate objects. What makes this tricky is that the entire call graph needs to be evaluated. For example, this is not a valid UDF:

Public Function Foo()  'Invalid
    Bar
    Foo = 42
End Function

Private Sub Bar()
    ActiveSheet.Range("A1").Value = 42
End Sub

...and that is complicated further by custom classes...

'SheetFrobber.cls
Public Sub FrobSheet()
    ActiveSheet.Range("A1").Value = 42
End Sub

'Module1.bas
Public Function Foo()  'Invalid
    Dim Bar As New SheetFrobber
    Bar.FrobSheet
    Foo = 42
End Function

...and unreachable code:

Public Function Foo()  'Always valid
    If False Then
        ActiveSheet.Cells(1, 1) = 42
    End If
    Foo = 42
End Function

---

As far as the quick-fixes go, I don't think the Option Private Module fix (or exclusion for that matter) is a good idea - public functions in standard modules decorated with that option are still available as UDFs in the containing workbook. Excel simply hides them from the cell function IntelliSense. This means that an Option Private Module quick-fix would simply be hiding the same problem from the user as opposed to "fixing" it.