The first thing I spotted is that you used a Set statement on vNames.
That makes it a Variant of type object, but you are returning a variant that
does not contain an object.
Delete "Set" so the line reads... vNames = GetStrategicNames()
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Walter"
wrote in message
Trying to return an array of cell values which will then be passed to another
function. The function is a variant, and returns a variant/string array to a
variant variable in the calling sub. Everything works fine in the debugger
until I step across the "End Function" line. Then I get a type mismatch
error. The function's array is correctly loaded with all the values it should
have, and is being passed back to a variant. What am I missing?

Private Sub BuildReport()
Dim wsSLA As Worksheet
Dim wsPrjPrd As Worksheet
Dim oCell As Object
Dim vNames As Variant
Dim i As Integer

Set wsPrjPrd = Sheets("Projects & Products")
Set wsSLA = Sheets("SLA Report")

wsPrjPrd.Cells.Copy Destination:=wsSLA.Cells

Set vNames = GetStrategicNames()
FindStrategicProjects (vNames)
'more code here after jumping this hurdle
End Sub

Private Function GetStrategicNames() As Variant
Dim ws As Worksheet
Dim rngStrat As Range
Dim rngStratPrj As Range
Dim i As Integer
Dim strNames() As String

Set ws = Sheets("Strategic")
Set rngStrat = ws.Cells.Find(what:="Strategic")
Set rngStratPrj = ws.Range(rngStrat.Offset(1, 0).Address,
rngStrat.Cells.End(xlDown).Address)
i = rngStratPrj.Rows.Count

ReDim strNames(i - 1) As String
For i = 0 To UBound(strNames)
If rngStratPrj.Cells(i + 1).Value < "Strategic" Then
strNames(i) = Trim(rngStratPrj.Cells(i + 1).Value)
End If
Next
GetStrategicNames = strNames
End Function

I have spent hours reading posts about passing arrays from functions, and I
had this working smoothly. After adding another function to the workbook, I'm
getting errors. BTW, the new function does not reference anything in this
code, and is not yet called by any code, so it shouldn't affect anything.