To ‘Err’ is Human

EDIT

I have stopped updating this blog. This link can also be found in my Website.

http://www.siddharthrout.com/2011/08/01/to-err-is-human/

 

I am yet to see a programmer who can write flawless code and when I say flawless code I don’t mean just

Sub Sample()
    MsgBox "Hello World!"
End Sub

A good code can take you places and when I mean places, I not only mean geographical regions but also various corners of “The Web World”.

I met a person once while answering questions in one of the forum and she casually mentioned one thing which has stuck with me for good. Not that I didn’t know that fact but I never knew there was a phrase for it. It is

“You are never too old to learn and you are never too young too teach”

I was always under the impression that I wrote good code but after listening to that comment, I seriously gave it a thought and was flooded by questions from within

“Do I actually write good code? Is there nothing else to learn?”

“Is there no way an error can happen in my code? And if it happens how will my code react?”

“Have I covered every possible scenario that my code will come across?”

“Can I recognize my code even if I look at it after 15 years?”

and many more…

To my surprise, I had very vague answers. Surprised? Even I was!

Writing “Good Code” is not rocket science! It is similar to cooking your favorite meal or painting if I may say so. These are some of the few things that you need to take care while coding.

1. Abracadabra!  – Think before you code
2. Big Brother is watching you! – Option Explicit
3. Private Ryan! – Declare Variables with meaningful names
4. Mirror, mirror upon the wall… – Indent your code
5. We all are getting Old – Comment your code
6. Don’t write a novel – Write precise code
7. Et tu, Brute? – Proactive Programming
8. To be, or not to be- Late Binding vs Early Binding
9. Avoid List– Things to avoid
10. Dry Runs – Test before you deploy

Let’s visit them one by one

Abracadabra!  - Think before you code

Let’s face it! We are not Harry Potter…

We are more like architects.

Before constructing a building, an architect has to draw a plan of the building which is also referred to as Blueprint. Based on the blueprint, the building is then built meticulously. Architects have to think of many things before they draw up a plan for a building. First they have to think about what the building will be used for. How many people are going to use the building at the same time? What types of activities will these people do in the building? etc etc.

Same is the approach that we need to take when we write code or build an application. The idea of writing a code or developing an application always excites me. My favorite way to create a “blueprint” is to take a notepad and jot everything that comes to my  mind. I have noticed in the past that the first thing that happens to me when I pick up a notepad is that I get swarmed by ideas and thoughts. However that doesn’t bother me. I ensure that I jot all of them down. Once I feel composed and more focused, I revisit my notes. This is the moment when I usually have a faint idea on how my “building” looks like. For example, let’s assume that we need to develop an application for ourselves. The mere fact that we are developing something for ourselves can be very exciting. Let’s say, we need to develop an application for “Personal DVD Management”. Once you do the exercise that I mentioned above, your notepad might look something like this.

Based on the application that you are developing or the code that you are writing, you might have to revisit the notepad many times. During these many ‘times’ you will edit your notes or you might strike out some of them. As the time goes by you will notice that your thoughts about the “building” are more structured. The faint image which you had earlier about your “building” becomes more and more prominent. Once you finally see the complete and clear image of your “building”, you are ready to construct your building… I mean write code

Big Brother is watching you! – Option Explicit

Ok so now we are ready to code! Yeah!

The very first thing that we need to do is declare “Option Explicit” on the top of the code. For example

Option Explicit

Sub Sample()

End Sub

Now why is this important?

There are two main reasons for using Option Explicit.

1) It forces you to declare your variables as a specific data type.

VB defaults the variable to being type Variant. A Variant type variable can hold any kind of data from strings, to integers, to long integers, to dates, to currency etc. By default “Variants” are the “slowest” type of variables. If you want to store a number with decimals then you might want to declare it as a double or currency. For example

Dim MyHeight as Double
Dim MoneySpent as Currency

2) It keeps a watch on your code checking for spelling mistake that might happen when you type your variable. Consider this example

In this example we will try to calculate Mass of an object.

Sub CalculateMass()
    Dim Mass As Double, Density As Double, Volume As Double

    Density = 3.5
    Volume = 4.2

    Mass = Density * Volume

    MsgBox "The mass is " & mas
End Sub

You will notice that we don’t get the correct result and we don’t get any error. What could be the reason?

