Excel VBA: Element Not Being Stored in Loop

Question

The following issue has me perplexed. I have created a function to provide the column index of a particular column based on several inputs:

An Array (from a Range of column headers)
The number of data elements (Integer)
An Array of selected column names
An Array to hold the results for passage back to the sub.
Counters (global)

Obviously, the intention here is to pass the column indices to other functions and routines for offsetting and additional processing.

While the function itself is working, it is NOT working for the second data element. K will be recorded with appropriate index, but it won't be passed to the array. Is there something I am missing here?

Public Function getIndex(ByRef all_names As Variant, ByVal Elements As Integer, check_names() As Variant, resultindex() As Variant) As Variant
    ReDim resultindex(1 To Elements)
    For i = LBound(all_names) To UBound(all_names)
        For j = 1 To Elements
            For k = LBound(all_names, 2) To UBound(all_names, 2)
                If all_names(i, k) = check_names(j) Then                                    ' checks colName against dynamic names
                    resultindex(j) = k                                                      ' colIndex takes index of selected column
                    Debug.Print resultindex(j)
'                    k = UBound(all_names, 2)                                               ' Jump to end?
                End If
            Next k
         Next j
    Next i
End Function

Is there a particular reason why the second element is not stored? I have tried this with several different inputs and have achieved the same result. Would really appreciate anyone who's good at nested-loops to give me a nod in the right direction on this. Thanks.

Edit: it looks like it is working in the immediate window. The appropriate indices are being snagged as expected, but the second element is not being passed out.

Verifying that the values were passed:

    results(i) = getIndex(subArray(), Elements, selNames(), results())
colIndex() = results()

For i = 1 To Elements
    Debug.Print colIndex(i)
Next i
Erase result

VBA is a little weird compared to many common languages with "return" statements. VBA creates an automatic variable with the same name as the function, and whatever you assign to this variable is what gets returned. So right before the function ends (assuming you want to return the array resultindex) you would add the line getIndex = resultIndex. — Blackhawk
– Blackhawk, Commented Feb 14, 2017 at 22:46
I've posted the code I was using above. Slai: ByRef was a good idea, but no dice. @Blackhawk is that an issue? Like most other people I picked up VBA as a necessity and may have learned some bad habits. — Dave Reilly
– Dave Reilly, Commented Feb 14, 2017 at 22:54
You never assign a return value to your function. Maybe getIndex = resultIndex ? And it should be called as results = getIndex( ... ) — chris neilsen
– chris neilsen, Commented Feb 14, 2017 at 22:55

chris neilsen · Accepted Answer · 2017-02-14 23:06:03Z

You never assign a return value to your function. You also seem to be using resultindex as a ByRef parameter to hold the results. You don't need to do both.

Refactoring, try

Public Function getIndex( _
  ByRef all_names As Variant, _
  ByVal Elements As Integer, _
  check_names() As Variant) As Variant

    Dim i As Long, j As Long, k As Long
    Dim resultindex() As Variant
    ReDim resultindex(1 To Elements)
    For i = LBound(all_names) To UBound(all_names)
    For j = 1 To Elements
        For k = LBound(all_names, 2) To UBound(all_names, 2)
            If all_names(i, k) = check_names(j) Then                                    ' checks colName against dynamic names
                resultindex(j) = k                                                      ' colIndex takes index of selected column
                Debug.Print resultindex(j)
                Exit For
            End If
        Next k
    Next j, i
    getIndex = resultindex
End Function

And call it like this

results = getIndex(subArray(), Elements, selNames())

For i = 1 To Elements
    Debug.Print results(i)
Next i

Collectives™ on Stack Overflow

Excel VBA: Element Not Being Stored in Loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related