(too OT for here)

For sure!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

It's not a simple colour match algorithm!

Sorry I misunderstood, read it as all intended as a solution for
Charlotte's Q

I thought she got what she needed from the code I posted earlier. She
mentioned she was on her way to building her own solution from that
starter help with parsing the file.

She indicated an interest in comparing hers/mine. I'm putting it
through final testing before writing the help text and so will upload
it over the weekend. (Or I hope to! I have Lou Gehrig's and so Energy
and PhysicalEffort are aimed at obligatory activities<)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

There could be more than one list of colours, typically they range
from as few as 40 to 1000+ colours. Maybe provide links to samples.

I was not thinking I'd include importing from other lists, due to the
fact that their structures would most likely vary. The user would have
to parse any 3rd party lists into my data file's structure and append
it directly to the file. the structure I'm using is...

Color Name,rValue,gValue,bValue

...which my WriteTextFile procedure will properly append to existing
data. (Most importantly, the file must not have any blank lines AND,
all lines must end with vbCrLf or vbNewLine)

The utility will sort the data during an Update action. (does not
require changing the data, but does require editing any textbox in
fraDataEditor)

As for record count, each RGB value can have 256 variations without
duplicates. That's millions of colors (256*256*256)!!! Way more than a
worksheet has rows, but not likely ever going to be used. (That's got
me thinking about including a 'quick find' feature for color names<g!
Perhaps v2...)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"GS" wrote in message

the structure I'm using is...

Color Name,rValue,gValue,bValue

..which my WriteTextFile procedure will properly append to existing data.
(Most importantly, the file must not have any blank lines AND, all lines
must end with vbCrLf or vbNewLine)

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.

As for record count, each RGB value can have 256 variations without
duplicates. That's millions of colors (256*256*256)!!! Way more than a
worksheet has rows, but not likely ever going to be used. (That's got me
thinking about including a 'quick find' feature for color names<g!
Perhaps v2...)

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.

Regards,
Peter T

"GS" wrote in message

will upload it over the weekend. (Or I hope to! I have Lou Gehrig's and
so Energy and PhysicalEffort are aimed at obligatory activities<)

Sorry to hear that, but I assume it helps to keep the little grey cells fit
with this VBA stuff :)

Regards,
Peter T

"GS" wrote in message

will upload it over the weekend. (Or I hope to! I have Lou
Gehrig's and so Energy and PhysicalEffort are aimed at obligatory
activities<)

Sorry to hear that, but I assume it helps to keep the little grey
cells fit with this VBA stuff :)

Absolutely!!!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"GS" wrote in message

the structure I'm using is...

Color Name,rValue,gValue,bValue

..which my WriteTextFile procedure will properly append to existing
data. (Most importantly, the file must not have any blank lines
AND, all lines must end with vbCrLf or vbNewLine)

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!<g)

As for record count, each RGB value can have 256 variations without
duplicates. That's millions of colors (256*256*256)!!! Way more
than a worksheet has rows, but not likely ever going to be used.
(That's got me thinking about including a 'quick find' feature for
color names<g! Perhaps v2...)

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

"GS" wrote in message

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.

No worries there, a sheet is vastly larger than any list of colours would
need with all conceivable attributes. Also all the colour lists and
associated info any user is likely to find could be easily stored in a
worbook

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.

UDFs could recalc attributes but advise user not to totaly mess it up!

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!<g)

[snip]

You've obviously got a clear idea how you want to structure things which is
always good. Might also be worth at some point stepping back and considering
what users' potential objectives might be with both colour lists and colour
match in Excel.

Regards,
Peter T

No worries there, a sheet is vastly larger than any list of colours
would need with all conceivable attributes. Also all the colour lists
and associated info any user is likely to find could be easily stored
in a worbook

AFAIC, the "ColorNamesManager.xls" project's concept is OT for this
thread. I'm just including it here because it's not OT for Charlotte's
original request. I'm just using the project to 'rebuild' my 'lost'
listbox-based dataform.

I'll be posting a new thread for the complete database example using
pure VBA in a userform/worksheet UI. This example focuses on using a
workbook as the database file, worksheet as the data table, and
userform UI that demos using a textbox-based dataform AND listbox-based
dataform. It's more generic than the color names project in that the
DataEditor is designed to handle any number of datafields, and also
includes a listview-based dataform (with resolve for using missing OCX
on newer WinVers).


UDFs could recalc attributes but advise user not to totaly mess it
up!

Hmm! Users can freely edit the data in a worksheet, file, or via the
dataform. That's why I'm including a help ("?") button on the dataform.
This is a userform-based help system using text stored in constants.
It's simple as simple gets, using a combox for selecting topics to
display in its textbox. The intent of it is so users will be 'informed'
on how to work with this project.

The above mentioned database example project has its own CHM because
I'm building it as more of a training tool than anything else! I use a
CHM because it includes screenshots of the different dataforms, and a
separate TOC item for each dataform. It's currently -still under
construction- because it hasn't been a priority on my list of 'things
to get done' (diff list than 'things to do'<g)!


You've obviously got a clear idea how you want to structure things
which is always good.

Absolutely!
Fact is.., for me this project is more a demo on data management using
standard VB file I/O functions and arrays in a listbox-based userform
UI. The color name/rgb data really has nothing to do with project
structure, and so serves as user-specified 'dummy data' for this
'inspired' project. That means you can -technically- replace the data
filename with another data filename and work with its contents in the
same project (if the data fields match this project's -custom-
DataEditor). This, of course, also requires the data file be properly
structured to be used as a database. (..definitely NOT CSV format<g)


Might also be worth at some point stepping back
and considering what users' potential objectives might be with both
colour lists and colour match in Excel.

Good advice if I was looking to do with this project what you've
already done with yours. Given my above comments, suffice to say I'm
not really interested to go there, Peter, because that's already been
*well done* by yourself!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"GS" wrote in message
You've obviously got a clear idea how you want to structure things which
is always good.

Absolutely!
Fact is.., for me this project is more a demo on data management using
standard VB file I/O functions and arrays in a listbox-based userform UI.
The color name/rgb data really has nothing to do with project structure,
and so serves as user-specified 'dummy data' for this 'inspired' project.
That means you can -technically- replace the data filename with another
data filename and work with its contents in the same project (if the data
fields match this project's -custom- DataEditor). This, of course, also
requires the data file be properly structured to be used as a database.
(..definitely NOT CSV format<g)

That's great, I'll look out for it

Regards,
Peter T

"GS" wrote in message
You've obviously got a clear idea how you want to structure things
which is always good.

Absolutely!
Fact is.., for me this project is more a demo on data management
using standard VB file I/O functions and arrays in a listbox-based
userform UI. The color name/rgb data really has nothing to do with
project structure, and so serves as user-specified 'dummy data' for
this 'inspired' project. That means you can -technically- replace
the data filename with another data filename and work with its
contents in the same project (if the data fields match this
project's -custom- DataEditor). This, of course, also requires the
data file be properly structured to be used as a database.
(..definitely NOT CSV format<g)

That's great, I'll look out for it

Regards,
Peter T

Be aware that the listbox version isn't forthcoming due to finding a
resolve for the listview. Listview is a much better suited control and
so I now recall why I discarded the listbox example.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion