vba extract array from array of arrays

Question

I have a number of arrays that have different strings in them like

array1() = Array("one","two","three")
array2() = Array("siz","help")

I combine the strings into an array of the string arrays

bigArray() = Array(array1(),array2(),...)

For k = 0 To # in bigArray
    reason = causes(xValue, bigArray(k))
Next

however I get an error at bigArray(k) when I try to pass it to the function

Public Function causes(xValue As String, keyWords() As Variant) As String

what error do you get? It might be able to resolve by doing keywords as Variant (without the parentheses). — David Zemens
– David Zemens, Commented Jul 12, 2018 at 18:13

Mathieu Guindon · Accepted Answer · 2018-07-12 18:32:47Z

5

The empty parentheses on the left-hand-side of the assignment are redundant and confusing, remove them.

I presume bigArray is declared as such, if at all:

Dim bigArray [As Variant]

If it's not declared, specify Option Explicit at the top of your module (always do this!), and declare every variable you're using - otherwise you allow VBA to happily compile & run typos, and that inevitably turns into embarrassing, hard-to-find bugs, and possibly duplicate questions on Stack Overflow.

A Variant can hold anything, including an array, or a jagged array (i.e. array of arrays).

The keywords parameter though...

Public Function causes(xValue As String, keyWords() As Variant) As String

Is declared as an array where each element is a variant. While the variant element can indeed be an array, when you're passing arrays around as parameters there's no way to say "where each element is an array of variant elements", so you'll have a much easier time if you just wrap it in a Variant (and then assert that you're looking at an array):

Public Function causes(xValue As String, keyWords As Variant) As String
    Debug.Assert IsArray(keyWords)
    Debug.Assert IsArray(keyWords(LBound(keyWords))

Your For loop is assuming what the lower boundary is:

 For k = 0 To # in bigArray

A loop that's not making any assumptions would be:

For k = LBound(bigArray) To UBound(bigArray)

answered Jul 12, 2018 at 18:32

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

4 Comments

Richard D Over a year ago

worked great the assert call did the trick. I appreciate your help.

Mathieu Guindon Over a year ago

@RichardD Debug.Assert only serves as a programmatic breakpoint that's useful for debugging - it halts execution when the assertion fails, that's all it does; making the parameter a Variant is what fixes it =)

dwirony Over a year ago

@MathieuGuindon When you say it halts when it fails, do you mean when it returns False?

Mathieu Guindon Over a year ago

@dwirony Debug.Assert doesn't return anything, but it does take a Boolean parameter. Debug.Assert False is essentially identical to an actual breakpoint, yeah.

Collectives™ on Stack Overflow

vba extract array from array of arrays

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related