Wednesday, August 1, 2012

Using The Timer Function In Excel To Find The Best Solution To VBA Problems

Usually there is more than one solution to any given problem facing Excel developers. For example, you could use the range method to select cells or the current region function.

Fortunately, Excel provides the functionality to test your preferred method for speed and this article explains how to use the timer function to compare different strategies.

Scenarios For Speed Testing

One example might be to determine the best way to total a selection of cells using VBA. You might loop through each cell and add to a sub-total or use the in-built sum function directly in code. Common sense will tell you the sum function will be faster but let's work through the timer function and put it to the test.

Our scenario involves adding the values of 1500 separate cells.

First, we'll create two separate functions which will hold the different solutions.

function TimeSum() ct = Application.WorksheetFunction.Sum(Range(Selection.Address)) end function function timeCells() total=0 for x=1 to selection.rows.count total=total + activeCell.offset(1,0).value next end function

Now we'll create a procedure that will select the range, and time the two functions separately.

sub test startTime = timer Application.Run ("timeCells") endTime = timer elapsed = endTime - startTime Debug.Print a(x); elapsed startTime = timer Application.Run ("timeSum") endTime = timer elapsed = endTime - startTime Debug.Print a(x); elapsed end sub

As you might expect, the sum function is much faster and the timer returns a value of 0 seconds.

timeCells 0.0390625 timeSum 0

To get a more accurate figure, we'll increase the iterations to 50, meaning each function totals the cells 50 times. We can pass a string which tells the function how many times to perform the calculation, allowing us to extend the testing over a number of iterations.

In this scenario the timer returns a more meaningful result.

timeCells 2.191406

timeSum 0.0078125

With the number of iterations greatly increased, the sum function has taken less than.01 seconds to complete the task, whereas looping through each cell took over 2 seconds.

This scale of difference could be particularly relevant in large spreadsheets or in cases where you are using one worksheet to update another.

Summary

In most cases whichever method you use to solve a problem doesn't affect performance noticeably until the iterations become sizeable. However, it's good programming practice to ensure you're using the optimum method for the task at hand.

No comments:

Post a Comment