multidimensional array not getting passed from function (excel VBA)

Question

In the following function I build a 5 by 2 array. the syntax is

[code]

function alpha( code)

dim ancestors(5,2)
(code)

dim result(11)
result(8) = code
result(9) = ancestors
result(10) = code

alpha = result


end function

sub 

dim ancestors5(5,2)
alpha_result = alpha( code )

ancestors5(5,2) = alpha_result(9)


end sub

[/code]

An even simpler code is as follows:

function alpha(code) as variant

dim arr(5,2)

result(1) = arr
result(2) = something_else
alpha = arr

end function

sub

dim arr2(5,2)

call = alpha(code)

arr2(5,2) = call(1)

end sub

temp =

As you can see from the screen shots there is definitely something in the alpha_result(9) array but it is not getting passed on to the ancestors5 array.

enter image description here

dim ancestors(5,2) actually creates a 6x3 array, since the default lbound is zero, and you're declaring the ubounds as 5 and 2. So you get 0-5 and 0-2. Would be useful to update your question with a compilable example of the problem: right now it's pretty hard to follow. Your alpha function doesn't return anything for example... — Tim Williams
– Tim Williams, Commented Oct 6, 2014 at 3:29
I tried declaring ancestors as ancestors(6,3) but that didn't do anything. — user147178
– user147178, Commented Oct 6, 2014 at 3:33
You need to improve your sample code so people can see what you're trying to do there. — Tim Williams
– Tim Williams, Commented Oct 6, 2014 at 3:37
code improved slightly. the code your produced below doesn't seem to reflect what I'm trying to do. — user147178
– user147178, Commented Oct 6, 2014 at 4:31
See my edit. It's still unclear what you're trying to do: what do you expect to happen in your code? If you're trying to do a direct array assignment from alpha_result(9), that's not how it's done in VBA — Tim Williams
– Tim Williams, Commented Oct 6, 2014 at 4:56

Tim Williams · Accepted Answer · 2014-10-06 04:53:58Z

1

Is this what you mean?

Function Alpha()
Dim a(5, 2)
Dim b(11)

    a(1, 1) = "test" 'e.g.
    b(9) = a

    Alpha = b
End Function

Sub tester()
    Dim arr, arr2  ' Variants
    Dim arr3(5, 2) ' declare empty array with dimensions 0-5, 0-2

    arr = Alpha() '>> arr is now a 1-d array with dimensions 0-11

    MsgBox arr(9)(1, 1) '>> "test" value from 2-d array stored at
                        '    arr(9)
    'or...
    arr2 = arr(9)     'arr2 is now a 2-d array with dimensions 0-5, 0-2
    MsgBox arr2(1, 1) '>> "test"

    ' it's unclear what you *want* to happen here:
    arr3(5, 2) = arr(9) '<< here you're assigning the 2-d array
                        '   stored in arr(9) to the element of
                        '   arr3() at 5,2
End Sub

edited Oct 6, 2014 at 4:53

answered Oct 6, 2014 at 4:12

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

multidimensional array not getting passed from function (excel VBA)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related