VBA Naming Conventions: Best Practices

In any programming language it is quite natural for you to use several variables and objects to develop code. Throughout this process, you might get confused referencing them for the first time or reusing them for the second time. For example, you might create a variable to store an integer and somewhere in the middle of the code, you might accidentally store a string in that variable.

When variables are not used appropriately, there’s a good chance you could run into one of these issues:

  1. Your code is not organized properly
  2. Maintenance of the code becomes takes too much overhead
  3. If maintenance needs to be done by another developer, it takes a very long time for the user to understand the code and maintain it

Solution

In order to overcome the problems above and keep your code neat and organized, it is wise to use standard naming conventions. These will help us define the variables and objects with names that contain a hint, usually 3 to 4 letters, stating their purpose or usage. As we develop our code, these naming formats will prevent the misuse of these variables in a way that deviates from their purpose.

For example:

If we define a variable to store the month , we can name the variable “mnth “. In the middle of the code there are chances that you could use this variable to store the month as a string in one place and an integer equivalent of a month in another place. This might later lead to confusion and maintenance overhead.

In this case, the variable can be defined with the the names below to avoid confusion:

  1. “strmnth" or “str_mnth" to store a string i.e. August for instance
  2. “intmnth" or “int_mnth" to store the month as a number i.e. 8 (for August)

VBA Naming Conventions

In VBA we have several datatypes which don’t need to be defined before use. For example, there is a common datatype named “variant" which can be used to store data of any type.

As I’ve been saying, there are predefined standard naming conventions that can be used for each such datatype.

The standard format to define a variable is :

[<Scope of the variable>] + <type of the variable> + < name of the variable>

Explanation

Scope of the Variable:

This defines the accessibility/visibility of a variable within/outside the framework/project/code modules. In general, scope is classified as either “Private" or “Public". “Public" variables are also called “Global" variables.

The value should be denoted by a single letter as below.

  1. “m" for Private variables and
  2. “g" for Public/Global variables

However, the square brackets indicate that this is optional.

Types of variables:

This refers to the data type of the variable. The suggested list of short codes that can be used for each of the datatypes available in VBA are listed below.

S.no Data type Suggested short code Data type info
1 integer I (or) int 16-bit (4 bytes) signed integer
2 long l (or) lng 32-bit (8 bytes) signed integer
3 string s (or) str a VB string that can hold around 2 billion UNICODE characters
4 numeric n (or) num integer or long data type (4 to 8 bytes)
5 currency c (or) cur 64-bit (16 bytes) integer divided by 10000
6 variant v (or) var a VB variant – can hold data of any type
7 boolean b (or) bln a true or false value
8 double dbl a double-precision (8 bytes) floating point number
9 single Sng a single-precision (4 bytes) floating point number
10 float Flt a floating point number of any precision (any no. of bytes)
11 object obj (or) o a generic object variable (late binding)
12 control ctl (or) ctrl a generic control variable
13 decimal Dcl 16 bytes / 16-bit decimal value
14 Dictionary Dic or dict A dictionary object like an array to store key and value pairs
15 Array arr A single / multi dimensional list that can store values. It is a very common object used in all programming languages.
16 Workbook wrkbk An excel object that represents an excel workbook
17 Worksheet wrksht An excel object that represents an excel worksheet
18 Range rng A sequence of cells in an excel sheet
19 shapes shp A shape object of Ms. Office

Name of the variable:

The name of the variable is a user-defined name that explains the purpose of the variable.

For example:

  1. Fname indicates the firstname
  2. Lname indicates the lastname
  3. Dob indicates some date of birth
  4. Age indicates the age of a person
  5. Fath_age can indicate the father’s age

Examples for naming variables or objects in VBA

