Excel VBA find last entry in an array (but not in the spreadsheet)

Question

I have some Excel code that assembles an array of file names, and then loops through and extracts some data from them. The data is not in the spreadsheet itself - it is completely assembled within VBA. New files are added each month, so the number will vary.

My problem is that code that was working is no longer working, and I'm trying to figure out a workaround. (Related: Error: Microsoft Excel has stopped working - But I didn't change anything)

UBound finds the size of the array. But the array is not completely filled with data. How do I find the last item in the array that has something in it?

I am searching and finding answers that relate to finding the number of items on a spreadsheet, but this doesn't really use a worksheet. IT seems like CountA might be what I want, but Excel: Find last value in an array doesn't have an example that I can figure out to make work in my case.

In other words, I'd like to use something besides UBound in the code below, so I don't go past the entries that have something in them.

    FName = Array("april2010.xls", "feb2010.xls", "jan2010.xls", "july2010.xls", "june2010.xls", _
            "mar2010.xls", "may2010.xls", "sep2010.xls", "..\FINAL-MO-BAL-2011\APRIL2011.xls", _
            "..\FINAL-MO-BAL-2011\AUG2011.xls", "..\FINAL-MO-BAL-2011\DEC2011.xls", _
            "..\FINAL-MO-BAL-2011\FEB2011.xls", "..\FINAL-MO-BAL-2011\JAN2011.xls", _
            "..\FINAL-MO-BAL-2011\JULY2011.xls", "..\FINAL-MO-BAL-2011\JUNE2011.xls", _
            "..\FINAL-MO-BAL-2011\MARCH2011.xls", "..\FINAL-MO-BAL-2011\MAY2011.xls", _
            "..\FINAL-MO-BAL-2011\NOV2011.xls", "..\FINAL-MO-BAL-2011\OCT2011.xls", _
            "..\FINAL-MO-BAL-2011\SEP2011.xls", FName2, FName3, FName4, FName5, FName6, _
            FName7, FName8, FName9, FName10, FName11, FName12, FName13, FName14, FName15, _
            FName16, FName17, FName18, FName19, FName20, FName21, FName22, FName23, FName24, _
            FName25, FName26, FName27, FName28, FName29, FName30, FName31, FName32, FName33, _
            FName34, FName35, FName36, FName37, FName38, , FName39, FName40, FName41, FName42, _
            FName43, FName44, FName45, FName46, FName47, FName48, FName49)

If IsArray(FName) Then
    Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    WorkbookName = ThisWorkbook.Name
    rnum = 1
    For Fnum = LBound(FName) To UBound(FName)
        Set mybook = Nothing
        On Error Resume Next
        Set mybook = Workbooks.Open(Filename:=FName(Fnum), ReadOnly:=True)

        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .EnableEvents = False
            .CutCopyMode = False
            .DisplayAlerts = False
            .Visible = False
        End With

        On Error GoTo 0

        If Not mybook Is Nothing Then
            On Error Resume Next

empty it in into another array (initially sized the same) but excluding where len =0 or some such logic, increment a counter with this and then redim preserve counter -1 at end? — QHarr, Dec 22 '17 at 20:39
Or loop the array and Exit the loop when test condition is met e.g. =VbNullString and take the current index? — QHarr, Dec 22 '17 at 20:40
Can you provide more information about ***no longer working*** and ***not completely filled with data*** ? Do you mean that some `FNameXX` variable is empty, or that it refers to a file that doesn't exist on your filesystem ? — Robin Mackenzie, Dec 23 '17 at 00:01
Some of the FName entries have not been filled with anything. — thursdaysgeek, Dec 23 '17 at 00:29
But...I can count them as the data is put in, and use that for my count! — thursdaysgeek, Dec 23 '17 at 00:31
The directory name seems to be ambiguous. The path seems to be wrong. — Dy.Lee, Dec 23 '17 at 03:37

score 2 · Answer 1 · answered Dec 22 '17 at 21:30

Use the Filter function

Dim fName As Variant
Dim fltName As Variant

