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:
- Your code is not organized properly
- Maintenance of the code becomes takes too much overhead
- 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
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.
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:
- “strmnth” or “str_mnth” to store a string i.e. August for instance
- “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>
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.
- “m” for Private variables and
- “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.
- Fname indicates the firstname
- Lname indicates the lastname
- Dob indicates some date of birth
- Age indicates the age of a person
- 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
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
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
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.
These are in lowercase. They define or indicate the scope of the variable. RVBA guidelines prescribe several prefixes as mentioned below.
- ‘a’ – array object name
- ‘i’ – index
- ‘m’ – Private variables
- ‘s’ – local or Static variables
- ‘g’ – Global or Public variables
- ‘c’ – count of a specific object type
- ‘h’- a handle to any windows object
- ‘r’ – a parameter or argument that is passed by a reference
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”|
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)|
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.
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.
- Min – the absolute first element in any array or a similar object
- First – the first element in an array or a similar object
- Last – the last element in an array or a similar object
- Lim – the upper boundary of an array or similar object
- Max – the absolute last element of an array or a similar object
- Cnt – An item that is used as a counter for an object like an array.
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.
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.