The result: We get a message box which says “The mass is” and there is no value after that. Why? The reason is simple enough. You are using a variable which is “mass” but when displaying the message you made a spelling mistake (“mas”)

Now let’s try the same code with “option Explicit”

Option Explicit

Sub CalculateMass()
    Dim Mass As Double, Density As Double, Volume As Double

    Density = 3.5
    Volume = 4

    Mass = Density * Volume

    MsgBox "The mass is " & mas
End Sub

Now if we run it, Option Explicit will ensure than an error will generate to make you aware of what went wrong. See the below image.

Private Ryan! – Declare Variables with meaningful names

Variables are ‘containers’ which hold values that are used in your code. It is essential that you give them appropriate names and declare them correctly.

Consider this example

Option Explicit

Sub Sample()
    Dim M, D, V

    D = 3.5
    V = 4

    M = D * V

    MsgBox M
End Sub

By simply looking at it, can you make out what the code does? I am sure you can’t. There are 3 things that I would like to draw attention to

1) Give your procedure a meaningful name for example

Option Explicit

Sub CalculateMass()

End Sub

This ensures that if you are skimming through your code looking for something in particular, just by looking at it’s name you can decide whether you want to inspect the entire code in that procedure or move on to next.

2) Similarly give your variables a meaningful name. This will ensure that you don’t end up confusing yourself later whether “D” should be multiplied by “V” or should it be multiplied by “M” as you wouldn’t know which is which. Naming them as below definitely makes your life easier as you know what that variable is supposed to do.

Dim Mass , Density , Volume

3) And lastly, declaring the types of variables.

As I mentioned earlier, If you do not specify the type of the variable, VB defaults the variable to being type Variant. And you wouldn’t want that as it would slow down your code as the VB Compiler takes time to decide on what kind of variable you are using. Variants should also be avoided as they are responsible for causing  possible “Type Mismatch Errors”. It’s not that we should never use Variants. They should only be used if you are unsure what they might hold on code execution.

Here is an interesting read on Variable Types

http://msdn.microsoft.com/en-us/library/aa261347%28v=vs.60%29.aspx

This might differ from VBA to VBScript to Vb6 to VB.net

Mirror, mirror upon the wall… – Indent your code

Why is indenting your code important. Before I explain it, consider this example

Sub Sample()
Dim sFolder As String, sFileSpec As String
Dim sFiles() As String, sCurFile As String
Dim lCt As Long
sFolder = ActiveWorkbook.Path
sFileSpec = "*.xl*"
ReDim sFiles(1 To 1)
lCt = 1
sFiles(1) = Dir(sFolder & "\" & sFileSpec)
Do
sCurFile = Dir()
If Len(sCurFile) > 0 Then
lCt = lCt + 1
ReDim Preserve sFiles(1 To lCt)
sFiles(lCt) = sCurFile
End If
Loop Until Len(sCurFile) = 0
MsgBox UBound(sFiles) & " files found."
End Sub

By looking at the above code, you will realize it is not clearly readable. Indenting basically helps YOU

1) In making the code readable.

Try digging up an old code that you wrote which is not indented. You will notice that you will have a tough time following the logic of the code.

2) In inspecting separate chunks of code. This is important if you are tying to find errors.

The most common error that you might face is missing level(s) of nesting or loops. For example, if I take the above code and delete a line can you tell me which line am I missing?

Sub Sample()
Dim sFolder As String, sFileSpec As String
Dim sFiles() As String, sCurFile As String
Dim lCt As Long
sFolder = ActiveWorkbook.Path
sFileSpec = "*.xl*"
ReDim sFiles(1 To 1)
lCt = 1
sFiles(1) = Dir(sFolder & "\" & sFileSpec)
Do
sCurFile = Dir()
If Len(sCurFile) > 0 Then
lCt = lCt + 1
ReDim Preserve sFiles(1 To lCt)
sFiles(lCt) = sCurFile
Loop Until Len(sCurFile) = 0
MsgBox UBound(sFiles) & " files found."
End Sub

3) In easily maintain the code

Coding is based on logic and logic can change. The best way to check this is to pick up a code which you wrote several years back. I am sure that you will realize that the same code could have been written in several ways. If your code is indented then it is easier to update the relevant section(s) of the code.

Indentation by default is not a requirement of most programming languages. But it is wise to indent your code Here is how the above code will look after indenting.

