Re: can .range return a 1D array?

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 Site
http://www.decisionmodels.com

"Bruce Bowler" <bbowler@xxxxxxxxxxx> wrote in message
news:pan.2008.05.14.12.09.35@xxxxxxxxxxxxxx
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...

public myExcel As excel.Application

Public Sub loadData()
Dim wbk As excel.Workbook
Dim wks As excel.Work***
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
stepSize = 3
For i = firstRow To lastRow Step stepSize
With wks
sData = .Range(.Cells(i, 13), .Cells(i + (stepSize - 1), 13))
End With
Next i
wbk.Close
Set wks = Nothing
Set wbk = Nothing
myExcel.Quit
End Sub

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


.

Loading