Matrix

[garnote]

Hi all,

I have two matix named «One» and «Two»
One : ( 3 rows and 1 column )
1
2
3
Two : ( 5 rows and 1 column )
4
5
6
7
8
With the names of the matrix, how is possible generate
another matrix like this :
Three : ( 8 rows and 1 column )
1
2
3
4
5
6
7
8
Number of rows in each matrix are variables !

Thanks
Serge

 

[JE McGimpsey]

not sure what you mean by "matrix" - a range of cells? I'm assuming not
VBA since you posted in worksheet.functions.

If so, you can create a named range containing both using the union
operator (,)

Choose Insert/Name/Define

Name in Workbook: Three
Refers To: =One,Two

then you can use the combined range:

=SUM(Three) ==> 36
=COUNT(Three) ==> 8

but Three won't act as a continuous range, so functions like

=SUMPRODUCT(Three, B1:B8)

will fail.

If I've missed the mark, post back.

 

[garnote]

Yep !
In french, union is ;

Thank you very much

Serge

 

[Alan Beban]

If the OP needs a continuous range, and the functions in the freely
downloadable file at http://home.pacbell.net/beban are available to his
workbook, he can use

Name in Worbook: Three
Refers To: =Transpose(MakeArray(One,Two,1))

Alan Beban

 

[garnote]

It's really curious, when I use
Name : Three
Reference : =One;Two
I can use the name Three for a graphic
but I can't see the matrix Three in my worsheet ?
{=Three} don't work !!!

Serge

 

[JE McGimpsey]

it doesn't work in the same way that

=B1:B3;D15

array-entered into F1:F8 doesn't work - it simply joins them into the
named range.

You'll need to use a macro (or the add-in that Alan Beban wrote) to use
the joined range as a continuous array.

 

[Harlan Grove]

You'll need to use a macro (or the add-in that Alan Beban wrote)
to use the joined range as a continuous array.

....

Not necessarily. If both arrays are single column, multiple rows, then
hardcoding is one possible way to do this, e.g., entering a multiple cell
array formula like

=INDEX(Three,{1;2;3;1;2;3;4;5},0,{1;1;1;2;2;2;2;2})

This could be generalized as

=INDEX(Three,SeqThree-(SeqThree>ROWS(INDEX(Three,0,1,1)))
*ROWS(INDEX(Three,0,1,1)),1,1+(SeqThree>ROWS(INDEX(Three,0,1,1))))

where the extra name SeqThree is defined as

=ROW(INDIRECT("1:"&(ROWS(INDEX(Rng_1_2,0,1,1))
+ROWS(INDEX(Rng_1_2,0,1,1)))))

As for udfs, Alan's MakeArray is overkill for converting arbitrary ranges
into 1D arrays and too restrictive to be completely general. For more
generality, see

http://groups.google.com/[email protected]


If this is only needed for ranges, simpler is better.

Function rtoa(rng As Range) As Variant
Dim n As Long, rv() As Variant, c As Range

ReDim rv(1 To rng.Cells.Count, 1 To 1)

For Each c In rng
n = n + 1
rv(n, 1) = c.Value
Next c

rtoa = rv
End Function

 

[JE McGimpsey]

Very nice! I had forgotten all about that syntax (and I doubt I could
have come up with the generalization if I had remembered!).

 

[Alan Beban]

If this is only needed for ranges, simpler is better.

Function rtoa(rng As Range) As Variant
Dim n As Long, rv() As Variant, c As Range

ReDim rv(1 To rng.Cells.Count, 1 To 1)

For Each c In rng
n = n + 1
rv(n, 1) = c.Value
Next c

rtoa = rv
End Function

To get it to work I had to change it to

Function rtoa(ByVal rng As Range) As Variant or
Function rtoa(rng)As Variant

Alan Beban

 

[Harlan Grove]

To get it to work I had to change it to

Function rtoa(ByVal rng As Range) As Variant or
Function rtoa(rng)As Variant

