Should stdArray support multi-dimensional arrays?

[sancarn]

@lopperman and I were discussing in reddit DM's:

@lopperman
I was also wondering what you thought about making stdArray support multiple array dimensions -- at least 2, so that it could be loaded from an Excel Range or used to set the value of an Excel Range.

@sancarn
Yeah I've often wondered how if at all we'd be able to do that with current architecture. I think it's better though if we produce other classes for specific data structures e.g. stdTable or stdMatrix etc. because generally speaking the types of operations you want to do on a table generally are different to an array and you'd prefer a nicer interface too. There is stdEnumerator.CreateFromListObject() already, but i'd prefer a better variant of this.

@lopperman
I think there's a minimal level of effort that would be required to make an stdRangeArray (or call it whatever). 90% of what I use arrays for either load it from a range or set a range value to it. Whether it's a single cell - dimensions (1 to 1, 1 to 1), or single column (1 to X, 1 to 1), or a single row (1 to 1, 1 to X), having an easy to use 1-based 2-dimensional array I think would be worth it
It could explicitly not support objects, and would always be 2 dimensional. I like the idea of making that in your std library, but I wouldn't use it unless it basically supported Tim Hall's dictionary. Any chance you would be ok with that dependecy for Mac Users?

Thinking a bit more about it -- I'd be willing to take a shot at removing the scripting dictionary depending you have in stdArray (replacing it with use of keyed colllection). If that doesn't totally kill performance, then it would be worth trying that on the 'range' array as well. What do you think?

[sancarn]

So my concern is less about the effort required to make such a class, but rather would it bring any benefit? So I'd be totally happy if there is a use case. So far here I've seen 2:

Updating a single cell

In this case you don't need an array at all, and using Range("...").value = whatever is I think trivial enough?

Update a row or column

So this to me sounds like something I envision stdTable resolving. I'm still not 100% on the structure of stdTable but I'd be looking at something like this:

stdTable_instance.update(ByVal colName as string, ByVal callback as stdICallable) E.G.

With stdTable.CreateFromName("Test")
  'Update column Energy from values in other columns
  .update("Energy", stdLambda.Create("0.5 * $1.mass * $1.velocity ^ 2"))
  
  'Update relativistic velocities with relativistic Kinetic energy
  .where(stdLambda.Create("$2.velocity > 0.5 * $1").bind(c)) _ 
  .update("Energy", stdLambda.Create("((1 - $2.velocity ^ 2 / $1 ^ 2)  ^ (-0.5) -1) * $2.mass * $1 ^ 2").bind(c))

  'Read-write Access to values if required
  Msgbox .values(1,1) 'First row, first column
  .values(1,1) = "hello world"

  'Essentially looping through queries of this table
  Dim row as stdTable
  For each row in .rows
    Debug.Print row.values(1,1)
  next

  'Get a detatched copy of a query (preventing `update` from updating the original table.
  set detatched = .query(stdLambda.Create("$1.mass < 100")).detatch()
  detatched.update("Comment", "Tiny")
  
  'Save to another table
  Call detatched.saveToRange(Sheet1.Range("A1"))

  'in an ideal world joins would be great too
End With

In this instance you can very quickly update a whole column. If you have a reference to a row, you can update that row too.

If you could give examples where you can do things with a 2d array which you can't do with something like this, that'd be good to know about :)

[dangrazh]

I have built a class which probably does pretty much what @lopperman is referring to: a 2 dimensional data table that allows you to read a csv file, load data from an Excel range or add data manually. It provides navigation and data access functions and basic indexing (for now unique indices only - based on a HashTable class from Francesco Balena). You can find it in the following repository.

The main use case I developed it for, is to be able to work with tabular data without the need of relying on Excel Ranges during computation. It allows to store nested structures as well, as you can have a given item to be an object itself, so you can nest data tables and create more complex structures. Probably you could get to the same results with stdArray, but (at least for me) this class has proven very useful, in particular to manage table like data which makes sense to keep togehter.

To give you some use cases, you can load data from various sources, do lookups merge/enhance data, calculate new fields, do a distinct selection over a subset of fields and continue whatever you need to do on this subset of data. In more practical terms, I have built some more advanced NLP applications involving text cleansing, stemming, vectorization, similarity (distance) calculation and clustering based on a distance matrix, all using this class to store the various data sets needed in the process. Just to give you an idea how such a data structure can be used. A maybe more simple use case is a performance measurement class I have built, which uses the data table to store the raw data. This class can be found here.

