Geek asks and answers

Filter 2D arrays in Excel VBA

Using Excel and VBA, I wanted to get advice on how to best filter data in an array (you can use a pivot table in the same way), strictly using VBA. I am creating a UserForm that is going to make some decisions on data based on existing data. I can imagine how to do this well enough, but I am not so good at VBA programming.

Here is an example

A       B       C
bob     12      Small
sam     16      Large
sally   1346    Large
sam     13      Small
sally   65      Medium
bob     1       Medium

To capture data in an array, I could use

Dim my_array As Variant

my_array = Range("A1").CurrentRegion

Now I am familiar with the loop using 2D arrays, but I wondered: what is the most efficient way to filter the data of a 2D array (without repeating the loop through the array)?

For example, how can I get, you can say, get such data:

data_for_sally As Variant 'rows with sally as name in ColA
data_for_sally_less_than_ten As Variant ' all rows with sally name in ColA and colB < 10
data_for_all_mediums as Variant ' all rows where ColC is Medium

? , , . .

+5
2

, VBA.

, , :

  • = > ?
  • = > , , , , , , .

OO, , ( ) , ( , ). ( ArrayFilter), .

Dim filter As New ArrayFilter
With filter
    .name = "sam"
    .category = "Medium"
    .maxValue = 10
End With


filter.add(1, "sam") 'column 1
filter.add(3, "Medium") 'column 3
filter.addMax(2, 10) 'column 2


filteredArray = getFilteredArray(originalArray, filter)

GetFilteredArray : , , :

If filter.isValidLine(originalArray, lineNumber) Then 'append to new array

  • , , . .
  • - ,
  • :

  • , . , , . .
  • : getFilteredArray , ,
  • , , /.

ps: , getFilteredArray. : / , , , .

filters.add filter, filteredArray 'filters is a dictionary

, getFilteredArray, - :

For each f in filters
    'Check if all conditions in f and newFilter are the same
    'If they are:
    getFilteredArray = filters(f)
    Exit Function
Next

'Not found in cache: compute the result
+5

' credited to ndu
Function Filter2DArray(ByVal sArray, ByVal ColIndex As Long, ByVal FindStr As String, ByVal HasTitle As Boolean)
  Dim tmpArr, i As Long, j As Long, Arr, Dic, TmpStr, Tmp, Chk As Boolean, TmpVal As Double
  On Error Resume Next
  Set Dic = CreateObject("Scripting.Dictionary")
  tmpArr = sArray
  ColIndex = ColIndex + LBound(tmpArr, 2) - 1
  Chk = (InStr("><=", Left(FindStr, 1)) > 0)
  For i = LBound(tmpArr, 1) - HasTitle To UBound(tmpArr, 1)
    If Chk Then
      TmpVal = CDbl(tmpArr(i, ColIndex))
      If Evaluate(TmpVal & FindStr) Then Dic.Add i, ""
    Else
      If UCase(tmpArr(i, ColIndex)) Like UCase(FindStr) Then Dic.Add i, ""
    End If
  Next
  If Dic.Count > 0 Then
    Tmp = Dic.Keys
    ReDim Arr(LBound(tmpArr, 1) To UBound(Tmp) + LBound(tmpArr, 1) - HasTitle, LBound(tmpArr, 2) To UBound(tmpArr, 2))
    For i = LBound(tmpArr, 1) - HasTitle To UBound(Tmp) + LBound(tmpArr, 1) - HasTitle
      For j = LBound(tmpArr, 2) To UBound(tmpArr, 2)
        Arr(i, j) = tmpArr(Tmp(i - LBound(tmpArr, 1) + HasTitle), j)
      Next
    Next
    If HasTitle Then
      For j = LBound(tmpArr, 2) To UBound(tmpArr, 2)
        Arr(LBound(tmpArr, 1), j) = tmpArr(LBound(tmpArr, 1), j)
      Next
    End If
  End If
  Filter2DArray = Arr
End Function
0

More articles:


All Articles