My Coding Technique

Dick has been kind enough to allow me to mirror posts from DDoE, but gets little back. Therefore as a guest poster I thought I would post some ramblings I made today on excel user group regarding my own particular coding techniques. (It also allows him the weekend off)

I have recently been doing most of my coding in Visual Studio, doing some ‘real’ work for a change, coding using, and

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)


Now, the above code will work (I know, why the application….. It’s just to prove a point), but two finger typing makes it very slow and painful to write and often, when using the objects, intellisense gives you no clues as to what properties, methods, or child objects are available to you. It also makes it very laborious to debug.

The Range(“C1?)… is also very dangerous as if you have multiple workbooks open, or your workbook has multiple worksheets, you may find that Range(“C1?)… does not refer to the C1 you think it does as your code may have made another workbook active. (Remember, you are just using ActiveWorkbook).

Now consider this code, which does the same.

First you will notice we declare two variables (wb and wks (could be anything really)) to ‘carry’ the two objects (the Workbook and the Worksheet respectively). In VBA, if you have an ‘Object’ variable you have to ‘Set’ it (unlike ‘value’ variables which can just be assigned e.g. myVal=0) and we do this by using the ActiveWorkbook (hopefully we are sure that the activeworkbook is the correct one at this point!) and then we ‘Set’ the Worksheet object variable by assigning the Sheet1 worksheet. (Note that I am using wb in front of this assignment. That’s because I know that wb refers to the correct workbook).

What I am sure of now is that whatever I do with wb or wks will always refer to those two objects. Whatever’s active, without activating them and without selecting. Your code will run quicker and there will be no screen flashing (hence I really have no need for the Application. ScreenUpdating, etc).

The second and third benefits of this approach are that I now only refer to Workbook.Worksheets(“Sheet1?) as wks and, while typing wks and the period to use properties and methods of the Worksheet, I am sure to get a ‘clue’ from the VBE’s intellisense, as below. This does not always happen when using full notation as VBA seems to lose track of the object it is using. By declaring it in a ‘strong typed’ way, it knows and guides you through.


The last construct that I use all the time is the With…End With block. This gives you a further shortcut when you intend to make a lot of changes to a single object, (I also use it to make changes to objects ‘around’ the referenced one, see Offset(…) below)

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


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

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

Code explanation

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.

Posted in Uncategorized

