CODEWITHSUNDEEP
exceluser-defined-functionsdynamic-arraysvba
Jill448
Jill448

Reputation: 1793

Arrays in Excel VBA macro

I have a folder with many files, out of which I need to: open the files for this week, store them in an array, pass them to a sub, and loop through them for getting summary information.

I am able to get the desired day files from the below code. But the code is throwing an error for storing it in the array and passing it to the array.

Sub BatchProcessing()
    firstday = Date - Weekday(Date) + 2  'To get the 1st day of week
    lastday = Date - Weekday(Date) + 6   'To get the 5th day of week
    MyPath = "P:\Data\"                  'Path where my files were present       
    Dim Day
    Dim strArray(0 To 5) As String
     iCount=0
       For Day = firstday To lastday     'To loop through all 5 day files
          formatted_date = Format(Day, "yyyyMd")
          MyTemplate = "TestFile" & formatted_date & ".xlsx"   ' Set the template.
          Workbooks.Open MyPath & MyTemplate
          strArray(iCount) = ActiveWorkbook.Name   
          iCount = iCount+1    
       Next

    CreateStats(strArray)    'Calling a sub which will do the required calculation 
End Sub


Sub CreateStats(strArray As String)
 For Each element in strArray
    set OriginalWorkbook = strArray(i)
     'Do the processing'
 Next
End Sub

Upvotes: 1

Views: 3433

Answers (2)

StoriKnow
StoriKnow

Reputation: 5867

Your strArray variable is of type Single. If you want that variable to be a string array, you must declare it as such:

Dim strArray(0 to 5) As String

EDIT:

Now that you've changed your code to use strArray() As String rather than strArray As Single, you should update your CreateStats sub procedure to accept an array as a parameter. It should now look something like this:

Private Sub CreateStats(myArray() As String)

As you have it now, your procedure only accepts a single string. It must accept an array of strings. Once you have that, you can loop through each string and do your processing.

Upvotes: 3

techturtle
techturtle

Reputation: 2587

By naming your array strArray it appears that you are going to have an array of strings, and in fact you attempt to store workbook names in it. However, you declared array to be Single, which is a numeric data type. Depending on what your CreateStats(strArray) sub does, you may need to change that from Single to String or possibly set up another array to hold the Single and this one to hold the String.

Upvotes: 1

Related Questions