Alan Beban's Column Vector Routine

[ExcelMonkey]

Been trying to use Alan Beban's Column Vector routine to extract the column
vector of a 2-D array. Not sure if I understand what its doing. The code
below populates a 2-D array (FirstArray)with random number then prints them
to the immediate window. Then I use Alan's Column Vector Array to create a
new array (SecondArray) which has only the first column of data from the
FirstArray. When I check the first two column items in the FirstArray, you
can see below they match the 1st column of the array printout (0,0 and 0,1).
I expected to see a 1-D array as a result of the Alan’s function . Secondly,
given that its still a 2-D array, it appears that the columns values from the
first array are in different element addresses (0,1 and 1,1 compared to 0,0
and 0,1). I have not posted Alan’s code for the function. Although I think
I can as he freely gives it away over the internet.

Option Base 0

Sub Main()
Dim FirstArray() As Variant
Dim SecondArray() As Variant
Dim X As Double
Dim Y As Double

ReDim FirstArray(0 To 1, 0 To 3)
For X = 0 To UBound(FirstArray, 1)
For Y = 0 To UBound(FirstArray, 2)
FirstArray(X, Y) = Round(Rnd() * 1000, 0)
Next
Debug.Print FirstArray(X, 0), FirstArray(X, 1), FirstArray(X, 2),
FirstArray(X, 3)
Next

SecondArray = ColumnVector(FirstArray, 0) ‘This is Alan’s Function

End Sub

IMMEDIATE WINDOW
706 533 580 290
302 775 14 761


?FirstArray(0,0)
706
?FirstArray(1,0)
302
?SecondArray(0,1)
706
?SecondArray(1,1)
302

 

[Alan Beban]

There is no concept of a one-dimensional "vertical" array in Excel; all
"vertical" arrays are 2-D.

The ColumnVector function returns a 2-D array whose LBound of the second
dimension is 1, as you have seen. To get the result you are seeking you
can use

SecondArray = ConvertBase(ColumnVector(FirstArray, 0),0,0)

which will return a 0,0 based array rather than a 0,1 based array.

Alan Beban

 

[Alan Beban]

I tried sending you an email at (e-mail address removed),
but it got bounced. Does your collection of Array Functions include the
one called "ReplaceSubArray"? I've had a computer mishap and lost that one.

Thanks,
Alan Beban

 

[ExcelMonkey]

Hey Alan. Knew you would be out there somewhere. I don't have the file on
me right now. Its on my laptop and I will need a day or two to get back to
you. So if you can hold on, I will post more later. Looking forward to
understanding this. Must admit I didn't know you could not have a 1-D
verticle array. My goal is to use the code provided and then send the
SecondArray through a Sort Function and than a Histrogram Routine. Was
running into problems with this. Might have something to do with how the
Sort and histrogram are set up too.

Will get back to you ASAP.

In the mean time you can email me at (e-mail address removed) (take
out the ---).

EM