25 thoughts on “My Coding Technique

  1. Hi Nick

    Nice post.
    I too have a similar style to coding. I use With / End With where possible.

    I also try to get away with never having to .Select a range before acting on it.


  2. Funny, even though I use With/End With pretty religiously, it never occurred to do it with the Application properties as you did here. If I did I probably would have had a With/End With bracketing the pair of opening Application properties and the closing ones, but I suppose that makes it less efficient. Looking at your code at first I thought your With rng section was nested inside the With Application section. It is physically, but not practically. I always think of nested Withs as being related.

  3. “I also try to get away with never having to .Select a range before acting on it”

    Sadly it is not possible to perform all operations without selecting

    Ex : If you want to add a Query to a Sheet and then Delete it
    The sheet has to be selected

    Set DestSht = Sheet1
    With DestSht.QueryTables.Add(Connection:=StringToArray(Ar1), Destination:=Range(“A” & EndRow + 1))
    .CommandText = StringToArray(Ar2)
    .Name = QryNm
    .FieldNames = False
    .PreserveFormatting = True
    .AdjustColumnWidth = False
    .RefreshStyle = xlInsertDeleteCells
    .SaveData = True
    .Refresh BackgroundQuery = True
    End With

  4. Intellisense is great when debugging but when that is over your code can usually be improved. In the example I find use of the With rng nested inside With Application unintuitive and confusing. If all the code is nested inside a With Application then why not make full use of the objects within it;
    Sub Demo2()
    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    With .ActiveWorkbook.Worksheets(“Sheet1?)
    .Name = “Data”
    With .Range(“A1?)
    .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
    End With
    .ScreenUpdating = True
    .EnableEvents = True
    End With
    End Sub

  5. Hugh

    I actually agree. Sometimes it can be overdone and maybe that is in this case. Certainly you could use a ‘.’ before each object as they are ‘children’ of the application object.

    I probably end up with that as doing more now in, HTML and even XML (in ribbonX with the new ribbon) I tend to set my end tags now in VBA in the same way (As I declare the opening tag and just add a line in the middle). Does that make sense? That way, I remember to close and end up with some distant With…End With’s (That’s what so nice about DevExpress’s Coderush and the lines you see joining in the top VS2008 image.

  6. Sam – “If you want to add a Query to a Sheet and then Delete it, The sheet has to be selected.”

    Are you sure? I’ve been using some code lately that adds a query table to a hidden sheet, extracts some of the data, then deletes it. I’m not real familiar with the code, but I did modify it to do the query table on the hidden sheet, so that the main sheet contains extracts from the query table without the clutter of the query table.

  7. Hi Jon…

    Sorry about that you are right….thanks for making me look at my code agaon

    The mistake I was making was Destination:=Range(“A” & EndRow + 1))
    I would have to
    Dim DestRng as Range
    Set DestRng = DestSht.Range(“A”&EndRow+1))

    and then


  8. Nick: I pretty often don’t indent as I write (I’m lazy). But Stephen Bullen’s excellent Smart indenter makes a fully indented routine just one click away.

  9. I just hope that eventually it does it all for you, like VS200x.

    Still though it has trouble with the scenario where you suddenly decide you want a With…End With, so with the line


    you put ‘With’ before rng and move to the position before the ‘.’, hit enter and end up with. (As it puts the close in for you)

    With rng
    End With

    At least it just puts a squiggly blue line under the part that can’t be compiled, not that abrupt error dialog with the system ‘dong’, that cuts you in half! ;-)

  10. Querytables has puzzled me since our upgrade to 12 -my code couldn’t get to them anymore. Mystery seem solved, and in case I’m not the only one that missed Gainer’s blogpost: They are now WorkbookConnections, part of Workbook properties, not Worksheet.

    Dim Cnn As WorkbookConnection
    Set Cnn = ThisWorkbook.Connections(1)
    MsgBox Cnn.ODBCConnection.Connection

  11. Since this thread started as a “best know method” for coding, I thought I’d get on my soapbox about constants. I try my best (deadlines permitting) to eliminate all uses of “some stuff”, where the code needs some string. Since I put my local constants above my variable DIM statements and I put my global (oh the horror) constants above all my functions / subroutines, my future changes are a snap. One might think this is no big deal, but when your “some stuff” represents a column from some other spreadsheet that your data is pulled from, and then they add a new column (without telling you, no doubt), it’s a time consuming task to find all references to that column (and all those to the right of it) and change them all. With constants you make the old constant (for instance “AG”) read the new value (now “AH”), and change all the ones to the right too (old “AH” becomes new “AI”, etc).

  12. Dan

    That’s a good point, with my venturing into the .Net World, they make it even easier with Resource Files. (Again using DevExpress’s Coderush), just click any occurrence of your typed string and get several choices. (inc your Local Constant). Mine is usually Create Resource. That way one file holds all your string/constantName pairs. Works really well.

    I have to say if/when we get an update to VBE (.net????) if we get anywhere near the quality of VS200x or the extensibility model to allow other tools to. We’re in for a treat :-)

  13. @Dan Maxey: Yes, it helps to use constants to identify columns in external data sources. But, consider taking it a step further. Recently, I completed a project where the client received data for 8 (expected to increase to 12 or 15) data sources from different corporate groups around the world. Every month someone added / moved some data from some column to another. Not to mention the location of the header row also shifted. My solution was to create a function that located a column based on a header string. Now, as long as the header strings remained intact, the code could adjust to headers being in an arbitrary row (obviously with preceding rows being blank) and data being in arbitrary columns. Further, by building a map of the layout “up front” the code could gracefully fail if it found a header to be missing.

  14. Tushar Mehta – consider using a soundex or approximate matching function rather than exact matching to locate possibly slightly altered header strings if no exact match were found. Display the close but not exactly equal headers in a listbox in a dialog, and allow the user to select the ‘correct’ one.

    The exact header strings and/or possibly matching regular expression patterns should be constants in the module code.

  15. Thanks Jan Karel. Now I’m confused again BG…
    Seems that Listobject won’t work for files made in older versions, while Connections does. In native 12 files, both methods seem to access the same object.

  16. This would be really useful to me if I could get it to work. I’m using Excel 2003 and I get a subscript out of bounds error (runtime error 9) when it hits: Set wks = wb.Worksheets(“Sheet1?). Anyone have an idea why?

  17. @jmbob666: Basically “subscript out of bounds” means the code has tried to access an item in an array, and the item doesn’t exist. Make sure your worksheets collection actually has a sheet in it named “Sheet1?.

  18. Nick,

    Thank you for sharing your technique. I admit to starting out as Phase I, and perhaps operating today in between your Phase II and the technique you outlined. I have gone back to look at the last macro I worked on, and find that your technique indeed would have kept me out of a lot of trouble. One question that I have is regarding check boxes from the control menu, in the past I would use:

    If ActiveSheet.CheckBox1.Value = True Then MsgBox “True”

    However, that errors out when I try to use your technique:

    Sub wbwksvariables()

    Dim wb As Workbook
    Dim wks As Worksheet

    Set wb = ActiveWorkbook
    Set wks = wb.ActiveSheet

    If wks.CheckBox1.Value = True Then MsgBox “True”

    End Sub

    I find that intellisense will lead me to OLEObjects, and have tried:

     If wks.OLEObjects(CheckBox1).Value = True Then MsgBox “True”


     If wks.OLEObjects(“CheckBox1”).Value = True Then MsgBox “True”

    but haven’t been able to get either of those to work. Thanks for your help.

Leave a Reply

Your email address will not be published. Required fields are marked *