How to make VBA function "VBA only" and disable it as UDF

If you use Option Private Module in the module in which the function appears, the function can be declared as Public and used in any of your other modules within your VBA project, but won't be accessible by other applications or projects, including Excel itself.

This will return a #VALUE error if used in Excel.

Function VBAOnly() As Variant    If TypeName(Application.Caller) <> "Range" Then        VBAOnly = 1 'or some other return value    Else        VBAOnly = CVErr(xlErrValue)    End IfEnd Function

Instead of writing a Function, write a Sub, and set the return via a ByRef argument. This way your function will be invisible to Excel (except via Alt F8, or Developer tab > Macros) and won't appear in Excel's intellisense.

Instead of

Function Add(Num1 As Double, Num2 As Double)    Add = Num1 + Num2End Function

use

Sub AddInvisible(ByRef Result As Double, Num1 As Double, Num2 As Double)    Result = Num1 + Num2End Sub

Note

1. That ByRef is not strictly necessary (since arguments are by default by reference in VBA) but it serves as a useful reminder that Result carries the return value.

2. You will need to make the necessary changes to your code, for example:

   z = Add(x,y)

would become

AddInvisible z,x,y

as demonstrated below:

Sub DemoAddInvisible()    Dim Num1 As Double    Dim Num2 As Double    Dim Result As Double 'Result initialises to 0    Num1 = 1    Num2 = 2    AddInvisible Result, Num1, Num2    MsgBox Result ' See that Result has become 3End Sub

A downside of all this is that the new code is somewhat harder to understand.