Directly access an Array in ListBox.CellTag

Greetings all… I’ve looked all through the docs and both forums and can’t figure out if there is a way to do this. I want to directly access an array which has been put in the CellTag of ListBox cells. I know I can do it by setting an array to the Celltag like this:

MyArray = Me.CellTag(row, column)
g.ForeColor = Colors(MyArray (0))

But because this is done in the CallBackgroundPaint event which gets called thousands of times, I was hoping to save the transfer to the array and access the array values directly in CellTag. Is there some way to do this? I know this doesn’t work:

// doesn’t work
g.ForeColor = Colors(Me.CellTag(row, column).MyArray(0))

but was hoping there is some other direct way. Thanks in advance for any help.

why not store the colors in an array NOT attached to the cell tag. and use the cell tag to hold the INDEX to that array


Because I have 4 pieces of information that are stored in the array in the CellTag. I just mentioned element 0 to keep the question simple. I know I can get direct access to base data types like Integer, String, Boolean, etc… I am asking about an array.

There really isn’t any syntax to access the elements of an array stored in a Variant. That said, there is very little overhead involved in the assignment. It’s a reference, not a copy of the contents.

Thanks Tim and Dave, just making sure I wasn’t missing something. I normally don’t worry about optimizing down at this level unless it is going to be called a bunch of times, like in a Cell paint event.

Are you sure doing this is too slow:

Initialize when adding row

Dim CellColors() as Color = Array (&cFF0000,&c00FF00,&c0000FF, &cFF00FF) ' Whatever Listbox.CellTag(row,col) = Cellcolors
Then in the paint event:

Dim CellColors() as Color = Listbox.CellTag(row,col)

The slow step is the initial assignment because it has to crete the variant… I doubt retrieving teh color from the array stored in the celltag will be a significant hit.

No, that only stores one additional piece of information associated with the cell (color). I have 4 discrete pieces of information, 2 of them data from the database that I do not want to have to query in the middle a Cell Paint event (which is accessed thousands of items).

then create a class… or a structure and store THAT instead

Thanks Dave. I had working with a class initially, but I don’t see what the advantage is over a simple, 4 element Integer array. The array holds the data I need in the cell. No sure how changing it to a class would help.

An array is stored in your cell as a variant… a variant takes time to convert to/from the proper datatypes. A class would store a POINTER, and the data would be whatever type you defined in the class, not a variant (personally for me “variants are evil”, right up there with GOTO and GOSUB)

Thanks, I’ll look at doing it as a class again. My initial thinking was it would take less time to create an array than instantiate a class object.

I don’t think one way is going to be faster than the other, but I would lean toward a class, because it is cleaner. It’ll be easier to figure out what someObject.TextColor is than to remember that it is the zeroth element of an array.

Thanks all, I’ll revisit the class implementation I had initially.