Is it possible to write a UDF in VBA that contains

Excel 2010 ships with some functions that contain a period in their name. For example STDEV.S and PERCENTILE.EXC

Is it possible to assign a name to my own function with a name such as PERCENTILE.CUSTOM using VBA?

For example, I would like to reference the following function using the formula =NAME.SUFFIX()

Option Explicit

Function NAMEdotSUFFIX() As Variant

    NAMEdotSUFFIX = 42

End Function

回答1:

It is not possible from VBA. This MSDN article shows how to define a function in VBA, and specifically this article lays out the rules for element names.

回答2:

As @SiddharthRout already suggested, external libraries have fewer restrictions than VBA when it comes to declaring function names, so periods are permitted in external names.

Despite the MSDN documentation, it is possible to use some special characters in VBA identifiers, including UDFs. The special characters can even be the first/only character in the identifier. The special characters will vary in their availability, according to the code-page of the VBA project and VBE.

This is a perfectly valid VBA function:

Public Function Foo·Bar()
  Foo·Bar = 5
End Function

And can be used in an Excel formula:

=Foo·Bar()
5

Likewise, this gem of a function uses a non-breaking space as the function name:

Public Function  ()
    = 6
End Function

But while the function with an NBSP is valid, sadly, it can't be used in an Excel formula.

It is possible to use:

Public Function ·()
  · = 5
End Function

BONUS HACK

Somebody forgot to tell the Enum team about the identifier rules. And while Enums can't be used in Excel formulas, you can use any character except a carriage return or square-brackets inside square brackets, as an enum name or member.

This is perfectly valid VBA:

Enum [Foo : Bar = 5 : End Enum]
  [.B!a r"] = 6
End Enum

And the VBE pretty-printer turns it into this unparseable mess:

Enum Foo : Bar = 5 : End Enum
  [.B!a r"] = 6
End Enum