Error 13 when passing an array to a function that should fill it

Question

First of all: I am fairly new to VBA, so please excuse me if the question is rather trivial, but this mistake has been keeping me busy all day - I have absolutely no clue.

I am working on a small macro to look through a folder, count the files and fill a 2d array with the full file name and a specific section of the name. So I am creating an array in my main sub and call the function that takes the empty array as a parameter and fills it.

My macro looks somewhat like this:

Private Sub whatever()
    Dim arr(10, 2) As String
    Dim count As Integer
    CheckFolder(arr, "somepath", count)
End Sub

Sub CheckFolder(ByRef arr() As String, strPath As String, count As Integer)

    Dim fso, oFolder, oSubfolder, oFile, queue As Collection
    Set fso = CreateObject("Scripting.FileSystemObject")

    Dim fileCount As Integer
    Dim temp(10, 2) As String
    fileCount = 1

    WriteToLog "zähle Files in Ordner " & strPath & "... "

    Dim path As String
    path = ActiveWorkbook.path & "\" & strPath
    Set queue = New Collection
    queue.Add fso.GetFolder(path) '- Pfad zum Ordner

    Do While queue.count > 0
        Set oFolder = queue(1)
        FolderName = fso.GetFileName(oFolder)
        queue.Remove 1 'dequeue
        For Each oSubfolder In oFolder.SubFolders
          queue.Add oSubfolder 'enqueue
        Next oSubfolder
        For Each oFile In oFolder.Files
            Filename = fso.GetFileName(oFile)
            '- my guess is the next two line are the problem?
            temp(fileCount, 1) = Filename
            temp(fileCount, 2) = StringCutter(Filename)
            fileCount = fileCount + 1
        Next oFile
    Loop

    arr = temp
    count = fileCount

End Sub

I am not sure, but I think that those two lines are the problem (as the rest of the logic worked perfectly fine before)

temp(fileCount, 1) = Filename
temp(fileCount, 2) = StringCutter(Filename)

The function "StringCutter" that is called here, returned a substring of the filename. I tested that function before and I works, so I don't think it is causing any problem.

I would be much appreciated if someone could tell me where my mistake is.

EDIT: this is the StringCutter function, that takes a string and cuts out a certain portion of it and returns this portion. As mentioned before, this function works perfectly fine when I use it outside of filling an array.

Function StringCutter(str As String) As String

    Dim ret As String
    Dim retLen As Integer
    Dim pos As Integer

    retLen = Len(str)
    ret = Right(str, (retLen - 31))
    pos = InStr(ret, "_")

    If (pos > 0) Then
        ret = Left(ret, (pos - 1))
    Else
        ret = Left(ret, 4)
    End If

    StringCutter = ret

End Function

I hope that helps

What error do you get and on which line? What is the value of `filecount` when it errors? — SJR, Mar 06 '19 at 19:58
@SJR: I get error 13: "type mismatch". My macro jumps to the error routine when I try to execute "arr = temp". — McMuellermilch, Mar 07 '19 at 08:19
@AlexdeJong: I edited my post and added the function. Hope that helps — McMuellermilch, Mar 07 '19 at 08:20
@TimWilliams: My function iterates through a folder and all subfolders, increases a counter variable on every file in these folders. It also creates and fills an array with two values on every iteration: arr(i,1) = fill filename through the Filename variable, arr(i,2) = a small substring extracted from the filename through the function StringCutter. In the end I set the array that has been passed to the function equals my generated and filled array (this is where I get the error). — McMuellermilch, Mar 07 '19 at 08:26

score 1 · Answer 1 · answered Mar 07 '19 at 09:20

1

In VBA, I don 't think you can copy one array into another like that - arr = temp - you can with range arrays, but not pure arrays. You need to cycle through all the values and copy one at a time:

For y = LBound(temp, 2) To UBound(temp, 2)
    For x = LBound(temp, 1) To UBound(temp, 1)
        arr(x, y) = temp(x, y)
    Next
Next

answered Mar 07 '19 at 09:20

CLR

11,284
1
11
29

2

You [absolutely can](https://stackoverflow.com/a/22909268/11683) copy arrays directly. They only restriction is that they can't be fixed size, which `arr` is. – GSerg Mar 07 '19 at 09:31
Also, watch out for folders with filenames of less than 32 characters - you should consider trapping that eventuality in `StringCutter` so you don't attempt to shorten something beyond its length. – CLR Mar 07 '19 at 09:40
Eventhough the function is build to iterate through folders as well, the function will only be called on one folder which has files in it that have all the same kind of name, so the -31 is calculated. But I know that it is not the best style to have so functionality as hardcoded as that. – McMuellermilch Mar 07 '19 at 22:08

score 0 · Accepted Answer · answered Mar 07 '19 at 09:33

0

I think I figured it out! I was using the variable "Filename" which I guess is from the oFile element, because I didn't create it. Maybe that's why the types weren't compatible. Creating a Dim fileName AS String and using this variable here:

For Each oFile In oFolder.Files
        fileName = fso.GetFileName(oFile)
        temp(fileCount, 1) = fileName
        temp(fileCount, 2) = StringCutter(fileName)
        fileCount = fileCount + 1
 Next oFile

solved the problem. Thank you all for your help! :)

answered Mar 07 '19 at 09:33

McMuellermilch

95
1
10

I can't see how that would help with the error on `arr = temp`. You can't directly assign to a fixed-size array. – GSerg Mar 07 '19 at 09:39
Hi @GSerg, I helps because now I don't need temp and with that arr=temp. I can pass in the array arr as a parameter and fill it directly without using the temp array anymore. My problem was that I was trying to use a non String variable when filling my array. At least I think that was the problem. I am very new to VBA, so I am not really sure what I am doing, but at least now my sub fills the passed array without any error – McMuellermilch Mar 07 '19 at 09:41
If you don't declare a variable it's always a variant. Use `Option Explicit`, which enforces predeclared variables. – Nacorid Mar 08 '19 at 10:22
thanks for the tipp! that means I can use any variable in my code without declaring it and vba will just make it a variant? – McMuellermilch Mar 08 '19 at 22:13
You can, but you should not do that, because typos happen and are a nightmare to debug without `Option Explicit`. – Nacorid Mar 11 '19 at 13:37

Error 13 when passing an array to a function that should fill it

2 Answers2