Hello all. I'm trying to find an easy way of converting a selected index within a 2-dimensional array into a 1-dimensional array. For example, if I have the following:
TwoDArr(1,1)="OneOne"
TwoDArr(1,2)="OneTwo"
TwoDArr(2,1)="TwoOne"
TwoDArr(2,2)="TwoTwo"
... I want to do something like this:
OneDArr=TwoDArr(2): with the result being OneDArr(1)="TwoOne" and OneDArr(2)="TwoTwo". See what I mean?
I have created a function to do it the long way round as follows:
Function TwoD_OneD(arr() As Variant, Index As Integer) As Variant
' arr is a 2D array. Index is the number of the first dimension. All the items in the second dimension will be taken into a new array
Dim TempArr()
Dim y As Integer, UB As Integer
UB = UBound(arr, 2)
ReDim TempArr(1 To UB)
For y = 1 To UB
If arr(Index, y) <> "" Then
TempArr
= arr(Index, y)
Else
' Reached the end of the actual data (although the array may be potentially larger)
ReDim Preserve TempArr(1 To y - 1)
Exit For ' Curtail the size of the array to only that which actually holds data.
End If
Next y
TwoD_OneD = TempArr
End Function
The above function is called as:
OneDArr=TwoD_OneD(TwoDArr, 2)
It works, but I am not satisfied with it. For a start, it's messy to have to redim a temporary array to the entire size of the input array's second dimension, when the selected index of the first dimension might not necessarily use all the elements of the second dimension. (I mean for example, arr(1,x) could go up to x=10; but arr(2,x) could go up to x=5. However because in the first series the second element goes up to 10, that must also be the size of the second element - even though items 6-10 are blank)
I found a page (How can I get one dimension from a bi-dimensional array in VB.Net? - Stack Overflow) which seemed to suggest that VBA could handle this natively, with a single line (OneDArr=TwoDArr(index)). However when I tried that, I got a compile error. Either Excel's flavour of VBA does not handle this sort of thing, or I'm doing it wrong.
Can anyone suggest a better way of doing this than the messy function I am using?
TwoDArr(1,1)="OneOne"
TwoDArr(1,2)="OneTwo"
TwoDArr(2,1)="TwoOne"
TwoDArr(2,2)="TwoTwo"
... I want to do something like this:
OneDArr=TwoDArr(2): with the result being OneDArr(1)="TwoOne" and OneDArr(2)="TwoTwo". See what I mean?
I have created a function to do it the long way round as follows:
Function TwoD_OneD(arr() As Variant, Index As Integer) As Variant
' arr is a 2D array. Index is the number of the first dimension. All the items in the second dimension will be taken into a new array
Dim TempArr()
Dim y As Integer, UB As Integer
UB = UBound(arr, 2)
ReDim TempArr(1 To UB)
For y = 1 To UB
If arr(Index, y) <> "" Then
TempArr
= arr(Index, y)Else
' Reached the end of the actual data (although the array may be potentially larger)
ReDim Preserve TempArr(1 To y - 1)
Exit For ' Curtail the size of the array to only that which actually holds data.
End If
Next y
TwoD_OneD = TempArr
End Function
The above function is called as:
OneDArr=TwoD_OneD(TwoDArr, 2)
It works, but I am not satisfied with it. For a start, it's messy to have to redim a temporary array to the entire size of the input array's second dimension, when the selected index of the first dimension might not necessarily use all the elements of the second dimension. (I mean for example, arr(1,x) could go up to x=10; but arr(2,x) could go up to x=5. However because in the first series the second element goes up to 10, that must also be the size of the second element - even though items 6-10 are blank)
I found a page (How can I get one dimension from a bi-dimensional array in VB.Net? - Stack Overflow) which seemed to suggest that VBA could handle this natively, with a single line (OneDArr=TwoDArr(index)). However when I tried that, I got a compile error. Either Excel's flavour of VBA does not handle this sort of thing, or I'm doing it wrong.
Can anyone suggest a better way of doing this than the messy function I am using?
