2

I can write the following valid VBA functions:

Public Function C() As Long
  C = 5
End Function

Public Function R() As Long
  R = 6
End Function

But I can't use them in an Excel formula, without qualifying them with the VBA project or module name.

FWIW, Function N and Function T are valid too, but they clash with built-in functions.

I think C and R must be reserved names in Excel formulas, but I don't understand why. Any use of C/R as function calls would be parseable as function calls because they would be immediately followed by a (, whereas any use of C/R as R1C1 notation would not be immediately followed by a (.

Interstingly, I can call the functions if I qualify the function call with the name of the project and/or module:

=VBAProject.C()
5

=Module1.C()
5

=VBAProject.Module1.C()
5
Community
  • 1
  • 1
ThunderFrame
  • 9,352
  • 2
  • 29
  • 60

2 Answers2

6

From the Microsoft documentation:

Tip: You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.

I was aware of this limitation in defining named ranges. Evidently it extends to naming UDFs as well (which makes sense).

John Coleman
  • 51,337
  • 7
  • 54
  • 119
1

In the Windows 1252 codepage, there are several characters that can't be used as the first/only character in a formula call to User Defined Function, but those UDFs can be called when prefixed with the project name or module name, or both:

'Without Project/Module prefix, these characters are 
'reserved for Column/Row shorthand
=VBAProject.C()
=VBAProject.N()

'Without Project/Module prefix, these characters are 
'reserved for built-in function names
=VBAProject.R()
=VBAProject.T()

'Without Project/Module prefix, these characters are 
'special characters
=VBAProject.€()
=VBAProject.ˆ()
=VBAProject.˜()
=VBAProject.Ñ()

Interestingly, if the function Ñ() takes an argument, and the Project/Module name is omitted, then Excel auto-converts the formula to N(arg), which is a completely different function, and Ñ(arg) will not be called.

ThunderFrame
  • 9,352
  • 2
  • 29
  • 60