use a udf in vba excel for multiple cells

Question

a user defined function I use in excel

Function Hex2RGB(hex As String) As String

    Red = Val("&H" & Mid(hex, 1, 2))
    Green = Val("&H" & Mid(hex, 3, 2))
    Blue = Val("&H" & Mid(hex, 5, 2))

  Debug.Print RGB(Red, Green, Blue)
    Hex2RGB = RGB(Red, Green, Blue)

End Function

I have a worksheet with a 10 times 10 matrix for which this function would need to be used for every cell in the range. There has to be some way that I don't have to click on each cell and enter =Hex2RGB("value") manually? when I write value I am referring to something like OD62A2....which is the colour than to be transformed in excel format

Where do you store hex values? If you don't store them somewhere in the workbook, there is unfortunately no other way (unless you insert those hex codes based on some logic such as value +1) — Robert J.
– Robert J., Commented Aug 5, 2014 at 11:53
the hex values are stored in the worksheet ( in the 10 times 10 matrix) — Hein
– Hein, Commented Aug 5, 2014 at 13:41

AnalystCave.com · Accepted Answer · 2014-08-05 12:11:08Z

0

Use a range parameter instead of value as below. Set the function manually to the first cell (e.g. left upper cell in the matrix) then copy and paste it across the matrix - the range will change

Public Function Hex2RGB(r As Range) As String
    Dim hex As String: r.Value
    Red = Val("&H" & Mid(hex, 1, 2))
    Green = Val("&H" & Mid(hex, 3, 2))
    Blue = Val("&H" & Mid(hex, 5, 2))

  Debug.Print RGB(Red, Green, Blue)
    Hex2RGB = RGB(Red, Green, Blue)

End Function

answered Aug 5, 2014 at 12:11

AnalystCave.com

5,0242 gold badges25 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hein Over a year ago

if I try this I get a value! error. In the first cell I typed Hex2RGB("value"). How do I have to enter it?

Robert J. Over a year ago

instead of typing "value" you target a cell as visible from function ...(r as Range)

Hein Over a year ago

I am quite new to vba....how do I do that? do i highlight all cells and then just enter in the function bar in excel =Hex2RGB?

Hein Over a year ago

i already set the function manually to the first cell in the top left with =Hex2RGB("value") but then I get an value! error.

David Zemens · Accepted Answer · 2014-08-05 15:39:43Z

0

Step 1: Add the following macro. This macro will iterate over the cells in the selected range, and replace them with a formula based on its own value, which I think is what you are trying to do.

Sub GetHexValues()
    Dim rng as Range
    Dim cl as Range
    Set rng = Range(Selection.Address)
    For each cl in rng.Cells
        cl.Formula = "=Hex2RGB(" & Chr(34) & cl.Value & Chr(34) & ")"
    Next
End Sub

Step 2 Make sure your function is:

Public Function Hex2RGB(hex) As String
    Red = Val("&H" & Mid(hex, 1, 2))
    Green = Val("&H" & Mid(hex, 3, 2))
    Blue = Val("&H" & Mid(hex, 5, 2))

    Debug.Print RGB(Red, Green, Blue)
    Hex2RGB = RGB(Red, Green, Blue)

End Function

Step 3: Select the matrix range. (NB: Normally I recommend not using Select but as a means of user-input it is OK to do that here, I think.)

There is generally speaking, no way to use a UDF worksheet function to manipulate a range of cells in the manner you're describing. UDF's can generally not alter many properties and values of cells/ranges except the cell that is calling the function. This is to avoid circular reference/etc. (there are workarounds to this limitation, but generally accepted rule is to use Subroutines to manipulate the worksheet objects).

edited Aug 5, 2014 at 15:39

answered Aug 5, 2014 at 14:29

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

6 Comments

Hein Over a year ago

hello...that is a nice solution when I copy your code and run the macro after selection the range I get error 1004 objectdefind err . it is in the line cl.Formula= line. I set the function before the macro so it is not missing....

David Zemens Over a year ago

Looks like I forgot a quotation mark at the end of that line. I will revise the answer.

Hein Over a year ago

sorry but I dont see a difference...I thought 4 quot marks should be alright?

David Zemens Over a year ago

I added a quotation mark at the end which was previously omitted, but that still doesn't work. See updated answer now.

Hein Over a year ago

could you tell me what the CH(34) does?

|

Collectives™ on Stack Overflow

use a udf in vba excel for multiple cells

2 Answers 2

4 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related