Monthly Archives: August 2011

VB.Net and Excel


EDIT

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

http://www.siddharthrout.com/vb-dot-net-and-excel/

 

A whole new page dedicated to Excel Automation from VB.net added 🙂

Hope it helps 🙂

VBA Excel–Allow Paste Special Only


EDIT

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

http://www.siddharthrout.com/2011/08/15/vba-excelallow-paste-special-only/

 

While recently answering a post in msdn forum, I realized that the Robert Gold(Vice President of Business Intelligence in Bostwick Laboratories, Inc) was facing the same problem that I was facing couple of years back when I was working as a Team Leader in one of the BPO’s. The MIS Department was reporting to me and one of the main jobs was to create ‘Trackers’ for various departments. My team used to take hours to design a particular tracker only to realize after few days that the users were spoiling the format of the tracker by copying and pasting. Completely protecting it with a password was not the option. Nor did we find any option in Excel which forced selective pasting. It was then we devised this macro which allowed the user to paste but behind the scenes, converted that paste into paste special – values.

The only drawback of this method is that if the macros are disabled then this won’t work. But thanks to the IT Department, they ensured that the macros was enabled on each and every pc. After that we never faced a problem with users messing up the format of the sheet.

So how does this code work? What is the logic behind it? I would explain this in 3 sections.

  • Logic
  • Planting the Code
  • Code

LOGIC:

Whatever action that you perform in Excel Spreadsheet is stored in a list which is called the Undo List. So when you do an Undo for the first time, Excel refers to this list and then Undoes the last action that you performed. So if we can undo a ‘Paste’, then we can save the format of the sheet. All we need to do is

  1. Undo a paste
  2. Do a Paste Special – Values

There are few things that we need to take care of in this logic.

  1. We not only check for a “Paste” in the Undo list but also for an “Autofill” as Autofill can also spoil formats in a workbook.
  2. Don’t clear or set new text in the Clipboard between an UNDO and PASTESPECIAL else you will loose the original data stored in a clipboard.

PLANTING THE CODE:

It depends on your requirement. Do you want to preserve the format in all sheets in the workbook or just one sheet?

If you want to preserve the format in all sheets in the workbook then follow these steps

  1. Open the VBA Editor
  2. Double click on “ThisWorkbook” to open the Code Area on the right as shown in the picture below. Paste the code (Code I – Given below in the Code section) in that Code Area.

image

If you want to preserve the format in only one sheet in the workbook (Say Sheet1) then follow these steps

  1. Open the VBA Editor
  2. Double click on “Sheet1 (Sheet1)” to open the Code Area on the right as shown in the picture below. Paste the code (Code II – Given below in the Code section) in that Code Area.

image

THE CODE:

  • Code I : Preserve the format in all sheets in the workbook
Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim UndoList As String

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    On Error GoTo Whoa

    '~~> Get the undo List to capture the last action performed by user
    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)

    '~~> Check if the last action was not a paste nor an autofill
    If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" Then GoTo LetsContinue

    '~~> Undo the paste that the user did but we are not clearing the clipboard
    '~~> so the copied data is still in memory
    Application.Undo

    If UndoList = "Auto Fill" Then Selection.Copy

    '~~> Do a pastespecial to preserve formats
    On Error Resume Next
    '~~> Handle text data copied from a website
    Target.Select
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

    Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    On Error GoTo 0

    '~~> Retain selection of the pasted data
    Union(Target, Selection).Select

LetsContinue:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.description
    Resume LetsContinue
End Sub
  • Code II : Preserve the format of a single sheet in the workbook
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim UndoList As String

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    On Error GoTo Whoa

    '~~> Get the undo List to capture the last action performed by user
    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)

    '~~> Check if the last action was not a paste nor an autofill
    If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" Then GoTo LetsContinue

    '~~> Undo the paste that the user did but we are not clearing the clipboard
    '~~> so the copied data is still in memory
    Application.Undo

    If UndoList = "Auto Fill" Then Selection.Copy

    '~~> Do a pastespecial to preserve formats
    On Error Resume Next
    '~~> Handle text data copied from a website
    Target.Select
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

    Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    On Error GoTo 0

    '~~> Retain selection of the pasted data
    Union(Target, Selection).Select

LetsContinue:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.description
    Resume LetsContinue
End Sub

And you are ready. Now when the user pastes in the worksheet, the data will be pasted as “Values” only.

Hope this helps Smile

Office 365 – Excel


EDIT

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

http://www.siddharthrout.com/2011/08/10/office-365-excel/