The approach of both classes is clearly not the same as your stdVBA collection of classes, this is just to illutrate the potential use cases for a 2d array.

[sancarn]

Hi @dangrazh and thanks for the input. First let me say this looks like a great class and really useful tool for VBA devs! Well done and thanks for making it Open Source :)

The main use case I developed it for, is to be able to work with tabular data without the need of relying on Excel Ranges

Indeed, as discussed in my response to the initial post, I can totally see the use case of a class for handling tables/tabular data. But in these cases I'd much prefer a tool explicitely for this. I.E. stdTable or something like your class.

I was really looking for other use cases other than handling tabular data (or data with a very specific structure).

[lopperman]

@dangrazh, I took a look at your Data-Table. Agree with @sancarn that you did a nice job on that. Unfortunately it would be a massive undertaking to modify that to work with Mac (All my VBA code is PC and Mac compatible). If did fork the repo and make cHashTable compatible with Mac OS -- if you wnat to open it up for Pull request, I could submit that -- or just copy the 'CopyMemory' section with the change.

Kind of tangential to this thread, but I did some testing with cHashTable. I also created a custom hash class a while back, but I never implemented the ability to remove items (no need so far, but I'm sure others would appreciate it). cHashTable does an excellent job adding items, but is very slow with retrieval. I wanted to make sure I was retrieving items in the most optimal way -- could you let me know if I'm not?

Below is the code I ran against your cHashTable, and against my pbHash.

If you want to test the pbHASH.cls, there are not dependencies, except you will need to add this Type to a common module:

'   ~~~ ADD KVP TYPE TO PUBLIC MODULE ~~~
    Public Type KVP
      KEY As String
      value As Variant
    End Type

FETCH PERFORMANCE DIFFERENCE

'(pbHash) Fetched 1000 items in 0.01074219 seconds
'(cHashTable) Fetched 1000 items in 43.75977 seconds

Public Function HashTest()
    'pbHash performance test
    'add 100,000 items
    'Retrieve random 1000 items

'   TEST RESULTS
    '(pbHash) Added   100000      items in   1.266602      seconds
    '(pbHash) Fetched 1000      items in    0.01074219      seconds

    Dim hash As New pbHASH
    hash.CompareMode = vbTextCompare
    Dim i As Long, startTimer As Single
    startTimer = Timer
    hash.SetExpectedCount 200000
    
    Debug.Print "Start:", Now
    For i = 1 To 100000
        hash.AddKV "Key" & i, Timer
    Next i
    Debug.Print "End:", Now
    Debug.Print "(pbHash) Added", hash.Count, "items in", Timer - startTimer, "seconds"
    'HashTest
    '(pbHash) Added   100000      items in   1.266602      seconds
    
    Dim rslt, idx As Long, key As String
    startTimer = Timer
    For i = 1 To 1000
        idx = CLng(Math.Rnd * (hash.Count - 1))
        key = hash.KeyByIndex(idx)
        rslt = hash.Item(key)
        'Debug.Print key, rslt
    Next i
    Debug.Print "(pbHash) Fetched 1000", "items in", Timer - startTimer, "seconds"
    '(pbHash) Fetched 1000      items in    0.01074219      seconds

End Function

Public Function TestHash()
    'cHashTable performance test
    'add 100,000 items
    'Retrieve random 1000 items

'   TEST RESULTS
    '(cHashTable) Added      100000  items in      1.536133 seconds
    '(cHashTable) Fetched 1000     items in   43.75977      seconds

    Dim hash As New cHashTable
    Dim i As Long, startTimer As Single
    startTimer = Timer
    hash.IgnoreCase = True
    hash.SetSize 200000, ListSize:=100000
    
    Debug.Print "Start:", Now
    For i = 1 To 100000
        hash.Add "Key" & i, Timer
    Next i
    Debug.Print "End:", Now
    Debug.Print "(cHashTable) Added", hash.Count, "items in", Timer - startTimer, "seconds"
    'TestHash
    '(cHashTable) Added      100000  items in      1.536133 seconds
    
    Dim rslt, idx As Long, key As String
    startTimer = Timer
    For i = 1 To 1000
        idx = CLng(Math.Rnd * (hash.Count - 1))
        key = CStr(hash.Keys()(idx))
        rslt = hash.Item(key)
        Debug.Print key, rslt
    Next i
    Debug.Print "(cHashTable) Fetched 1000", "items in", Timer - startTimer, "seconds"
    '(cHashTable) Fetched 1000     items in   43.75977      seconds
End Function