Most efficient way to search and manipulate Data: Array, classobject, collection?-CodePudding

My goal is to read and manipulate a list of data from different customers in an efficient way.

I have data looking like this. With 40 columns and 500 rows.

I want to make this data useable (searchable and manipulateable) in my userforms and functions. For example, i want to filter this data to all customers from one city. Or Show only male customers in a Listbox. Or add a new address to a specific customer.

The different approaches i have used are as follows:

Storing every row in an array on startup
Creating a Class clsCustomer and creating an object for every row on startup
Reading from the table by accessing specific ranges

I have not yet used collections at all.

Things i noticed:

reading and writing to ranges is very slow
using arrays feels odd to write, since the references are always numbers
creating a class feels like i am only recreating an array, unless i use methods/functions with it

What would be the most resource efficient way to write this in vba?

in terms of speed
in terms of readability of the code

CodePudding user response：

Please, try to understand the next fast way of data processing. Since your question do not clarify too much about the other columns content, it assumes that in the fourth column the cities exist:

Sub ArrayDictionaryApproach()
  Dim sh As Worksheet, lastR As Long, arr, arrItem, i As Long, dict As Object
  
  Set sh = ActiveSheet 'use here any sheet you need
   lastR = sh.Range("A" & sh.rows.count).End(xlUp).row
   arr = sh.Range("A2:E" & lastR).value 'place the range in an array for faster iteration
   
   Set dict = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(arr)
        If Not dict.Exists(arr(i, 4)) Then
            dict.Add arr(i, 4), arr(i, 2)
        Else
            dict(arr(i, 4)) = dict(arr(i, 4)) & "|" & arr(i, 2)
        End If
    Next i
    Debug.Print dict.Keys()(0), dict.Items()(0)
    arrItem = Split(dict.Items()(0), "|")
    Debug.Print UBound(arrItem), arrItem(0)
End Sub

It will return all customer names from a specific city, split by "|". Using Split function you can place them in an array and deal with any needed way of processing...