GS said:
If storing purely as semi hidden data only need name and long colour
values, RGB etc can be worked out on the fly. However as this is for
Excel a sheet seems like an obvious place to store all the details.
If on a sheet user might want to include intermediate lines say with
info to categorise groups of colours. If the cell doesn't include a
valid colour skip it, even the simple demo I posted here allows for
that albeit specifically for use with the given sample list of
colours.
I agree! The project can be modified accordingly since using a data
file involves an intermediate initial step in getting the data from the
file into the worksheet and listbox. Once there, all data manipulation
occurs between the worksheet and listbox. The file has to be updated
when changes are made and so extra coding is required for that process.
As written, the stored data can have any number of fields meaning the
'table size' is pretty much limited to how many cols/rows Excel allows.
One caveat I see with your idea is what happens when the list changes
from items being added/removed. The worksheet list gets sorted and so
any ancilliary cols would be affected, no? I could sort the array
before updating the listbox, but then VBA's sort order is different
than what results in a worksheet sort, and it takes much longer to do.
So if more fields were added to the data file *before* running the
utility they'd be included when the userform displays.
Note:
I like to 'properly' structure my delimited data files for conventional
db handling. That means the 1st line in the file ALWAYS contains
fieldnames, AND my WriteTextFile does NOT add a blank line to the end
of the file. This means I get no empty records at the end of a
recordset, and so using arrays is an easy approach to working with the
data. The 1st record starts at array(1) and the record count is always
the array's UBound. This makes putting the entire file contents into a
blank worksheet (complete with a header row of fieldnames) a breeze. I
use dynamic range names that use...
=OFFSET($A$1,1,0,COUNTA($A:$A)-1,COUNTA(1:1))
...for the defined name's 'RefersTo'. This means the indexing of
zero-based array created when 'Split'ing the data file into a variant
aligns with the indexing of the 2D array of data in the worksheet
range. IOW, rngData.Rows(1) or rngData.Cells(1) indexes with mvData(1)
because mvData(0) contains fieldnames. This also makes loading headings
in a listview or 1-row listbox really simple.
I'm going with 'keep it simple' for this project so its 'core'
structure is stable enough to add features/functions as desired. This
is the approach I take with my other projects; they release as 'core'
apps that support using 'plugins' for user-defined extended
features/functions. (That means my addin apps support their own
'addins' for user-specific enhancement! said:
I don't recall any list of more than 3000 named colours, if that
many. The larger lists tend to be for paint/ink suppliers sometimes
with 'coded' names. There have been various attempts to 'describe'
otherwise unnamed colours in visually intuitive ways, but it's not as
easy as it sounds.
That's good to know! I briefly courted the awful thought of having to
parse the data file in 'blocks', and arrange the worksheet list in
'areas' if the amount of data got huge enough to need that. I was
relieved of the horros associated with that thought by thinking to use
ADODB in that case. Thanks to Rob Bovey's database programming
examples, this makes managing large amounts of data a simple task!<g>
Sorry about any 'long-in-tooth'!
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
