Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
I stumbled across this nice one:
http://gauth.fr/2011/09/get-a-color-...b-combination/ Very neat indeed :-) But made in JavaScript, which I know virtual nothing about... I was wondering, if anyone has made something similar in VBA? Can it be done? How? CE --- Denne e-mail er fri for virus og malware fordi avast! Antivirus beskyttelse er aktiveret. http://www.avast.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
I stumbled across this nice one:
http://gauth.fr/2011/09/get-a-color-...b-combination/ Very neat indeed :-) Yes.., quite impressive to say the least! But made in JavaScript, which I know virtual nothing about... I was wondering, if anyone has made something similar in VBA? Don't know, but maybe Karl Peterson has something you can modify to do similar. Can it be done? How? Simplest approach: Parse the file "dataset.js" into columns and use a lookup function to return a name based on user input. (I'd probably have it work both ways, meaning user can optionally enter a name and return the RGB value!) I see that this file is one continuous string and so may need to be edited so you can use Split() for each color name value. As is.., you can parse each piece of color data using "}," as a delimiter to load your initial array. After that each element is a comma delimited set of property:value pairs where you can Split() each part into a temp array, then split again into a 2nd temp array using ":" as the delimiter. (you only need to extract the UBound value of this 2nd array) Once you get there you can decide how to put the table into a worksheet. I'd probably build an output 2D array in memory, then 'dump' that into the worksheet (OR a delimited file where I can search for my RGB values, OR just store the data so it's easily retrievable into a workable array at runtime)! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
I see that this file is one continuous string and so may need to be
edited so you can use Split() for each color name value. I was referring to the fact the entire string is enclosed in "[]" and so these should be removed before you Split() the file contents into an array. You can do so after you copy/paste the file from the website into a text editor, OR use Replace() to remove them programatically. Since the contents paste with the carat after the trailing "]" I'd just backspace it out, Ctrl+Home, delete the leading "[", then save the file. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
Charlotte, try this on the dataset.js file 'as is'...
Option Explicit Sub ConvertColorData() Dim vTextIn, v1, v2, vTextOut, sTmp$ Dim n&, k&, i& sTmp = ReadTextFile("C:\Users\Garry\Documents\VBA_Stuff\d ataset.js") 'Replace "}," with "|" as the line delimiter sTmp = Replace(sTmp, "},", "|") 'Filter out unwanted characters vTextIn = Split(FilterString(sTmp, ",:|"), "|") 'Store the data in a normal csv file WriteTextFile Join(vTextIn, vbLf), _ "C:\Users\Garry\Documents\VBA_Stuff\dataset.tx t" ReDim vTextOut(1 To UBound(vTextIn) + 1, 1 To 4) For n = LBound(vTextIn) To UBound(vTextIn) v1 = Split(vTextIn(n), ",") For k = LBound(v1) To UBound(v1) v2 = Split(v1(k), ":") vTextOut(n + 1, k + 1) = v2(1) Next 'k Next 'n Cells(1, 1).Resize(UBound(vTextOut), UBound(vTextOut, 2)) = vTextOut End Sub Function ReadTextFile$(Filename$) ' Reads large amounts of data from a text file in one single step. Dim iNum As Integer On Error GoTo ErrHandler iNum = FreeFile(): Open Filename For Input As #iNum ReadTextFile = Space$(LOF(iNum)) ReadTextFile = Input(LOF(iNum), iNum) ErrHandler: Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description End Function 'ReadTextFile() Function FilterString$(ByVal TextIn As String, _ Optional IncludeChars As String, _ Optional IncludeLetters As Boolean = True, _ Optional IncludeNumbers As Boolean = True) ' Filters out all unwanted characters in a string. ' Arguments: TextIn The string being filtered. ' IncludeChars [Optional] Keeps any characters. ' IncludeLetters [Optional] Keeps any letters. ' IncludeNumbers [Optional] Keeps any numbers. ' ' Returns: String containing only the wanted characters. Const sSource As String = "FilterString()" 'The basic characters to always keep Const sLetters As String = "abcdefghijklmnopqrstuvwxyz" Const sNumbers As String = "0123456789" Dim i As Long, CharsToKeep As String CharsToKeep = IncludeChars If IncludeLetters Then _ CharsToKeep = CharsToKeep & sLetters & UCase(sLetters) If IncludeNumbers Then CharsToKeep = CharsToKeep & sNumbers For i = 1 To Len(TextIn) If InStr(CharsToKeep, Mid$(TextIn, i, 1)) Then _ FilterString = FilterString & Mid$(TextIn, i, 1) Next End Function 'FilterString() Sub WriteTextFile(TextOut$, Filename$, Optional AppendMode As Boolean = False) ' Reusable procedure that Writes/Overwrites or Appends ' large amounts of data to a Text file in one single step. ' **Does not create a blank line at the end of the file** Dim iNum As Integer On Error GoTo ErrHandler iNum = FreeFile() If AppendMode Then Open Filename For Append As #iNum: Print #iNum, vbCrLf & TextOut; Else Open Filename For Output As #iNum: Print #iNum, TextOut; End If ErrHandler: Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description End Sub 'WriteTextFile() -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
Replace this
vTextIn = Split(FilterString(sTmp, ",:|"), "|") with this vTextIn = Split(FilterString(sTmp, "," & ":" & "|"), "|") if your reader shows the ":|" part as a smiley! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
Testing another character combo...
vTextIn = Split(FilterString(sTmp, "|,:") -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
A better parse to dataset.txt:
Sub ConvertColorData() Dim vTextIn, v1, v2, vTextOut, sTmp$ Dim n&, k&, i& sTmp = ReadTextFile("C:\Users\Garry\Documents\VBA_Stuff\d ataset.js") 'Replace "}," with "|" as the line delimiter sTmp = Replace(sTmp, "},", "|") 'Filter out unwanted characters vTextIn = Split(FilterString(sTmp, "|,:"), "|") ReDim vTextOut(1 To UBound(vTextIn) + 1, 1 To 4) For n = LBound(vTextIn) To UBound(vTextIn) v1 = Split(vTextIn(n), ",") For k = LBound(v1) To UBound(v1) v2 = Split(v1(k), ":") vTextOut(n + 1, k + 1) = v2(1) Next 'k vTextIn(n) = Join(Application.Index(vTextOut, n + 1, 0), ",") Next 'n 'Store the data in a normal csv file WriteTextFile Join(vTextIn, vbLf), "C:\Users\Garry\Documents\VBA_Stuff\dataset.tx t" 'Create lookup table Cells(1, 1).Resize(UBound(vTextOut), UBound(vTextOut, 2)) = vTextOut End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
Here's a userform I threw together that shows how to use "dataset.txt"
https://app.box.com/s/23yqum8auvzx17h04u4f Note that the file doesn't contain every possible RGB combination, and so there's opportunity to add your own names. If interested, I can import the parsed file into a worksheet and edit the values, then export it a a csv. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
Typos...
Here's a userform I threw together that shows how to use "dataset.txt" https://app.box.com/s/23yqum8auvzx17h04u4f Note that the file doesn't contain every possible RGB combination, and so there's opportunity to add your own names. If interested, you can import the parsed file into a worksheet and edit the values, then export it as a csv. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
Here's a userform I threw together that shows how to use
"dataset.txt" https://app.box.com/s/23yqum8auvzx17h04u4f Note that the file doesn't contain every possible RGB combination, and so there's opportunity to add your own names. If interested, I can import the parsed file into a worksheet and edit the values, then export it a a csv. After some thought (and a good night's sleep), I believe the userform can be easily modified to 'Add' new names and RGB combos to the "dataset.txt" file so it never actually needs to be loaded into a worksheet. I'm not sure what (if any) practical use this has other than say designing custom paint colors, but it has captured my interest enough that I started a XLS project named "ColorNameManager" which I will provide a download link to when it's done to my satisfaction. This will include a zip file containing the original "dataset.js" file and the above named workbook in XLS format... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
"Charlotte E." wrote in message I stumbled across this nice one: http://gauth.fr/2011/09/get-a-color-...b-combination/ Very neat indeed :-) But made in JavaScript, which I know virtual nothing about... I was wondering, if anyone has made something similar in VBA? Can it be done? How? I did but it was a long time ago. At the time I couldn't find any examples had despite extensive searching. Originally it was VBA but I put it in a VB6 ComAddin, a colour match tool as a small feature of a much larger range of colour related stuff for Excel. Briefly this is "how" to go about it. First you need a swatch of defined and optionally "named" RGB colours to match against, your example seems to be using this list of named and defined colours - http://gauth.fr/2011/09/get-a-color-...b-combination/ But there are others, not least the well known swatch (1100+ colours) from a certain print ink producer, or say the 140 named html colours. The hard bit is to define a virtual "colour space" that reflects the very different way the human eye perceives colour differences vs the actual RGB differences. Eg the eye perceives green as occupying a much larger relative space than its neighbour in the spectrum cyan. There are various example spaces out there but replicating them is difficult. I gave up and devised my own colour space as a semi regular 3D space, complex but regular enough to be defined with an algorithm. Then map all the colours in the list in the space with XYZ coordinates from a given reference point, and similar with the RGB you want to match. Finally the simple bit, calculate all the 3d distances of your colour to match to each of the mapped swatch colours. The best match is the one with the shortest distance in the space, though in a large swatch a good idea to return and rank a few other close matches and let your own eye judge the best. And that's all there is to it! Regards, Peter T |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
The hard bit is to define a virtual "colour space" that reflects the
very different way the human eye perceives colour differences vs the actual RGB differences. Eg the eye perceives green as occupying a much larger relative space than its neighbour in the spectrum cyan. There are various example spaces out there but replicating them is difficult. I gave up and devised my own colour space as a semi regular 3D space, complex but regular enough to be defined with an algorithm. Then map all the colours in the list in the space with XYZ coordinates from a given reference point, and similar with the RGB you want to match. Finally the simple bit, calculate all the 3d distances of your colour to match to each of the mapped swatch colours. The best match is the one with the shortest distance in the space, though in a large swatch a good idea to return and rank a few other close matches and let your own eye judge the best. And that's all there is to it! Hi Peter, What purpose is a "virtual color space"? What purpose does the "xyz" assignments serve? VB's RGB() function only requires the 3 RGB values, and so I don't get the need for all the extra 'fluff' if using that function to define/name colors derived from RGB values. Am I missing something? -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
"GS" wrote in message Hi Peter, What purpose is a "virtual color space"? What purpose does the "xyz" assignments serve? VB's RGB() function only requires the 3 RGB values, and so I don't get the need for all the extra 'fluff' if using that function to define/name colors derived from RGB values. Am I missing something? Hi Garry, You can't imagine how effort much went into developing what I described and you call fluff <g. But actually you're right, or rather we both are, it depends. With a relatively small number of well separated colours to match against a simple RGB 'distance' comparison is probably fine, and (from a quick look) that's what the JavaScript example is based on. However for closer matching, particularly with a larger number of less different colours, a colour space that reflects the way the human eye differentiates colours is a better albeit more complicated approach. Try this to replicate the JavaScript demo: copy the whole page I referred to last time to Sheet1. Clear the top two rows and you should have three columns of colours with their definitions. Run the following to split names & web-hex colours into cols A & B (I ended up with #100c08 in B4) Sub abc() Dim pos As Long Dim rng As Range, c As Range Set rng = Range("a2:a690") For Each c In rng If Len(c) Then pos = InStrRev(c, "#") If pos Then c.Offset(0, 1) = Mid(c, pos, 7) c = Left(c, pos - 1) End If End If Next End Sub The following makes 100 random colours and returns the best "linear" match for each Sub test_match() Dim rx&, gx&, bx&, clrX& Dim ra&, ga&, ba&, clrA& Dim dist As Double, minDist As Double Dim lBestMatch As Long, rBestCell As Range Dim sHex As String Dim c1 As Range, c2 As Range Dim cClr2Match As Range For Each c1 In Range("f3:f102") clrA = Int(Rnd() * vbWhite) c1.Value = clrA c1.Interior.Color = clrA getRGB clrA, ra, ga, ba minDist = vbWhite For Each c2 In Worksheets("Sheet1").Range("b2:B690") sHex = c2 If Len(sHex) Then If Left(sHex, 1) = "#" Then getRGBfromHEX sHex, clrX, rx, gx, bx dist = ((ra - rx) ^ 2 + (ga - gx) ^ 2 + (ba - bx) ^ 2) ^ 0.5 If dist < minDist Then minDist = dist lBestMatch = clrX Set rBestCell = c2 End If End If End If Next With c1.Offset(, 1) .Value = rBestCell & " " & lBestMatch .Interior.Color = lBestMatch End With Next End Sub When done should see a list of 100 random colours in col-F and the best matched in col-G. FWIW there's a lot more to colour than meets the eye! Regards, Peter T |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
Way to go, Gary, and thank you so much for your effort :-)
I can't wait to see what you come up with :-) I tried to download your userForm, but couldn't import it, since it seem to be missing a .FMX file??? I'm currently working on a solution myself, so it'll be interesting to compare :-) Regards, CE "GS" wrote in message ... Here's a userform I threw together that shows how to use "dataset.txt" https://app.box.com/s/23yqum8auvzx17h04u4f Note that the file doesn't contain every possible RGB combination, and so there's opportunity to add your own names. If interested, I can import the parsed file into a worksheet and edit the values, then export it a a csv. After some thought (and a good night's sleep), I believe the userform can be easily modified to 'Add' new names and RGB combos to the "dataset.txt" file so it never actually needs to be loaded into a worksheet. I'm not sure what (if any) practical use this has other than say designing custom paint colors, but it has captured my interest enough that I started a XLS project named "ColorNameManager" which I will provide a download link to when it's done to my satisfaction. This will include a zip file containing the original "dataset.js" file and the above named workbook in XLS format... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- Denne e-mail er fri for virus og malware fordi avast! Antivirus beskyttelse er aktiveret. http://www.avast.com |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
Thanks for providing more detail!
Your project sounds like it was worth the effort you put into it for its intended purpose. I feel, though, that this goes far beyond Charlotte's request and so is why I trimmed out 'the fluff'!<g FWIW there's a lot more to colour than meets the eye! I totally agree! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
Sorry about the userform issue. You could open the file in a text
editor and just copy/paste the code part into an empty userform code window. (The 'frx' file is a binary component of the same userform) The 'frm' file is a VBA component exported as any other so I'm not understanding why you can't just 'Import...' it into a project. I decided there's no value to include the original dataset.js file since it won't be used by the project once the txt file is created. That means I'll include the txt file and XLS. Note that they must be stored in the same folder because refs to the txt file are 'ThisWorkbook.Path'! I'll post a link to the project later today (hopefully). Aside from having the same ability to display color and name (if one exists) for entered RGB values, it will also have the ability to 'Add/Remove/Rename'. If you want any other features included before I finish it please post. How it works: (In case you copy/paste the code) RGB values and their respective names are stored in a plain text file. Userform: Loads data into a module-scope variant in its 'Initialize' event; Caption: "Color Name Manager"; Controls: 3 textboxes (txtR, txtG, txtB) for entering RGB values; 3 labels (Red, Green, Blue) for the above textboxes; 1 textbox (txtColorName) for displaying/entering names; 1 label (Color Name) for the above textbox; 1 label (lblColor) for displaying the color as its 'BackColor'; Buttons: Show Name Displays the name of the RGB values entered. If name doesn't exist, displays "Name Not Found" and prompts to add a new name. New Name Remove (to be added) Rename (to be added) Done These are managed as context-sensitive items via 'SetButtonState', so you can only take appropriate actions. The data is managed via editing the array during runtime; The txt file is updated whenever a change in data occurs; If you try adding a new name to existing RGB values you are prompted to rename Yes/No. If you try adding an existing name to non-existing RGB values you are notified of this and prompted to change its RGB values Yes/No. If you try to remove a name you are asked to confirm the action Yes/No. Pressing the 'Escape' key unloads the userform. I'm considering displaying the txt file contents in a listbox instead of a worksheet. This will change the userform slightly to show 'pages' for "RGB" and "Data" so you can view all existing data within the userform instead of having to scroll a worksheet. If I go with this, the listbox page will also have full editing capability. It occurs to me, though, that I should build 3 userforms and let you choose which one to go with... Userform1: as described with the controls previously mentioned; Userform2: listbox style with full editing capability; Userform3: a 2-page that contains both versions; ...unless you post your preference before I finish! <FWIW I'm not a fan of textbox-based data forms (generally speaking) and so I already have a listbox format I use for my projects. IMO, this is the better way to go because it's less confusing to the user as to what's being done exactly (or what/how to do an action). (I also have a datagrid version and a ListView version that have non-scrolling header rows, but these require the OCXs being shipped/registered on the host machine and so aren't an option for a VBA project) I use 2 multi-column listboxes; 1 for field headings and 1 for data. Editing of selected data is done via textboxes. Selecting a name in the list will display its color same as described earlier. I'm inclined to use the color name as the primary key in the list, followed by its respective RGB values. This makes sorting the list much easier. (Names are easier to find in a sorted list) Normally (to keep it simple) I'd use 1 column for names and 1 column for the RGB values as a comma delimited list. The RGBs can have separate columns if desired (please advise), but that means adding 2 more textboxes for editing (which is no problem). -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
After a bit more thought I've decided to go with only the listbox
version. (You already have the info for the textbox version) This will include separate columns for the RGB values so extra coding isn't required for the RGB() function when displaying color. Also, the text file is renamed "colornames.dat" and is sorted ascending! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
"GS" wrote in message Thanks for providing more detail! Your project sounds like it was worth the effort you put into it for its intended purpose. I feel, though, that this goes far beyond Charlotte's request and so is why I trimmed out 'the fluff'!<g Yeah I got carried away with the 'proper' way to do it when the simple way is probably good enough. Not sure if you tried the example I posted but if you find any differences in matches with the JavaScript demo it's because it matches against about 100 additional colours not in the list I referred to. As for 'it's intended purpose', it's main features worked with the old 56 colour palette in I think novel ways but when 2007 arrived...! Regards, Peter T |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
"GS" wrote in message
Thanks for providing more detail! Your project sounds like it was worth the effort you put into it for its intended purpose. I feel, though, that this goes far beyond Charlotte's request and so is why I trimmed out 'the fluff'!<g Yeah I got carried away with the 'proper' way to do it when the simple way is probably good enough. Nothing wrong with that, my friend! Happens to me more often than not, and so usually concludes with much code housekeeping. I was initially thinking to put together a 'quick-n-dirty' solution before you posted here. After reading your comments I decided that this, like every other project worth doing, is worth doing well. Problem arose when I couldn't find my listbox dataform sample and so had to start from scratch. The data context isn't as important as the structure that manages the data, which I already had in place at one time. Seems I discarded it in favour of using a listview or my Spread.ocx component I got from Farpoint (back when Farpoint was!) Point of it is that neither of those controls can be used after Vista without installing/registering them, and the latter requires a developer license. No problem for VB6 apps because I use a manifest and so they run reg-free. I can do run Excel apps reg-free only for DLLs via 'LoadLibrary', but that doesn't work for OCXs! Not sure if you tried the example I posted but if you find any differences in matches with the JavaScript demo it's because it matches against about 100 additional colours not in the list I referred to. Haven't used the API yet, nor your example. It's pretty clear what it does and how it works, though, just reading it through. I plan to make a wrapper that simply returns the info (or custom Err.Description). -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
"GS" wrote in message
Point of it is that neither of those controls can be used after Vista without installing/registering them, and the latter requires a developer license. No problem for VB6 apps because I use a manifest and so they run reg-free. I can do run Excel apps reg-free only for DLLs via 'LoadLibrary', but that doesn't work for OCXs! AIUI even in XP still had to register but the difference since Vista is the UAC must be turned off or permission given. Can be done manually, or via cmd (without turning off the UAC), or with an installer subject to the user accepting the prompt that appears. I've never looked into RegFree and manifest, always meant to! However even Regfree won't help with Office 64bit, right? Seems for VBA the only solution for the old favourite ocx's is to roll your own. You mentioned listview, was thinking about adapting that one day. Regards, Peter T |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
"GS" wrote in message
Point of it is that neither of those controls can be used after Vista without installing/registering them, and the latter requires a developer license. No problem for VB6 apps because I use a manifest and so they run reg-free. I can do run Excel apps reg-free only for DLLs via 'LoadLibrary', but that doesn't work for OCXs! AIUI even in XP still had to register but the difference since Vista is the UAC must be turned off or permission given. Can be done manually, or via cmd (without turning off the UAC), or with an installer subject to the user accepting the prompt that appears. I've never looked into RegFree and manifest, always meant to! However even Regfree won't help with Office 64bit, right? Not for OCXs, but DLLs should still be possible so long as 'LoadLibrary' is available! Seems for VBA the only solution for the old favourite ocx's is to roll your own. You mentioned listview, was thinking about adapting that one day. It takes a bit more code to implement, but still requires its library to be registered. This is why I did my initial listbox-based dataform way back before UAC days and dropped support for mscomctl.ocx. I see, though, that similar libs ship as DLLs in recent versions of Windows and so may be worth re-exploring use of the listview!! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
"GS" wrote in message Point of it is that neither of those controls can be used after Vista without installing/registering them, and the latter requires a developer license. No problem for VB6 apps because I use a manifest and so they run reg-free. I can do run Excel apps reg-free only for DLLs via 'LoadLibrary', but that doesn't work for OCXs! AIUI even in XP still had to register but the difference since Vista is the UAC must be turned off or permission given. Can be done manually, or via cmd (without turning off the UAC), or with an installer subject to the user accepting the prompt that appears. I've never looked into RegFree and manifest, always meant to! However even Regfree won't help with Office 64bit, right? Not for OCXs, but DLLs should still be possible so long as 'LoadLibrary' is available! There is a ptrSafe version of LoadLibrary. So are you saying with RegFree you can use a VB6 aX dll in Office 64 and call it from VBA, as a ComAddin too? Seems for VBA the only solution for the old favourite ocx's is to roll your own. You mentioned listview, was thinking about adapting that one day. It takes a bit more code to implement, but still requires its library to be registered. This is why I did my initial listbox-based dataform way back before UAC days and dropped support for mscomctl.ocx. I see, though, that similar libs ship as DLLs in recent versions of Windows and so may be worth re-exploring use of the listview!! FWIW the mscomctl treeview has been adapted to VBA, can also use that for a simple listview that's used for the only reason you can include add an icon to the item. Regards, Peter T |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
"GS" wrote in message
Point of it is that neither of those controls can be used after Vista without installing/registering them, and the latter requires a developer license. No problem for VB6 apps because I use a manifest and so they run reg-free. I can do run Excel apps reg-free only for DLLs via 'LoadLibrary', but that doesn't work for OCXs! AIUI even in XP still had to register but the difference since Vista is the UAC must be turned off or permission given. Can be done manually, or via cmd (without turning off the UAC), or with an installer subject to the user accepting the prompt that appears. I've never looked into RegFree and manifest, always meant to! However even Regfree won't help with Office 64bit, right? Not for OCXs, but DLLs should still be possible so long as 'LoadLibrary' is available! There is a ptrSafe version of LoadLibrary. So are you saying with RegFree you can use a VB6 aX dll in Office 64 and call it from VBA, as a ComAddin too? Not sure about that because I expect the DLL needs to be x64. I don't have MSO x64 installed and so can't say. Perhaps you can ask Rob Bovey about this. Also, Olaf Schmidt provided the reg-free stuff for VBA and so you can ask him about MSO x64 in the classic VB forums. Note that this would be 'OT'! Seems for VBA the only solution for the old favourite ocx's is to roll your own. You mentioned listview, was thinking about adapting that one day. It takes a bit more code to implement, but still requires its library to be registered. This is why I did my initial listbox-based dataform way back before UAC days and dropped support for mscomctl.ocx. I see, though, that similar libs ship as DLLs in recent versions of Windows and so may be worth re-exploring use of the listview!! FWIW the mscomctl treeview has been adapted to VBA, can also use that for a simple listview that's used for the only reason you can include add an icon to the item. Yes, I've seen that project but not tried it. I don't see how a treeview can also be used as a listview (or am I misunderstanding you), but I can do similar as both with my Spread.ocx. Again, though, it needs to be registered and so is not an option for VBA projects that need to be reg-free. (My apps are 100% portable and so MUST be reg-free) Otherwise, I have no issue installing/registering mscomctl.ocx. (I do plan to investigate using the newer DLL reg-free, though!) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
"GS" wrote in message
snip I've never looked into RegFree and manifest, always meant to! However even Regfree won't help with Office 64bit, right? Not for OCXs, but DLLs should still be possible so long as 'LoadLibrary' is available! There is a ptrSafe version of LoadLibrary. So are you saying with RegFree you can use a VB6 aX dll in Office 64 and call it from VBA, as a ComAddin too? Not sure about that because I expect the DLL needs to be x64. I don't have MSO x64 installed and so can't say. Perhaps you can ask Rob Bovey about this. But can't compile a dll as 64bit, 32bit only. It's not Win64 that's the problem but Office64. Also, Olaf Schmidt provided the reg-free stuff for VBA and so you can ask him about MSO x64 in the classic VB forums. Note that this would be 'OT'! Well not OT over there I assume! But I guess this is getting OT here already. Seems for VBA the only solution for the old favourite ocx's is to roll your own. You mentioned listview, was thinking about adapting that one day. snip for mscomctl.ocx. I see, though, that similar libs ship as DLLs in recent versions of Windows and so may be worth re-exploring use of the listview!! FWIW the mscomctl treeview has been adapted to VBA, can also use that for a simple listview that's used for the only reason you can include add an icon to the item. Yes, I've seen that project but not tried it. Perhaps you might find the demo file that acts as an extended 'Project Explorer' handy! I don't see how a treeview can also be used as a listview (or am I misunderstanding you), but I can do similar as both with my Spread.ocx. If the listview only contains a single column it has the same appearance and in effect functionality as a treeview that has multiple root nodes but no branches. Only reason to use a listview in such a case is for it's capability to add icons to items, as can do with a treeview. Regards, Peter T |
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
"GS" wrote in message
snip I've never looked into RegFree and manifest, always meant to! However even Regfree won't help with Office 64bit, right? Not for OCXs, but DLLs should still be possible so long as 'LoadLibrary' is available! There is a ptrSafe version of LoadLibrary. So are you saying with RegFree you can use a VB6 aX dll in Office 64 and call it from VBA, as a ComAddin too? Not sure about that because I expect the DLL needs to be x64. I don't have MSO x64 installed and so can't say. Perhaps you can ask Rob Bovey about this. But can't compile a dll as 64bit, 32bit only. It's not Win64 that's the problem but Office64. MSOx64 uses VBA7 for both 32/64 bit versions. This is not an issue! MSOx64 needs x64 DLLs AFAIK! That excludes using VB6 DLLs all together!! Also, Olaf Schmidt provided the reg-free stuff for VBA and so you can ask him about MSO x64 in the classic VB forums. Note that this would be 'OT'! Well not OT over there I assume! But I guess this is getting OT here already. Absolutely! Seems for VBA the only solution for the old favourite ocx's is to roll your own. You mentioned listview, was thinking about adapting that one day. snip for mscomctl.ocx. I see, though, that similar libs ship as DLLs in recent versions of Windows and so may be worth re-exploring use of the listview!! FWIW the mscomctl treeview has been adapted to VBA, can also use that for a simple listview that's used for the only reason you can include add an icon to the item. Yes, I've seen that project but not tried it. Perhaps you might find the demo file that acts as an extended 'Project Explorer' handy! I don't see how a treeview can also be used as a listview (or am I misunderstanding you), but I can do similar as both with my Spread.ocx. If the listview only contains a single column it has the same appearance and in effect functionality as a treeview that has multiple root nodes but no branches. Only reason to use a listview in such a case is for it's capability to add icons to items, as can do with a treeview. Ah! So it's the other way around; using listview as a treeview! I don't think the icon factor makes this worth the trouble for displaying data in a VBA userform. Please have a look at my listbox-based example when I'm done with it. Though Charlotte's usage only includes 4 fields of data, it can handle as many fields as a listbox max ColumnCount. This should be more than ample for a database solution that's flat-table text file based, and uses standard VB file I/O functions. IMO, that's as simple as it gets for working with smaller amounts of data. If it does grow large it can be easily 'dumped' into a SQLite db file and worked with via ADODB. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#26
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
Typo...
MSO (since v2010) uses VBA7 for both 32/64 bit versions. This is not an issue! MSOx64 needs x64 DLLs AFAIK! That excludes using VB6 DLLs all together!! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#27
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
"GS" wrote in message If the listview only contains a single column it has the same appearance and in effect functionality as a treeview that has multiple root nodes but no branches. Only reason to use a listview in such a case is for it's capability to add icons to items, as can do with a treeview. Ah! So it's the other way around; using listview as a treeview! I don't think the icon factor makes this worth the trouble for displaying data in a VBA userform. No, use a treeview to resemble a listview, albeit for the limited purpose I described (and can't use aX/ocx controls) Please have a look at my listbox-based example when I'm done with it. Though Charlotte's usage only includes 4 fields of data, it can handle as many fields as a listbox max ColumnCount. This should be more than ample for a database solution that's flat-table text file based, and uses standard VB file I/O functions. IMO, that's as simple as it gets for working with smaller amounts of data. If it does grow large it can be easily 'dumped' into a SQLite db file and worked with via ADODB. Can you point me to where it is (or again if as I assume it's linked somewhere in this long thread!) Regards, Peter T |
#28
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
"GS" wrote in message
If the listview only contains a single column it has the same appearance and in effect functionality as a treeview that has multiple root nodes but no branches. Only reason to use a listview in such a case is for it's capability to add icons to items, as can do with a treeview. Ah! So it's the other way around; using listview as a treeview! I don't think the icon factor makes this worth the trouble for displaying data in a VBA userform. No, use a treeview to resemble a listview, albeit for the limited purpose I described (and can't use aX/ocx controls) Please have a look at my listbox-based example when I'm done with it. Though Charlotte's usage only includes 4 fields of data, it can handle as many fields as a listbox max ColumnCount. This should be more than ample for a database solution that's flat-table text file based, and uses standard VB file I/O functions. IMO, that's as simple as it gets for working with smaller amounts of data. If it does grow large it can be easily 'dumped' into a SQLite db file and worked with via ADODB. Can you point me to where it is (or again if as I assume it's linked somewhere in this long thread!) I'll be posting a download link (in this thread) for Charlotte *when I'm done with it*! (As I said, I couldn't find my original project so I'm redoing it from scratch) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#29
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
Wow, I'd really started something here, haven't I? :-)
Really looking forward to your solution... ....the solution I'm trying to implement is to look up the individual RGB values, within the function itself - not 'smart' or pretty, but it is as fast as my skills will take me :-) Thanks for all your inputs ans suggestions. CE "GS" wrote in message ... "GS" wrote in message If the listview only contains a single column it has the same appearance and in effect functionality as a treeview that has multiple root nodes but no branches. Only reason to use a listview in such a case is for it's capability to add icons to items, as can do with a treeview. Ah! So it's the other way around; using listview as a treeview! I don't think the icon factor makes this worth the trouble for displaying data in a VBA userform. No, use a treeview to resemble a listview, albeit for the limited purpose I described (and can't use aX/ocx controls) Please have a look at my listbox-based example when I'm done with it. Though Charlotte's usage only includes 4 fields of data, it can handle as many fields as a listbox max ColumnCount. This should be more than ample for a database solution that's flat-table text file based, and uses standard VB file I/O functions. IMO, that's as simple as it gets for working with smaller amounts of data. If it does grow large it can be easily 'dumped' into a SQLite db file and worked with via ADODB. Can you point me to where it is (or again if as I assume it's linked somewhere in this long thread!) I'll be posting a download link (in this thread) for Charlotte *when I'm done with it*! (As I said, I couldn't find my original project so I'm redoing it from scratch) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com --- Denne e-mail er fri for virus og malware fordi avast! Antivirus beskyttelse er aktiveret. http://www.avast.com |
#30
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
What's important is that your solutions meets your needs!
Since I don't have a need for this, I decided to make a generic utility named "Color Name Manager" that has the following features: allows you to add, edit, remove, and rename color name data; uses a comma delimited text file as its data storage; manages data with standard VB file I/O functions and arrays; is dialog based; creates its own menuitem; has task specific context management for user action controls; has a simple help system; finds RGB values for a given name; finds (existing) names for RGB values; It's an XLS with its 'IsAddin' property set True so it's hidden when open, but could be saved as XLA if desired. On first startup it notifies user of menuitem location in both early/late versions. Also, the VBA project (2 userforms, 2 modules) is accessible for modifying. I still have the Rename feature to finish plus write the Help text. Sorry it's taking longer than initially expected, but I seem to have discarded my initial listbox based data form sample and so had to start from scratch!<g I'll keep pluging away at it between other obligations so it gets done sooner than later, and 'off my plate' for now. Perhaps we can swap projects<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 |
#31
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
"GS" wrote in message
What's important is that your solutions meets your needs! Since I don't have a need for this, I decided to make a generic utility named "Color Name Manager" that has the following features: allows you to add, edit, remove, and rename color name data; uses a comma delimited text file as its data storage; manages data with standard VB file I/O functions and arrays; is dialog based; creates its own menuitem; has task specific context management for user action controls; has a simple help system; finds RGB values for a given name; finds (existing) names for RGB values; Wow, all that for a simple colour match algorithm, and you said I added too much fluff! <vbg Charlotte, if all you want to do is get the name of the colour that best matches your RGB see the example I included in my second post in this thread. It does exactly that using a simple "linear" calculation and replicates the javascript demo you referred to. It should be straightforward to adapt to your requirements. Regards, Peter T |
#32
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
Wow, all that for a simple colour match algorithm, and you said I
added too much fluff! <vbg It's not a simple colour match algorithm! The development of this utility was inspired by your explanation of the time/effort you put into your project! There's no 'fluff' in my project; it just reflects its name in terms of features which fit what the name implies. It's being released as a 'finished product' and so requires the effort necessary to that, IMO! It also writes the data to a worksheet named range and so can be used in a lookup formula if desired. What do you think a "Color Name *Manager*" utility should do?<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 |
#33
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
Just curious, Peter, about where you are located having used this
spelling... colour ...as I'm located in eastern Ontario, Canada but I use/speak enUS! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#34
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
"GS" wrote in message Wow, all that for a simple colour match algorithm, and you said I added too much fluff! <vbg It's not a simple colour match algorithm! Sorry I misunderstood, read it as all intended as a solution for Charlotte's Q What do you think a "Color Name *Manager*" utility should do?<g There's no simple answer because it depends on peoples' objectives, and with colour match would probably be diverse. But like anything as a minimum a means to input and store data, input the query, process, and output, all with a suitable UI. 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. A sheet is ideal for colours because you can store all the various attributes (RGB, Hex, HSL, name, etc) in cells and display each colour (in 2003 coloured shapes as cells can only display 56 colours). Or for display only could also show each colour in a form in a variety of ways and retrieve from elsewhere. Input the colour to match, consider how, or maybe a coloured pixel 'picked' from the screen. Output the matched colour, its name and specs, to the form, perhaps to the clipboard too. As I mentioned my old app included a colour match utility as a small feature of a much larger range of colour related stuff. Regards, Peter T |
#35
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
"GS" wrote in message Just curious, Peter, about where you are located having used this spelling... colour ..as I'm located in eastern Ontario, Canada but I use/speak enUS! UK, ie English-English! |
#36
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
"GS" wrote in message
Just curious, Peter, about where you are located having used this spelling... colour ..as I'm located in eastern Ontario, Canada but I use/speak enUS! UK, ie English-English! Nice! How do you like living in the midst of all that UK history? -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#37
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
There's no simple answer because it depends on peoples' objectives,
and with colour match would probably be diverse. But like anything as a minimum a means to input and store data, input the query, process, and output, all with a suitable UI. And so is why I'm putting all the effort into it as a finished product! 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. A sheet is ideal for colours because you can store all the various attributes (RGB, Hex, HSL, name, etc) in cells and display each colour (in 2003 coloured shapes as cells can only display 56 colours). Or for display only could also show each colour in a form in a variety of ways and retrieve from elsewhere. It's just displaying RGB as BackColor of a label. The code verifies if a new entry (or edited listitem) already has the RGB values OR name entered. I found this was a needed feature since Charlotte's link color names list has, for example, both "Aqua" and "Cyan" with the same RGB values. Thus I've 'removed' "Cyan" for now since it's a standard color. (the list should only have non-standard names to start with, IMO, leaving inclusions up to the end user) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#38
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
"GS" wrote in message I found this was a needed feature since Charlotte's link color names list has, for example, both "Aqua" and "Cyan" with the same RGB values. Thus I've 'removed' "Cyan" for now since it's a standard color. (the list should only have non-standard names to start with, IMO, leaving inclusions up to the end user) Why only non standard names, isn't that for the user to decide. If there are duplicates maybe return both, eg return "Aqua = Cyan". Indeed it's not unusual for colour lists to include 2+ names that refer to the same RGB, and in the case of this list there are are many duplicates besides cyan. |
#39
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
"GS" wrote in message Nice! How do you like living in the midst of all that UK history? Old! Only kidding, I appreciate having a sense of history around. Not always though, I arrived once in one particular country in the middle of troubles and asked a local to explain the background, and was told "need to go back 3500 years and work forwards to begin to understand..." it took a long time! (too OT for here) |
#40
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get a color name from any RGB combination?
"GS" wrote in message
I found this was a needed feature since Charlotte's link color names list has, for example, both "Aqua" and "Cyan" with the same RGB values. Thus I've 'removed' "Cyan" for now since it's a standard color. (the list should only have non-standard names to start with, IMO, leaving inclusions up to the end user) Why only non standard names, isn't that for the user to decide. If there are duplicates maybe return both, eg return "Aqua = Cyan". Indeed it's not unusual for colour lists to include 2+ names that refer to the same RGB, and in the case of this list there are are many duplicates besides cyan. As you say, it's up to the user to decide. I'm talking about the initial startup list I provide with the utility. *I prefer* to not include standard colors already in the standard palette. Thus users can add them if they want them included in the db, and/or edit/remove/rename existing colors to their liking! The intent is to supply a 'starter list' only. (I'm just treating it as 'dummy data'!<g) The reason is that we all see the standard colors differently, so how Red appears to me doesn't necessarily match how it appears to you. Custom named RGB values are already how that respective color appears someone else, not necessarily how it appears to the user of this utility(IMO). I haven't deliberately looked for other duplicates because that's not on my agenda. This I'm leaving up to the user! If a matching name and/or RGB is encountered the user is notified and prompted for how they want to handle their input values when adding/editing data. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
|
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom color for font color and or background shading color | Excel Programming | |||
combination | Excel Discussion (Misc queries) | |||
Combination | Charts and Charting in Excel | |||
combination | Excel Programming | |||
combination | Excel Programming |