How To Use VBA Functions And Sub Routines In Excel To Minimise Your Coding

Using functions and sub-routines in VBA can be confusing for new Excel developers. But efficient organization of your code can be dramatically improved by the use of functions and subs.

What Are VBA Functions And Sub-Routines?

A function or sub is simply a way to minimize unnecessary coding and make your code easier to follow. Here's a simple example of a sub which clears the contents from sheet(1).

Sub test()

' branch to new sub here
clearContents(1)
end sub

sub clearContents(s)
sheets(s).clearContents
end sub

The difference between a sub and function is that a sub generally performs a task whereas a function returns a value. Here's a function that tells you how many rows are in a current region:

Sub test()
' sheet no. and the active cell is passed to the function
noRows= curRegRows(1, "a1")
End Sub

Function curRegRows(s As Long, r As String)
Sheets(s).Activate
Range(r).Activate
ActiveCell.CurrentRegion.Select
curRegRows = Selection.Rows.Count
End Function

Fairly straightforward and you might wonder why bother with the extra sub. The truth is, most coding is written "on the fly" as most programming tasks appear simple to begin with. But in the code to clear the content from a sheet,we might also need to reset formatting, column widths and delete any charts as well:

Sheets(1).Activate
With Cells
.ClearContents
.ClearFormats
.ColumnWidth = 10
End With

x = ActiveSheet.ChartObjects.Count
If x > 0 Then

For Each s In ActiveSheet.ChartObjects
s.Delete
Next
End If

The code can quickly become difficult to follow and yet all it's doing is clearing a worksheet before the rest of the code can run.

Organizing Functions And Subs

The way forward is to create your own code library where you save fully tested functions and subs to be called as needed by any new code.

In the above scenario where we wanted to clear the contents from sheet(1), instead of writing the code, we could simply call the sub which is saved elsewhere in the code window.

Sub test()
clearContents(1)
end sub

The idea is for your main sub to be a series of statements rather than a jumble of code. A typical scenario might be that you are importing data from another Excel file and creating a chart from the data:

Clear contents from sheet(1)Import the dataCreate a chart from the new data

With your subs and functions already written your main routine should look something like this:

Sub refreshData()
clearContent(1)
importData(filename)
createGraph(columnNos)
End sub

Summary

There are many reasons why it makes sense to use functions and sub-routines. Apart from reducing errors and bugs, organizing your code efficiently makes your own programming easier to follow.

Andy L Gibson is a former Web Site programmer rediscovering his interest in Excel applications for small business. His blog at http://solutions4business.wordpress.com/ holds the VBA code for all of his articles on Excel and is available for FREE download. He is developing an Excel application which will contain working examples of VBA methods and solutions.

View the original article here