VBA return an array

Question

I have the following code which is meant to obtain the sheet names and put them into an array:

Function GetSheetNames(WBook) As String()
    Dim TempArray() As String        
    NoSheets = WBook.Sheets.Count
    ReDim TempArray(1 To NoSheets)
    For i = 1 To NoSheets
        TempArray(i) = WBook.Sheets(i).Name
    Next i        
    GetSheetNames = TempArray
End Function

I tried to refactor the code to get rid of the TempArray variable:

Function GetSheetNames(WBook) As String()    
    NoSheets = WBook.Sheets.Count
    ReDim GetSheetNames(1 To NoSheets)
    For i = 1 To NoSheets
        GetSheetNames(i) = WBook.Sheets(i).Name
    Next i
End Function

However it returns an error. Is such thing even possible in VBA? Do I always need a temporary local variable inside a function which at the end gets assigned to the function name in order to return it?

Gary's Student · Accepted Answer · 2014-05-31 10:28:02Z

2

You need the temp array:

Function GetSheetNames(WBook As Workbook)
    NoSheets = WBook.Sheets.Count
    ReDim ary(1 To NoSheets)
    For i = 1 To NoSheets
        ary(i) = WBook.Sheets(i).Name
    Next i
    GetSheetNames = ary
End Function

Sub MAIN()
    Dim arry
    arry = GetSheetNames(ActiveWorkbook)
    For Each a In arry
        MsgBox a
    Next a
End Sub

answered May 31, 2014 at 10:28

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA return an array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related