6

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
Community
  • 1
  • 1
JustinJDavies
  • 2,663
  • 4
  • 30
  • 52
  • 6
    Yes (sort of) - you can create a module named "name" and your function "suffix" would be contained in that module. In the sheet you can then use `=Name.Suffix()` – Tim Williams Apr 16 '13 at 16:02
  • 1
    In VBA, you cannot have a UDF name with a DOT like in `STDEV.S` Having said that you can however create a VSTO solution where you can give that kind of name in ClickOnce installer. – Siddharth Rout Apr 16 '13 at 16:07
  • 1
    Tim, could you put your answer as a 'proper' answer so that I can accept it. Thanks – JustinJDavies Apr 18 '13 at 18:45

2 Answers2

6

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
ThunderFrame
  • 9,352
  • 2
  • 29
  • 60
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.

Rai
  • 1,328
  • 11
  • 20
  • 1
    I'm accepting this answer as technically correct, according to the question. However please note Tim and Sid's comments, which offer practical solutions to work around this limitation – JustinJDavies Jun 02 '14 at 08:22