Basic practices for easy readable VBA code

-

1      Use option explicit

This will force you to declare variables.
Option explicit can be typed in at the top of your module before any code or it can be automatically placed there by selecting the following items from the menu:
àTools Options and then select “require variable declaration”   
2      Declare the variables as you use them

If you declare the variables at the top of the code it becomes very messy

3      Indent your code and use blank lines

Shift + Tab align all the code up

Tab can then be used to indent the code line by line where appropriate                between loops and other programming statements.

4      Give variables nice names
Use long descriptive names. You don’t have to use e.g. ‘lng’ in front if it is a long variable. Control + Space will help to complete the variable name when typing it (in case you worry that the writing the names will slow down programming) and then name of the variable will give an indication of the variable type.
Always use i and j when it comes to loops.
5      Use magic numbers
These are numbers that appear in our code that are not 0 or 1.
They have some magic number that we don’t really know what it will represent.
These numbers should be represented by a constant / Enum. Look at the code below which is difficult to read:
                result = rg.Cells(row + 6, ( 1 * 3) + 4).value
                Bonus – result * 4
When we use PasteSpecial we have different options like PasteAll, PasteComments ect. All these are basically a text representation of a number.

I we look at Enums (short for Enumerator) as in the code below:

Enum DataColumns
                dcAmount = 1
                dcCheck = 2
                dcResult = 3
                dcBonus = 4
End Enum

Each column is represented by an Enum and we can use the Enum name instead of a number in our code. This make changes easy when there are changes in columns.

We can further do the following to the Enum to use it even more to our benefit:

Enum DataColumns
                dcAmount = 1
                dcCheck = dcAmount + 1
                dcResult = dcCheck + 1
                dcBonus = dcResult + 1
End Enum

6      Use variables instead of ranges
This is unique to Excel VBA but the most important technique
The code below flow from the previous example and look how easy it is to read:
Sub ReadDataNew()
                Dim rg as Range
                Dim rg = shData.Range(“A1”).CurrentRegion
                Dim amount as Long, check as Long, result as Long, bonus as Long
                Dim i as long
                For i = 1 to rg.Rows.Count
                                amount = rg.Cells(i,dcAmount).value
                                check = rg.Cells(i,dcCheck).value
                                result = rg.Cells(i,dcResult).value
                                bonus = rg.Cells(i,dcBonus).value
                                If amount = check Then
                                                result = result + bonus
                                ElseIf amount < check Then
                                                Result = result – bonus
                                EndIf
                Next i
7      Do not repeat code
This can be easily achieved by using function and sub procedures.The advantage is that if there are changes to code it only needs to be changed at one place and not all over you program.

Comments

Post a Comment

Popular posts from this blog

VBA Developer - Digitally sign your macro project

-
Image
Digitally sign your macro project This article describes how you can digitally sign a macro project by using a certificate. If you don't already have a digital certificate, you must obtain one. To test macro projects on your own computer, you can create your own self-signing certificate by using the Selfcert.exe tool. Obtain a digital certificate for signing You can obtain a digital certificate from a commercial certificate authority (CA) or from your internal security administrator or information technology (IT) professional. To learn more about certificate authorities that offer services for Microsoft products, see the list of  Microsoft root certificate program members . Your macro projects and Microsoft Office Because a digital certificate that you create isn't issued by a formal trusted certificate authority, macro projects that are signed by using such a certificate are known as  self-signed projects . Microsoft Office trusts a self-s...
Read more

Distribute your VBA project to other Microsoft Users

-
We often spend a lot of time to write VBA code to assist us in completing tasks in Microsoft Applications whether an Excel spreadsheet, Access database, Outlook Message, Word Document. This code can be used by other users on the same scenario. We can make our code available to other users. This code can be protected to ensure integrity. The procedure below describe how to create distributed software for an Excel application. The process for other Microsoft applications will be similar. 1. Once you have completed, debugged and tested your program and are happy that it works, protect      your code as follows: Right click on your module or project name and select VBAProject Properties. Assume your sub name is "ProtectedCode" In the General Tab type in the name of your project e.g. "CodeForDistribution". In the Protection Tab select Lock project for viewing  and enter a password for your project. Click on OK. 2. Save your project but select the file type...
Read more

How far can you stretch the Excel LET function?

-
Image
 Excel LET Function Definition and background and Syntax The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function. Similar to variables in programming, LET is accomplished through Excel’s native formula syntax. To use the LET function in Excel, you define pairs of names and associated values, and a calculation that uses them all. You must define at least one name/value pair (a variable), and LET supports up to 126. =LET(name1_of_variable1, name_value1, calculation_or_name2_of_variable2,[name_value2_of_variable2, calculation_or_name3_of_variable3  . . .  ]) The LET function can take up to 132 variables. The LET function with more than one variable can look like this: =LET(name of variable1, name_value1, name of variable2, name_value2, calculation) Argument Description name1 Required The first name to assign. Must start wi...
Read more