CODEWITHSUNDEEP
excelvbaexcel-2007user-defined-functions
Skifozoa
Skifozoa

Reputation: 303

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

I'm writing a VBA function which I want to be publically available in other VBA modules within the same document, however I don't want it to be available as a UDF (User defined function).

If I use the public access modifier however my function is also be available as a UDF-formula that can be called from the cells within the workbook. I don't want this.

Is there an access modifier or other way that can help me obtain this "VBA only" behaviour?

Kind regards

Upvotes: 8

Views: 4452

Answers (6)

Philip Swannell
Philip Swannell

Reputation: 935

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 + Num2
End Function

use

Sub AddInvisible(ByRef Result As Double, Num1 As Double, Num2 As Double)
    Result = Num1 + Num2
End 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 3

End Sub

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

Upvotes: 1

pgSystemTester
pgSystemTester

Reputation: 9932

There's a little bit of confusion among answers, so here's a more encompassing explanation:

Technically... all functions in the standard VBA Modules can be called if the exact formula is entered. Even Formulas in Private Functions within Private Modules

Example

Option Private Module
Private Function hiddenEverythingExample() As String
    hiddenEverythingExample= "NOPE!!!"
End Function

Will still return a value if a cell has =hiddenEverythingExample()

enter image description here

However, I believe the objective of the OP was to avoid having the itellisense populate these functions in the Excel Formula bar.

The most frequent way I accomplish this is create a specific Module for all VBA-Only functions and put Option Private Module in the module definition (area above the functions).

enter image description here

This ensures all functions in this module will NOT appear in itellisense, but still be accessible to other modules along with itellisense.

Defining asPrivate Function also accomplishes this, but then the function is scoped exclusively to that Module, which may or may not be the requirement.

Note that YowE3K infers that a function must be both Private Function AND Option Private Module, but only one OR the other is necessary to eliminate the itellisense.

Upvotes: 2

YowE3K
YowE3K

Reputation: 23994

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.

Upvotes: 10

KFleschner
KFleschner

Reputation: 499

Using the Private modifier should only allow the execution in the module the function exists in.

Upvotes: -1

Dick Kusleika
Dick Kusleika

Reputation: 33165

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 If

End Function

Upvotes: 5

SeanC
SeanC

Reputation: 15923

pass a parameter that only allows the function to run if a "magic* value is given to it.

Example - This will give the error #NAME! unless you know what the key is:

Function VBAOnly(key As Long)

If key <> 12345 Then
    VBAOnly = CVErr(xlErrName)
    Exit Function
End If

VBAOnly = True

End Function

Upvotes: -1

Related Questions