CODEWITHSUNDEEP
arraysexcelvbadictionary
Ernesto
Ernesto

Reputation: 609

Single data dictionary in VBA

I have what seems to me like a simple problem. I have an array of values that I need to loop and delete. The problem is that an array can only be redimmed (cant delete the element in the middle).

I was contemplating using a dictionary, but I would not really use the value, only the key, which seems like a waste... Is there such thing as a "key" list?

For context, what I would like to do is something like:

  1. Create array: {1,2,3,4,5,6,7}

  2. Delete an element from the middle: e.g. delete(3) = {1,2,4,5,6,7}

As I said, I can use a dictionary, but seems like a waste!

Upvotes: 2

Views: 353

Answers (2)

AnalystCave.com
AnalystCave.com

Reputation: 4974

Use an ArrayList: 

Set myAl = CreateObject("System.Collections.ArrayList")        
myAl.Add ("The") 'Add Item
myAl.Add ("dog") 'Add Item
myAl.Remove ("dog") 'Remove an item

'An example of how to traverse the items of an ArrayList:
For Each Item In myAl
    Debug.Print Item
Next Item

You might just as well use the Dictionary and I don't expect that there will be a lot of overhead, nevertheless, the ArrayList data structure is probably the one you are looking for.

There are other data structures like Hashtables, Stacks, Queues but they seem less appropriate for your need.

Upvotes: 3

user4971703
user4971703

Reputation:

I hardly ever put values in a dictionary. I just use keys. Don't worry about it. No data uses no memory.

These are your options.

From .NET (try to avoid these as there is already the COM overhead - the .NET overhead as well just duplicates COM functionality)

•ArrayList—An array class that doesn't have a fixed size. You can just keep adding items to it.

•Hashtable—This class is similar to the Scripting.Dictionary class. You can add items and look them up by key.

•Queue—This is a first in, first out (FIFO) collection. You push items in, and then read them out at a later time in the same order.

•Stack—A first-in, last out (FILO) collection. You push items onto the stack, and then pop them off in reverse order.

•SortedList—Similar to the Hashtable, except when you iterate through the items,n they're always sorted by the key.

See https://msdn.microsoft.com/en-us/library/aa719110(v=vs.71).aspx

COM Objects

You have VBScript's Scripting.Runtime's Dictionary (vbscript is pastable into VBA)

'This dedups
Set Dict = CreateObject("Scripting.Dictionary")
On Error Resume Next
Dict.Add "Key1", "Data"
Dict.Add "Key2", "Data"
Dict.Add "Key3", "Data"
If Err.Number <> 0 then
    If LCase(Arg(1)) = "l" then
        Dict.Remove Line
        Dict.Add Line, ""
    End If
End If
For Each thing in Dict.Keys()
    Msgbox thing
Next

Then there is the ADO Recordset, which can be saved and loaded from file.

'This sorts and filters
Set rs = CreateObject("ADODB.Recordset")
With rs
.Fields.Append "SortKey", 4 
.Fields.Append "Txt", 201, 5000 
.Open
.AddNew
.Fields("SortKey").value = 1
.Fields("Txt").value = "Line1"
.UpDate
.AddNew
.Fields("SortKey").value = 2
.Fields("Txt").value = "Line2"
.UpDate
.AddNew
.Fields("SortKey").value = 3
.Fields("Txt").value = "Line3"
.UpDate

'Sorting
.Sort = "SortKey ASC"

'Filtering
.filter = "Sortkey < 3"

'Writing it out
Do While not .EOF
    MsgBox .Fields("Txt").Value
    .MoveNext
Loop

Upvotes: 1

Related Questions