2D arrays

[chrisjones]

In the sub called 'test' I have created a 2D array, I also have a utility method called arrayToString which simpley concats all elements of the array and returns it. Is it possible to obtain a sub 1D array from this 2D array and send it to arrayToString. Something like

Debug.Print arrayToString(arr(2 to 1, 10 to 1))

 
Public Sub test()
    Dim arr(1 To 10, 1 To 10) As Integer
    Dim i As Integer
    Dim j As Integer
    For i = LBound(arr) To UBound(arr)
        For j = LBound(arr) To UBound(arr)
            arr(i, j) = i * j
        Next j
    Next i
    
    Debug.Print arrayToString(arr(2 to 1, 10 to 1))
    
End Sub
 
Private Function arrayToString(ByRef arr() As Integer) As String
    Dim i As Integer
    Dim str As String
    
    str = "["
    For i = LBound(arr) To UBound(arr)
        str = str & arr(i) & ","
    Next i
    arrayToString = Left(str, Len(str) - 1) + "]"
End Function

[Bob Phillips]

Explain a bit more what byou want to do, and what you want to do with the array.

[chrisjones]

Ok to try and explaing a little differently, if I have a 2d array such as

1,1,1,1
2,2,2,2
3,3,3,3
4,4,4,4

How can I get the 1D array '2,2,2,2'

Thanks

[Bob Phillips]

I thought that was what you would be wanting.

Either

ary1D = Application.Index(ary, 2, 0)

or

ary1D = Application.Transpose(Application.Index(ary, 0, 2))

depending on whether you want rows or columns

[chrisjones]

Great, that is what I am looking for.

Ps. Is there a vba api anywhere?

[Bob Phillips]

API? What do youb mean? There is automation from other VBA hosting products.

[chrisjones]

I am trying to use the suggested method Application.index(arr,i,j), however i keep getting the following error

Type mismatch: array of user definer type expected

What is goign wrong??

 
Public Sub test()
    Dim arr() As Integer
    arr = create2DArray()
    Debug.Print IsArray(arr) ' Returns true
    Debug.Print IsArray(Application.Index(arr, 2, 0)) ' Returns true
    
    'Type mismatch: array of user definer type expected??
    Debug.Print (arrayToString(Application.Index(arr, 2, 0)))
    
End Sub
 
 'This takes a string and returns a string reprenstation delimited by commas e.g [1,2,3]
Private Function arrayToString(ByRef arr() As Integer) As String
    Dim i As Integer
    Dim str As String
    
    str = "["
    For i = LBound(arr) To UBound(arr)
        str = str & arr(i) & ","
    Next i
    arrayToString = Left(str, Len(str) - 1) + "]"
End Function
 
'creates and returns a 2d integer array
Private Function create2DArray() As Integer()
    Dim arr(0 To 10, 0 To 10) As Integer
    Dim i As Integer
    Dim j As Integer
    For i = LBound(arr) To UBound(arr)
        For j = LBound(arr) To UBound(arr)
            arr(i, j) = i * j
        Next j
    Next i
    create2DArray = arr
End Function

PS an api is a big list of all pre defined classes and methods, check out the java one http://download.oracle.com/javase/1.4.2/docs/api/

Thanks in adavnce

Chris

[Bob Phillips]

I know what an API is,l but I am asking what you mean by a VBA API. VBA is hosted, so is doesn't need an API, but maybe you mean tha Object Model?

[Bob Phillips]

As to your problem

[vba]


Public Sub test()
Dim arr() As Integer
arr = create2DArray()
Debug.Print IsArray(arr) ' Returns true
Debug.Print IsArray(Application.Index(arr, 2, 0)) ' Returns true

'Type mismatch: array of user definer type expected??
Debug.Print (arrayToString(Application.Index(arr, 2, 0)))

End Sub

'This takes a string and returns a string reprenstation delimited by commas e.g [1,2,3]
Private Function arrayToString(ByRef arr As Variant) As String
Dim i As Integer
Dim str As String

str = "["
For i = LBound(arr) To UBound(arr)
str = str & arr(i) & ","
Next i
arrayToString = Left(str, Len(str) - 1) + "]"
End Function

'creates and returns a 2d integer array
Private Function create2DArray() As Integer()
Dim arr(0 To 10, 0 To 10) As Integer
Dim i As Integer
Dim j As Integer
For i = LBound(arr) To UBound(arr)
For j = LBound(arr) To UBound(arr)
arr(i, j) = i * j
Next j
Next i
create2DArray = arr
End Function
[/vba]

[chrisjones]

Thanks for pointing out my mistake, I see you have changed integer to variant as Application.index method returns a variant array. This is why an API would be helpful as you would be able to see the return type of methods etc

Now I want to use this to print out my 2D array, this work fine where i declare my array with even dimension but if I was to change my array declaration to the below it gives a runtime error??

Dim arr(1 To 10, 1 To 5) As Integer

 
Public Sub test()
    print2DArray create2DArray()
End Sub
 
 'This takes a string and returns a string reprenstation delimited by commas e.g [1,2,3]
Private Function arrayToString(ByRef arr() As Variant) As String
    Dim i As Integer
    Dim str As String
    str = "["
    For i = LBound(arr) To UBound(arr)
        str = str & Format(Val(arr(i)), "000") & ","
    Next i
    arrayToString = Left(str, Len(str) - 1) + "]"
End Function
 
'creates and returns a 2d integer array
Private Function create2DArray() As Integer()
    Dim arr(1 To 10, 1 To 10) As Integer
    Dim i As Integer
    Dim j As Integer
    For i = LBound(arr) To UBound(arr)
        For j = LBound(arr) To UBound(arr)
            arr(i, j) = i * j
        Next j
    Next i
    create2DArray = arr
End Function
'This used the method arrayToString to print out 2D arrays
Private Function print2DArray(ByRef arr() As Integer)
    Dim i As Integer
    Dim varr() As Variant
    For i = LBound(arr) To UBound(arr)
        varr = Application.Index(arr, i, 0)
        Debug.Print arrayToString(varr)
    Next i
End Function

Thanks again

Chris