Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Returning an Array from a called function within a macro | New Users to Excel | |||
Returning an array from the INDEX function | Excel Worksheet Functions | |||
[Newbie question] Returning an array from a function | Excel Programming | |||
function returning array | Excel Programming | |||
returning an array from a custom function | Excel Programming |