Results 1 to 2 of 2

Thread: HelpVBA code to return field name rather than value.

  1. #1
    Join Date
    Apr 2014
    Posts
    1

    HelpVBA code to return field name rather than value.

    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!

  2. #2
    Join Date
    Nov 2020
    Posts
    35
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •