Research IT Application Development


Excel Performance

Assigning Values to a Range of Cells

When there is a need to set the values to a lot of cells in a worksheet, the temptation can be to simply use a loop to assign them. In the following examples, assume WkS is Set to an existing worksheet in an open workbook. To assign the numbers 1 to N, where N = 100, in the first 100 cells of column A, one might write:   

Dim WkS  As Excel.Worksheet
Dim Ix As Integer
Dim N As Integer

N = 100
For Ix = 1 To N

WkS.Cells(Ix, 1) = Ix


The performance on the above code will be slow. Instead of setting each cell value, calculate all the cell values in an array and assign the array to a range.

First create a function to return an array of values:

Public Function ACounter(ByVal N As Integer) As Integer()

Dim iCtr()     As Integer     
Dim Ix         As Integer
ReDim iCtr(1 To N, 1 To 1)
For Ix = 1 To N
        iCtr(Ix, 1) = Ix
ACounter = iCtr

End Function

Now use the function to assign the values to the range:

Dim WkS   As Excel.Worksheet
Dim R   As Excel.Range
Dim N    As Integer

N = 100
Set R = WkS.Range("A1:A" & N)
' Setup the array to populate the count column
R.Value = ACounter(N)


This will perform much faster because the VB code has to reference Excel only once instead of N times.

Return to previous page