Return Array All Values

Question

Would appreciate any guidance in understanding why the following code only returns the last value of an array. I and trying to return the file name of all open Workbooks then select whatever Workbook based on its Array index. The following code finds and returns the name of all open Workbooks but only assigns and returns the last found name to the Array. I am using Preserve with ReDim which accoring to my research should keep adding each File Name to the Array. For my testing I was using four open Workbooks but it only returns the name of the fourth found. Code is:

Sub SelectWB()
    Dim xWBName As String
    Dim xWb As Workbook
    Dim xSelect As String
    Dim WkbCount As Variant
    Dim i As Variant
    Dim MyArray As Variant
    Dim FilName As String
    
    
    xNum = Application.Workbooks.Count
    
         
    For Each xWb In Application.Workbooks

        WkbCount = WkbCount + 1
        xWBName = xWb.Name
        'xWBName = xWBName & WkbCount & " - " & xWb.Name & vbCrLf
     
    
     MyArray = Array(xWBName)
     
        ReDim Preserve MyArray(WkbCount) As Variant
        
        MyArray(WkbCount) = xWBName
    Next
    
    
    For i = LBound(MyArray) To UBound(MyArray)
    
    Debug.Print MyArray(0)
    Debug.Print MyArray(1)
    Debug.Print MyArray(2)
    Debug.Print MyArray(3)
    
    
    Next i
    
      
    FilName = MyArray(0)
    xTitleId = "Open Workbooks"
    'xSelect = Application.InputBox("Enter one of the workbooks below:" & vbCrLf & xWBName, xTitleId, "", Type:=2)
     xSelect = FilName
    Application.Workbooks(xSelect).Activate
End Sub

Answer 1

The issue in your code lies in the placement of the line MyArray = Array(xWBName). Currently, you are assigning a new array with only one element to MyArray in each iteration of the loop. Therefore, in each iteration, you are overwriting the previous array with a new array containing only the current workbook name.

To fix this issue, you need to move the line MyArray = Array(xWBName) outside the loop and initialize MyArray as an empty array before the loop. Then, inside the loop, you can use ReDim Preserve to increase the size of the array and assign the workbook name to the appropriate index.

Here's the modified code:

Sub SelectWB()
    Dim xWb As Workbook
    Dim MyArray As Variant
    Dim WkbCount As Long
    Dim i As Long
    Dim FilName As String
    
    WkbCount = Application.Workbooks.Count
    ReDim MyArray(1 To WkbCount) As Variant ' Initialize MyArray with the correct size
    
    For Each xWb In Application.Workbooks
        WkbCount = WkbCount + 1
        MyArray(WkbCount) = xWb.Name ' Assign the workbook name to the appropriate index
    Next
    
    For i = LBound(MyArray) To UBound(MyArray)
        Debug.Print MyArray(i)
    Next i
    
    FilName = MyArray(1) ' Access the first workbook name using index 1
    
    ' Rest of your code...
    xTitleId = "Open Workbooks"
    'xSelect = Application.InputBox("Enter one of the workbooks below:" & vbCrLf & xWBName, xTitleId, "", Type:=2)
    xSelect = FilName
    Application.Workbooks(xSelect).Activate
End Sub

Answer 2

Kedar Thank you very much for taking the time to answer my post although I was able to solve myself before seeing your post. My solution aligned with your suggested changes. I have also added to the code and have reposted here as the final code maybe useful for others. The final update includes code to cycle through all open Workbooks then ask the user which open Workbook to work on then proceeds to call another macro to do further work on the selected Workbook. Again - many thanks for taking the time to respond with a solution. The problem can be marked as SOLVED.

Sub SelectWB()
    Dim xWBName As String
    Dim xWb As Workbook
    Dim xSelect As String
    Dim WkbCount As Variant
    Dim i As Variant
    Dim MyArray() As String
    Dim FName As String
    
'This macro evaluates all open Workbooks and asks the user to select which one to work on then calls another macro
'to perform further actions on the selected Workbook (in this case insert x Rows after y Row).

    xNum = Application.Workbooks.Count
    
    ReDim Preserve MyArray(1 To xNum) As String
    
    For Each xWb In Application.Workbooks

        WkbCount = WkbCount + 1
        xWBName = xWb.Name
                
        MyArray(WkbCount) = xWBName
            
    Next
    
    For i = LBound(MyArray) To UBound(MyArray)
    
      Workbooks(MyArray(i)).Activate
            numa = MsgBox("Is this the Workbook to insert Rows:- ? " & MyArray(i), 3, "Call RowIns")
 
            If numa = 6 Then
                ' MsgBox "Yes option pressed!"
                Workbooks(MyArray(i)).Activate
                Call RowIns
                ' Exit or ask to do on all open
                Exit Sub
            ElseIf numa = 7 Then
                'MsgBox "No option pressed!"
            Else
                '  MsgBox "Cancel pressed!"
                Exit Sub
            End If
        'End If
    
     Next i
          
    FName = MyArray(i)
    xTitleId = "Open Workbooks"
    xSelect = FName
    Application.Workbooks(xSelect).Activate
End Sub