Running VBA in Excel 2016

[gplhl]

Many years ago, I got hold of a macro to list file names in a folder and its sub folder. All worked flawlessly until work updated to Excel 2016.

The macro is no longer listed in the macro lists to use. It does use a private function to create the list that populates the spreadsheet.

Even if I click in the code to run, it then brings up the macro list and doesn't run the code.

Anyway of getting this one working again?

Option Explicit

  Sub GetFileList()
     Dim myDir As String, myList()
     With Application.FileDialog(msoFileDialogFolderPicker)
         If .Show = True Then
             myDir = .SelectedItems(1)
         End If
     End With
     On Error Resume Next
     myList = SearchFiles(myDir, "*", 0, myList())
     If Err = 0 Then
         Range("A1").Value = "File Path"
         Range("B1").Value = "File Name"
         Range("A2").Resize(UBound(myList, 2), UBound(myList, 1)).Value = _
         Application.Transpose(myList)



     Else
         MsgBox "No file found"
     End If
     On Error GoTo 0
     Application.StatusBar = False
End Sub

Private Function SearchFiles(myDir As String _
     , myFileName As String, n As Long, myList()) As Variant
     Dim fso As Object, myFolder As Object, myFile As Object
     Set fso = CreateObject("Scripting.FileSystemObject")

     For Each myFile In fso.GetFolder(myDir).Files
         If (Not myFile.Name Like "~$*") * (myFile.Name <> ThisWorkbook.Name) _
         * (myFile.Name Like myFileName) Then
             n = n + 1
             ReDim Preserve myList(1 To 2, 1 To n)
             myList(1, n) = myDir
             myList(2, n) = myFile.Name

         End If
     Next
     For Each myFolder In fso.GetFolder(myDir).SubFolders
         SearchFiles = SearchFiles(myFolder.Path, myFileName, n, myList)
     Next
     SearchFiles = IIf(n > 0, myList, "")

End Function

 

[RoryA]

I can't see anything in that code that should be affected by 2016. When you click in the code to run, are you sure you are clicking inside the first Sub and not the Function code?