returning an array from a function

[Eric]

I get a type mismatch error at the exit function statement below, and I
don't see how to return a string array in VBA other than via using a variant
return.

Thanks, Eric
----------------------------------------------------------------------------------------------
Public Function WorkbookSheetsToArray(aWkbk As Workbook)
If aWkbk Is Nothing Then Exit Function

On Error GoTo HandleErr
Dim sheetNames() As String
Dim i As Integer, sheetCount As Integer

sheetCount = aWkbk.Sheets.Count
ReDim sheetNames(1 To sheetCount)

For i = 1 To sheetCount
sheetNames(i) = aWkbk.Sheets(i).Name
Next i

WorkbookSheetsToArray = sheetNames

ExitHere:
Exit Function<-----------------crashing here, even though it doesn't get
caught by error handler

HandleErr:
DisplayError "Error " & Err.Number & " (" & Err.Description & _
") in procedure WorkbookSheetsToArray of Module
modExcelWorkBookUtility"
Resume ExitHere

End Function

 

[Jezebel]

Your problem must lie in how you are calling the function. Your code works
correctly called like this --

Dim pArr() As String
Dim pVar As Variant
pArr = WorkbookSheetsToArray(ActiveWorkbook)
pVar = WorkbookSheetsToArray(ActiveWorkbook)


If you want to return an explicit string array, declare the function as
such --

Public Function WorkbookSheetsToArray(aWkbk As Workbook) as string()