VBA Programming Tutorial – Using Functions and Subroutines

So you have started to write vba code but you are not quite sure if you are doing it correctly, or if you are “on track” to write professional quality macros.

Why even bother to write pro quality code?

Well mainly because it makes your job easier and you deliver a better quality product to your client for probably no more time invested than it takes to write a poor program.

If you learn how to write quality code you will position yourself to be able to build complex applications in minimum time, and get a reputation for getting things done and delivering a quality product.

With obvious long term career and financial benefits.

So what is Professional Quality Code?

I would say quality code is code that is reliable and relatively easy to understand and achieves the clients (you may even be your own client) business objectives and enables you to extend and enhance your code in the future.

The first of many secrets to writing great code is to break it into small parts.

Years ago I was asked the question “How do you eat an elephant”?
Notwithstanding that I had absolutely no interest in or motivation or desire to consume poor Dumbo, it appears that the answer was one bite at a time.

So it is with programming, break the problem that you wish to solve into small bite sized chunks.

Although this may not sound like a major revelation, I never cease to be amazed by the amount of “professional” code that is just one long cryptic 1000 line essay.

So there are many elements to quality code and don’t get me wrong, I am not a geek and do not seek to make you “geeky” however this is just practical stuff to help you get what you want done with the minimum of aggravation.

So when we look at some of the elements of Pro code, two of them that spring to mind are that good code is (a) reliable and (b) easy to understand.

In this article I am going to deal with just one of the building blocks that take you in the direction of writing code that is reliable and easy to understand.

That building block is the proper use of functions and subroutines to strengthen you macro or program.

How to Write a VBA Function Or Subroutine

The basic rule is that a function or subroutine should do one task and one task only.

This means that it is optimised to carry out the task that it’s name implies.

So for  example a valid function name could be “getLastName()”.

The purpose of this is self explanatory, you would not use this particular function to look up a postal or zip code from a database.

In Visual Basic For Applications (vba) or your Excel or Word macro, functions are used to denote packages of code that can (but do not have to) return a value to the calling program, subroutines are packages of code that do not return values to their calling programs.

They can however communicate with their calling program using arguments (don’t worry about this right now).

The next section will show you the basic principles for breaking a program down into subroutines, the codes lines use pseudo code in place of real code in order to illustrate the concept without getting bogged down in vba details.

The challenge is to write code to model driving to town to collect your dry cleaning.

Without using subroutines you could have one code listing that goes on for thousands of lines which can get very complex very quickly.

Using subroutines we can tackle one part of the problem as a complete unit for example lets write code to model the “Drive Car To Town” part of the problem

  Sub DriveCarToTown()
    Open House Door
    Walk Over To Car
    Insert Key In Car Door Lock  
    Open Car Door
    Sit down On Drivers Seat
    Close Car Door
    Put on Seat Belt
    Insert Key Into Ignition
    Turn Key To Start Engine
    Put Right Foot on Brake
    Release Hand Brake
    Engage Automatic Gearbox To Drive
    Ensure there are no obstacles in front of car
    Remove Foot From Footbrake
    Steer Car to end of driveway
    Apply Foot Brake
    Check For oncoming Traffic
    Engage indicators and turn onto road
  End Sub

Now all of the above commands were grouped into the single command “DriveCarToTown”, this means that 18 individual commands were summarised into one command. This is much better than having those 18 commands hidden in a listing of thousands of lines.

The program that you wish to write might however is called “GetSuitFromDryCleaners” and in Visual Basic For Application might look like…

  Sub GetSuitFromDryCleaners()
    DriveCarToTown   ' Call the other routine rather than list all it's code here
    CollectDryCleaning
    DriveCarHome
  End sub

The above program “GetSuitFromDryCleaners” seems relatively simple however it’s three lines of code mask the complexity of possibly 100 lines or more of code that makes up the individual functions that comprise those three lines.

Let’s call this functional programming, as we have grouped lines of code that are relevent to achieving some task into a function or subroutine of that name.

So if we did not create functions to “Group” our commands then the program GetSuitFromDryCleaners could get hugely complex real fast as it could end up containing thousands of lines of code.

The purpose of functional programming (like all good programming) is to reduce complexity so that us humans can understand, amend and enhance the software which is best done by not having to wade through tons of irrevelant detail.

To that end the program DriveCarToTown could be further broken down into more subroutines in order to make it even more managable and understandable.

  Sub GoToCar()
    Open House Door
    Walk Over To Car
    Insert Key In Car Door Lock  Open Car Door
    Sit down On Drivers Seat
  End sub

  Sub PrepareToDrive()
    Close Car Door
    Put on Seat Belt
    Insert Key Into Ignition
    Turn Key To Start Engine
    Put Right Foot on Brake
  End sub

  Sub DriveToEndOfDriveway()
    Release Hand Brake
    Engage Automatic Gearbox To Drive
    Ensure there are no obstacles in front of car
    Remove Foot From Footbrake
    Steer Car to end of driveway
  end sub

 sub TurnOntoMainRoad()
   Apply Foot Brake
   Check For oncoming Traffic
   Engage indicators and turn onto road
 end sub

How about rewriting the “DriveCarToTown” program to look like the next exhibit which shows us calling using the code in the illustration above. Now the code is much more human readable. In fact the code in the following is subroutines is valid vba (it’s just that the code in the called subroutines is not. e.g. “PrepareToDrive”)

  Sub DriveCarToTown()
    gotToCar
    PrepareToDrive
    DriveToEndOfDriveway
    TurnOntoMainRoad
  End Sub

Now when you look at the DriveCarToTown program you can see at a glance that it is broken into four main sections (A) GoToCar (B) PrepareToDrive (C) DriveToEndOfDriveway and (D) TurnOntoMainRoad.

The program is broken into bite sized chunks and you can quickly get to what ever piece of code interests you the most.

You can go to the “GetSuitFromDryCleaners” program and then narrow your focus to the “DriveCarToTown” subsection and quickly know that the part you are curious about is the “DriveToEndOfDriveway” subsection and then review the six highly relevent lines of detailed code there, namley…

  Release Hand Brake
  Engage Automatic Gearbox To Drive
  Ensure there are no obstacles in front of car
  Remove Foot From Footbrake
  Steer Car to end of driveway

So go ahead and try this now and let me know what you think.

I am happy to answer any quick "on topic" questions.

Remember writing this type of code is iterative, sometimes you will be “in the flow” of things and quickly churn out 100 lines of code. But when you are finished, go back and tidy it up.

For each grouping of lines, ask yourself ,what is the one name that describes what these lines are doing?

Then put them into a function of that name.

If you find the function that you created can be further broken down into smaller subroutines which do just one thing, then go ahead and keep subdividing for as long as you believe that it makes common sense to do so.

Ideally I would say that functions or subroutines should contain roughly 5 to 30 lines of code.