VBA Coding Part 2

In the seventies there was a computer pundit, Edsger W. Dijkstra  who used to claim that a programmer only needed to use three statements to construct any programme:

Assignment: LET,

Branching: IF THEN ELSE,

and: LOOP

He considered everything else a confusing frippery. Whilst this is possibly true, using the full range of statements available within a programming language is the best way to achieve an efficient program. DO you care … If you’re writing an excel Macro and it works, probably not.

An example of needing to be efficient is. I was once asked to look at a company’s process to migrate the data from system A to system B. The data extraction from system A took 60 hours and they wanted to do it over the weekend without disruption. By making the programming more efficient, I reduced the extraction to 120 minutes. This meant I could do the migration overnight, rather than at the weekend. Saving them the cost of all that weekend work.

So lets take a look at the rest of the code:

   Do Until tvar = ""
      Do Until tvar = ""
        Cells(y, x) = tvar
        x = x + 1
        tvar = trim(Cells(y, x))
      x = 1
      y = y + 1
      tvar = trim(Cells(y, x))

The DO statement introduces the start of a LOOP in the code.

You will notice that between the While and the Loop the lines of code are indented. Why have I done this? It is a visual clue to the code included in the loop. It serves no purpose, but can save a lot of time when tracking down a bug in the code.

It might seem odd that the very next line of the Loop code is another Do Loop section. You will also see that this is further indented. Again, this serves only to highlight the inner loop.

Look at the code and I am sure you will make sense of it:

Do Until tvar="" Means that the loop will run until tvar = "" (The cell is empty)
Cells(y, x) = tvarReturns the contents of tvar into the spreadsheet cell on row Y, Column x.
x = x + 1Is incrementing the variable x (take the contents of x and add one)
tvar = trim(Cells(y, x))Is reloading the variable tvar with the next cell on row y.

And then the loop causes the code to return to the do statement which will run until the condition is TRUE
The final part of the programme executes after the inner loop has finished.

x = 1Resets the column pointer to one.
y = y + 1Increments the row pointer
tvar = trim(Cells(y, x))Once more reloads the variable tvar to satisfy the outer loop

The outer loop repeats until the condition is TRUE


That was a brief insight into how my mind works! I tend to think a little like a computer and so I find these things easy to write and understand. I hope it has tempted you to consider trying to extend your macro writing beyond simply duplicating a simple sequence of tasks.