Need code that converts colors from the RGB code to the Natural Color System (NCS)

[biancamtf]

I have this spreadsheet with 255 RGB Codes. I need a code to convert them to the Natural Color System (NCS).

I've tried AI solutions, which gave me VBA codes but they don't seem to work.

Example of AI generated VBA code:

Function RGBtoNCS(r As Integer, g As Integer, b As Integer) As String
    ' Map basic RGB values to NCS approximations
    ' This is an estimated mapping, and not an exact conversion
    
    Dim ncsColor As String
    Dim minDiff As Double
    Dim diff As Double
    Dim i As Integer
    Dim closestNCS As String
    
    ' List of common colors in NCS and their approximate RGB equivalents
    Dim ncsColors(6, 3) As Variant
    ncsColors(0, 0) = "NCS S 0500-N": ncsColors(0, 1) = 255: ncsColors(0, 2) = 255: ncsColors(0, 3) = 255 ' White
    ncsColors(1, 0) = "NCS S 9000-N": ncsColors(1, 1) = 0: ncsColors(1, 2) = 0: ncsColors(1, 3) = 0 ' Black
    ncsColors(2, 0) = "NCS S 1080-Y70R": ncsColors(2, 1) = 255: ncsColors(2, 2) = 0: ncsColors(2, 3) = 0 ' Red
    ncsColors(3, 0) = "NCS S 0580-Y": ncsColors(3, 1) = 255: ncsColors(3, 2) = 255: ncsColors(3, 3) = 0 ' Yellow
    ncsColors(4, 0) = "NCS S 1080-G20Y": ncsColors(4, 1) = 0: ncsColors(4, 2) = 255: ncsColors(4, 3) = 0 ' Green
    ncsColors(5, 0) = "NCS S 1075-R90B": ncsColors(5, 1) = 0: ncsColors(5, 2) = 0: ncsColors(5, 3) = 255 ' Blue
    
    ' Initialize minimum difference with a large number
    minDiff = 999999

    ' Loop through the NCS colors to find the closest match
    For i = 0 To 5
        diff = Sqr((r - ncsColors(i, 1)) ^ 2 + (g - ncsColors(i, 2)) ^ 2 + (b - ncsColors(i, 3)) ^ 2)
        
        If diff < minDiff Then
            minDiff = diff
            closestNCS = ncsColors(i, 0)
        End If
    Next i
    
    RGBtoNCS = closestNCS
End Function

When I try to use the formula they give after inserting the VBA code, it shows the error "We found a typo in your formula and tried to correct it". If you don't accept this correction, it shows the error "There's a problem with your formula", displayed in the image.

I'd like a different code to convert from RBG to NCS. But if you have any idea on how to make this VBA code work, that's great too.


Thanks!

Bianca,
BR.

 

[Dan_W]

I've never heard of NCS, but now I'm intrigued and will go off and read up about it.
But getting to the problem - there is something odd about that screen capture, but I just can't put my finger on it - have you put spaces in the formula somewhere? It should look like

And the function 'works' for me, insofar as it doesn't produce an error message and returns a result. I don't know if that result is correct, but there it is.

I don't know if that was helpful at all...

 

[PeteWright]

Hi @biancamtf ,

first time I've heard about the NCS color system. Pretty interesting.

Regarding your question, I don't get any errors when applying your formula:

Maybe you typed in a commas but your operating system is expecting semicolons.
Please check your regional settings:

@Dan_W Spaces or Line Breaks are fine in Excel.
Instead of typing

=functionX(a,b,c)

you can also type

= functionX( a, b, c )

 

[PeteWright]

One more thing that comes to my mind:

You need to put your code into a new Module in order to make it work.

Unlike Procedures (Subs) Functions have to be placed in Modules if they should be "visible" in a worksheet and declared as "Public Function" or just "Function".
Otherwise the worksheet will not "know" the function and throw an error.

 

[Dan_W]

One more thing that comes to my mind:

You need to put your code into a new Module in order to make it work.

Unlike Procedures (Subs) Functions have to be placed in Modules if they should be "visible" in a worksheet and declared as "Public Function" or just "Function".
Otherwise the worksheet will not "know" the function and throw an error.

I had thought that too, but I would've expected a different error.

 

[biancamtf]

Thank you for the replies! The formula is in fact working now.

I've tried redoing the steps in VBA Editor and Excel, but I think what truly worked was using the semicolon instead of commas! With that, I've got the same results you did.

But now I've realised there's a different problem: the code is much more limited than it was supposed to be. It only results with 6 different NCS codes (0500-N, 9000-N, 1080-Y70R, 0580-Y, 1080-G20Y and 1075-R90B), when it was supposed to result in much more varied codes. The problem is in the way the code was written, which makes the conversion to not accomodate the variety of the NCS.

In the following image there's an example of what the result should be close to (something around 7005-Y80R) and what the code is resulting (9000-N).

So now I'll try to find a way of correcting the code... (If you have any ideas, I'd appreciate it )

But anyways, your replies helped me figure out where the problem was, so thank you so much!!

 

[PeteWright]

Thank you for the replies! The formula is in fact working now.

I've tried redoing the steps in VBA Editor and Excel, but I think what truly worked was using the semicolon instead of commas! With that, I've got the same results you did.

But now I've realised there's a different problem: the code is much more limited than it was supposed to be. It only results with 6 different NCS codes (0500-N, 9000-N, 1080-Y70R, 0580-Y, 1080-G20Y and 1075-R90B), when it was supposed to result in much more varied codes. The problem is in the way the code was written, which makes the conversion to not accomodate the variety of the NCS.

In the following image there's an example of what the result should be close to (something around 7005-Y80R) and what the code is resulting (9000-N).

View attachment 117595

So now I'll try to find a way of correcting the code... (If you have any ideas, I'd appreciate it )

But anyways, your replies helped me figure out where the problem was, so thank you so much!!

Glad it helped
I searched a little bit on this topic and it seems that ncscolour.com is the official (Swedish) website of the NCS color system.
Summing up what I've read any color can be written in the NCS scheme by finding it's hue and nuance but this is just a visual conversion.
So far I haven't found any formula to convert from RGB (or other) color notations.

If you do have any kind of formula or something helpful to do so, please let me know.
I could write the VBA function then.

Best regards