Reading from and writing to Ranges

VBA developers often need to read data in from Ranges and write results out to other Ranges. It’s a very common task in dashboards for example. The snag is that reading or writing 1 cell at a time can be slow, especially if you have a lot of input or output to deal with. However there are ways to work more efficiently with large tables.

Example 1 – Reading Ranges

Let’s say you’ve need to read some data from A1:A1000 on a worksheet into an array. One way to do this is as follows:

For i = 1 To 1000
    MyArray1(i) = Range("A" & i).Value
Next

However it’s actually very easy to achieve almost the same thing in 1 simple line of code:

MyArray2 = Range("A1:A1000").Value

There is a catch but – in my opinion – it’s a minor one. MyArray2 will actually have 2 dimensions, whereas MyArray1 is constructed with 1. In this example, MyArray2 will have the following bounds:

Dimension 1 ~ 1 To 1000 (representing the row)
Dimension 2 ~ 1 To 1 (single column)

So instead of using MyArray1(i) you now use MyArray2(i, 1). The speed boost can be significant and this next example should help to illustrate the point.

Example 2 – Writing Ranges

Create a blank spreadsheet and coopy the following macro into a blank Module. The idea is that in our code we first do some calculations and store the results in an array. When the code finishes, that array is displayed in cells on a worksheet. Obviously my example calculations are very simple but you could replace them with anything you wish.

Sub test()
    Const rMax As Long = 1000   'total rows
    Const cMax As Long = 100    'total columns

    Dim t As Single     'used to time how long each version takes
    Dim v As Variant    'array containing results of my calculations
    Dim r As Long       'row counter
    Dim c As Long       'column counter

    'This is my "calculation routine" ...
    ReDim v(1 To rMax, 1 To cMax)
    For r = 1 To rMax
        For c = 1 To cMax
            v(r, c) = r + c
        Next
    Next

    'Now let's create the output ...

    'The slow version!
    Cells.Clear
    t = Timer   'start the timer
    For r = 1 To rMax
        For c = 1 To cMax
            Cells(r, c).Value = v(r, c)
        Next
    Next
    MsgBox "Slow version took " & Round(Timer - t, 1) & " seconds"

    'Quick version
    Cells.Clear
    t = Timer   'restart the timer
    Range("A1").Resize(rMax, cMax).Value = v
    MsgBox "Quick version took " & Round(Timer - t, 1) & " seconds"
End Sub

On my PC I got the following messages:

Slow version took 5.4 seconds
Quick version took 0.1 seconds

..see what the comparison is on your machine.

(As a final note, if you do output these results 1 cell at a time then you may not need to store the results in an array at all. I’ve only structured it like this so I can directly compare the time taken creating outputs. In doing so, I hope this illustrates that there are occasions when this might be a good thing to do!)

Repetition

One issue that crops up surprisingly often in spreadsheets is repeating the same calculation.  I see two key problems with this:

1. Inefficiency.
2. Harder to maintain.

Inefficiency

Imagine you didn’t have a spreadsheet.You would never work out the same thing twice, especially if it wasn’t easy! You’d simply find where you had done it before and use it again.

You can do the same thing with spreadsheets. Just store the result in a cell (preferably an appropriately named one) and you can reuse it.

Maintenance

If you duplicate a calculation then you have to maintain every copy of it. Say something changes – perhaps a new parameter is added. Then you might change the calculation in one place but not the other. Or you might make a mistake in one of them. Removing duplication frequently makes it much easier to find and fix errors.

Like so many things it boils down to common sense. Have you experienced spreadsheets like these? Any other top tips? Let me know!

VBA Tip – Screen Updating

Say you’ve written some VBA code that does various things with a spreadsheet. It does exactly what you want so you’re essentially happy! However it may seem a bit slow or it just jumps around a lot on screen while it’s running. In short it does the job but it doesn’t look very professional.

Let’s face it – you really don’t want to change your code. It works! You just wish you could hide what was going on so that the user doesn’t get distracted (or worse confused) by things jumping around on screen. If the user could switch the screen off while the code ran then that would be great! That’s not going to happen but you can do the next best thing. Insert this line at the top of your lovely bit of code:

Application.ScreenUpdating = False

When Excel does something with a spreadsheet it redraws what’s shown on screen. This simple line of code prevents that happening. The rest of your code is still doing all the same things as before but Excel doesn’t draw any of it. This gives a really nice clean feel to a lot of processes.

At the end of your lovely code just remember to switch it back on again! Excel should do this for you but it’s a good habit to get into just in case something doesn’t quite go according to plan. (You can really confuse users if you forget!) The command is:

Application.ScreenUpdating = True

There is one added bonus, which you may have already guessed. Because Excel has got less work to do (no redrawing what’s on screen) you may actually find that the same bit of code runs faster. Which is always nice!