Sub Sample()
    Dim sFolder As String, sFileSpec As String
    Dim sFiles() As String, sCurFile As String
    Dim lCt As Long

    sFolder = ActiveWorkbook.Path
    sFileSpec = "*.xl*"

    ReDim sFiles(1 To 1)

    lCt = 1

    sFiles(1) = Dir(sFolder & "\" & sFileSpec)

    Do
        sCurFile = Dir()

        If Len(sCurFile) > 0 Then
            lCt = lCt + 1
            ReDim Preserve sFiles(1 To lCt)
            sFiles(lCt) = sCurFile
        End If
    Loop Until Len(sCurFile) = 0

    MsgBox UBound(sFiles) & " files found."
End Sub

So um, Mirror, mirror upon the wall…

We all are getting Old – Comment your code

Another bitter truth. We are growing old. After all we are just humans

If I dig up a code that I wrote few days back, I would have no problems in following the logic of the code if the code is indented. If I inspect a code (indented) which I wrote couple of years back, I will take sometime to understand the logic. Now see this piece of code.

Option Explicit

'~~> This procedure calculates the mass
Sub CalculateMass()
    '~~> Define variables here
    Dim Mass As Double, Density As Double, Volume As Double

    '~~> Assign value to density here
    Density = 3.5

    '~~> Assign value to Volume here
    Volume = 4.2

    '~~> This calculates the mass
    Mass = Density * Volume

    '~~> This displays the mass
    MsgBox "The mass is " & Mass
End Sub

How much time do you think you took to understand what this code does?

Inserting comments, like Indentation is not a requirement. But yes, I can tell you from experience that in the future you wouldn’t want to spend half a day trying to figure out what some function or procedure did and why you used it in a certain way

Don’t write a novel – Write precise code

The longer your code is the longer the code will take time to execute and not to mention the time you would take to understand what it does. What can be written in few lines shouldn’t take 50 lines.

You can write precise code by

1) Avoiding duplicate code segments. There are scenarios where you have to use same lines of code again and again. For example see these two codes which basically do the same thing in Excel 2003.

Let’s say we have an Excel spreadsheet which has different values from Row 1 to Row 5 for Density in Column A, Volume in Column B. And we need to populate the Mass in Column C

CODE 1 (Normal Code)

Option Explicit

'~~> Procedure to calculate the mass
Sub CalculateMass()
    Dim Mass As Double, Density As Double, Volume As Double

    '~~> Get values from A1 and B1 and update mass in C1
    Density = Sheets("Sheet1").Range("A1").Value
    Volume = Sheets("Sheet1").Range("B1").Value
    Mass = Density * Volume
    Sheets("Sheet1").Range("C1").Value = Mass

    '~~> Get values from A2 and B2 and update mass in C2
    Density = Sheets("Sheet1").Range("A2").Value
    Volume = Sheets("Sheet1").Range("B2").Value
    Mass = Density * Volume
    Sheets("Sheet1").Range("C2").Value = Mass

    '~~> Get values from A3 and B3 and update mass in C3
    Density = Sheets("Sheet1").Range("A3").Value
    Volume = Sheets("Sheet1").Range("B3").Value
    Mass = Density * Volume
    Sheets("Sheet1").Range("C3").Value = Mass

    '~~> Get values from A4 and B4 and update mass in C4
    Density = Sheets("Sheet1").Range("A4").Value
    Volume = Sheets("Sheet1").Range("B4").Value
    Mass = Density * Volume
    Sheets("Sheet1").Range("C4").Value = Mass

    '~~> Get values from A5 and B5 and update mass in C5
    Density = Sheets("Sheet1").Range("A5").Value
    Volume = Sheets("Sheet1").Range("B5").Value
    Mass = Density * Volume
    Sheets("Sheet1").Range("C5").Value = Mass
End Sub

CODE 2 (Precise Code)

'~~> Procedure to calculate the mass
Sub CalculateMass()
    Dim Mass As Double, Density As Double, Volume As Double
    Dim rowCount As Long

    '~~> Loop through rows to get values from A and B and update mass in C
    With Sheets("Sheet1")
        For rowCount = 1 To 5
            Density = .Range("A" & rowCount).Value
            Volume = .Range("B" & rowCount).Value
            Mass = Density * Volume
            .Range("C" & rowCount).Value = Mass
        Next rowCount
    End With
End Sub

2) Removing the unnecessary lines of code. For example see these two codes which basically do the same thing in Excel 2003

