HelpVBA code to return field name rather than value.

[Geeks]

Hello,

I have a code that finds the first, second, and third minimum value in a row across the fields. Now, I am trying to find a code to look at these values, find the field it is located, and return the field name. I tried several variations of my code to return the field name rather than the value, but have been unsuccessful to this point.

Function NthMinimum(intPosition As Integer, ParamArray FieldArray() As Variant) As Variant


Dim varTempArray() As Variant, varTempValue As Variant, intArrayValues As Integer
Dim I As Integer, J As Integer

ReDim varTempArray(UBound(FieldArray))
intArrayValues = 0

' Transfer the non-Null values to a temporary array
For I = 0 To UBound(FieldArray)
If IsNull(FieldArray(I)) = False Then
varTempArray(intArrayValues) = FieldArray(I)
intArrayValues = intArrayValues + 1
End If
Next I

If intArrayValues > 1 Then
' Sort the temporary array, lowest to highest (Bubble sort)
For I = 0 To intArrayValues - 2
For J = I + 1 To intArrayValues - 1
If varTempArray(J) < varTempArray(I) Then
varTempValue = varTempArray(J)
varTempArray(J) = varTempArray(I)
varTempArray(I) = varTempValue
End If
Next J
Next I

' Remove duplicate values
I = 0
While I < intArrayValues - 2
If varTempArray(I) = varTempArray(I + 1) Then
For J = I To intArrayValues - 1
varTempArray(J) = varTempArray(J + 1)
Next J
intArrayValues = intArrayValues - 1
End If
I = I + 1
Wend
End If


If intPosition <= intArrayValues Then
NthMinimum = varTempArray(intPosition - 1)
Else
' The requested position is higher than the number of values in the array
NthMinimum = Null
End If


End Function

As you can see, this works to find these values while ignoring NULLS. If anyone has any suggestions on how to return the field name, I would greatly appreciate it!

Thanks in advance!

[June7]

ParamArray allows only 1-dimension array, need 2-dimension in order to pass both field values and field names. Explore methods of building and passing 2-dimension array. An alternative is to open a recordset object filtered for the one record and read field values and names into array for bubble sort.