It’s worth taking a deeper look at the Range object, as it is a central element to reading, writing, and modifying data on a worksheet. This post uses a classic speed test to time the most efficient method of interacting with a spreadsheet.
This post within the Getting Started with VBA series finishes the focus on The Range Object. The previous two posts have discussed how versatile the range object is. Like many things in Excel, there are many ways to get to the same end result. When it comes to reading, writing, and modifying data on a worksheet, the range object provides many different options. Through the implementation of a speed test, this post provides a thorough analysis on three of those options. A detailed description of the speed test is provided below. A video at the end of this post walks through the speed test.
The following macro-enabled Excel file contains all the examples presented in this post. Macros must be enabled upon opening.
The Speed Test
A speed test has been implemented to test the amount of time it takes a VBA script to transfer data between cells in a spreadsheet. The speed test contains three different approaches: (1) using the Range Copy/PasteSpecial methods, (2) the Range Value property, or (3) the Cells Value property. In each test, three different values will be moved from one cell to another. In the first iteration, blank cells write the value. In each subsequent iteration, the values are simply overwritten.
For each approach in the speed test, two test types exist. The first is a single iteration test that simply goes through the script once. The second is a stress test, which forces the method to be repeated many times. The stress test requests for an input from the tester, which can range from 1 to 10,000 repetitions. In the test overview below, 100 iterations are used as the stress test. A video can be viewed below walking through the 100 iteration test.
Results from a 10,000-iteration test are also provided near the end of this post.
The Range.Copy and Range.Paste Methods
The first post on the range object provided an overview of copying contents from one cell to another using the Range object’s Copy and PasteSpecial methods. The end solution resulted in the final coding as presented here:
Application.CutCopyMode = False
This script accomplishes its goal — it copies text from 3 cells in column A and pastes values in 3 cells in column D. While this Copy/PasteSpecial actions appear to be only two commands in the VBA editor, there is a lot going on here. Just to highlight a few of the steps on the back end:
- On Copy
- Excel selects the copy range.
- Excel activates the clipboard.
- A call is made to the Workbooks.Worksheets.Range object to read value and formatting contents.
- Cell contents and format details are sent to the operating system.
- The operating system stores the copy contents in memory through a clipboard object.
- On Paste
- Excel selects the paste range.
- The operating system calls the clipboard object to pull copied value and formatting attributes.
- The operating system passes copied contents to Excel.
- A call is made to the Workbooks.Worksheets.Range object to write value and formatting contents.
- Excel closes the clipboard.
These steps are processor intensive and inefficient. Unfortunately, it’s extremely common to see the Copy/PasteSpecial methods used heavily in VBA scripts, as it is the Macro Recorder’s primary means of moving values around a spreadsheet. Even some experienced VBA developers will still rely on the copy/paste actions to move spreadsheet contents around. Many of us have encountered extremely useful macros that automate tedious processes. However, sometimes these macros are best started just before a long lunch break…
Additionally, other third party applications, which use the clipboard, may slow down these processes even further.
What does this mean for the speed test? Well, unfortunately it is not a good picture for the Copy/PasteSpecial methods. The single iteration test comes in at 62.5 milliseconds, which isn’t much time, but it’s obvious that time is wasted as it was clearly visible that cells are being activated.
62.5 milliseconds is negligible, so why even bother looking at this? If this was a larger script, or an operation that required the action to be repeated several times, this could become a noticeable issue. In the stress test, the VBA script was rerun 100 consecutive times. The total time required clocked in at almost 6 seconds — 5,929.7 milliseconds.
One other item to point out with this approach is that Excel became unresponsive around 80 iterations (see video around the 12 second mark) and took an addition 1 to 2 seconds to get back to a responsive state after the script was run. This would not be an acceptable result for a professional business application.
As an alternative to the Copy and PasteSpecial methods, the Value property is a property within the Range object which quickly and efficiently stores a cell’s value to memory and skips all the processor overhead experienced with the clipboard. Using the Value property, we’re able to streamline the code from the Copy and PasteSpecial methods as follows:
Range("F3").Value = Range("B3").Value
Range("F4").Value = Range("B4").Value
Range("F5").Value = Range("B5").Value
For one, this script changes the total lines of code from 10 lines to 6 lines. Also, by being clear cut, this code is much easier to maintain and will be more obvious to others on a team.
The one drawback of this approach is that only the value will be transferred. No formatting elements or formulas will be transferred. Regardless, there are also properties that will transfer cell formulas and formats more efficiently than the Copy and PasteSpecial methods.
Onto the results. The a single iteration did not register on the speed test at all, as it came in under the test machine’s CPU tick time in Excel (estimated around 8 milliseconds). For 100 iterations, the timer calculated 19.5 milliseconds — still too fast for a user to notice any processing time what-so-ever (this also means a single iteration takes about 0.2 milliseconds).
A quick overview of the benefits of the Value property over the Copy/PasteSpecial methods.
- Provides streamlined and easy to read code.
- Skips the slow windows clipboard and the processes that interact with it.
- Does not show any screen flicker or cell selects.
- Does not make Excel unresponsive after the script completes its execution.
The final test does not directly involve the range object. The Cells property is a property that can exist under both the Worksheets object or the Range object. However, the Cells property does return a Range object, meaning that any method or property associated with the Range object can also be used with the Cells property. The script used is as follows:
Cells(3, 7).Value = Cells(3, 2).Value
Cells(4, 7).Value = Cells(4, 2).Value
Cells(5, 7).Value = Cells(5, 2).Value
This script is very similar to the Range object version of the script, but the Cells property is slightly more efficient than the Range object. As a result, we do have a slightly faster run time.
Again, the single iteration came in under the test computer’s CPU tick time with Excel, so it didn’t even register. The 100-iteration stress test took the same amount of time as the Range.Value approach at 15.6 milliseconds. Given that Excel only registered 2 CPU ticks on both tests, it’s worth testing a larger sample. The next section will test all three approaches using 10,000-iterations.
Further Testing of Range and Cells
The time savings between the Value Property and the Cells Property was a too negligible for the test machine to notice with the 100-iteration test. Given that, it is worthwhile to force a more stringent test to see a better comparison. This test also includes a 10,000-iteration test of the Range.Copy/PasteSpecial method.
As can be seen in the 10,000-iteration test, the Cells.Value property approach is about 170 milliseconds faster than the Range.Value property approach. This difference would not be noticeable to the user, so we can only call the Cells.Value property slightly more efficient. The Cells.Value property does have some other advantages, however — It’s biggest being that it uses a column index number as opposed to a column letter. This makes running loops–particularly those that cycle through columns–much easier (loops will be discussed later in this series). Otherwise, unless you have a massive project that needs to cycle through hundreds of thousands, or millions of data points, either the Range.Value or Cells.Value approaches will be fine.
We can’t forget about the Copy/PasteSpecial method test. The 10,000 iteration stress test came in at an abysmal 743,781 milliseconds, or about 12 and 1/2 minutes. It’s also worth noting that if we were to derive 10,000-iterations from the previous 100-iteration test (5,929.7 x 100), we would ascertain that the 10,000-iteration test would come in at 592,970 milliseconds, or over 2 1/2 minutes faster than the actual 10,000-iteration test. However, the law of diminishing returns comes into play here, as computing limitations–such as heat–impact speed during processor intensive operations.
Speed Test Video and Download
Video showing the single and 100-iteration tests.
Stress Test Notes
- In addition to the core script that was being test (scripts that were displayed in the example above), additional operations were added to the test scripts for timing and looping. These operations added some overhead to each iteration, estimated at around 0.15-0.20 milliseconds per. Regardless, identical methods of timing and looping were applied to each test, making the comparisons valid.
- Test results will vary based on processor speed and several other factors. The tests in this post were done on a laptop with a Skylake i7-6700HQ at 2.6GHz, 12GB RAM, Windows 10, Excel 2016.