S.no Datatype and purpose of the variables / objects Suggested variable or object names
1 Global string variable for Firstname gstrFname (or)  g_str_Fname
2 Private integer variable for Age mintage (or) m_int_age
3 Boolean variable to represent “finding some value” blnflag (or) bln_flag
4 String to represent a car model strcarmod (or) str_carmod
5 Global string variable to store username gstrusername (or) g_str_usr
6 Private string variable to store Password mstrpwd (or) m_str_pwd
7 Long variable to store rate of interest (ROI) lngROI (or) lng_roi
8 Date variable to store data of birth dtdob (or) dt_dob
9 Array to store the list of weekdays arrweekdays (or) arr_weekdays
10 Dictionary object to store username and passwords diccred (or) dic_cred
11 Worksheet to list the student details wrkshtstud (or) wrksht_stud
12 Workbook to hold all loan details wrkbk_loan (or) wrkbkloan
13 Shape to represent a note shpnote (or) shp_note
14 Range of cells to define and use for formatting header of a table rngheader (or) rng_header
15 A global variant to hold the salary gvarsal (or) g_var_sal
16 Private numeric variable to represent the year of birth mnumyob (or) m_num_yob
17 Currency variable to store the bank balance cur_bbal (or) curbbal

The code to declare these in the same order as above is available in the below code snippet.

Sub sample_coding()
Dim gstrFname, g_str_Fname As String
Dim mintage, m_int_age As Integer
Dim blnflag, bln_flag As Boolean
Dim strcarmod, str_carmod As String
Dim gstrusername, g_str_usr As String
Dim mstrpwd, m_str_pwd As String
Dim lngROI, lng_roi As Long
Dim dtdob, dt_dob As Date
Dim arrweekdays, arr_weekdays ' as array - late binding
Dim diccred, dic_cred As Dictionary
Dim wrkshtstud, wrksht_stud As Excel.Worksheet
Dim wrkbk_loan, wrkbkloan As Excel.Workbook
Dim shpnote, shp_note As Shape
Dim rngheader, rng_header As Range
Dim gvarsal, g_var_sal As Variant
Dim mnumyob, m_num_yob As numeric
Dim cur_bbal, curbbal As Currency
End Sub
Variable declarations using naming conventions

Naming form controls

A user form in any VBA project can have numerous controls that are automatically named sequentially.

For example, if we add 5 textboxes in a user form, they automatically have the names textbox1, textbox2, textbox3, textbox4 and textbox5. In this case, it’s difficult to keep in mind the purpose of each textbox.

Also, a user form generally has a label and an input control for every field of data stored in its backend database.

For instance, on a login user form , we can find a pair of controls (a label and a textbox) for Username. Here using naming conventions, we cannot name both controls str_username. It would be too confusing. Hence, we have to name the label lblusername and the textbox txtusername.

From this explanation, it probably is clear that VBA form controls also have naming conventions which need to be put to practice. Below is a list of form controls in VBA available with a short explanation. The naming conventions suggested for them are provided along with some examples .

S/N Control Description Suggested naming convention Example
1 Command button A button to do some action cmd cmdSubmit
2 Button A button to do some action btn btnCancel
3 Combo Box A dropdownlist from which the user can select values. It can be modified to be a’ textbox cum listbox’. cbo cboSubjects
4 CheckBox Used for selecting options. Any number of check boxes can can be selected at a time. This is found in a group. cb cbHobbies
5 CheckBox Used for selecting options. Any number of check boxes can can be selected at a time. This is found in the form of a list. cbl cblSubscriptions
6 List Box Similar to a combobox with the difference that multiple options can be selected. Depending on the length of the list and the display size, the control might ave scrollbars within. lst lstMonths
7 Text Box An input control with several flexible properties. txt txtName
8 Scroll Bar A scrollbar control that helps in navigation. scr scrStudentInfo
9 Spin Button Used to increase or decrease numeric value by clicking on up or down arrows that are displayed in the spin button spn spnDay
10 Option Button Used for true or false values. Only one option button in a group can have a true value at a time. On selecting one option, the other options automatically become deselected or false. opt optGender
11 Label Used to display some text to the user, which cannot be changed during runtime (static text). Eg: Labels and instructions lbl lblStuName
12 Image A control that can display images img imgPhoto
13 Toggle Button Button that has on and off positions tgl tglMode
14 UserForm A user form that contains all other controls frm frmLogin
15 Frame Can help in grouping the other controls fra frmAddress
16 Tabstrip Helps create tabs depending on your requirements tbs tbsTeachers
17 Multipage Multipage control groups other controls into multiple categories and helps to display several controls in a small area mup mupEmployee

Introduction to the Reddick VBA Naming Conventions

