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.

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.

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" 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.

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" wrote:

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" 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.

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
==================================


"Walter" wrote in message
...
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" wrote:

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" 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.