0

I have a situation in VBA where I have an array of elements of my own user defined type.

The type is as follows:

Type CommentInfoStruct
REF As String ' a reference for an item, usually a single letter
NB As Long ' number of items
COMMENT As String ' comment on item, eg "This item needs special paint colour"
End Type

Essentially what I need to do is loop through the array, and check if there are items where the COMMENT matches between two items. If they do match, I need to combine the two items into one like so:

Item A: REF="A" NB=2 COMMENT="Special colour"

Item B: REF="B" NB=3 COMMENT="Special colour"

New item (stored at previous index of Item A, with Item B now deleted from the array): REF="A/2,B/3" NB=5 COMMENT="Special colour"

What's the cleanest way to do this? I've searched and can't seem to find a simple way of deleting array elements and feel I must be missing something. Also keeping track of the limits of the loops I've tried to write seems tricky (since the array redims within the loops).

WhatEvil
  • 481
  • 5
  • 18
  • try to use [dictionary or collection](http://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure) with `key=COMMENT` – Dmitry Pavliv May 01 '14 at 08:38

1 Answers1

0

Keep a counter that starts with the ubound of the array
Loop through each entry. When you find the two that you want to combine, do it, then move the last array entry to the spot you just combined with another one.
Now subtract one from your total counter. The array still has more entries, but your counter is what counts here.
Last step is to re-dimension the array to one less that before.

ar(1)=A
ar(2)=B
ar(3)=C
ar(4)=B
ar(5)=D

Combine 4 and 2, Move 5 into the spot left by 4.
Spot 5 can now be removed by redim preserve ar(4)

Don J
  • 18
  • 5
  • Seems reasonable. I wonder though, would it be better (in terms of efficiency) to go through the loop a first time, but when an array element needs to be deleted, actually change the values to some key value (eg. `REF="xxx"`), and then in a second iteration, copy each element of the array to a new array, but skip the ones where REF="xxx". Just thinking that there might be less seeking to array elements, copying etc. that way around, making it faster in execution? I'm not familiar enough with the internal mechanics to know. – WhatEvil May 01 '14 at 16:30