Excel Snippets

Naming Conventions for VBA Subroutines and Functions

Every VBA subroutine and function must have a name, but there are certain rules that must be adhered to and conventions that are advisable for various reasons. Below is a list of these rules:

  1. VBA is not case sensitive when it comes to the names of routines (subroutines or functions). So two subroutines named "Main" and "main" will look like two subroutines with the same name to VBA.
    2.  
  2. Names must be unique within the same module or none of your VBA routines will run, even those not involved in the duplicate naming.
    3.  
  3. Although VBA allows the same name to be used in different modules, it is inadvisable to do so because it can create confusion to the user (as well as the developer).
    4.  
  4. It is good practice to give a name to a routine that is descriptive of its functionality.
    5.  
  5. Names cannot be longer than 255 characters.
    6.  
  6. Names cannot have spaces in them.
    7.  
  7. Names can consist of letters, numbers and certain special characters: _, ?, {, }, [, and ].
    8.  
  8. Names must begin with a letter.
    9.  
  9. Names cannot contain most special characters (other than those listed above). If you use a character that is not allowed, VBA will issue an error message when you try to run the routine (and the code will not execute).
    10.  
  10. Avoid using names that VBA uses as part of its language, as it complicates coding. For example, there is a VBA operator (function) called Right. If you name a subroutine Right and wish to use that VBA operator as part of the code, you will have to use it by prefixing VBA, i.e. VBA.Right.

Conflicting names

Sub Right()
Dim X, Y as string

X = “This is a test”

Y = Right(X,4)

Msgbox Y

End Sub

 

Sub Main()
Dim X, Y as string

X = “This is a test”

Y = Right(X,4)

Msgbox Y

End Sub

Create the first subroutine above and try to run it. An error message pops up and the run aborts.

Next create a second subroutine identical to the first but give it the name Main. Click on run. You will get the same error message.

How to use a VBA built-in function name for a subroutine

Sub Right()
Dim X, Y as string

X = “This is a test”

Y = VBA.Right(X,4)

Msgbox Y

End Sub

Modify the first subroutine by adding the VBA prefix to Right.

When you run this code you get a message box with “test” in it, since the Right function returns the specified number of characters on the right end of the string, but you will still not be able to execute the routine named Main unless you also prefix Right with VBA. This is because its use for one routine affects all subroutines and functions in the project.

See how complicated it can get? Because of the inconvenience this practice creates and the questionable necessity of engaging in it, you should avoid using built-in function names for anything other than calling that function.