CODE 1 (Normal Code)

Option Explicit

'~~> This procedure fills Excel's 10000 cells with random values and then removes the duplicates
Sub FillExcelCells()
    Dim rowCount As Long

    '~~> Activate the necesary Sheet
    Sheets("Sheet1").Activate

    '~~> Loop through all the cells and store random numbers
    For rowCount = 1 To 10000
        Sheets("Sheet1").Range("A" & rowCount).Select
        Sheets("Sheet1").Range("A" & rowCount).Value = Int((10000 - 1) * Rnd() + 1)
    Next rowCount

    '~~> Sort the Range
    Sheets("Sheet1").Range("A1").Select
    Sheets("Sheet1").Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False

    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.SmallScroll Down:=-39
    ActiveWindow.ScrollRow = 9838
    ActiveWindow.ScrollRow = 9709
    ActiveWindow.ScrollRow = 9449
    ActiveWindow.ScrollRow = 8968
    ActiveWindow.ScrollRow = 8319
    ActiveWindow.ScrollRow = 7245
    ActiveWindow.ScrollRow = 6003
    ActiveWindow.ScrollRow = 4818
    ActiveWindow.ScrollRow = 4040
    ActiveWindow.ScrollRow = 3317
    ActiveWindow.ScrollRow = 3076
    ActiveWindow.ScrollRow = 2521
    ActiveWindow.ScrollRow = 2298
    ActiveWindow.ScrollRow = 2113
    ActiveWindow.ScrollRow = 1724
    ActiveWindow.ScrollRow = 1372
    ActiveWindow.ScrollRow = 1038
    ActiveWindow.ScrollRow = 872
    ActiveWindow.ScrollRow = 668
    ActiveWindow.ScrollRow = 538
    ActiveWindow.ScrollRow = 464
    ActiveWindow.ScrollRow = 446
    ActiveWindow.ScrollRow = 427
    ActiveWindow.ScrollRow = 409
    ActiveWindow.ScrollRow = 390
    ActiveWindow.ScrollRow = 353
    ActiveWindow.ScrollRow = 334
    ActiveWindow.ScrollRow = 297
    ActiveWindow.ScrollRow = 279
    ActiveWindow.ScrollRow = 242
    ActiveWindow.ScrollRow = 223
    ActiveWindow.ScrollRow = 205
    ActiveWindow.ScrollRow = 168
    ActiveWindow.ScrollRow = 149
    ActiveWindow.ScrollRow = 112
    ActiveWindow.ScrollRow = 94
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 20
    ActiveWindow.ScrollRow = 1

    Selection.Sort Key1:=Sheets("Sheet1").Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

    '~~> Delete duplicates
    For rowCount = 10000 To 2 Step -1
        Sheets("Sheet1").Range("A" & rowCount).Select
        If Range("A" & rowCount).Value = Range("A" & rowCount - 1).Value Then
            Sheets("Sheet1").Rows(rowCount).Delete shift:=xlUp
        End If
    Next rowCount
End Sub

CODE 2 (Precise Code)

This code discards the unnecessary lines of code.

'~~> This procedure fills Excel's 10000 cells with random values and then removes the duplicates
Sub FillExcelCells()
    Dim rowCount As Long

    With Sheets("Sheet1")
        '~~> Loop through all the cells and store random numbers
        For rowCount = 1 To 10000
            .Range("A" & rowCount).Value = Int((10000 - 1) * Rnd() + 1)
        Next rowCount

        '~~> Sort Range
        .Range("A1:A10000").Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

        '~~> Delete duplicates
        For rowCount = 10000 To 2 Step -1
            If .Range("A" & rowCount).Value = .Range("A" & rowCount - 1).Value Then
                .Rows(rowCount).Delete shift:=xlUp
            End If
        Next rowCount
    End With
End Sub

Et tu, Brute? – Proactive Programming

I am not in favor of either aggressive programming or defensive programming. I feel (and this is absolutely my opinion) the best programming style is “Proactive Programming”

You should know what your code is supposed to do and under which circumstances it could fail. Now it is genuinely not possible to account for every such instance(s) where your code could fail. For that we have to ensure that we handle all possible future errors proactively. Errors can pop up from any corner of your code. A line which you thought could never fail suddenly throws an error on your face.

Let’s consider this simple and interesting example. What we will try here is to create an instance of IE and then navigate to a particular site. In fact, I tried it and it works beautifully. In such a case what can go wrong?

