Writing To A Range Using VBA
If you need to use a VBA procedure to write values to a range, most people would probably create a loop and write the values one cell at a time. Like this:
' Fill a range by looping through cells
Dim CellsDown As Long, CellsAcross As Long
Dim CurrRow As Long, CurrCol As Long
Dim StartTime As Double
Dim CurrVal As Long
' Change these values
CellsDown = 500
CellsAcross = 200
Cells.Clear
' Record starting time
StartTime = Timer
' Loop through cells and insert values
CurrVal = 1
Application.ScreenUpdating = False
For CurrRow = 1 To CellsDown
For CurrCol = 1 To CellsAcross
Range("A1").Offset(CurrRow - 1, CurrCol - 1).Value = CurrVal
CurrVal = CurrVal + 1
Next CurrCol
Next CurrRow
' Display elapsed time
Application.ScreenUpdating = True
MsgBox Format(Timer - StartTime, "00.00") & " seconds"
End Sub
On my system, writing 100,000 values using a loop takes 9.73 seconds.
A faster way to accomplish the task is to put the value in an array, and then transfer the array to the worksheet. The procedure below writes 100,000 values in 0.16 second -- about 60 times faster than the looping method.
' Fill a range by transferring an array
Dim CellsDown As Long, CellsAcross As Long
Dim i As Long, j As Long
Dim StartTime As Double
Dim TempArray() As Double
Dim TheRange As Range
Dim CurrVal As Long
' Change these values
CellsDown = 500
CellsAcross = 200
Cells.Clear
' Record starting time
StartTime = Timer
' Redimension temporary array
ReDim TempArray(1 To CellsDown, 1 To CellsAcross)
' Set worksheet range
Set TheRange = Range(Cells(1, 1), Cells(CellsDown, CellsAcross))
' Fill the temporary array
CurrVal = 0
Application.ScreenUpdating = False
For i = 1 To CellsDown
For j = 1 To CellsAcross
TempArray(i, j) = CurrVal
CurrVal = CurrVal + 1
Next j
Next i
' Transfer temporary array to worksheet
TheRange.Value = TempArray
' Display elapsed time
Application.ScreenUpdating = True
MsgBox Format(Timer - StartTime, "00.00") & " seconds"
End Sub
Joe S:
The same concept is also efficient for modifying large blocks of data on a sheet
such as using the Trim function on many cells.
Sub test1()
Dim myRange
Dim lngctr As Long
myRange = Range("A1:A10000").Value
For lngctr = LBound(myRange) To UBound(myRange)
myRange(lngctr, 1) = Trim(myRange(lngctr, 1))
Next
Range("A1:A10000").Value = myRange
End Sub
Sub test2()
4 December 2006, 12:41 pmDim myRange As Range
Dim lngctr As Long
For Each myRange In Range("A1:A10000").Cells
myRange.Value = Trim(myRange.Value)
Next
End Sub
MacroMan:
Thanks John, great tip.
4 December 2006, 12:41 pmDave:
Here are my times:
first one: 4.88 sec
4 December 2006, 12:44 pmsecond one: .11 sec
John Walkenbach:
The times I gave were for Excel 2007. Excel 2003 is a bit faster: 7.49 / 0.10
4 December 2006, 2:07 pmKevin Fitting:
This works great for data... any ideas on cell formatting? I have a script where I read all data and cell formats into an array, sort the array, then put the data and formats back on the sheet. I would like to use the assign array to sheet method but I have to put the formats on as well and they are considerably slower using the cell by cell method!
Kevin
4 December 2006, 7:14 pmHuaming Jian:
Is there a way of transfering one portion of the array to one worksheet? For example, copy the first three columns to one sheet and the rest to another sheet.
Thanks for the great tips, John.
Huaming
4 December 2006, 10:35 pmjkpieterse:
AFAIK, you can only use the Formula and the Value property to bulk-read and write to/from an array
5 December 2006, 6:08 amJon Peltier:
Kevin -
If discontiguous collections of cells need to have the same formatting applied, you could build up a range using Union(), and then apply the particular formatting to all affected cells in one step. A bit more coding, but if it is reduces the perceived wait, it's probably worth it.
5 December 2006, 6:35 amJon Peltier:
Huaming -
Between reading and writing, put a loop that splits TempArray into TempArray1 and TempArray2, then dump each into the respective worksheets.
5 December 2006, 9:09 amHuaming:
Hi, Jon,
I made two arrays (DataArray1 and DataArray2) by spliting a big one as you said and dumped them into a chart by using:
ActiveChart.SeriesCollection.NewSeries
n = ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(n).XValues = DataArray1
ActiveChart.SeriesCollection(n).XValues = DataArray2
Somehow the chart would not take these two arrays, while I can paste them onto a worksheet. Is there something special that I have to do when transferring array into a chart? I'd like to use the array technique for charting to avoid showing large area of data on the worksheet.
Thank you very much and best regards,
Huaming
5 December 2006, 2:06 pmJon Peltier:
Huaming -
What you have to do is make sure the array is less than about 253 characters long when written like this:
={1.234567890123,2.345678901234,3.456789012345}
This means truncating as many characters as necessary:
={1.234,2.345,3.456}
Even with truncating, your limit is around 125 points, which assumes single digit integer values. Not very useful. Go one step further and dump the arrays into the worksheet, and point the chart series at these ranges.
6 December 2006, 7:06 amHuaming:
Hi, Jon,
Indeed the series that I made using the array techniques exceeded way too much than the 253 characters limits. It worked fine when the data are transferred into a worksheet and have the chart to retrieve data from there. It is just a little inconvenient to have a lots of data showing up on the sheet. I have to put them away from the viewing area.
Thank you very much for your timely and on-the-spot advice.
Huaming
6 December 2006, 9:12 amJon Peltier:
Use a hidden sheet for the chart data.
I seem to have fielded this question about a dozen times in the past week on various forums. Maybe I should write up a better summary on my site, so I can just point people to that.
6 December 2006, 10:11 amHuaming:
Please DO write up a "cheat sheet" summary for this, Jon. I have learned quite a few of charting tricks from you. My most favorite one is how to make "scientific/exponential notation (with superscript formating)" labels on X-Axes. I earned a cup of Star Bux coffee by showing it to my colleague. Not any more. We always appreciate your good work.
Huaming
6 December 2006, 11:18 amDM/Diddy:
I had someone else's code last week that copied a large amount of data from an Access query to an Excel sheet. 11,000 records, 10 fields/ record. He did it field by field, line by line. Needless to say, this took over 20 minutes to run.
I rewrote it using DAO's recordset GetRows method, which can copy the entire results of a query into a variant array. After transposing it (couldn't find a shortcut for this), I just plunked it into a range using your same method. Result: it takes less than a minute now. Sweet!
6 December 2006, 4:47 pmDick Kusleika:
DM/Diddy:
I think:
7 December 2006, 8:14 amRange("MyExcelRange").Value = Application.Transpose(vaMyArray)Randy Harmelink:
The amount of difference between the two timings will increase as you add additional calculations to the spreadsheet as well. Especially if you are writing to a range that is used in other calculations in the workbook. Did you try a timing on the first loop with recalculation turned off until after the entire range has been posted? I'm not sure if it will make much of a difference in an otherwise empty workbook...
15 December 2006, 6:13 pmGary Winey:
Thanks JoeS for comment 1. I have been using this technique for a long time to write or read large blocks of data to an Excel sheet but I never thought about using it in other contests as well. Thanks for helping me think outside the box!
20 December 2006, 10:21 amNitesh:
Can i do the same thing in a function and not a "sub"?
8 January 2007, 11:38 amBill Grissom:
I just discovered the same issue in passing arrays to an Excel Chart Object using ".XValues =" and ".Values =", during my first Excel VBA project (have used QB45 & VB6 for years). Jon's explanation of a 253 character limit makes sense of my tests where I hit different limits (from 15 to 117 pts) depending on what the exact values were (# characters in the numbers). Not sure why I found no explanation on MSDN. As Hauming, I would prefer not passing values via a worksheet, both for speed and simplicity, but will use a hidden area for now. Microsoft KB #139401 article discusses the inverse problem of reading existing Chart values into an array. You must transpose the receiving array or define it as a 2-D array ValOut(npts,1). I tried several variations on this theme to input values, but no luck.
10 January 2007, 4:46 pmDoug Jenkins:
Nitesh - in a word, yes.
Declare the function as a variant
Redim it to the required size
Put your data in the array
finally:
MyFunction = MyArray
Enter the function as an array formula, or use Index() to access a sub-set of the elements.
11 January 2007, 11:07 pmDoug Jenkins:
Correction: Dimension the VBA array to the right size, the function itself just needs to be declared as a variant.
11 January 2007, 11:11 pmadam:
I am trying to create a chart with 1 set of x-values, but 2 different y values so I have two data sets. The problem is that at a given x-value, one of the y-values produces an error while the other does not. If I chart the two lines, any y-values with this error, "#N/A N.A.", will show up as zero on the chart, hence ruining any trendline. How do I NOT include these data points that have errors using code?
19 January 2007, 11:59 amErik Eckhardt:
It's not Application.Transpose. It's
Application.WorksheetFunction.Transpose(vaMyArray)
22 February 2007, 11:55 amErik Eckhardt:
Also, be aware that there are limitations of passing arrays to Excel ranges, including using the built-in Transpose function. See http://support.microsoft.com/kb/177991 for more information. Anyone who considers himself an Excel programmer should read this article as it also touches on some general concerns about data types, as well as using Excel through COM.
I am getting a type mismatch error when trying to transpose an array created by GetRows, which is strange because it is only 12 x 713 in size, and an array 21 x 23040 from a different recordset transposes just fine, using the exact same code (it's just a different query). I can't quite figure it out. I think I'll have to write my own transpose function using the CopyMemory Windows API function.
22 February 2007, 1:31 pmJocelyn Paine:
John,
That's a very useful trick you posted - thanks! Can a similar trick be used to quickly fill a range with formulae? jkpieterse says "AFAIK, you can only use the Formula and the Value property to bulk-read and write to/from an array". I tried the Formula property, by altering your code as follows:
...
Dim TempArray() As String
Dim CurrVal As Long
Dim CurrValAsString As String
...
For i = 1 To CellsDown
For j = 1 To CellsAcross
CurrValAsString = "=" & CurrVal
TempArray(i, j) = CurrValAsString
CurrVal = CurrVal + 1
Next j
Next i
...
TheRange.Formula = TempArray
This (on Office Excel 2003), didn't update the formulae, but just put the string values into the cells. So A1 became "=0", and so on.
19 March 2007, 4:45 amJon Peltier:
Jocelyn -
If each cell in the range has the same formula, .Formula applies the formula as a formula. If the cells receive different formulas, .Formula works like .Value, entering the formula as text. You need to replace all = in the range with =, which re-enters the formulas as formulas:
Selection.Formula = "=row()+column()"
End Sub
Sub TrickyFmla()
Dim sFmla(1 To 3, 1 To 3) As String
sFmla(1, 1) = "=row()+column()"
sFmla(2, 1) = "=12/Row()"
sFmla(3, 1) = "=1/row()/column()"
sFmla(1, 2) = "=sin(row()*pi()/180)"
sFmla(2, 2) = "=4^3"
sFmla(3, 2) = "=ln(10)"
sFmla(1, 3) = "=cos(row()*pi()/180)"
sFmla(2, 3) = "=sqrt(2)"
sFmla(3, 3) = "=na()"
With ActiveCell.Resize(3, 3)
.Formula = sFmla
.Replace "=", "=", xlPart
End With
End Sub
Charles Williams:
Jocelyn,
It works fine if you define the array of formulae as a variant, then you dont need to replace = with =
so in Jon's example use
Dim sFmla(1 To 3, 1 To 3) As variant
20 March 2007, 2:49 amJon Peltier:
Charles -
"Dim sFmla(1 To 3, 1 To 3) As Variant"
Doh! I knew I'd done this without the .Replace "=", "=" piece, but I couldn't recreate it on the fly.
20 March 2007, 5:56 amDavy:
With ActiveCell.Resize(3, 3)
21 March 2007, 12:15 am.Formula = sFmla
.Value = .Value 'This jsut works fine.
'.Replace "=", "=", xlPart
End With
Jon Peltier:
Davy -
Since the formula is only in the cell as a text string, .Value=.Value reenters the formula, the same way that replacing the equals sign does. I suppose someone could go through to see which approach (.value=.value, replace =, variant array) is fastest, but I'll just use the variant array, because it's easiest.
21 March 2007, 8:08 pmPatrick O'Beirne:
Just to point to a limitation of the array method: no element can have more than 911 characters, if it has the write stops at that point.
See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;818808&Product=xl2003
You may receive a "Run-time error 1004" error message when you programmatically set a large array string to a range in Excel 2003
This issue may occur if one or more of the cells in an array (range of cells) contain a character string that is set to contain more than 911 characters.
http://support.microsoft.com/?scid=kb;en-us;832136
24 May 2007, 2:14 amData may be truncated when you transfer array data to cells in an Excel worksheet
This problem may occur when one of the following conditions is true:
• In Excel 2007, the VBA array is longer than 8,203 characters in length.
• In Excel 2003 and in earlier versions of Excel, the VBA array is longer than 1,823 characters in length.
To work around this problem, populate each cell in your worksheet one at a time from the array, instead of populating the whole range at one time.
166342 (http://support.microsoft.com/kb/166342/) Description of the limitations for working with arrays in Excel
Jon Peltier:
"To work around this problem, populate each cell in your worksheet one at a time from the array, instead of populating the whole range at one time."
Kind of negates the speed benefits of array-to-range, doesn't it?
I can think of an alternative, though. Make a second array the same dimensions as the first. Populate array 2 with the long elements, and remove them from array 1. Dump array 1 to the range as before, then only populate the cells that have long elements in array 2.
24 May 2007, 5:12 amPatrick O'Beirne:
"Dump array 1 to the range as before, then only populate the cells that have long elements in array 2."
Yes, the computation time to skip long elements is less than the paste time.
I did some speed tests and after saving time by
.calculation=xlmanual
.screenupdating=false
the array method is still about 20 times faster than writing individual cells. The times vary randomly a lot, which I presume is because so many background tasks are going on as well in the PC. I'd need to prepare a special test PC for real testing, but the difference is still clear enough.
25 May 2007, 2:51 amskirby:
Can someone help me to understand what it wrong with the following code? It outputs all 0's. I've copied the code at the top of the page here, and it works fine. But when I alter it to my own needs, it doesn't work. I know it's something simple, but my simple mind is still lacking.
Sub temp()
Dim numbers(100) As Single
Dim i As Integer
Dim therange As Range
Set therange = Range("A1:A100")
For i = 1 To 100
numbers(i) = Rnd * 100
Next
therange.Value = numbers
7 June 2007, 10:05 amEnd Sub
Dick Kusleika:
skirby: Arrays that you write to ranges must be two dimensions: rows and columns.
Dim numbers(1 To 100, 1 To 1) As Single
Dim i As Integer
Dim therange As Range
Set therange = Range("A1:A100")
For i = 1 To 100
numbers(i, 1) = Rnd * 100
Next
therange.Value = numbers
End Sub
skirby:
Nevermind. I figured it out. Since arrays in Excel are horizontal, I had to transpose the array to get it to populate the range of cells correctly. This is a great site, though.
7 June 2007, 11:31 amskirby:
Thanks for the fast response. The following also works:
Sub temp()
Dim numbers(1 To 10) As Single
Dim i As Integer
Dim TheRange As Range
Set TheRange = Range("A1:A10")
For i = 1 To 10
numbers(i) = i
Next
TheRange.Value = Application.WorksheetFunction.Transpose(numbers)
7 June 2007, 11:33 amEnd Sub
pcfremont:
Does this work for array of string? I tried but didn't work. Is there a way to get around without using loop.
Thanks,
6 August 2007, 10:33 pmpcfremont:
Well, it worked when I use the transpose function.
6 August 2007, 10:38 pmNeha Gupta:
Hi,
This is Neha Gupta.
4 October 2007, 12:05 amPlease tell me how to populate the data from VBA to Multiple Excel sheets based on your criteria.
Shady Hassan Aly:
If you are working with recordsets:
11 October 2007, 12:42 amPut the whole recordset into multidimentional array in one step...this is at least 10 times faster than doing using a recordset to get data in a loop..
Max:
Hi, can someone tell me wht is wrong with my code (see below). I am trying to use the above example of filling an array within VBA and then later pasting it into Excel.
So far, it only deposits cells full of zeros!
I am a newby at VBA:)
Sub Plot_kc_disp()
'this will enable plotting of kc/km with displacement using the Newmark sliding block program
'
Dim kc As Single 'critical acceleration
Dim km As Single 'maximum acceleration (ie PGA)
Dim R As Integer 'Row number
Dim MaxD As Single 'Max displ
Dim myRange As Range 'output range for results
Dim StartTime As Double 'timer
Dim TempArray() As Double 'temporary array
R = 1
kc = 0
'speed tweaks
Application.ScreenUpdating = False
Let km = Worksheets("Input data").Range("G8")
' Record starting time
StartTime = Timer
' Redimension temporary array
ReDim TempArray(1 To 300, 1 To 2)
' Set worksheet range
Worksheets("Output Sheet").Select
Set TheRange = Range(Cells(1, 1), Cells(300, 2))
Do While (kc / km)
30 October 2007, 1:59 pmJ.O.:
myRange = Range("A1:A10000").Value
doesn't work; only gives an error.
20 November 2007, 2:15 pmDick Kusleika:
JO: What did you Dim myRange as?
20 November 2007, 5:21 pmSree:
Can anyone tell me whats wrong with this code..I cant seem to get all the values of array a. the code results in a(1) being written a 100 times..
Thanks
Sub test()
Dim a() As Variant
Dim ranger As Range
For i = 1 To 100
ReDim Preserve a(1 To i)
a(i) = Rnd()
Next i
Set ranger = Worksheets("Sheet1").Range(Cells(1, 1), Cells(100, 1))
6 May 2008, 9:34 amranger.Value = a
End Sub
Doug Jenkins:
Sree
The problems is that if you declare a one dimensional array it is treated as being equivalent to a row rather than a column.
There are two ways you can fix your code; either make the range 1 row x 100 columns, or declare the array as 100 x 1.
So either change the set ranger line to:
Set ranger = Worksheets("Sheet1").Range(Cells(1, 1), Cells(1, 100))
or change as below:
Dim ranger As Range
ReDim Preserve a(1 To 100, 1 to 1)
For i = 1 To 100
a(i,1) = Rnd()
Next i
Note that the redim needs to be outside the For Loop, because you can only redim preserve the last dimension, but unless there is some reason why you need to redim every cycle of the loop, that is the better place for it to be anyway.
6 May 2008, 11:55 pmJon Peltier:
Or transpose the array when writing it:
ranger.Value = WorksheetFunction.Transpose(a)
7 May 2008, 5:20 am