Returning an array from a function

[Walter]

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.

 

[Jim Cone]

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" <[email protected]>
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.

 

[Rick Hansen]

Walter, make the change that Jim suggusted , then make the following in your
second sub. I've marked the line changes with <<Changed>>. Your Function
GetStrategicNames() wasn't returning anything. Your function required you to
return a variant type variable, your code was trying returning string array,
thus the return type for your function was the wrong type. The changes I
made should get you back on track...

enjoy, Rick (FBKS, AK)


Private Function GetStrategicNames() As Variant
Dim ws As Worksheet
Dim rngStrat As Range
Dim rngStratPrj As Range
Dim i As Integer
Dim strNames() As Variant '<< Changed>>

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(0 to i - 1) As String '<<Changed>>
For i = LBound(strNames) To UBound(strNames) '<<Changed>>
If rngStratPrj.Cells(i + 1).Value <> "Strategic" Then
strNames(i) = Trim(rngStratPrj.Cells(i + 1).Value)
End If
Next
GetStrategicNames = strNames
End Function

 

[Walter]

Perfect. Thanks guys. Had to make one other change- can't change data types
with ReDim so had to remove "as String" from that statement.

Examples from other posts didn't declare a data type for the function, which
if I understand correctly results in a default to type Variant, and assigned
an array (of longs) to the return value. Why would that work where my string
array couldn't be returned? (See post "Return an array of values from a
function" from 12/13/2004)

Is it possible to declare a function as an array? I suppose in this case it
would be just as easy to return a range.

Always nice to have extra sets of eyes to find my mistakes make me think of
different approaches. Thanks again!!!

 

[Rick Hansen]

Morning Walter, I'm glad you you've got your code straighten out. I'm
sending demo code using "User Defined" variables, to pass back data in a
function. If have any questions post me back..

enjoy, Rick (Fbks,Ak)

========================
Option Explicit

Type Xrec
Ar() As Variant
cRow As Integer
cCol As Integer
Str As String
End Type

Sub testvar()
Dim X As Xrec
Dim r As Integer, c As Integer


X = Fillvar()

For r = 1 To X.cRow
For c = 1 To X.cCol
Debug.Print X.Ar(r, c)
Next c
Next r
Debug.Print X.Str

End Sub

Function Fillvar() As Xre
Dim X As Xrec
Dim hold As Range

Set hold = Range("b3").CurrentRegion
X.cRow = hold.Rows.Count
X.cCol = hold.Columns.Count
ReDim X.Ar(1 To X.cRow, 1 To X.cCol)
X.Ar = hold
X.Str = "We Be Done"
Fillvar = X

End Function
==================================