Array not passing to function

[trough]

I believe I have an array function IF statement that returns an array that I then try to pass to a user function to perform further processing of the data before returning to the workbook with the final array answer. However, it appears as though the user function isn't even getting ran. I set a break at the Redim line and it's never seen. It's like nothing is happening. I don't normally work with ranges or the passing of arrays so I've been researching but I am not having any luck. Could someone please tell me if I'm passing the data into and out of the function properly? Column H shows the result I desire in column E where the function is being used.


Excel 2010

	A	B	C	D	E	F	G	H
Smith, John	-	JDoe
Doe, John	JDoe	TCarver
Jones, Mary	-	-
,	-	-
Carver, Tim	TCarver	-
____________|
__|

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"] Name
[/TD]
[TD="align: center"] User [/TD]
[TD="align: center"] Active [/TD]
[TD="align: right"][/TD]
[TD="align: center"] UserList [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]JSmith[/TD]
[TD="align: center"]No[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#VALUE![/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]JDoe[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#VALUE![/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]MJones[/TD]
[TD="align: center"]No[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#VALUE![/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]SLincoln[/TD]
[TD="align: center"]No[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#VALUE![/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]TCarver[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: right"][/TD]
[TD="align: right"]#VALUE![/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]|[/TD]
[TD="align: center"]|[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Result of IF statement[/TD]

[TD="align: center"]|[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]|[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Desired result of UserList function[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E2:E6[/TH]
[TD="align: left"]{=UserList(IF(A2:A6=", ","-",IF(C2:C6="No","-",B2:B6)))}[/TD]
[/TR]
[TR]
[TH]G2:G6[/TH]
[TD="align: left"]{=IF(A2:A6=", ","-",IF(C2:C6="No","-",B2:B6))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Option Explicit
Option Base 1


Function UserList(UName() As String)

' declare variables
Dim MyList() As String
Dim nList As Long
Dim i As Long
Dim j As Long

' get list size and dimension dynamic variable
nList = UBound(UName)
ReDim MyList(nList)

' populate dynamic list with names
j = 0
For i = 1 To nList
 If UName(i) <> "-" Then
  j = j + 1
  MyList(j) = UName(i)
 End If
Next i

' populate empty variable positions
If j < nList Then
 For i = j + 1 To nList
  MyList(i) = "-"
 Next i
End If

' write back to workbook
UserList = MyList()

End Function

 

[VBA Geek]

try to make these changes

Function UserList([B]UName As Variant[/B]) 

' declare variables
Dim MyList() As String
Dim nList As Long
Dim i As Long
Dim j As Long

' get list size and dimension dynamic variable
nList = UBound(UName)
[B]ReDim MyList(1 To nList)[/B]

' populate dynamic list with names
j = 0
For i = 1 To nList
 If UName(i[B], 1[/B]) <> "-" Then
  j = j + 1
  MyList(j) = UName(i[B],[/B] [B]1[/B])
 End If
Next i

' populate empty variable positions
If j < nList Then
 For i = j + 1 To nList
  MyList(i) = "-"
 Next i
End If

' write back to workbook
UserList = MyList()

End Function

 

[RoryA]

Isn't this the same question you posted here?

 

[trough]

It works now except I had to change the last line to:

UserList = Application.Transpose(MyList)

This is interesting because I had read that when passing arrays the type had match exactly what the data type actually was (in this case String) and that I couldn't use variant. I had tried Variant before but missed treating it as a 2-dimensional array.

Thank you.