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.Worksheet
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

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" 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...

public myExcel As excel.Application

Public Sub loadData()
Dim wbk As excel.Workbook
Dim wks As excel.Worksheet
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

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/

Breaking my own rules and answering 2 people in 1 post... (sorry, I'm
having a bad day)

On Wed, 14 May 2008 06:37:58 -0700, T Lavedas wrote:

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.


Consider the example of implementing an algorithm that "works best" with a
1D array. Consider the case where sometimes you want to call that code
with row (or portion of a row) worth of data and the SAME code with a
column (or portion there of) worth of data. In 1 case the subscripts are
(I,1), in the other they're (1,I). Yes, I know I could implement it with
2 loops going from lbound(x,1) to ubound(x,1) and lbound(x,2) and ubound
(x,2). Now suppose I (or someone else who borrowed the code) wants to
call it with an array created via the ARRAY function. Yep, could code
that too, but the code is *MUCH* simpler to understand (and less likely to
contain errors) if it treats the input as a vector rather than an array.

I know moan and groan, it's not going to change. I'll just live with it.

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).

At least it got something right (not wanting to start a 0 vs 1 flame
war :-)

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.

Actually the FOR loop is needed for other things (I neglected to include a
[snip - do stuff] after the "end with"). My fault.

Bruce Bowler wrote:

Consider the example of implementing an algorithm that "works best" with a
1D array. Consider the case where sometimes you want to call that code
with row (or portion of a row) worth of data and the SAME code with a
column (or portion there of) worth of data. In 1 case the subscripts are
(I,1), in the other they're (1,I). Yes, I know I could implement it with
2 loops going from lbound(x,1) to ubound(x,1) and lbound(x,2) and ubound
(x,2). Now suppose I (or someone else who borrowed the code) wants to
call it with an array created via the ARRAY function. Yep, could code
that too, but the code is *MUCH* simpler to understand (and less likely to
contain errors) if it treats the input as a vector rather than an array.

I know moan and groan, it's not going to change. I'll just live with it.


Perhaps the following might be useful.

If arr is a single column 2-D array, then

arr = Application.Transpose(arr) will convert it to a 1-D array.

And if arr is a single row 2-D array, then

arr = Application.Index(arr,1,0) will convert it to a 1-D array.

And if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, then

arr = OneD(arr) will convert either a single row 2-D array or a single
column 2-D array to a 1-D array, and will leave a 1-D array as a 1-D array.

Alan Beban

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/