fName = Array("april2010.xls", "feb2010.xls", "jan2010.xls", "july2010.xls", "june2010.xls", _
            "mar2010.xls", "may2010.xls", "sep2010.xls", "..\FINAL-MO-BAL-2011\APRIL2011.xls", _
            "..\FINAL-MO-BAL-2011\AUG2011.xls", "..\FINAL-MO-BAL-2011\DEC2011.xls", _
            "..\FINAL-MO-BAL-2011\FEB2011.xls", "..\FINAL-MO-BAL-2011\JAN2011.xls", _
            "..\FINAL-MO-BAL-2011\JULY2011.xls", "..\FINAL-MO-BAL-2011\JUNE2011.xls", _
            "..\FINAL-MO-BAL-2011\MARCH2011.xls", "..\FINAL-MO-BAL-2011\MAY2011.xls", _
            "..\FINAL-MO-BAL-2011\NOV2011.xls", "..\FINAL-MO-BAL-2011\OCT2011.xls", _
            "..\FINAL-MO-BAL-2011\SEP2011.xls", "FName2", "FName3", "FName4", "FName5", "FName6", _
            "FName7", "FName8", "FName9", "FName10", "FName11", "FName12", "FName13", "FName14", "FName15", _
            "FName16", "FName17", "FName18", "FName19", "FName20", "FName21", "FName22", "FName23", "FName24", _
            "FName25", "FName26", "FName27", "FName28", "FName29", "FName30", "FName31", "FName32", "FName33", _
            "FName34", "FName35", "FName36", "FName37", "FName38", "", "FName39", "FName40", "FName41", "FName42", _
            "FName43", "FName44", "FName45", "FName46", "FName47", "FName48", "FName49")

fltName = Filter(fName, ".")

Debug.Print LBound(fltName), UBound(fltName)

I'm not sure what FName2, FName3, etc are supposed to be. You said there were array elements with no data, so maybe they are just placeholders to demonstrate.

At any rate, I filtered on a dot so filter out what wasn't a file name. You may want to filter on something different.

I set fltName as type Variant, same as FName, and when I get to the fltName = Filter(FName, "."), it gives me a type mismatch error. On the other hand, since all files will end in .xlsx, then a period is a good thing to filter on. — thursdaysgeek, Dec 22 '17 at 22:09
With the example data given, if you filter on '.xlsx' `fltName` will be `Nothing` as no item will match. Otherwise, this code is the fastest you can get. Be sure to use `LBound(fltName)` in your loop as the filtered array always is starting at index 0, not 1 (which you might use in your code). — user1016274, Dec 23 '17 at 15:48
Type Mismatch on the `Filter` function means that `fName` is not an array. When you get that error, got the Immediate Window and type `?typename(fname)` and you if you get something other than `Variant()` or `String()` or some other array type, then you're code isn't putting an array into fName. — Dick Kusleika, Dec 26 '17 at 15:46

score 1 · Answer 2 · answered Dec 22 '17 at 20:55

1

You could do something like this right before you start your loop through the FName array:

Dim LastFilled As Integer    'a variable to hold the last location filled in the array
'This is checking the array from the last to first 
For i = UBound(FName) To 1 Step -1
    If FName(i) Is Not Empty Then
        LastFilled = i
        Exit For
    End If
Next i

Then replace your UBound(FName) with LastFilled

answered Dec 22 '17 at 20:55

Mike

1,853
3
45
75

I get Run-time error '424': Object required on the If FName(i) Is Not Empty line. But that could still be a useful clue. – thursdaysgeek Dec 22 '17 at 23:52

score 0 · Answer 3 · answered Dec 23 '17 at 09:34

Instead of skipping the error, check that the entry in the array is not empty and that the path exists.

You may also have to change the current directory with ChDir if CurDir doesn't return the expected one.

Something like:

Dim i As long, fname As String

' set the current directory with the directory of this workbook '
ChDir ThisWorkbook.Path

For i = LBound(FNames) To UBound(FNames)
    fname = FNames(i)

    ' if has entry '
    If Len(fname) Then

        ' if file exists '
        If Len(Dir(fname)) Then

            ' open workbook '
            Set wb = Workbooks.Open(Filename:=fname, ReadOnly:=True)


        End If
    End If
Next

Excel VBA find last entry in an array (but not in the spreadsheet)

3 Answers3