LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 78
Default Returning an array from a function

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.
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Returning an Array from a called function within a macro [email protected] New Users to Excel 1 May 18th 07 03:47 PM
Returning an array from the INDEX function Agenor Excel Worksheet Functions 2 November 28th 06 12:44 AM
[Newbie question] Returning an array from a function dorutzu Excel Programming 4 December 8th 04 10:45 PM
function returning array dreamer[_18_] Excel Programming 1 June 8th 04 10:43 AM
returning an array from a custom function Ron Davis Excel Programming 2 September 15th 03 11:02 AM


All times are GMT +1. The time now is 10:31 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"