For i = firstRow To lastRow
arrData(n) = .Cells(i, 13).Value
n = n + 1
Next i
It's always going to be faster to manipulate two arrays in VBA than to load
an array cell by cell in a loop or especially to write from an array to
cells in a loop. Use this:
vInputArray = .Range(.Cells(firstrow,13), .Cells(lastrow,13)).Value
ReDim arrData(firstRow To lastRow)
For i = firstRow To lastRow
arrData(i) = vInputArray(i, 13)
Next i
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -
http://PeltierTech.com
_______
"T Lavedas" wrote in message
...
On May 14, 8:35 am, "Charles Williams"
wrote:
Hi Bruce,
Excel always loads a range into a variant as a 2D array.
Usually its simplest to just process it as a 2D array, but you could copy
the data into a 1D array if you really need to, however the performance
impact of using 2 array indices is insignificant.
Charles
__________________________________________________
The Excel Calculation Sitehttp://www.decisionmodels.com
"Bruce Bowler" wrote in message
...
First, I apologize if this is the wrong group, but it seemed "close".
NB
I'm calling this code from VBA in access, but you'll note there are no
access components (directly) involved, which leads me to believe it's
more
likely an excel (or maybe VBA) "problem".
Feel free to redirect me and I'll be off if I was wrong...
I have the following bit of code...
{snip}
The code works *almost* as expected. The data is correct, etc, but
sData
ends up being a 2 dimensional array (3,1) and I'd really like it to be
a 1
dimensional array (3). You and I can see that the second dimension in
the .range doesn't change. How can I convince the computer to make
sData
a 1 dim array?
Thanks!
Bruce
OK, that is what I have found, though the documentation doesn't
mention it. It makes sense, since a worksheet is a two dimensional
structure (rows and columns.
One thing I was surprised to discover is that the array has a base of
1 not the default zero of VBA. That is, the lower bound of the array
is always (1,1).
In this particular situation, it appears to me that the logic is of
the code posted is very confused. The FOR loop is completely
unnecessary to returning an array. In fact, it isn't - it's merely
storing a different array many times into the variable.
The same (correct, I think) results would be achieved with this ...
Public Sub loadData()
Dim wbk As excel.Workbook
Dim wks As excel.Worksheet
Dim arrData as Variant
Set myExcel = excel.Application
fName = "source.xls"
Set wbk = myExcel.Workbooks.Open(fName, , ReadOnly)
Set wks = wbk.Sheets("Results")
firstRow = 2
lastRow = wks.Rows.End(xlDown).Row ' not certain this is right
arrData = .Range(.Cells(firstRow, 13), .Cells(lastrow, 13))
wbk.Close
Set wks = Nothing
Set wbk = Nothing
myExcel.Quit
End Sub
If a one dimensional array is a must, then this might suffice ...
Public Sub loadData()
Dim wbk As excel.Workbook
Dim wks As excel.Worksheet
Dim arrData() as Variant
Set myExcel = excel.Application
fName = "source.xls"
Set wbk = myExcel.Workbooks.Open(fName, , ReadOnly)
With wbk.Sheets("Results") ' wks
firstRow = 2
lastRow = .Rows.End(xlDown).Row
ReDim arrData(lastRow - firstRow)
n = 0
For i = firstRow To lastRow
arrData(n) = .Cells(i, 13).Value
n = n + 1
Next i
End With ' wks
wbk.Close
Set wks = Nothing
Set wbk = Nothing
myExcel.Quit
End Sub
Tom Lavedas
===========
http://members.cox.net/tglbatch/wsh/