Assembling large "array"

[Walter Briscoe]

I am using Excel 2003 on Windows Vista

I have a file of London fare data. (Shows fares between origin and
destination stations.)
It consists of about 600 rows and columns.
Because Excel 2003 only supports 256 columns in a sheet, I need to split
my data over more than one sheet.

The data ought to be symmetrical and mostly is. However, I have found
one pair of stations where the AB price and BA price are different.
It takes several elapsed days to assemble that file from the Internet.
I would like to identify asymmetric data to assess the extent of that
asymmetry. i.e. I assume AB price = BA price is probably correct.

I want to assemble an "array" in memory.
I can read data from a single sheet with code like this:
Dim PricesA_D As Variant
PricesA_D = Sheets("A-D").Range("B2", _
Sheets("A-D").Cells.SpecialCells(xlCellTypeLastCell).Address)
' The first row and column contain station names

I think I need a picture.
Sheets("A-D") might start
Abbey Wood Acton Central Acton Main Line ...
Abbey Wood X 1.23 2.34
Acton Central 3.21 X 3.45
Acton Main Line 2.34 3.45 X
....

I have shown asymmetric data between Abbey Wood and Acton Central.

Sheets("E-K") station names might start
Ealing Broadway Ealing Common Earls Court ...
Abbey Wood
Acton Central
Acton Main Line
....

I want an "array" in which the E-K data is appended to the A-D data.

I tried
Dim Prices As Variant
Prices = Application.Union( _
Sheets("A-D").Range(
"B2", _
Sheets("A-D").Cells.SpecialCells(xlCellTypeLastCell).Address), _
Sheets("E-K").Range(
"B2",
Sheets("E-K").Cells.SpecialCells(xlCellTypeLastCell).Address), _
Sheets("L-R").Range(
"B2",
Sheets("L-R").Cells.SpecialCells(xlCellTypeLastCell).Address), _
Sheets("S-Z").Range(
"B2",
Sheets("S-Z").Cells.SpecialCells(xlCellTypeLastCell).Address))

I got Run-time error '1004':
Method 'Union' of object '_Application' failed

I assumed that was because Union tries to create a sheet with more than
256 columns.

To test that assumption, I tried
Prices = Application.Union( _
Sheets("A-D").Range("B2", "T20"), _
Sheets("E-K").Range("B2", "T20"))
but got the same error.

1) Why does Union get 1004?
2) How do I construct a large "array" with minimal coding?
3) After
Dim PricesA_D As Variant
PricesA_D = Sheets("A-D").Range( _
"B2", _
Sheets("A-D").Cells.SpecialCells(xlCellTypeLastCell).Address)
Locals shows StationsA_D has Type Variant/Variant(1 to 623, 1 to 151)
What code will find those values 623 and 151 in StationsA_D?

My original problem of finding asymmetric data can easily be solved by
comparing cells on my 4 sheets.
I want to be able to process copies of sheet data in memory.

 

[merjet]

1. A Range is a class *within* another class, Worksheet. You can use
Union to combine two Ranges, both from the same Worksheet, but you
can't make one Range from two different Worksheets.
2. Since Prices is a Range, you should have used Set Prices = ...
3. I think you will have to settle for creating an array that is not a
Range, e.g. as follows.

Dim dArr(1 To 3, 1 To 6) As Double
Dim iRow As Integer
Dim iCol As Integer
For iRow = 1 To 3
For iCol = 1 To 3
dArr(iRow, iCol) = Sheets("A-D").Cells(1 + iRow, 1 +
iCol).Value
Next iCol
For iCol = 4 To 6
dArr(iRow, iCol) = Sheets("E-K").Cells(1 + iRow, iCol -
2).Value
Next iCol
Next iRow

For this I changed your X's to 0's. If you want to use X's, declare
dArr as Variant.

 

[Walter Briscoe]

In message <[email protected]
..com> of Thu, 23 Feb 2012 07:05:06 in microsoft.public.excel.programming

1. A Range is a class *within* another class, Worksheet. You can use
Union to combine two Ranges, both from the same Worksheet, but you
can't make one Range from two different Worksheets.

Thank you for that perception. It NOW makes sense to me.
The output of Union is a Range.
Therefore that must fit the criteria for a Range.
i.e. It must only refer to one sheet/

2. Since Prices is a Range, you should have used Set Prices = ...

You're right. I find Set quite unnatural, and regularly fall over my
failure to use it. I do not understand why Prices = Range("B2", "D4")
does not give a compilation error. Instead it gives a "Run-time error
'91': Object variable or With block variable not set".
Dim foo2 As Long: foo2 = "Hello, World!" also does not give a
compilation error. I guess VBA authors decided to economise on
compilation checks. The logic is that you don't check because you can't
always check. I feel better for composing those thoughts.

3. I think you will have to settle for creating an array that is not a
Range, e.g. as follows.

Dim dArr(1 To 3, 1 To 6) As Double
Dim iRow As Integer
Dim iCol As Integer
For iRow = 1 To 3
For iCol = 1 To 3
dArr(iRow, iCol) = Sheets("A-D").Cells(1 + iRow, 1 +
iCol).Value
Next iCol
For iCol = 4 To 6
dArr(iRow, iCol) = Sheets("E-K").Cells(1 + iRow, iCol -
2).Value
Next iCol
Next iRow

For this I changed your X's to 0's. If you want to use X's, declare
dArr as Variant.

The problem with that useful example is that it makes many accesses to
the sheet. I was going to ask how I could find the dimensions of a
variant array after filling it.
[Why is Set not needed in the code below?]

Dim PricesA_D As Variant
PricesA_D = Sheets("A-D").Range(
"B2",
Sheets("A-D").Cells.SpecialCells(xlCellTypeLastCell).Address)

After running that code, In the Immediate Window, I showed:
?ubound(PricesA_D)
623
?ubound(PricesA_D,1)
623
?lbound(PricesA_D)
1
?ubound(PricesA_D,2)
151
?lbound(PricesA_D,2)
1

and in the Locals Window, I showed
PricesA_D Variant/Variant(1 to 623, 1 to 151)
and
PricesA_D(1) Variant(1 to 151)
....
PricesA_D(623) Variant(1 to 151)

I now have enough information to code my problem as I wish to do.
Thank you very much to merjet <[email protected]> for help.

 

[GS]

Just to add some food for thought...

The array could have been loaded like this...

PricesA_D = Sheets("A-D").UsedRange

... and its dims could have been retrieved like this...

Debug.Print UBound(PricesA_D) & vbTab & UBound(PricesA_D, 2)

-OR-

With Sheets("A_D").UsedRange
Debug.Print .Rows.Count & vbTab & .Columns.Count
End With

To access the data in code you can do something like this...

Dim i&, j& 'as Long
For i = LBound(PricesA_D) To UBound(PricesA_D)
For j = LBound(PricesA_D, 2) To UBound(PricesA_D, 2)
'process each element in Dim2 for each element in Dim1
PricesA_D(i, j) = PricesA_D(i, j)
Next 'j
Next 'i

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion