Function doesn't return array

Question

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

How are you calling the function?

chris neilsen
– chris neilsen

2016-11-18 00:10:27 +00:00
Commented Nov 18, 2016 at 0:10 — chris neilsen
– chris neilsen, Commented Nov 18, 2016 at 0:10
arr = GetMy6Digits(Worksheet.Range("A" & i))

ViggieSmalls
– ViggieSmalls

2016-11-18 00:17:06 +00:00
Commented Nov 18, 2016 at 0:17 — ViggieSmalls
– ViggieSmalls, Commented Nov 18, 2016 at 0:17

user6432984 · Accepted Answer · 2016-11-18 00:20:46Z

2

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

answered Nov 18, 2016 at 0:20

user6432984

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Function doesn't return array

1 Answer 1

Correction

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Correction

Comments

Your Answer

Sign up or log in

Post as a guest

Related