Excel VBA Array() function causing type mismatch?

Question

I've created the following function to look for files and give errors if the files aren't found:

Public Function checkFileExistence(arrFileNames() As String, Optional bShowErrMsg As Boolean = False) As Boolean
' This function looks for every file name in arrFileNames and returns True if all exist else False
' Optional: bShowErrMsg = True will tell the user which file was missing with a MsgBox
Dim file As Variant

For Each file In arrFileNames
    If Len(Dir(file, vbNormal)) = 0 Then
        checkFileExistence = False
        If bShowErrMsg = True Then MsgBox (file & " was not found.")
        Exit Function
    End If
Next file
checkFileExistence = True
End Function

When I go to call it, I get a type mismatch error though. This happens with a predefined array and also when trying to use the Array() function:

.
Dim filesToFind(1 To 3) As String
filesToFind(1) = "image.png"
filesToFind(2) = "test.png"
filesToFind(3) = "test.fred"

Debug.Print checkFileExistence(filesToFind, True)
Debug.Print checkFileExistence(Array("image.png", "test.png", "test.fred"), True)

This also happens if arrFileNames() is a Variant. What am I doing wrong?

Change arrFileNames() As String to arrFileNames As Variant — dwirony
– dwirony, Commented Mar 1, 2018 at 18:37

Mathieu Guindon · Accepted Answer · 2018-03-01 18:46:40Z

7

Array doesn't return a typed array (e.g. String()).

Change your signature to take a Variant instead:

Public Function checkFileExistence(arrFileNames As Variant, Optional bShowErrMsg As Boolean = False) As Boolean

And you can always validate that you're looking at an actual array, with the IsArray function:

    If Not IsArray(arrFileNames) Then Err.Raise 5, "CheckFileExistence", "Expected array, but received a " & TypeName(arrFileNames) & "."

Also I'd warmly recommend changing your loop to a For...Next loop. Arrays don't want to be iterated with For Each - see this article.

For i = LBound(arrFileNames) To UBound(arrFileNames)

edited Mar 1, 2018 at 18:46

answered Mar 1, 2018 at 18:37

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

4 Comments

dwirony Over a year ago

Hi Mat - "Array doesn't returned a typed array". - Could you elaborate on this for me please? I figured it out by messing around in the module, but I actually have no idea why this works.

DukeSilver Over a year ago

Ah! That did it. The article link is also much appreciated.

dwirony Over a year ago

So I guess what I'm wondering is why arrFilesNames() As Variant doesn't pass?

Mathieu Guindon Over a year ago

@dwirony I've yet to figure out the correct wording for this, but essentially... the way arrays get passed around in VBA is a bit clunky ;-) Variant() says "every item is a Variant" (and VBA won't take an Integer() or String() for it), while Variant says "this can be anything including an array of anything".

Collectives™ on Stack Overflow

Excel VBA Array() function causing type mismatch?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related