Microsoft Office 365 is a cloud-based service for organizations which need a technology that enables flexibility and cost-effectively adds value to their organization. It delivers feature-rich productivity tools to users while helping to relieve the burden of managing and maintaining business systems—freeing up IT departments and businesses to focus on initiatives that can deliver true competitive advantage.

Microsoft Office 365 for professionals and small businesses is relatively very easy to use. Everyone can work together easily with anywhere access to email, web conferencing, documents, and calendars. The first time I heard about it, I was actually curious to know what it was all about. So today I finally registered as a trial user at this link.

http://www.microsoft.com/en-in/office365/online-software.aspx

Microsoft Office 365 is a set of web-enabled tools that lets you access your email, important documents, contacts, and calendar from almost anywhere and any device (Depends on Wi-Fi capability or depends on carrier network availability)

These tools include Microsoft Office Web Apps, which are web versions of the Microsoft Office desktop apps that you are already familiar with like Microsoft Word, Excel and PowerPoint.

With these Office Web Apps you can create and edit documents on any PC with a web browser.

Basic requirements: Office 2007+, IE 7+, Windows XP SP3+
Full Requirements:
OS:     Windows XP SP3 / Windows Vista SP2 / Windows 7 / Mac OS X 10.5 (Leopard), 10.6 (Snow Leopard) / Windows Server 2003 / Windows Server 2008
Office client requirements: Office 2007 SP2 or Office 2010 / Office 2008 for Mac & Entourage 2008 Web Services Edition / Office 2011 for Mac and Outlook 2011 for Mac / .NET 2.0 or later / Lync 2010
Browser requirements:
Internet Explorer 7 or above / Firefox 3 or above / Safari 4 or above / Chrome 3 or above

There are basically three different plans for Office 365.

  • Microsoft Office 365 for professionals and small businesses
  • Microsoft Office 365 for midsize businesses and enterprises
  • Microsoft Office 365 for education

You can check them out at this link

http://www.microsoft.com/en-in/office365/plans.aspx

After you login this is how your main portal looks.

image

In this section we will be covering Excel.

When you click on the Excel icon, a new Excel document opens in a browser window which looks like this

image

There are 3 tabs that one has access to when working on a document in a web interface. Let’s see what do they cover.

FILE TAB:

The File Tab looks like this.

image

Two main things that I liked in this is the Automatic Save so that you don’t have to save again and again. It is handy if you have a bad connection. There is no ‘Save’ button as the workbook gets saved automatically. Some might argue that this could be a disadvantage but then remember that there is always the Undo button Winking smile. However you do have an option of a “Save As”. The other thing that I liked is the ability to download this online workbook to your computer and vice versa (covered later in this post)

For now, let’s save our document with a name that we want. Click on the File Tab ~~> Save As and then give it a name “Sample

image

HOME TAB:

The Home Tab looks like this.

image

One disadvantage working with online documents is that you lose access to your “Right Click” menu. However, the Home Tab lists many of those “Right Click” options. For example Insert, Delete, Number Formats etc… If you are familiar with Excel 2007 / 2010 then you can make out what the above buttons are for.

INSERT TAB:

The Insert tab just has two button and looks like this

image

I tried few Excel shortcuts and was pleased to discover that they worked just fine in the online document as well. For example to insert a hyperlink, you may press, CTRL K Smile

MY PANEL (Team Site)

You might be wondering that this all sounds cool but how do I access my document that was saved or how do I upload a document from my computer online? For that you need to click on “Team Site”.

image

When you click on Team Site, you are presented with many options.

If you notice there is a small panel on the left.

The “Documents” link on the left panel gives you access to options like

  • Upload documents to make them available to anyone with access to this site
  • Create a new shared document using the Office Web Apps

image

When you click on that link you are taken to “Documents” section which look like this:

image

If you notice, right at the bottom is the file that we saved. Smile

If you click on the file, you will now have access to some very basic operations that you can now perform.

image

Let’s go back one step. This is where we had come when we clicked on the “Team Site

image

The same saved file can be accessed from this view as well.

image

The “Site Actions” menu gives you further access to many options like editing the web parts of the current page, inviting new users to your site and other “Site Settings”

image

Well, that’s pretty much to it.

If you are interested, I would recommend enrolling as a Trial User and checking it out. I have already given the link at the top.

Hope this helps. Smile

VBA – Control Arrays


EDIT

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

http://www.siddharthrout.com/2011/08/05/vba-control-arrays/

These are the few things that we will be covering in this post.

  • What is a Control Array?
  • Can we create Control Arrays in VBA?
  • Why do we need a Control Array?
  • Examples of Control Arrays in VBA
    a) Using existing textboxes in the UserForm and assigning a same set of event procedures
    b) Creating new textboxes at runtime and assigning them same set of event procedures

