none
Worksheetfunction not working in VBA RRS feed

  • Question

  • Hi,

    Trying to get following code working but it bombs out at the bold statement and I don't see why. Any ideas?

    [Code]

    Public Function MostCommonVal(Data As Range) As Variant
       Dim varResult           As Variant
       Dim strAreas            As String
       Dim strParent           As Parent
       Dim ParentParent        As String
       Dim strCrit             As String
       Dim intCount            As Integer
       Dim varResults          As Variant
       
       ''' limit data range to usedrange, need to adapt this
       Set Data = Application.Intersect(ActiveSheet.UsedRange, Data)
       Stop
       ReDim varResults(1 To Data.Columns.Count, 1 To 2)
       With WorksheetFunction
          For intCount = 1 To Data.Columns.Count
             Set rngAreas = Data.Columns(intCount)
             'strCrit = "MODE(MATCH(" & rngAreas & "," & rngAreas & ", 0))"
             varResults(intCount, 2) = .Mode(.Match(rngAreas, rngAreas, 0))
          Next intCount
       End With
       Stop
    End Function

    [End Code]

    Saturday, April 21, 2018 12:56 PM

Answers

  • When used in Excel itself, =MATCH(range,range,0) returns an array of values, not a single value.

    Application.WorksheetFunction.Match only works if Match returns a single value, it doesn't handle arrays.

    You can use Evaluate instead:

             varResults(intCount, 2) = Evaluate("MODE(MATCH(" & rngAreas.Address & "," & rngAreas.Address & ",0))")

    (I assume that the code that you posted is incomplete, as it is the function doesn't return anything)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, April 21, 2018 2:08 PM

All replies

  • When used in Excel itself, =MATCH(range,range,0) returns an array of values, not a single value.

    Application.WorksheetFunction.Match only works if Match returns a single value, it doesn't handle arrays.

    You can use Evaluate instead:

             varResults(intCount, 2) = Evaluate("MODE(MATCH(" & rngAreas.Address & "," & rngAreas.Address & ",0))")

    (I assume that the code that you posted is incomplete, as it is the function doesn't return anything)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, April 21, 2018 2:08 PM
  • Hi Hans,

    Thanks for the explanation and the proposal to use Evaluate. I was already thinking to use this method but realized this morning that the result of the function, doing it this way, could be wrong.

    MATCH can only evaluate one row/column and that is correct for one column but could be wrong over the whole range. And I am still struggling with some thoughts I have to think about, I'll post the final code as soon as I figured it out and will mark your replay as answer.

    Regards,

    JP

    [Code]

    Public Function MostCommonVal(Data As Range) As Variant
       Dim lngMax              As Long
       Dim lngTmp              As Long
       Dim lngCalc             As Long
       Dim intCol              As Integer
       Dim rngAreas            As Range
       Dim varResults          As Variant
       Dim varTmp              As Variant
       
       With Application
          .EnableEvents = False
          lngCalc = .Calculation
       End With
       
       On Error GoTo MostCommonVal_Error

       ''' limit data range to usedrange
       Set Data = Application.Intersect(Data.Parent.UsedRange, Data)
       ReDim varResults(1 To Data.Columns.Count, 1 To 2)
       
       For intCol = 1 To Data.Columns.Count
          Set rngAreas = Data.Columns(intCol)
          ''' get the position of the most common value, error 2042 if all values are unique
          varTmp = Evaluate("MODE(MATCH(" & rngAreas.Address & "," & rngAreas.Address & ",0))")
          If Not IsError(varTmp) Then
             ''' remember this value
             varResults(intCol, 1) = Evaluate("INDEX(" & rngAreas.Address & "," & varTmp & ")")
          End If
       Next intCol
       
       ''' count the occurrences in the range
       For intCol = 1 To UBound(varResults, 1)
          If Not IsEmpty(varResults(intCol, 1)) Then
             If IsNumeric(varResults(intCol, 1)) Then
                varResults(intCol, 2) = Evaluate("COUNTIF(" & Data.Address & "," & varResults(intCol, 1) & ")")
             Else
                varResults(intCol, 2) = Evaluate("COUNTIF(" & Data.Address & "," & Chr(34) & varResults(intCol, 1) & Chr(34) & ")")
             End If
          End If
       Next intCol
       
       ''' get the max
       lngMax = 0
       For intCol = 1 To UBound(varResults, 1)
          If IsEmpty(varResults(intCol, 2)) Then
             lngTmp = -1
          Else
             lngTmp = varResults(intCol, 2)
          End If
          If lngTmp > lngMax Then
             lngMax = lngTmp
             varTmp = varResults(intCol, 1)
          ElseIf lngTmp = lngMax Then
             If varTmp <> varResults(intCol, 1) Then
                varTmp = CVErr(xlErrNA)
                Exit For
             End If
          End If
       Next intCol
       
       MostCommonVal = varTmp

    MostCommonVal_Exit:
       On Error GoTo 0
       Exit Function

    MostCommonVal_Error:
       With Application
          .EnableEvents = True
          .Calculation = lngCalc
       End With
       MostCommonVal = CVErr(xlErrValue)
       Resume MostCommonVal_Exit
    End Function

    • Edited by JP Ronse Monday, April 23, 2018 9:17 AM
    Sunday, April 22, 2018 2:13 PM