Option Explicit

'~~> Create an instance of Internet Explorer and navigate to a website
Sub GoToWebSite()
    Dim appIE As Object
    Dim sURL As String

    Set appIE = CreateObject("InternetExplorer.Application")

    sURL = "http://www.siddharthrout.com"

    With appIE
        .Navigate sURL
        .Visible = True
    End With

    Set appIE = Nothing
End Sub

Nothing, Right? Not quite correct.

What if I distributed this code to a user who uses MS Windows but doesn’t like Internet Explorer? He went to the extent of uninstalling IE from his pc. Which browser he uses is immaterial at the moment but the very fact that he doesn’t have IE on his pc will crash the code. So how do we handle such kind of errors?

Consider this code.

Option Explicit

'~~> Create an instance of Internet Explorer and navigate to a website
Sub GoToWebSite()
    Dim appIE As Object
    Dim sURL As String

    On Error GoTo Whoa

    Set appIE = CreateObject("InternetExplorer.Application")

    sURL = "http://www.siddharthrout.com"

    With appIE
        .Navigate sURL
        .Visible = True
    End With

    Set appIE = Nothing

LetsContinue:
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

When the user runs this code, the code will check if IE exists. If IE exists then it will create an instance of IE and display the relevant website and if IE doesn’t exists then it will simply inform the user about the problem and then exit the code gracefully without crashing the code.

Different programming languages have different syntax for error handling. For an extensive article on Error handling related to a particular programming language I would recommend you to look it up in MSDN

To be, or not to be – Late Binding vs Early Binding

Another important thing while building applications is sometimes we need to automate other applications as we tried automating IE above. If you browse the web, you will realize that it is an ongoing debate on which binding one should follow. Personally I am more inclined toward Late Binding.

Late binding uses CreateObject to create an instance of the application object, which you can then control. To use Early binding, you first need to set a reference in your project to the application you want to manipulate.

The biggest drawback of Early Binding is that the end user might not have the same application which you have on your pc. For example if your application early binds with MS Excel 2003 (By setting reference to Microsoft Excel 11.0 Object Library) then that code will not run on pcs which have say MS Excel 2007 (Microsoft Excel 12.0 Object Library) or Excel 2010 (Microsoft Excel 14.0 Object Library). This can cause a lot of frustration to the end user.

In such cases, it is always preferable to use Late Binding.

Early Binding has it’s own advantages as well. For example the use of intellisense, Full access to the application’s object model via the Object Browser and VBA Help, faster compilation etc…

Finally it is up to you to decide on which type of binding you’d want to use

Avoid List – Things to avoid

• Avoid Shortcuts or Hacks

Writing precise code is different than writing hacks or short codes. Short codes or hacks are basically temporary adjustments made in the code so that it can run without giving any errors. This should be completely avoided as they are absolutely not reliable.

• Avoid Complex Codes

Complex codes can be be of two types. Sophisticated Code and Confusing Codes.

Sophisticated programming style is often used by advanced programmers. This style is often looked down upon by various programmers because this style has been misused quite often to project ones superiority. Till the time one refrains from such childish acts, I see no harm with this style. One should also refrain from this style when you are working in a team in an organization. This ensures that the code can be maintained effortlessly even after you are long gone.

Confusing Codes are complex codes which, not only confuses the developer who is inspecting it but also the developer who created it. This usually happens when the logic on which the code is based is not very sound or the code is not well Commented/Indented.

• Avoid unnecessary dependencies

Using 3rd party components in your code or application which the end user has no access to will certainly cause problems. If there is no alternative then get the necessary rights (If the 3rd party component is a commercial product) to distribute it with your application. Develop an installer to deploy it in the end user pc. And yes, keep the end user in the loop.

• Avoid Assumptions

This might sound funny to you but while programming, consider the end user as a newbie (even if the end user is an expert)! This is because the end user will never behave like you would expect them to. Like I mentioned earlier, elaborately plan your code and ensure that appropriate error handling has been taken care of.

Dry Runs – Test before you deploy

And last but not the least, test your code extensively before distributing your Code/Application. It’s very frustrating for the end user if the code/application crashes leaving them high and dry. Think of the time, energy and potential data loss that can happen when the code/application crashes.

Well that’s all I can think of at the moment. Hope you enjoyed reading it the same way I enjoyed writing it

About these ads