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))
      Loop
      x = 1
      y = y + 1
      tvar = trim(Cells(y, x))
   Loop

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

Finally

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.

Using VBA in Excel

After the last post, I got a couple of emails asking about using VBA in macros.
Remember that I am a programmer at heart, so there might be ways to do things using ‘raw’ excel functionality. I just find it easier to cut code.
My best advice on getting started is  is to use the macro recording function and then look at what gets recorded. Excel is writing VBA. So, with a little bit of knowledge it is possible to do quite complex things.
If I take the lines from the working version I will explain them a section at a time:

Sub trimit()

The macro is a VBA subroutine. The keyword Sub declares a subroutine Trimit() gives it a name. The last line is END SUB Each subroutine is bracketed by these lines allowing multiple routines to reside on the same piece of source code.

x = 1

If VBA finds no keyword at the start of a line it assumes the word LET. Therefore this line is creating a variable called ‘x’ and assigning a value to it of 1. (Read it as LET x = 1).  LET is the original BASIC command to assign a variable with a value. The variable is on the left of the equals sing, the value being assigned on the right.
The next line is almost exactly the same, except it declares a second variable called ‘y’.

y = 1

The fourth line is yet another assignment, but this is also using TWO VBA functions, rather than a specific value.

tvar = trim(Cells(y, x))


This means the line starts LET tvar =
I then use a Trim function. This removes any extra spaces from whatever is passed to it.

What is passed to it, is another function Cells. This takes two arguments. The y position (Row) and the x position, Column of the current sheet.

This means that 1,1 is cell ‘A1’ NOTE: The row is the first argument, and the column is the second.

If you’re using Excel, you already use functions. Functions are a useful piece of code that returns a result. EG: SUM([RANGE]) is something we all use. SUM is passed the Argument of a range and returns the value of all of the cells in that range added up.

 

Okay, that’s the first lesson. Hopefully, you can find where to create a macro/ VBA subroutine and understand how to assign a variable. The remaining part of the explanation will be along in the next few days.