none
Displaying Array Values from User Defined Function RRS feed

  • Question

  • Hello,

    I'm relatively new to Excel and VBA but I have a background in programming (namely PHP).

    I'm trying to pick up VBA to use in different Microsoft applications but mainly focused on Excel at the moment.

    Ok, I made a function in VBA (following a tutorial and modifying it based on my needs) that pulls the filenames from a specified folder and returns the data in an array. I wanted to have the each value in the array entered into each subsequent row. If I output the results off the array, one value at a time, to a MsgBox, the results are coming in.

    But when I try to see it in the spreadsheet, it just shows the first value in array in cell A1. This is what I have entered in A1.

    =IFERROR(INDEX(GetFileNames(), ROW()),"")

    Here is my function.

    Function GetFileNames()
        
        ' Set folder path
        Const FolderPath As String = "C:\Users\jbishop\Desktop\SOP Audit Excel Prototype"
        Const FileExt As String = "docx"
        
        Dim Result As Variant
        Dim i As Integer
        Dim MyFile As Object
        Dim MyFSO As Object
        Dim MyFolder As Object
        Dim MyFiles As Object
        
        Set MyFSO = CreateObject("Scripting.FileSystemObject")
        Set MyFolder = MyFSO.GetFolder(FolderPath)
        Set MyFiles = MyFolder.Files
        
        ' Research built-in Result function in VBA
        ReDim Result(1 To MyFiles.Count)
        
        i = 1
        
        ' Loop through filenames and return within an array
        For Each MyFile In MyFiles
            If InStr(1, MyFile.Name, FileExt) <> 0 Then
                  Result(i) = MyFile.Name
                  'MsgBox MyFile.Name
                  i = i + 1
            End If
            
            ' Debug by returning value to msgbox
            'MsgBox MyFile.Name
            
            Next MyFile
            ReDim Preserve Result(1 To i - 1)
            
            'Return value on function end
            GetFileNames = Result
        
    End Function
    

    It show's the filename of the first file in the folder specified by the function I made. I assume maybe I'm using the INDEX function wrong?

    All of your help is very much appreciated! I'm looking forward to learning more VBA.

    Thank you!


    Sunday, June 30, 2019 6:21 PM

Answers

  • I copied your code into a module and change the folder to a folder containing Word documents on my computer.

    As you can see, the first two .docx files are Adams.docx and Adreslijst.docx (there is a .docm file at the top, but that shouldn't be included).

    Here is the result. I entered the formula =IFERROR(INDEX(getfilenames(), ROW(A1)),"") in A1, then dragged the fill handle (the black square in the lower right corner of A1) down several rows (that is what I meant by filling down)

    The first filename is in A1, the second one in A2 etc.

    That looks perfectly OK to me...


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by mongoose00318 Sunday, July 7, 2019 11:58 PM
    Sunday, June 30, 2019 8:07 PM

All replies

  • Did you fill the formula down to A2, A3, …?

    What you have done should work, in principle - it did in a small test I performed just now.

    So it might help if you posted the code of the function.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, June 30, 2019 6:51 PM
  • OK I just posted the function as an edit to my first post.

    What do you mean by fill the formula down to A2, A3, etc.

    The number of files in the specified folder will increase over time, so I didn't want to have to limit it by saying A1:A10 for example...

    I just wanted it to continue to output a value to the next row until it has completed looping through the array.


    Sunday, June 30, 2019 6:55 PM
  • Strange...

    If I change this..

    =IFERROR(INDEX(GetFileNames(), ROW(A4)),"")

    Say I put a cell number in that formula, like A4...it shows the 3rd index of the array coming back from the function. 

    A5 - 4th index of array

    Etc...

    Is this any help?

    Sunday, June 30, 2019 7:02 PM
  • I copied your code into a module and change the folder to a folder containing Word documents on my computer.

    As you can see, the first two .docx files are Adams.docx and Adreslijst.docx (there is a .docm file at the top, but that shouldn't be included).

    Here is the result. I entered the formula =IFERROR(INDEX(getfilenames(), ROW(A1)),"") in A1, then dragged the fill handle (the black square in the lower right corner of A1) down several rows (that is what I meant by filling down)

    The first filename is in A1, the second one in A2 etc.

    That looks perfectly OK to me...


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by mongoose00318 Sunday, July 7, 2019 11:58 PM
    Sunday, June 30, 2019 8:07 PM
  • Ok I figured out how to make it populate the other cells. Need to figure out how to make it run the function everytime the file opens now.

    Thanks for your all of your help!

    Sunday, June 30, 2019 8:34 PM
  • In the Visual Basic Editor, double-click ThisWorkbook under Microsoft Excel Objects in the left hand pane (the Project Explorer).

    Copy the following code into the ThisWorkbook module:

    Private Sub Workbook_Open()
        Application.CalculateFull
    End Sub

    Switch to Excel.

    Save and close the workbook. The list will be refreshed each time the workbook is opened. Depending on the number of files in the folder, this may cause a delay while the workbook is opened.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, June 30, 2019 8:48 PM