Simply put, this shouldn't be necessary. Objects such as Ranges are always
passed by reference, though the reference could, I suppose, be a second
level reference or a stack location holding the reference.

I'm using XL2000 to test this, and using my original function, along with
=ROW() in each cell of A1:A20, the formula =rtoa((A8:A20,A1:A7)) returns
{8;9;10;11;12;13;14;15;16;17;18;19;20;1;2;3;4;5;6;7} as expected. What do
your function calls look like? And the ever popular question, what do you
mean by 'to get it to work'? That is, what type of error was the original
giving you?

 

[Alan Beban]

Sub test3001()
Set rng1 = Range("a1:b1,d1:e1")
Dim arr()
arr = rtoa(rng1)
End Sub

Compile error: ByRef argument type mismatch

Alan Beban

 

[Alan Beban]

Another way to avoid the problem is to declare rng1 explicitly as Range.

Alan Beban

 

[Harlan Grove]

Sub test3001()
Set rng1 = Range("a1:b1,d1:e1")
Dim arr()
arr = rtoa(rng1)
End Sub

Compile error: ByRef argument type mismatch

....

I see. So you believe the problem is with my udf rather than with your
calling syntax? When you declare a VBA function to take an argument of a
specific object type rather than a variant, you must pass it an direct
reference of that particular object type rather than a variant containing a
reference to an object of that particular object type.

For example, the following combination will throw the same compile time
error.

Function f(ws As Worksheet) As String
f = ws.Name
End Function

Sub bar()
Dim x As Variant
Set x = ActiveSheet
MsgBox f(x)
End Sub

Other than loss of efficiency (variants use more memory, and checking the
type of what they hold takes more time), there's no harm in changing the
Range argument to ByVal, so maybe that's the path of least resistance, but
variant's containing Range references can be converted to direct range
references without undue difficulty.

SomeArray = rtoa(Range(VariantRangeRef.Address(,,, 1)))


Now there actually is a problem in the udf, but it's not what you pointed
out. See Charles Williams's site for details.

http://www.decisionmodels.com/downloads.htm#AreasBugBypass

Took me a while to recall this problem.

 

[Alan Beban]

As for udfs, Alan's MakeArray is overkill for converting arbitrary ranges
into 1D arrays and too restrictive to be completely general. For more
generality, see

http://groups.google.com/[email protected]

Part of the additional generality, accepting multi-area ranges,
introduces the same bug that Harlan Grove mentioned in connection with
the rtoa function in this thread.

Alan Beban

 

[Harlan Grove]

...
Part of the additional generality, accepting multi-area ranges,
introduces the same bug that Harlan Grove mentioned in connection with
the rtoa function in this thread.

Correct. It seems there's no easy way to handle multiple area ranges in udfs.

I'm sure Alan's health warning implicitly includes his own MakeArray function,
which also suffers from this bug when passed multiple area ranges. I'm sure he's
tested this to see that this is so. It surely can't be the case that he believes
that just because his MakeArray udf implicitly uses only the first area of any
multiple area ranges it's passed that he would believe this relieves his udf of
the adverse consequences of this bug.

If the steps mentioned in AreasBugBypass2.xls linked in

http://www.decisionmodels.com/downloads.htm#AreasBugBypass

were followed, my udf, mkarray, would be healed. MakeArray, on the other hand,
would still be broken when passed multiple area ranges. Let A1:A20 each contain
the formula =ROW(). The formula

=MakeArray((A11:A20,A1:A10))

returns just {11,12,13,14,15,16,17,18,19,20} because MakeArray doesn't use range
object arguments directly. Instead, it only uses range arguments' .Value
properties, which effectively truncate multiple area ranges to their first area.

Since MakeArray doesn't fully process multiple area ranges, shouldn't either the
comments that serve as its documentation be ammended to document this limitation
or it should return an error to flag this as an error. Then again, maybe there's
an outside chance Alan would rewrite it so it works as claimed.