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

Contents

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.noData typeSuggested short codeData type info
1integerI (or) int16-bit (4 bytes) signed integer
2longl (or) lng32-bit (8 bytes) signed integer
3strings (or) stra VB string that can hold around 2 billion UNICODE characters
4numericn (or) numinteger or long data type (4 to 8 bytes)
5currencyc (or) cur64-bit (16 bytes) integer divided by 10000
6variantv (or) vara VB variant – can hold data of any type
7booleanb (or) blna true or false value
8doubledbla double-precision (8 bytes) floating point number
9singleSnga single-precision (4 bytes) floating point number
10floatFlta floating point number of any precision (any no. of bytes)
11objectobj (or) oa generic object variable (late binding)
12controlctl (or) ctrla generic control variable
13decimalDcl16 bytes / 16-bit decimal value
14DictionaryDic or dictA dictionary object like an array to store key and value pairs
15ArrayarrA single / multi dimensional list that can store values. It is a very common object used in all programming languages.
16WorkbookwrkbkAn excel object that represents an excel workbook
17WorksheetwrkshtAn excel object that represents an excel worksheet
18RangerngA sequence of cells in an excel sheet
19shapesshpA 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.noDatatype and purpose of the variables / objectsSuggested variable  or object names
1Global string variable for FirstnamegstrFname (or)  g_str_Fname
2Private integer variable for Agemintage (or) m_int_age
3Boolean variable to represent “finding some value”blnflag (or) bln_flag
4String to represent a car modelstrcarmod (or) str_carmod
5Global string variable to store usernamegstrusername (or) g_str_usr
6Private string variable to store Passwordmstrpwd (or) m_str_pwd
7Long variable to store rate of interest (ROI)lngROI (or) lng_roi
8Date variable to store data of birthdtdob (or) dt_dob
9Array to store the list of weekdaysarrweekdays (or) arr_weekdays
10Dictionary object to store username and passwordsdiccred (or) dic_cred
11Worksheet to list the student detailswrkshtstud (or) wrksht_stud
12Workbook to hold all loan detailswrkbk_loan (or) wrkbkloan
13Shape to represent a noteshpnote (or) shp_note
14Range of cells to define and use for formatting header of a tablerngheader (or) rng_header
15A global variant to hold the salarygvarsal (or) g_var_sal
16Private numeric variable to represent the year of birthmnumyob (or) m_num_yob
17Currency variable to store the bank balancecur_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/NControlDescriptionSuggested naming conventionExample
1Command buttonA button to do some actioncmdcmdSubmit
2ButtonA button to do some actionbtnbtnCancel
3Combo BoxA dropdownlist from which the user can select values. It can be modified to be a’ textbox cum listbox’.cbocboSubjects
4CheckBoxUsed for selecting options. Any number of check boxes can can be selected at a time. This is found in a group.cbcbHobbies
5CheckBoxUsed for selecting options. Any number of check boxes can can be selected at a time. This is found in the form of a list.cblcblSubscriptions
6List BoxSimilar 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.lstlstMonths
7Text BoxAn input control with several flexible properties.txttxtName
8Scroll BarA scrollbar control that helps in navigation.scrscrStudentInfo
9Spin ButtonUsed to increase or decrease numeric value by clicking on up or down arrows that are displayed in the spin buttonspnspnDay
10Option ButtonUsed 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.optoptGender
11LabelUsed to display some text to the user, which cannot be changed during runtime (static text). Eg: Labels and instructionslbllblStuName
12ImageA control that can display imagesimgimgPhoto
13Toggle ButtonButton that has on and off positionstgltglMode
14UserFormA user form that contains all other controlsfrmfrmLogin
15FrameCan help in grouping the other controlsfrafrmAddress
16TabstripHelps create tabs depending on your requirementstbstbsTeachers
17MultipageMultipage control groups other controls into multiple categories and helps to display several controls in a small areamupmupEmployee

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.noCategories of TagsExamples
1VariablesDim intSum As Integer, lngHeight As Long, curSalary As Currency
2PropertiesDim frmsStudentdata As Collection
3CollectionsDim intsMarks As Collection
4ConstantsDim lngcPi As Long ‘ a constant value Pi= 3.14
5Menu itemsmnuView, mnuInsert
6Data types: Enumerated typesPublic Const estcErr104 As String = “Invalid data type”
Public Const estcErr105 As String = “Subscript out of range”
Dim estAddress as String
7Data types:Classes and User-defined data typesPublic Type salPayroll
Dim salConveyance as salPayroll
8PolymorphismName the base class as “vehCar”
‘Declare
Dim vehBenz as vehCar'(derived class of vehCar)
Dim vehHyundai as vehCar'(derived class of vehCar)
9Procedure namesBuilt-in and User-defined:
txtPwd_OnChange, cmdSave_Click, cmdCancel_DblClick
PrintReport, ExtractData, DisplayStatus
10ParametersPublic Sub FindAge(ByVal dateInput As Date , ByRef rintAge As Integer)
11LabelsDateOfBirth, 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.

One thought on “VBA Naming Conventions: Best Practices”

Leave a Reply

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