如何将VBA函数“纯VBA”化并将其禁用为UDF

Question

我正在编写一个VBA函数，希望它可以在同一文档中的其他VBA模块中公开使用，但我不希望它作为用户定义函数（UDF）的形式出现。

如果我使用公共访问修饰符，则我的函数也会作为可以从工作簿中的单元格调用的UDF公式提供。我不想让它这样。

是否有访问修饰符或其他方式可以帮助我实现这种“仅限VBA”的行为呢？

此致敬礼

- Skifozoa

---

你能详细介绍一下这个函数的作用吗？这可能有助于提出使用VBA类的建议，这些类仅适用于VBA，并且无法从工作表初始化。至少将预期的输入参数显示为输出值。 - John Alexiou

检查函数内的应用程序调用者类型名称。我还没有测试过，但我认为它应该允许您检查函数是否从单元格中调用。 - Tim Williams

Answer 1

如果你在该函数所在的模块中使用了 Option Private Module ，则该函数可以被声明为 Public 并可以在VBA项目中的任何其他模块中使用，但无法被其他应用程序或项目访问，包括Excel本身。

- YowE3K

Answer 2

如果在Excel中使用此函数，会返回#VALUE错误。

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

- Dick Kusleika

---

"xlErrName" 会更好 :) - Aprillion

谢谢，但我希望存在一种机制，使该函数对电子表格不可见。这样，该函数仍然可见（例如在自动完成中），只是返回一个错误。这并不完全符合我的想法。 - Skifozoa

在Dick的建议上点个赞。根据KFleschner的评论，将其设置为“私有函数”。 - brettdj

我不知道有什么方法可以使它在UI上完全不可见，但仍然可以从其他模块访问。我对XLLs并不是很了解，但它们可能提供这种灵活性 - 可能比它的价值更多的工作。作为最后一招，您可以向我们展示该函数，也许有人可以想到一些聪明的办法。 - Dick Kusleika

Answer 3

有些答案存在一些混淆，因此这里提供一个更全面的解释：

从技术上讲... 如果输入确切的公式，可以调用标准VBA模块中的所有函数。甚至是私有模块内的私有函数的公式也是如此。

例如

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

如果单元格具有=hiddenEverythingExample()，仍将返回一个值。

然而，我认为OP的目的是避免Intellisense在Excel公式栏中填充这些函数。 我最常用的方法是为所有仅限于VBA的函数创建一个特定的模块，并将Option Private Module放在模块定义（函数上方的区域）中。

这样可以确保此模块中的所有函数不会出现在 IntelliSense 中，但仍可与其他模块一起访问。

定义为Private Function也可以实现此目的，但是该函数仅适用于该模块，具体情况可能有所不同。

请注意，YowE3K 推断函数必须同时为Private Function和Option Private Module，但只需其中之一即可消除 IntelliSense。

- pgSystemTester

Answer 4

不要编写一个Function，而是编写一个Sub，并通过ByRef参数设置返回值。这样你的函数将对Excel不可见（除非通过Alt F8或开发者选项卡>宏），并且不会出现在Excel的智能感知中。

替换为：

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

使用

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

注意

1. ByRef 并不是必需的（因为在VBA中默认情况下参数是按引用传递的），但它作为一个有用的提醒，表明 Result 携带了返回值。

2. 您需要对您的代码进行必要的更改，例如：

   z = Add(x,y)

将变成

AddInvisible z,x,y

如下所示：

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

这样做的一个缺点是新代码有点难以理解。

- Philip Swannell

---

@phillp Swannell 如果没有定义 Result，你如何调用 AddInVisible 宏？ 可以展示一个完整的例子吗？ - pgSystemTester

@PGSystemTester 我已经修改了我的答案，以展示一个例子。 - Philip Swannell

我明白了，谢谢你的澄清。这是个聪明的想法。然而，我认为它有点失去了函数的“感觉”，在函数中，你只需要把某些东西放进去...就能得到一个结果。你的方法需要确保相同的变量被正确地作用域化。你还需要在每次运行时将“Result”重置为零。无论如何...很酷的想法。虽然我永远不会真正使用它，但我给了你一个赞。 - pgSystemTester

Answer 5

传递一个参数，只允许函数在给定一个特定的“魔法”值时运行。

例如 - 除非你知道这个“键”的内容，否则将会出现错误#名称!：

Function VBAOnly(key As Long)

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

VBAOnly = True

End Function

- SeanC

Answer 6

使用Private修饰符应该只允许在函数所在的模块中执行。

- KFleschner

---

不是这样的。初步测试表明，“Private”函数可以从Excel中访问。 - Jean-François Corbett