can .range return a 1D array?

[Bruce Bowler]

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

 

[Charles Williams]

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

 

[T Lavedas]

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

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/

 

[Bruce Bowler]

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


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.

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.

 

[Jon Peltier]

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
_______

 

[Alan Beban]

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

 

[Jon Peltier]

Much more elegant than the loop I posted.

- Jon

 

[Dave Peterson]

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

As will:

with application
arr = .transpose(.Transpose(arr))
end with

just another way...

 

[Alan Beban]

As will:

with application
arr = .transpose(.Transpose(arr))
end with

just another way...

Which reminds me . . .

Both arr = Application.Transpose(Application.Transpose(arr)) and
arr = Application.Index(arr,1,0)

will return a 1-based array, regardless of the base of the array that
was input.

If the add-in functions I referred to in my previous post are available
to the workbook

arr = OneD(arr) will return a 1-based or 0-based array, depending on the
base of the array that was input.

Alan Beban

 

[RB Smissaert]

This is some useful code (as it is very fast) to change the
LBound of arrays that are declared like this: Dim arr()

Option Explicit
Private Declare Function VarPtrAry _
Lib "msvbvm60" _
Alias "VarPtr" (Ary() As Any) As Long
Private Declare Sub CopyMemory _
Lib "kernel32" _
Alias "RtlMoveMemory" (Dest As Any, Src As Any, _
ByVal cBytes As Long)

Function GetArrayDims(arr As Variant) As Integer

'---------------------------------------'
'copied from Francesco Balena at: '
'http://www.devx.com/vb2themax/Tip/18265'
'---------------------------------------'
Dim ptr As Long
Dim VType As Integer
Const VT_BYREF = &H4000&

' get the real VarType of the argument
' this is similar to VarType(), but returns also the VT_BYREF bit
CopyMemory VType, arr, 2

' exit if not an array
If (VType And vbArray) = 0 Then
Exit Function
End If

' get the address of the SAFEARRAY descriptor
' this is stored in the second half of the
' Variant parameter that has received the array
CopyMemory ptr, ByVal VarPtr(arr) + 8, 4

' see whether the routine was passed a Variant
' that contains an array, rather than directly an array
' in the former case ptr already points to the SA structure.
' Thanks to Monte Hansen for this fix

If (VType And VT_BYREF) Then
' ptr is a pointer to a pointer
CopyMemory ptr, ByVal ptr, 4
End If

' get the address of the SAFEARRAY structure
' this is stored in the descriptor

' get the first word of the SAFEARRAY structure
' which holds the number of dimensions
' ...but first check that saAddr is non-zero, otherwise
' this routine bombs when the array is uninitialized
' (Thanks to VB2TheMax aficionado Thomas Eyde for
' suggesting this edit to the original routine.)
If ptr Then
CopyMemory GetArrayDims, ByVal ptr, 2
End If

End Function

Sub SetLBound(Ary() As Variant, lNewLBound As Long)

' "As Variant" for example only -- use your specific type
' Note that this won't work for string() or UDT() with strings
' Sets Ary's LBound to NewBound, returns previous LBound.
'-------------------------------------------------------------
Dim i As Integer
Dim AryPtr As Long
Dim PrevLBound As Long
Dim iDims As Integer

iDims = GetArrayDims(Ary)

If iDims = 0 Then
Exit Sub
End If

AryPtr = VarPtrAry(Ary) ' address of address of safearray struct
CopyMemory AryPtr, ByVal AryPtr, 4

AryPtr = AryPtr + 20 ' pointer to safearray.bounds.lLbound
CopyMemory PrevLBound, ByVal AryPtr, 4

'no point altering lBound to the existing lBound
If PrevLBound = lNewLBound Then
Exit Sub
End If

For i = 1 To iDims
CopyMemory ByVal AryPtr + (i - 1) * 8, lNewLBound, 4
Next

End Sub

Sub test()

Dim arr()

ReDim arr(0 To 10)

MsgBox LBound(arr), , "Original LBound"
SetLBound arr, 1
MsgBox LBound(arr), , "New LBound"

End Sub


RBS

 

[Alan Beban]

Doesn't something like the following, with a modest amount of error
trapping (not provided), suffice?

Function changeBounds(inputArray, newLB, newUB)
Dim arrV
arrV = inputArray
ReDim Preserve arrV(newLB To newUB)
changeBounds = arrV
End Function

Sub testabc1000()
Dim arr() As Integer
ReDim arr(1 To 10)
For i = 1 To 10
arr(i) = i
Next
arr = changeBounds(arr, 0, 11)
Debug.Print TypeName(arr), LBound(arr), UBound(arr), arr(0), arr(9)
End Sub

Alan Beban

 

[RB Smissaert]

Yes, simpler, but a lot slower as your Redim Preserve, as I understand it,
makes a full copy of the array. The posted code doesn't.

RBS

 

[Alan Beban]

Yes, simpler, but a lot slower as your Redim Preserve, as I understand it,
makes a full copy of the array. The posted code doesn't.

RBS

But you need a different SetLBound procedure for each type of array
(Integer(), Long(), etc.) and it won't work at all for String() arrays.
Do I have that right?

Alan Beban

 

[Alan Beban]

Yes, simpler, but a lot slower as your Redim Preserve, as I understand it,
makes a full copy of the array. The posted code doesn't.

RBS

And aside from not having a general SetLBound procedure and not being
available for String() arrays, the difference in speed from the general
code I posted is on the order of hundredths of a millisecond. Is that right?

Alan Beban

 

[RB Smissaert]

Yes, it can only be used in the situation as described.
Speed difference will obviously depend on the size of the array and/or the
loop count if it is run in a loop.
There will be situations though where it is useful and I use it.

RBS

 

[Alan Beban]

In xl2002, the following does as shown:

Function testxyz3(inputArray As Variant)
testxyz3 = TypeName(inputArray)
End Function

Sub xyz2()
Dim arr() As Integer
Debug.Print testxyz3(arr) '<--Prints Integer()
End Sub

Why with your original code, exept with test starting out

Dim arr() As Integer

does SetLBound arr,1 fail?

Alan Beban

 

[RB Smissaert]

Not sure, I didn't write the code and I haven't looked into it in any depth.
I just know it works in the situation as described and not in others.
Maybe ask in vb.general

RBS