Array one dimensional. Why does split work and not Array()

Question

Got a simple question. The Array() function in VBA does return a two dimensional array? I'm trying to create a one dimensional array with this function and use it in the filter() function but it says "type mismatch". And if that's the case, how can I force Array() to create a one dimensional array?

Sub tester()
    Dim xWorkb As Excel.Workbook
    Dim xFiles_target() As String
    Dim file_path As String

    xFiles_target = Array("Bella.xls", "Fizz.xls", "Milo.xls")

    file_path = Dir("C:\Users\hans\Desktop\")

    Do While Len(file_path) > 0
        Debug.Print file_path
        If UBound(Filter(xFiles_target, file_path)) >= 0 Then
            Debug.Print "found " & file_path
        End If
        file_path = Dir
    Loop
End Sub

See @paulfrancis answer then - it's because you declared xFiles_target() as String — Rory
– Rory, Commented Aug 20, 2014 at 15:47
Yes - you can't compare an array of values to a single value like that. — Rory
– Rory, Commented Aug 20, 2014 at 16:04
@uncool - Because the Filter never is True. The Filter Function will compare against the element in the array. The array consists of the values (just the values of the file names). Bella.xls, where the file_path contains something more. So if my understanding is correct you have to include the file path before the file name. Or you have to strip the file name from the file path, then compare with the array. — PaulFrancis
– PaulFrancis, Commented Aug 20, 2014 at 16:10
The Dir() in file_path will return any next file within the \desktop\ directory. That will say only the string Bella.xls ( nothing else ) when bella.xls is found. using the desktop\ is equivalent to using desktop\"" which iterates over all files in the directory to my understanding. — uncool
– uncool, Commented Aug 20, 2014 at 17:42

PaulFrancis · Accepted Answer · 2014-08-20 15:39:49Z

2

Array function does create a 1D Array. However the function requires the variable you are assigning to, to be of Variant type. It cannot be String/Number.

Dim tmpArr() As String
tmpArr = Array("Hello", "World")

The above is likely to throw Type mismatch error. However,

Dim tmpArr()
tmpArr = Array("Hello", "World")

The above will take it as such. That is no error will be thrown but the tmpArray now has two elements to it.

The Split function will result in a String array. Although Variant type can take in String, a Number array cannot take in the Split function.

More information about this neatly organized here : http://patorjk.com/programming/tutorials/vbarrays.htm

Hope this helps.

answered Aug 20, 2014 at 15:39

PaulFrancis

5,8191 gold badge23 silver badges38 bronze badges

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

Collectives™ on Stack Overflow

Array one dimensional. Why does split work and not Array()

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