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:
Sub LoopFillRange()
' 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
' 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.
Sub ArrayFillRange()
' 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
' 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
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()
Dim myRange As Range
Dim lngctr As Long
For Each myRange In Range(”A1:A10000″).Cells
myRange.Value = Trim(myRange.Value)
Next
End Sub
Thanks John, great tip.
Here are my times:
first one: 4.88 sec
second one: .11 sec
The times I gave were for Excel 2007. Excel 2003 is a bit faster: 7.49 / 0.10
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
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
AFAIK, you can only use the Formula and the Value property to bulk-read and write to/from an array
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.
Huaming -
Between reading and writing, put a loop that splits TempArray into TempArray1 and TempArray2, then dump each into the respective worksheets.
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
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.
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
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.
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
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!
DM/Diddy:
I think:
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…
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!
Can i do the same thing in a function and not a “sub”?
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.
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.
Correction: Dimension the VBA array to the right size, the function itself just needs to be declared as a variant.
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?
It’s not Application.Transpose. It’s
Application.WorksheetFunction.Transpose(vaMyArray)
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.
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.
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
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
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.
With ActiveCell.Resize(3, 3)
.Formula = sFmla
.Value = .Value ‘This jsut works fine.
‘.Replace “=”, “=”, xlPart
End With
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.
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
Data 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
“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.
“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.
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
End Sub
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
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.
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)
End Sub
Does this work for array of string? I tried but didn’t work. Is there a way to get around without using loop.
Thanks,
Well, it worked when I use the transpose function.
Hi,
This is Neha Gupta.
Please tell me how to populate the data from VBA to Multiple Excel sheets based on your criteria.
If you are working with recordsets:
Put 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..
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)
myRange = Range(”A1:A10000″).Value
doesn’t work; only gives an error.
JO: What did you Dim myRange as?
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))
ranger.Value = a
End Sub
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.
Or transpose the array when writing it:
ranger.Value = WorksheetFunction.Transpose(a)
At what point does this method become worthwhile? I am only dealing with 30 values, would I see an improvement? Just as some background, my behemoth spreadsheet is used (partly) for marking an attendance roll and writing a single character to each of 30 cells seems to be taking *forever*. I’m using Excel 2007.
Debra - if writing to 30 cells individually is taking forever then you should see a huge improvement writing them in one go as an array.
At what point does it become worthwhile? I’d say 2 or more cells.
I’m with Doug. If I need to read or especially write more than a single cell, I write extra VBA code (which executes rapidly) to create an array to minimize range access operations (which execute slowly).
It can be a different animal using String arrays.
TheRange.Value = TempArray can take as long as writing to each cell.
The funny thing about this thread is that you’ve got people who make their living writing VBA vs. people who make their living easier using VBA. I am sadly among the latter group, and the difference is pretty significant. When your job involves delivering a polished end product, and speed is absolutely a component of that, I agree with Jon completely. But the more common use of VBA is taking a manual process and automating it. That fact alone makes it at least 10x faster and probably 100 - 1000x faster. If spending 3x as long yields a 10,000 performance gain, it is unnoticed, unappreciated, and not worth the time for most people. Personally, unless I’m dealing with a maddeningly slow range write (i.e. it’s still not done once I’ve refilled my coffee), it’s not worth the effort to make it marginally faster.
To put it another way…two hours of coding to save 30 minutes once a week pays off in a month. 3 hours of coding to save 30 1/4 minutes once a week takes a month and a half. I could also add that that extra hour of coding is nowhere near as fun or rewarding as the magic that happens during the first 2. Anything that takes the fun out of coding is counterproductive in the long run.
zach - I’m in the same position as you; all my programming is to get my engineering done quicker/better, and almost all of it is only used by me, but I really don’t see where the extra time comes in. If you get used to using arrays and writing them in one operation to the spreadsheet the difference in coding time is negligible, in fact it would probably take me longer to do it any other way, because I’d have to think about how to do it.
The other thing is that old spreadsheets of mine that write one cell at a time are really annoying, even if they only waste a few seconds each time.
Doug is exactly right. Once you absorb a “best practice”, you tend to use it again and again. It doesn’t cost anything when you reuse it, because you do it right the first time. I’ve been writing VBA professionally for over five years, but I have been using arrays to exchange data with the worksheet for over ten years. I’ve been avoiding the Object.Select/Selection.Action code found in recorded macros for almost as long.
These kinds of discussions would be moot if one were to follow the “best best practice,” that being to modularize code.
Some may have read or heard about the concept of a “data abstraction layer” in the context of interacting with a database. Well, an Excel range *is* a database. A long time ago I wrote an abstraction layer for my code to interact with an Excel range. By modularizing this code, I don’t have to debate about best practice or coding time or debugging overhead. The abstraction layer works. I don’t have to test it. It becomes a case of ‘drop and use.’
As an aside, there are many routines that I never test when I use them. All the code to create a menu for an add-in is ‘drop and use.’ All the code to distinguish between RibbonX and non-RibbonX environments is ‘drop and use.’ All the code to implement help or link to my website or show an up-to-date copyright statement or… or… or… is ‘drop and use.’ If you asked me how one of my routines worked, I’d have to shrug and reply “I don’t know. The last time I looked at the code was 5 years ago - if not longer.”
Below is a simplified version of the 2 routines that constitute the abstraction layer. Since I simplified the actual code for illustration purposes, I may have introduced some glitches but hopefully not.
The mapInput function takes an Excel range and returns a 1D array. The mapOutput function takes a 1D array and returns either a 1D array or a Nx1 2D matrix.
Drop the 2 functions into any project and the UDF (or subroutine) can now “outsource” its I/O process.
Option Base 0
Function mapInput(X)
If Not TypeOf X Is Range Then
mapInput = X
ElseIf X.Columns.Count > 1 Then
If X.Rows.Count > 1 Then
'don't handle 2D matrix
Else
With Application.WorksheetFunction
mapInput = .Transpose(.Transpose(X.Value)) 'should it be value2?
End With
End If
ElseIf X.Rows.Count > 1 Then
mapInput = Application.WorksheetFunction.Transpose(X.Value)
Else
Dim Rslt: ReDim Rslt(0): Rslt(0) = X.Value: mapInput = Rslt
End If
End Function
Function mapOutput(X)
Dim myCaller
On Error Resume Next
Set myCaller = Application.Caller
On Error GoTo 0
If Not TypeOf myCaller Is Range Then
mapOutput = X
ElseIf myCaller.Columns.Count > 1 Then
mapOutput = X
Else
mapOutput = Application.WorksheetFunction.Transpose(X)
End If
End Function
What does the UDF I want to write look like? It’s modularized.
Dim Arr
Arr = mapInput(inX)
Arr = processUDF(Arr)
myUDF = mapOutput(Arr)
End Function
In fact, if one wanted to dispose with the Arr variable, one could write a 1 line UDF!
myUDF = mapOutput(processUDF(mapInput(inX)))
End Function
and processUDF would be the main code to process a 1D array and return a 1D array. For illustration purposes I wrote a trivial routine, which, with its one support function, looks like:
On Error Resume Next
ArrLen = UBound(X) - LBound(X) + 1
End Function
Function processUDF(inArr)
Dim I As Long
Dim Rslt: ReDim Rslt(ArrLen(inArr))
For I = LBound(inArr) To UBound(inArr)
Rslt(I - LBound(inArr) + LBound(Rslt)) = inArr(I) * 2
Next I
processUDF = Rslt
End Function
Note the seemingly overly complicated assignment to the Rslt() element. To me, it’s basic defensive programming that means I don’t care how the 2 arrays are declared. The code adjusts itself to the respective array bounds.
With the above code, I don’t care how the UDF is used. It works if the input is a 1D row (A1:C1 for example), a 1D column (A1:A3), a single cell (e.g., B2), or a constant array, such as {1,2,3}. The simplified version does not work with a column array, i.e., {1;2;3}.
I also don’t care if the function is array-entered in a single row (C2:E2 for example) or a column (C2:C4) or, for a single cell as the input, in a single cell.
The above function (i.e., myUDF) can also be called from a subroutine as in the two examples below:
Dim Arr: Arr = Split("1,2,3", ",")
Arr = myUDF(Arr)
MsgBox (Join(Arr, ","))
End Sub
Sub callFromSub2()
MsgBox (Join(myUDF(Split("4,5,6", ",")), ","))
End Sub
As a follow up to my previous post, what if one had a function with 2 or more parameters? The modularized approach means the code is trivial to extend.
Dim I As Long
If ArrLen(In1) <> ArrLen(In2) Then Exit Function
Dim Rslt: ReDim Rslt(ArrLen(In1))
For I = LBound(In1) To UBound(In1)
Rslt(I - LBound(In1) + LBound(Rslt)) = _
In1(I) * In2(I - LBound(In1) + LBound(In2))
Next I
processUDF2 = Rslt
End Function
Function myUDF2(In1, In2)
myUDF2 = mapOutput(processUDF2(mapInput(In1), mapInput(In2)))
End Function
or with myUDF2 broken up into discrete steps:
Dim Arr1, Arr2
Arr1 = mapInput(In1): Arr2 = mapInput(In2)
Dim Rslt: Rslt = processUDF2(Arr1, Arr2)
myUDF2 = mapOutput(Rslt)
End Function
Can I use this to export the content of an array to a comma delimited text file which can be saved and emailed, then imported into the same structure? I currently do this by writing a range cell by cell looping line by line (about 10,000 rows and about 15 columns), for example:
Set Cells = Workbook.Sheets(”Coding”).Range(”VILLAGES”).Columns(1)
For Each Cell In Cells.Cells
If Cell.Value = 1 Then
Write #1, _
Cell.Offset(0, 1).Value, _
Cell.Offset(0, 2).Value
End If
Next Cell
which is quick to export, but can be a bit slow to import on some of the older computers being used. So I’m wondering if it can be put into an array to make it faster?
Some of the content is numbers, some dates, some letter codes, and some words. Currently I Dim all items for import as String, but wondering if I should change some to Long if it is only a number? I note:
Jim Cone says:
October 22, 2009 at 1:37 pm
It can be a different animal using String arrays.
TheRange.Value = TempArray can take as long as writing to each cell.
Any advice to speed it up is most welcome!
Hi everyone,
I have some problem with reading from range to array. Because my data is too big (20 million cells in total). Does anyone know how to read this data without using the loop to read each cell. Thank you very much.
Ralph -
Use a loop to read large blocks of data.