Coding preferences

I was reviewing some VBA code someone else wrote recently. I always like to see how other people approach and solve development problems. I find that reviewing other devs code helps me think more deeply about the stuff I write.

One thing I have noticed in my code is that I rarely use

do ... while/until or
do while/until ... loop

(looping recordsets is one use, no others spring easily to mind) 

I almost exclusively use

For... next and
For each ... next

for all looping.

Don’t know if its force of habit, defensive programming (I like to find the end and check its reasonable before looping to it), just the type of stuff I do in VBA, or maybe just lack of imagination.

What do you do?

Do you find yourself tending to repeat the same mini designs?

do you have a preference?




23 Responses to “Coding preferences”

  1. Marcus Says:

    It’s always interesting looking at someone else’s code for a problem you’re interested in solving. Sometimes the reactions is “I wouldn’t have done it that way”. That’s not suggesting my way was right; just different. It’s also a pleasant surprise to have a: “Oh wow, I didn’t think of that!” reaction too.

    I too probably only use ‘Do’ loops to iterate recordsets but also text files. When iterating collections, arrays (using LBound & UBound) and objects I probably always use ‘For’ loops. Even when the loop is conditional, I typically abort with and ‘Exit For’ rather than a ‘Do Until’.

    “Do you find yourself tending to repeat the same mini designs?”

    Yup. I maintain a library of code which I can use in current projects. This has the advantage of saving time and using production tested, debugged code. It also has the disadvantage of allowing me to delude myself (inadvertently) that the existing code I have is the best solution for the task at hand without thinking the problem through thoroughly. Perhaps it’s that ROI thing again – having a solutions rapidly developed even though the code may be sub-optimal.

    Cheers – Marcus

  2. Jon Peltier Says:

    I use For (Each)/Next more frequently than Do/Loop, but lately I’ve used Do more frequently than in the past. I tend not to put my condition into the Do or Loop statement (like Do While or Loop Until), but use If statements wrapped around Exit Do. Somehow I feel more control this way.

    I have an extensive code library, a reasonable amount of algorithms in my head, and access to Google. I rely on one of the three, plus several decent Excel/VBA books, depending on my mood.

    My approach to filling a sheet with calculations has changed over time. It used to be suicide to rely on VBA for populating a sheet because of the time it took, so I used to rely almost entirely on worksheet formulas. I still prefer them of course, because they update so rapidly. But I’ve gotten better at array processing, and computers have gotten so much faster, that now I’ll use VBA to fill the sheet. Sometimes too it’s just easier to do things in a loop than in complex array formulas.

    Just my two pence, as if I’m an expert.

  3. Ross Says:

    >> Sometimes too it’s just easier to do things in a loop than in complex array formulas.

    Dam right, I must be quite lazy because I would rather spend 10 mins writing a UDF(or sub) and then watch the screen update slowly, than 1 hour (I’m no good with formulas!) writing an array function to do the same thing.

    I use For/Next mostly, I used do until for rs and other loops of unknown length, but tend to use a conditional test if possible.

    I never use while wend, I don’t see it much either, some times but not very often, is it a bit old fashion now?

  4. sam Says:

    For loops work fine for me …..may be thats because they were the first type of looping structures I learnt when I started programming with Basic.

    But all the “experts” tell me not to loop… use arrays/collections they say….I am slowly shifting…..


  5. Rob Bruce Says:

    I tend to make a judgement on each individual case. If finding the bounds of the data you’re dealing with adds overhead then a For loop is no good. However, the conditional on the Do loop may also add overhead, depending on what it is testing.
    Do loops are also more adaptable – Do While/Do Until/Loop While/Loop Until.
    I also try to avoid Exiting loops for reasons of code flow clarity, though, again, each case needs to be judged on its own merits.
    Ross, while/wend is indeed considered old-fashioned, and I have never used it. It was removed from VB.NET and (I’ve never worked out why) replaced with while/end while.

  6. MacroMan Says:

    As you know a For Each loop is recommended for arrays and since I mostly loop thru a range of cells, which in essence is an array, I usually use the For Each.

  7. Charlie Says:

    That’s an interesting question – funny how we do things without really thinking about them.

    In my practice, I typcially use for and for each, but there are times when do while seems appropriate and it just slips in there.

  8. Dennis Wallentin Says:

    Interesting question. I try to avoid any iteration as long as possible and instead use the Find and FindNext methods. When working with arrays the For Each is the preferable method I use.

    Kind regards,

  9. Biggus Dickus Says:

    I use For …. Next and For Each …. Next most of the time because it works for nearly all scenarios I need. If I am cycling through a list I use for Each until it reaches the end. If I am looking at cells for the first empty cell I’ll use for each and ask “If Isempty(c) Then Exit For” to break then loop.

    I have used “Do Loops” but mostly for variety I guess, but I’m sure that it is more effective in certain scenarios that For Next.

    There is some value in consistency though…

    Not a real professional answer but remember – I’m paid by the hour. That which gets the job done quicker works best for my clients (and ultimately for me). So the procedures I use tend to be standard as much as possible. So “For Next” is my buddy ;-)


  10. Harlan Grove Says:

    It seems none of the rest of you read many text files via VBA. Maybe you could kludge it with a For loop, but the most natural way to do it is

    Do While Not EOF(FileHandle)
    Line Input FileHandle, whatever

    FWIW, many numeric algorithms are coded with Do loops because they can have nonpredictable numbers of iterations, though there’s usually a maximum iteration count before stopping or prompting whether to continue (like Solver).

    Specifically in re Biggus Dickus ‘looking at cells for the first empty cell’, why wouldn’t you use the Range class’s .End property?

  11. Dennis Wallentin Says:


    Like most other sources textfiles can be treated in many ways so when You single out:

    “It seems none of the rest of you read many text files via VBA.”

    I have to disagree because using ADO/DAO is additional ways to work with textfiles.

    Kind regards,

  12. Marcus Says:

    “It seems none of the rest of you read many text files via VBA.”

    Ahem. “I too probably only use ‘Do’ loops to iterate recordsets but also text files.” ;)

    “why wouldn’t you use the Range class’s .End property?”

    Excel has a known bug which makes detecting the last cell in a range unpredictable. There are some posted workarounds (such as on John Walkenbach’s site) but even these have idiosyncrasies, such as clearing the undo stack.

    Kind Regards – Marcus

  13. Biggus Dickus Says:


    “Specifically in re Biggus Dickus ‘looking at cells for the first empty cell’, why wouldn’t you use the Range class’s .End property?”

    If I’m working down a column looking at every value and responding to it, quitting when I get to a blank cell works fine for me. I’ve never used the .End property that way but it may do the job, but this works for me.

  14. Dick Kusleika Says:

    I didn’t realize it was a preference. I use Do.Loop when I don’t know the bounds and For.Next when I do. At least that’s what I think. I might be surprised if I actually look back at my code.

  15. Simon Says:

    Dick I hadn’t thought of it as a preference till I was looking at other peoples code. I’m not saying one way is better, just noting I have a tendency towards for next. dunno why, personal style? or lack of?

  16. Harlan Grove Says:

    I can’t find any mention of the End bug in m.p.e.p or Walkenbach’s site, though I may not have entered the optimal search terms. I can say I haven’t had any surprises myself using it.

    As for processing text files, ADO/DAO may also be able to handle them, but I doubt it’d be useful for processing complex, multiline formats such as mainframe reports or PDF file converted to plain text. When I need to parse multiline logical records, I prefer lower end tools.

    But to amplify what Dick Kusleika wrote, Do is more natural when bounds are unknown or not applicable. For is more natural when the number of iterations is well defined, such as iterating through all cells in a range or all items in an array.

  17. Jon Peltier Says:

    I don’t know of any .End bug. The problem may be the .UsedRange issue, in which the used range isn’t reliably reset until the workbook is saved, and in which the used range includes differently formatted but empty cells as part of the range, not just cells with actual contents.

    I’ve read someplace that For Next isn’t reliable for some collections (e.g., SeriesCollection), in that members of the collection may be skipped or processed twice. In this case you need to count the elements in the collection, then use For iItem = 1 to nItems.

  18. Marcus Says:

    Hi Harlan,

    This link I was referring to:

    I assume this is what you were referring to?

    I also have some hesitation using ADO/DAO for processing text files. Too often I find that ADO/DAO misinterprets data in columns with mixed data types regardless of the IMEX flag in the connection string (Unless I’m missing something here).

    Regards – Marcus

  19. Jon Peltier Says:

    Marcus –

    That’s not an issue with .End, it’s an issue with .UsedRange, which I was referring to.

  20. Marcus Says:

    Sorry about that – I’ve obviously gotten myself confused.

    Regards – Marcus

  21. Dennis Wallentin Says:


    >>Too often I find that ADO/DAO misinterprets data in columns with mixed
    >> data types regardless of the IMEX flag in the connection string.

    It’s true that this can be an issue. Excel evaluates the first 8 cells (25 cells in later versions) in each column in order to decide the datatype. I know there was a very good blogpost and discussion about it at Daily Dose but at present I cannot find the post.

    Kind regards,

  22. Charles Says:

    I find the major problem with .END is that it ignores hidden rows etc, so it is unreliable if the end-user has a means of hiding things.


  23. Jon Peltier Says:

    Charles –

    You could of course use Range(“A65536”).End(xlUp) to find the last cell in column A with any data. Problem is when the user dumps any data anywhere, you never know if the last cell you’ve found is relevant to the data you are interested in.

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: