Cannot able to store value in a array - VBA

Question

I am trying to store a filename in a array but i am getting Type mismatch error. I have changed the data type but it didn't work. Kindly help.

The code block that is throwing error,

Sub Example2()
Dim objFile,objFile1,objFolder,objFolder1 As Object
Dim splitting, counter, filename, filename1, splitting1, counter1,As Variant
Dim myarray() As Variant

For Each objFile In objFolder.Files

splitting = Split(objFile.Name, "\", 9)

counter = UBound(splitting)

filename = splitting(counter)

    For Each objFile1 In objFolder1.Files

    splitting1 = Split(objFile1.Name, "\", 9)

    counter1 = UBound(splitting1)

    filename1 = splitting1(counter1)
    
    If srch1 = srch2 Then
    
    ReDim Preserve myarray(UBound(myarray) + 1)
    
    myarray() = filename1

    End If
    
    Next

    Next 

Answer 1

Get File Paths (to Array) Function

Links

Objects
FileSystemObject Object
GetFolder Method
File Object

The Code

Option Explicit

Function getFilePaths(ByVal FolderPath As String, _
                      Optional ByVal FirstIndex As Long = 1) _
         As Variant
    
    Dim fso As Object
    Dim fsoFldr As Object
    Dim fsoFile As Object
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFldr = fso.GetFolder(FolderPath)
    
    Dim LastIndex As Long
    LastIndex = FirstIndex - 1
    Dim Data() As Variant
    
    For Each fsoFile In fsoFldr.Files
        LastIndex = LastIndex + 1
        ReDim Preserve Data(FirstIndex To LastIndex)
        Data(LastIndex) = fsoFile.Path ' or .Name, .ParentFolder ...
    Next fsoFile
    
    getFilePaths = Data

End Function

Sub TESTgetFilePath()
    
    ' Define Folder Path ('fPath').
    Const fPath As String = "F:\Test\2020"
    ' Populate File Paths Array ('Data').
    Dim Data As Variant
    Data = getFilePaths(fPath)
    ' Validate File Paths Array.
    If IsEmpty(Data) Then
        MsgBox "No files found.", vbCritical, "Fail"
        Exit Sub
    End If
    ' Write title to the Immediate window (CTRL+G).
    Debug.Print "The List"
    ' Write values from File Paths Array to a String ('Result').
    Dim Result As String
    Result = Join(Data, vbLf)
    ' Write file paths to the Immediate window (CTRL+G).
    Debug.Print Result

End Sub

EDIT 1:

Sub Example2()
    
    Const FolderPath As String = "C:\Test"

    Dim fso As Object
    Dim objFolder As Object
    Dim objFile As Object
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set objFolder = fso.GetFolder(FolderPath)

    Dim LastIndex As Long
    LastIndex = -1
    Dim MyArray() As Variant
    
    For Each objFile In objFolder.Files
        LastIndex = LastIndex + 1
        ReDim Preserve MyArray(LastIndex)
        MyArray(LastIndex) = objFile.Name
    Next objFile
    
    Dim n As Long
    For n = LBound(MyArray) To UBound(MyArray)
        Debug.Print n, MyArray(n)
    Next n

End Sub

EDIT 2:

Sub Example3()
    
    ' For a fileformat aaa-bbb-rev*.*, where 'rev' is to be tested if greater.
    ' Two hyphens only.
    
    Const FolderPath As String = "F:\Test\2020\64568450"
    Const fSep As String = "-"
    Dim pSep As String
    pSep = Application.PathSeparator
     
    Dim fso As Object
    Dim objFolder As Object
    Dim objFile As Object
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set objFolder = fso.GetFolder(FolderPath)
    
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
   
    Dim FileParts As Variant ' An array containing the split file name.
    Dim fName As String      ' File part before the 2nd hyphen (minus) '-'
    Dim fRevision As String  ' File part after the 2nd hyphen (minus) '-'
    
    Dim LastIndex As Long
    LastIndex = -1
    Dim MyArray() As Variant
    
    ' Write file paths to array.
    For Each objFile In objFolder.Files
        FileParts = Split(objFile.Name, fSep)
        fName = FileParts(0) & fSep & FileParts(1)
        fRevision = FileParts(2)
        If Not dict.Exists(fName) Then
            dict(fName) = fRevision
        Else
            LastIndex = LastIndex + 1
            ReDim Preserve MyArray(LastIndex)
            If dict(fName) < fRevision Then
                MyArray(LastIndex) = FolderPath & pSep & fName _
                                                & fSep & fRevision
                dict(fName) = fRevision
            Else
                MyArray(LastIndex) = objFile.Path
            End If
        End If
    Next objFile
    
    ' Now 'MyArray' contains the list of file paths of the files to be moved.
    
    Dim n As Long
    For n = LBound(MyArray) To UBound(MyArray)
        Debug.Print n, MyArray(n)
    Next n

End Sub

Answer 2

Arrays in VBA can be either static or dynamic.

A static array is declared with a fixed size:

Dim myStaticArr(10) As String

declares an array with a fixed number of members (usually 11 as the lower index starts at 0, but you can overwrite this).
If you want to be sure about the lower index, you can specify

Dim myStaticArr(1 to 10) As String

Now you have 10 elements (from 1 to 10).

Similar, a multidimensional array can be defined

Dim myStaticArr3D(1 to 10, 1 to 5, 1 to 8) As String

Now you have an array with 10 * 5 * 8 members.

All of these arrays have in common that you need to declare at compile time the size of the array. The VBA compiler will reserve the necessary amount of memory and you cannot resize it.

If you don't know at compile time how large your array will be, you can declare it as dynamic array (as you do)

Dim myDynamicArr() as String

This reserves no memory at all. Before you can write something into the array, you need to tell VBA how big the array will be. This is done using the Redim statement. Easiest form:

Redim myDynamicArr(1 to 10) as String

Usually, this is done after calculating the size needed, so you will usually find the Redim having a variable that was used to calculate the needed size:

Redim myDynamicArr(1 to sizeNeeded) as String

Now there are cases where you find at runtime that the needed size is too small. You can issue another Redim to increase the size - but as you want to keep the content of the array, you specify the option Preserve:

Redim Preserve myDynamicArr(1 to 2*sizeNeeded) as String

This will double the size and keep the content of the first members (omitting the Preserve option will double the size but the content of the existing members will get lost).

To get the current size of an array, you can use the functions LBound and UBound. This can be used on static and dynamic arrays:

Dim myStaticArr(5 to 99) As String
Debug.Print LBound(myStaticArr), UBound(myStaticArr)
>> 5 99
Dim myDynamicArr() As String
ReDim myDynamicArr(1 to 20) 
Debug.Print LBound(myDynamicArr), UBound(myDynamicArr)
>> 1 20

However, if you have a dynamic array and you never assigned memory to it, the functions LBound and UBound will throw a runtime error 9 "Subscript out of range"

---

Now what you want to do is to increase the size of the array by 1 every time you find a new value. You achieve this with

ReDim Preserve myarray(UBound(myarray) + 1)

which will look to the current size of the array using the UBound-function and resize it by 1, preserving its contents. That's fine, except for the fact that the very first time this statement is hit, the size of the array is undefined.

The easiest way to handle this is to use a variable that keeps track of your array size:

Dim myArray() as String, myArraySize as Long
(...)

myArraySize = myArraySize + 1
ReDim Preserve myArray(1 to myArraySize)
myarray(myArraySize) = filename1

One remark: ReDim Preserve is a rather expensive command. If you are dealing with a few entries, this doesn't matter, but if you are dealing with 100s or 1000s of elements, you should consider to use a Collection.

Answer 3

You should index to the array to set the value after Redim:

ReDim Preserve myarray(UBound(myarray) + 1)
myarray(ubound(myarray)) = filename1

VBA arrays are so finicky and frustrating. I would add items to a string and after then split it to an array:

Dim strArray As String
strArray = ""
REM .....
strArray = strArray + filename1 + ","
REM .....
myarray = Split(strArray,",")