UDF Not Returning Array to Range

[FrankJO]

Hello,

In the following function below, I am trying to select a range of cells
containing text, extract the numbers from those text strings, assign each
string to an array index, and finally return the array as a range. I haven't
used an array in a function before, so I'm sure my error is there. The
number-extracting part works fine (the "Digi" loop) as I have used it
elsewhere, but I think I am not assigning the strings to the array properly.
I appreciate any suggestions!

Code:

Public Function ExtractFundID(ByVal HoldingNameRange As Range) As Range
Dim HoldingName As Range
Dim IDOutputList As Range
Dim IDOutputArray() As Integer
Dim OutputIndex As Integer
Dim IDString As String

IDString = ""
OutputIndex = 0

For Each HoldingName In HoldingNameRange

For Digi = 1 To Len(HoldingName)
If IsNumeric(Mid(HoldingName, Digi, 1)) Then
Chara = Mid(HoldingName, Digi, 1)
Else: GoTo Loopy
End If
IDString = IDString & Chara


Loopy:


Next Digi
IDOutputArray(OutputIndex) = IDString
IDString = ""
OutputIndex = OutputIndex + 1

Next HoldingName

ReDim Preserve IDOutputArray(OutputIndex)
ExtractFundID.Name = "FundIDRange"
ExtractFundID.Value = IDOutputArray



End Function

 

[Joel]

Try this

Public Function ExtractFundID(ByVal HoldingNameRange As Range) As Variant
Dim HoldingName As Range
Dim IDOutputList As Range
Dim IDOutputArray() As Integer
Dim OutputIndex As Integer
Dim IDString As String

OutputIndex = 0

For Each HoldingName In HoldingNameRange
IDString = ""
For Digi = 1 To Len(HoldingName)
If IsNumeric(Mid(HoldingName, Digi, 1)) Then
Chara = Mid(HoldingName, Digi, 1)
IDString = IDString & Chara
End If

Next Digi
OutputIndex = OutputIndex + 1
redim preserve IDOutputArray(OutputIndex)
IDOutputArray(OutputIndex) = IDString

Next HoldingName

ExtractFundID = IDOutputArray


End Function

 

[FrankJO]

Thanks for the response, Joel (and the improvements to the code), but
unfortunately I am still getting a "#VALUE!" error when I apply it in a cell.
I have also tried entering the function with "Cntrl + Shift + Enter", just in
case. The range that I am selecting in the function contains variations of
"Fund:1234", so I expect the function to just return "1234" (or whichever
number) for each cell in the range. No luck yet..

 

[Joel]

When I debug functions I do the following

1) Insert break point on the function line in the VBA window by click with
mouse on the line and then pressing F9
2) go back to worksheet a forced a change to the worksheet. I usually do
this by click on the cell with the function call. then go to the F(x) box on
the top of the worksheet a press on the end of the statement. then press
Enter. Excel should stop on the 1st line of the function where the break
point is set.
3) On the step through the code using F8, or add more break points using F9
and pressing F5 to continue to next break point.
4) I also add watch points to the VBA window by highlighting differnt
variable with mouse. then right click the mouse and select ADD Watch and
press OK on pop up window.

 

[FrankJO]

Update: I changed the code to what appears below, and I am no longer getting
a value error, but it is only returning the first item in the array, when it
should return all items in the array to a range of cells. Ideas?

Revised Code:

Public Function ExtractFundID(ByVal HoldingNameRange As Range) As Variant
Dim HoldingName As Range
Dim IDOutputList As Range
Dim IDOutputArray() As String
Dim OutputIndex As Integer
Dim IDString As String

OutputIndex = 0

For Each HoldingName In HoldingNameRange
IDString = ""
For Digi = 1 To Len(HoldingName)
If IsNumeric(Mid(HoldingName, Digi, 1)) Then
Chara = Mid(HoldingName, Digi, 1)
IDString = IDString & Chara
End If
Next Digi

OutputIndex = OutputIndex + 1
ReDim Preserve IDOutputArray(OutputIndex)
IDOutputArray(OutputIndex - 1) = IDString

Next HoldingName


ExtractFundID = IDOutputArray


End Function

 

[Joel]

It works, but you have to highlight the correct number of cells on the
worksheet to get it to work. Also use Shift+Control+Enter

 

[FrankJO]

Thanks, Joel! That did the trick.

It works, but you have to highlight the correct number of cells on the
worksheet to get it to work. Also use Shift+Control+Enter