Reddick VBA, or RVBA, naming conventions are guidelines invented by several authors to help VBA developers be on the same page by using standard naming conventions.

These are named after and edited by Greg Reddick. The conventions are specially designed for the VBA language.

The suggested syntax for an object name is

[<prefixes>]<tag>[<BaseName>[<Suffixes>]]

In general, square brackets are used to indicate that the part of the syntax is optional. On observing these square brackets in the syntax closely, one can understand that a suffix is optional within the BaseName.

Syntax Explanation:

Prefixes:

These are in lowercase. They define or indicate the scope of the variable. RVBA guidelines prescribe several prefixes as mentioned below.

  1. ‘a’ – array object name
  2. ‘i’ – index
  3. ‘m’ – Private variables
  4. ‘s’ – local or Static variables
  5. ‘g’ – Global or Public variables
  6. ‘c’ – count of a specific object type
  7. ‘h’- a handle to any windows object
  8. ‘r’ – a parameter or argument that is passed by a reference

For Example:

  1. astrBookTitles
  2. hWndLogin
  3. iastrHobbies

Tag:

This represents the datatype of the variable. It should be completely in lowercase. There are a wide range of standard naming tags that can be used. Below are the categories into which the tags are classified along with examples for each.

S.no Categories of Tags Examples
1 Variables Dim intSum As Integer, lngHeight As Long, curSalary As Currency
2 Properties Dim frmsStudentdata As Collection
3 Collections Dim intsMarks As Collection
4 Constants Dim lngcPi As Long ‘ a constant value Pi= 3.14
5 Menu items mnuView, mnuInsert
6 Data types: Enumerated types Public Const estcErr104 As String = “Invalid data type”
Public Const estcErr105 As String = “Subscript out of range”
Dim estAddress as String
7 Data types:Classes and User-defined data types Public Type salPayroll
Dim salConveyance as salPayroll
8 Polymorphism Name the base class as “vehCar”
‘Declare
Dim vehBenz as vehCar'(derived class of vehCar)
Dim vehHyundai as vehCar'(derived class of vehCar)
9 Procedure names Built-in and User-defined:
txtPwd_OnChange, cmdSave_Click, cmdCancel_DblClick
PrintReport, ExtractData, DisplayStatus
10 Parameters Public Sub FindAge(ByVal dateInput As Date , ByRef rintAge As Integer)
11 Labels DateOfBirth, RateOfInterest

BaseName:

These are word(s) that give information about what the variable represents. Uppercase needs to be used for the first letter of every word in the BaseName.

For Example:

  1. DataOfBirth
  2. DateOfJoining
  3. PenName

Suffixes:

Provide more information about the meaning or purpose of the BaseName. Uppercase needs to be used for the first letter of every word as in the BaseName. There is a wide range of standardized suffixes to use from. However, you are free to define your own suffix for any variable. RBVA provides some commonly used suffixes as below.

  1. Min – the absolute first element in any array or a similar object
  2. First – the first element in an array or a similar object
  3. Last – the last element in an array or a similar object
  4. Lim – the upper boundary of an array or similar object
  5. Max – the absolute last element of an array or a similar object
  6. Cnt – An item that is used as a counter for an object like an array.

For Example:

  1. iaintMarksMin
  2. iaintMarksMax
  3. iaintAverageCnt

While we follow the general naming conventions that are easy to remember and use, the RVBA naming guidelines can help in developing quality code. RVBA also provides a variety of naming conventions for objects used in Ms. Access, DAO,VB, ActiveX Data objects, ADO extension for DDL and Security (ADOX), JET & replication objects, common control and other custom control objects.

Personally, I would choose to follow the normal naming conventions (can also be user-defined) for a short term or small project to save yourself time. Maintenance would be limited too.

However, if you’re well versed in RBVA guidelines, then these cab be followed without a second thought. But when it comes to a permanent project that is going to be used in the long run, for example, a banking utility project, it would be a great option to follow the RVBA guidelines as there are allowances for the code to move from one developer to another for regular maintenance and enhancements.

Conclusion

Naming conventions make our life easier when it is time to maintain our code or perform enhancements. They help us to organize a bunch of code in proper categories and help us understand the purpose and type of the objects.

Leave a Reply

Your email address will not be published. Required fields are marked *