CODEWITHSUNDEEP
vbaexcelfunction
ViggieSmalls
ViggieSmalls

Reputation: 73

Function doesn't return array

I'm using the function below to find 6 digit numbers in a string. I need to return an array with all 6 digit values, however I keep getting an empty array or empty value whenever I use the function. When I debug the code everything seems fine (the array contains the values I want it to), but after it exits the function the values seem to have disappeared. Any guidance would be appreciated. 

Public Function GetMy6Digits(cell As Range)
Dim s As String
Dim i As Integer
Dim answer
Dim counter As Integer

Dim arrList As Object
Set arrList = CreateObject("System.Collections.ArrayList") 'Create the ArrayList

'get cell value
s = cell.Value

'set the counter
counter = 0
'loop through the entire string
For i = 1 To Len(s)
    'check to see if the character is a numeric one
    If IsNumeric(Mid(s, i, 1)) = True Then
        'add it to the answer
        answer = answer + Mid(s, i, 1)
        counter = counter + 1
        'check to see if we have reached 8 digits
        If counter = 6 Then
            Item = answer
            arrList.Add (Item)
            counter = 0
'           Exit Function
        End If
     Else
     'was not numeric so reset counter and answer
     counter = 0
     answer = ""
    End If
Next i

Dim arr() As String
ReDim arr(arrList.Count)

For i = 1 To arrList.Count
    arr(i) = arrList(i - 1)
Next i
GetMy6Digits = arr()

End Function

Upvotes: 0

Views: 204

Answers (1)

user6432984
user6432984

Reputation:

Your function returns a 0 based array but you are starting your assignments at 1.

For i = 1 To arrList.Count
    arr(i) = arrList(i - 1)
Next i

Correction

    For i = 0 To arrList.Count - 1
        arr(i) = arrList(i)
    Next i

Upvotes: 2

Related Questions