What is a Control Array?

A Control Array is a group of controls that share the same name type and the same event procedures. They are a convenient way to handle groups of controls (Same Type) that perform a similar function. All of the events available to the single control are available to the array of controls.

Can we create Control Arrays in VBA?

Even though, VBA doesn’t allow us to to create Control Array like in vb6 and vb.net, we can still create Control Array in VBA.

Why do we need Control Arrays in VBA?

Control Arrays mainly have these advantages

  1. Controls in a Control Array share the same set of event procedures. This results in you writing less amount of code.
  2. Control Arrays uses fewer resources.
  3. You can effectively create new controls at design time, if you need to.

Examples of Control Arrays in VBA

We would be covering the following here

a) Using existing textboxes in the UserForm and assigning a same set of event procedures
b) Creating new textboxes at runtime and assigning them same set of event procedures

Using existing textboxes in the UserForm and assigning a same set of event procedures

Let’s say you have 10 textboxes in your UserForm (see image below)

image

And you want all 10 to be numeric textboxes. Numeric textboxes are those text boxes where you can only type numbers. If it was just 1 TextBox, you would have a code like this

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
    '<~~ 48 to 57 is AscII code for numbers. 127 is for 'Delete' and 8 is for 'Backspace'
    Case 48 To 57, 127, 8
    Case Else
        KeyAscii = 0
    End Select
End Sub

Now imagine writing this code 10 times for each and every textbox?

This is where we will use Control Array of Textboxes and assign them the same procedure.

To start with, add a new Class. You can do that by right clicking on the “VBAProject~~>Insert~~>Class Module”. See the two images below.

image

image

Now paste this code in the code area of the Class1 Module

Public WithEvents TextBoxEvents As MSForms.TextBox

Private Sub TextBoxEvents_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
    '<~~ 48 to 57 is AscII code for numbers. 127 is for 'Delete' and 8 is for 'Backspace'
    Case 48 To 57, 127, 8
    Case Else
        KeyAscii = 0
    End Select
End Sub

image

and in the UserForm Initialize event, paste this code.

Option Explicit

Dim TextArray() As New Class1

Private Sub UserForm_Initialize()
    Dim i As Integer, TBCtl As Control

    For Each TBCtl In Me.Controls
        If TypeOf TBCtl Is MSForms.TextBox Then
            i = i + 1
            ReDim Preserve TextArray(1 To i)
            Set TextArray(i).TextBoxEvents = TBCtl
        End If
    Next TBCtl
    Set TBCtl = Nothing
End Sub

image

And you are done! Now when you run the UserForm, all the textboxes will now show the same behavior Smile

To test it, simply run your UserForm and try typing anything in the textboxes. You will notice that you will not be able to type anything other than numbers or pressing the ‘Delete’ and the ‘Backspace’ button. Similarly you can create other events for textboxes like change(), click() etc.

Creating new textboxes at runtime and assigning them same set of event procedures

Now let’s take another scenario. Instead of creating textboxes at design time and then assigning them same set of event procedures, what we want to do is to create these textboxes at run time and then assign them same set of event procedures.
Let’s say your UserForm now simply looks like this.

image

Create the Class module as I have shown above and then paste the code which I gave above for the Class module.

Paste this in the the Initialize event of the UserForm.

Option Explicit

Dim TextArray() As New Class1

Private Sub UserForm_Initialize()
    Dim ctlTBox As MSForms.TextBox
    Dim TBoxTop As Long, i As Long

    '~~> Decide on the .Top for the 1st TextBox
    TBoxTop = 30

    For i = 1 To 10
        Set ctlTBox = Me.Controls.Add("Forms.TextBox.1", "txtTest" & i)

        '~~> Define the TextBox .Top and the .Left property here
        ctlTBox.Top = TBoxTop: ctlTBox.Left = 50

        '~~> Increment the .Top for the next TextBox
        TBoxTop = TBoxTop + 20

        ReDim Preserve TextArray(1 To i)
        Set TextArray(i).TextBoxEvents = ctlTBox
    Next
End Sub

and you are done 🙂

To test it, simply run your UserForm. You will see that the TextBoxes automatically get created and you will not be able to type anything other than numbers or pressing the ‘Delete’ and the ‘Backspace’ button. This is how your UserForm will look.

image

Hope this helps Smile

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 OldComment your code
  6. Don’t write a novelWrite 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.

01

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?

image

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.

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 🙂