VBA TIPS
Getting values from a worksheet as an array, and putting it back into a worksheet

Usually when you'd like to get or to change the value in a cell, you can get the value from the cell and can write it back to the cell via direct access to the cell object. For example, this code

  Range("A1").Value = "Sample"  

would output the string "Sample" into Cell A1.

What if you'd like to repeat the same thing from cell A1 to cell A10000? Is there a better way to do this? An easy way would be to use a loop, but repeated access to many cells can take a long time.

In this case, an array might be best way to go. The values (and formulas) in the cells can be entered into a Variant type variable into an array at a stroke. It can also write the values back very quickly.

The following program substitutes values from A1 to A10000 cells for a variant type variable named buf.

Option Explicit


Sub Macro1()
    Dim buf As Variant

    'Get values as an array
    buf = Range("A1:A10000")

    Stop    'See your Locals window
End Sub



Run this code then have a look at the Locals window in the VBE (Visual Basic Editor). If you cannot find the Locals window in your VBE, click View > Locals window from the menu bar of the VBE.

Then, to expand the buf, click the + mark left to the buf.

Your screen should show as follows.



Looking at the buf entry in the Locals window, you can recognize it as Variant/Variant(1 to 10000, 1 to 1). It means the values on the worksheet has been entered into buf as a 2D array.

The size of this array can be thought of as 1 in an horizontal axis and 10000 in a vertical axis. As you can guess, it's exactly the same size as the range of the cells.

Next, let's change the values in the Variable named buf in the program code.

Example

The following program gets the values from cells A1 to A10000 as a variant type variable named buf, then adds a string "- modified" to each value and writes it back to the worksheet again.

Place the following in a standard module.

Option Explicit


Sub Macro2()
    Dim buf As Variant
    Dim i As Long

    'Get values as an array
    buf = Range("A1:A10000").Value

    'loop through the array and add a string to each element
    For i = LBound(buf, 1) To UBound(buf, 1)
        buf(i, 1) = buf(i, 1) & " - modified"
    Next

    'Put the array back into the worksheet
     Range("A1:A10000").Value = buf

End Sub


Please observe the setting method of the dimension of a Lbound function and a Ubound function in this code. LBound(buf, 1) retrieves the lower limit of the first dimension the horizontal axis, UBound(buf, 1) retrieves the upper limit of the first dimension of the vertical axis. If a dimension is omitted, a default value of 1 is assumed.

In other words, the first dimension stands for ROWS, the second dimension stands for COLUMNS.(shown in the picture below)

The structure of a 2D array


Since a worksheet has two dimensions as shown in the above picture, if both 2-dimensional arrays are the same size, it can be returned as it is. (See the following line)

  Range("A1:A10000").Value = buf  

As you can see it on the Locals window, the variable named buf exists as 1D array in the following code. But as I mentioned before, basically, it must be a 2D array when written in the worksheet.

The reason why I wrote it's basically, because 1D array is calld a vector data, and it's like a single row of data. Imagine the data has no row number but only has column number. Since 1D array can be one row, so 1D array can be returned to the single row as it is.

  Range("A1:C1").Value= Array("1,2,3")  

Example

Place the following in a standard module.

Option Explicit


Sub Macro3()
    Dim buf As Variant
    buf = Array(1, 2, 3)
    Range("A1:C1").Value = buf
    Stop    'See your Locals window
End Sub




Changing the subject... how can you change a 1D array into a 2D array? There is an easy way, using an existing Function for Excel.

It is possible to exchange dimensions with the TRANSPOSE function. Please note, for Excel 2000 and earlier versions, the TRANSPOSE function returns an error when the upper bound is bigger than 5461.



Take a look at the picture of the Locals window below, a 1D array is changed into a 2D array. Then you can write it into the worksheet.

Example

Place the following in a standard module.

Option Explicit


Sub Macro4()
    Dim buf As Variant
    buf = Application.Transpose(Array(1, 2, 3))
    Range("A1:A3").Value = buf
    Stop    'See your Locals window
End Sub



| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo