My Coding Technique

I have recently been doing most of my coding in Visual Studio, doing some 'real' work for a change, coding using VB.net, ASP.net and ADO.net.

The environment in VS2008 is much improved over the VBE in MS Office (VBA). For example, it auto-indents and, with the addition of Developer Express's Coderush product it even adds helpful lines between these indents. In large code projects it becomes a real chore to keep pressing the tab key to indent, but it is pretty essential if you are going to be able to easily 'read' and debug your code. Below is an example of my code in VS2008.

Another thing you also soon notice is the huge nature of the .NET framework meaning that if you don't 'type' your variables (that is declare them strongly as a certain 'type') you get little or no help at all and that makes the whole scenario impossible. (Well not impossible, but improbable if you are not the sort of person who can memorise Pi to 120 places or something!).

This brings me to my point in VBA.

When I first started coding, as with most I suspect, you simply recorded the code with the macro recorder and then amended that to get rid of all the unnecessary defaults it records. You then move onto hand coding but a little like this. (...again I suspect)

Sub Demo()
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveWorkbook.Worksheets("Sheet1").Name = "Data"
ActiveWorkbook.Worksheets("Sheet1").Range("A1").Value = "Nick"
ActiveWorkbook.Worksheets("Sheet1").Range("B1").Value = "Hodge"
'Now you could risk this...
Range("C1").Value = Range("A1").Value & " " & Range("B1").Value
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Sub Demo()
Dim wb As Workbook
Dim wks As Worksheet

Set wb = ActiveWorkbook
Set wks = wb.Worksheets("Sheet1")

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    
    With wks
        .Name = "Data"
        .Range("A1").Value = "Nick"
        .Range("B1").Value = "Hodge"
        .Range("C1").Value = .Range("A1").Value & " " & .Range("B1").Value
    End With
    
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

Option Explicit

Sub Demo()
Dim wb As Workbook
Dim wks As Worksheet
Dim rng As Range

Set wb = ActiveWorkbook
Set wks = wb.Worksheets("Sheet1")
Set rng = wks.Range("A1")

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    
wks.Name = "Data"
    
    With rng
        .Value = "Nick"
        .Offset(0, 1).Value = "Hodge"
        .Offset(0, 2).Value = .Value & " " & .Offset(0, 1).Value
        With .Font
            .Bold = True
            .Color = RGB(0, 0, 0)
        End With
        .Interior.Color = RGB(255, 0, 0)
    End With
    
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub

 

You'll notice we've added a rng variable, that refers to a Range object and then we 'set' that to the range A1 on Sheet1 (using our wks variable that we know refers to that sheet in the ActiveWorkbook (wb)). We then use that rng object in a With...End With block to assign stuff to that object. (notice too I use an internal With...End With block to assign stuff to the Font object). You'll also notice I moved the wks.Name line out of the block as it is no longer referring to the wks but the rng. Of course I could have incorporated the .Name into the rng block, by using

 

.Parent.Name="Data"

 

As the Worksheet (wks), is the 'Parent' object of the Range (rng) object.

 

Maybe the image below will help to show the components parts.

 

 

That's just a few pointers in what I do to make my code shorter, more readable, efficient and easier to debug. It also has the spin off in making the VBE tool work for you instead of against you.

 

Just my slant on things... Comments.