RGB function fails with Value error

[JenniferMurphy]

Apparently VB has an RGB function, but VBA does not. At least, when I try this in the Immediate wiondow,

=rgb(255,0,0)

I get "Compile error. Expected: line number or label or statement or end of statement". I don't understand that error, so I thought I'd write my own:

Function RGB(R As Long, G As Long, B As Long) As Long
  RGB = R + 256 * (G + (B * 256))
End Function

I put it in my person add-in module, but when I run it from a worksheet, it gets a Value error.

How can I either get a built-in RGB function or the one above to work?

Thanks

 

[Rick Rothstein]

I think the problem may be that VBA has a function named RGB so it is objecting to you calling your function by that name (how is it to know which RGB function to use when called?). But even renaming it, you still are making your function harder than it needs to be. Here I'll call the function xlRGB to distinguish it from VBA's RGB function. Give this a try...

Function xlRGB(R As Long, G As Long, B As Long) As Long
  xlRGB = RGB(R, G, B)
End Function

 

[RoryA]

In the immediate window, you would use:

?rgb(255,0,0)

and not:

=rgb(255,0,0)

 

[JenniferMurphy]

I think the problem may be that VBA has a function named RGB so it is objecting to you calling your function by that name (how is it to know which RGB function to use when called?).

I called it RGB because I thought I had determined that the RGB function was only in VB, not VBA. Apparently, I confused myself by using "=" instead of "?" in the Immediate window, as pointed out by RoryA.

But even renaming it, you still are making your function harder than it needs to be.

I used the explicit formula, rather than calling the function, because I had convinced myself that there was no such function. (sigh)

Here I'll call the function xlRGB to distinguish it from VBA's RGB function. Give this a try...

Function xlRGB(R As Long, G As Long, B As Long) As Long
  xlRGB = RGB(R, G, B)
End Function

I thought I tried that calling it MyRGB. I just copied your xlRGB function and it also fails with a #VALUE ! error. Then as I was preparing this reply, I discovered that all of the functions in my personal add-in are working. They all get #VALUE ! errors. I put breakpoints on the Function statements and they never get called.

What could I have done to cause my personal add-in to stop working? AAARRRGGG!!!

 

[JenniferMurphy]

(sigh) Thanks. The Alzheimer's is getting worse.

 

[JenniferMurphy]

I thought I tried that calling it MyRGB. I just copied your xlRGB function and it also fails with a #VALUE ! error. Then as I was preparing this reply, I discovered that all of the functions in my personal add-in are working. They all get #VALUE ! errors. I put breakpoints on the Function statements and they never get called.

What could I have done to cause my personal add-in to stop working? AAARRRGGG!!!

I just rebooted the computer and everything is working again.

Thanks for all the help.

 

[JenniferMurphy]

I think I just figured out why none of the functions in my personal add-in were working. I believe I must have failed to clear a breakpoint, so one function was still "running" and Excel couldn't call another one. That would explain why rebooting resolved it.

If I am right, the error messages leave a lot to be desired.