Function Returns Multidim Array

M

Murp

Is it possible to make a function return a multidimensional array? I can make
my function return an array, but I can't use the index to return the values
(e.g. MyMultiArray(1, 2)).

Here's my attempt:
Public Function LkpArray()

Dim strLkpMatrix(6, 2) As String

strLkpMatrix(0, 0) = "Lv1"
strLkpMatrix(0, 1) = "Level 1"
strLkpMatrix(0, 2) = "I1CLS"
strLkpMatrix(1, 0) = "Lv2"
strLkpMatrix(1, 1) = "Level 2"
strLkpMatrix(1, 2) = "I1DIV"
strLkpMatrix(2, 0) = "Lv3"
strLkpMatrix(2, 1) = "Level 3"
strLkpMatrix(2, 2) = "I1GRP"
strLkpMatrix(3, 0) = "Lv4"
strLkpMatrix(3, 1) = "Level 4"
strLkpMatrix(3, 2) = "I1CAT"
strLkpMatrix(4, 0) = "Lv5"
strLkpMatrix(4, 1) = "Level 5"
strLkpMatrix(4, 2) = "I1SCT"
strLkpMatrix(5, 0) = "Bind"
strLkpMatrix(5, 1) = "Binding"
strLkpMatrix(6, 0) = "Impt"
strLkpMatrix(6, 1) = "Imprint"
strLkpMatrix(6, 2) = "I1BRNC"

LkpArray = strLkpMatrix()

End Function
 
M

Marshall Barton

Murp said:
Is it possible to make a function return a multidimensional array? I can make
my function return an array, but I can't use the index to return the values
(e.g. MyMultiArray(1, 2)).

Here's my attempt:
Public Function LkpArray()

Dim strLkpMatrix(6, 2) As String

strLkpMatrix(0, 0) = "Lv1"
strLkpMatrix(0, 1) = "Level 1"
strLkpMatrix(0, 2) = "I1CLS"
strLkpMatrix(1, 0) = "Lv2"
strLkpMatrix(1, 1) = "Level 2"
strLkpMatrix(1, 2) = "I1DIV"
strLkpMatrix(2, 0) = "Lv3"
strLkpMatrix(2, 1) = "Level 3"
strLkpMatrix(2, 2) = "I1GRP"
strLkpMatrix(3, 0) = "Lv4"
strLkpMatrix(3, 1) = "Level 4"
strLkpMatrix(3, 2) = "I1CAT"
strLkpMatrix(4, 0) = "Lv5"
strLkpMatrix(4, 1) = "Level 5"
strLkpMatrix(4, 2) = "I1SCT"
strLkpMatrix(5, 0) = "Bind"
strLkpMatrix(5, 1) = "Binding"
strLkpMatrix(6, 0) = "Impt"
strLkpMatrix(6, 1) = "Imprint"
strLkpMatrix(6, 2) = "I1BRNC"

LkpArray = strLkpMatrix()

End Function
Click to expand...


The function must return a Variant type containing the
array:

Public Function LkpArray() As Variant

Since you did not specify the type of the function's return
value, it defaults to Variant, so your function is doing
what you want.

Maybe the issue is in how you are assigning the function's
result?? It should be something like:

Dim MyMultiArray As Variant
MyMultiArray = LkpArray()
x = MyMultiArray(1, 2)
 
6

'69 Camaro

Hi.
I can make
my function return an array, but I can't use the index to return the values
Click to expand...

This is a very subtle difference to what Marsh suggested, but it returns the
String value based upon the indexes passed to it for the multi-dimensional
array:

Public Function LkpArray(nCateg As Long, nSubCateg As Long) As String

On Error GoTo ErrHandler

Dim strLkpMatrix(6, 2) As String

strLkpMatrix(0, 0) = "Lv1"
strLkpMatrix(0, 1) = "Level 1"
strLkpMatrix(0, 2) = "I1CLS"
strLkpMatrix(1, 0) = "Lv2"
strLkpMatrix(1, 1) = "Level 2"
strLkpMatrix(1, 2) = "I1DIV"
strLkpMatrix(2, 0) = "Lv3"
strLkpMatrix(2, 1) = "Level 3"
strLkpMatrix(2, 2) = "I1GRP"
strLkpMatrix(3, 0) = "Lv4"
strLkpMatrix(3, 1) = "Level 4"
strLkpMatrix(3, 2) = "I1CAT"
strLkpMatrix(4, 0) = "Lv5"
strLkpMatrix(4, 1) = "Level 5"
strLkpMatrix(4, 2) = "I1SCT"
strLkpMatrix(5, 0) = "Bind"
strLkpMatrix(5, 1) = "Binding"
strLkpMatrix(6, 0) = "Impt"
strLkpMatrix(6, 1) = "Imprint"
strLkpMatrix(6, 2) = "I1BRNC"

LkpArray = strLkpMatrix(nCateg, nSubCateg)

Exit Function

ErrHandler:

MsgBox "Error in LkpArray( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Function ' LkpArray( )


Example usage:

Public Sub testLkUpArray()

On Error GoTo ErrHandler

MsgBox LkpArray(1, 2) ' Displays "I1DIV"

Exit Sub

ErrHandler:

MsgBox "Error in testLkUpArray( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub


HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
M

Marshall Barton

Certainly valid Gunny, but then the entire function must be
executed for every reference to an element. The way Murp
was doing it, the array only needs to be initialized once.
 
Top