CODEWITHSUNDEEP
arraysexcelstringvba
Richard D
Richard D

Reputation: 142

vba extract array from array of arrays

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

Upvotes: 1

Views: 1399

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

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)

Upvotes: 5

Related Questions