Get RGB values from Excel cells

I do custom Crystal reports for customers as a side-job and was presented with an interesting challenge this last week.

The issue was that the customer wanted to color-code fields in their Crystal report based on pre-defined fields. They provided a spreadsheet with the colors, and it was my job to get them somewhere that the report could use.

In the end, I had to use some VBA and some new SQL tables, but it works, and works well.

The first challenge was this - in Crystal reports you really only have a couple of conditional formatting options for color, either their color constants or an RGB scheme:
// This conditional formatting formula must return one of the following Color Constants:
//
// Color (red, green, blue)
// crBlack
// crMaroon
// crGreen
// crOlive
// crNavy
// crPurple
// crTeal
// crSilver
// crRed
// crLime
// crYellow
// crBlue
// crFuchsia
// crAqua
// crWhite
// crNoColor

Since the customer was providing the colors, I knew I had to go with RBG. But how to pull those values out of the provided spreadsheet? Enter VBA.

Thanks to the power of the Google, I took and modified some VBA code to pull the unique R code, G code, and B code (since Crystal expects 3 distinct strings, not a single string in their RBG formula) for each cell:

Function showR(rcell)
Dim myStr As String
Application.Volatile

myStr = Right("000000" & Hex(rcell.Interior.Color), 6)
showR = Application.Evaluate("=Hex2dec(""" & Right(myStr, 2) & """)")

End Function

Function showG(rcell)
Dim myStr As String
Application.Volatile

myStr = Right("000000" & Hex(rcell.Interior.Color), 6)
showG = Application.Evaluate("=Hex2dec(""" & Mid(myStr, 3, 2) & """)")

End Function

Function showB(rcell)
Dim myStr As String
Application.Volatile

myStr = Right("000000" & Hex(rcell.Interior.Color), 6)
showB = Application.Evaluate("=Hex2dec(""" & Left(myStr, 2) & """)")

End Function

That worked like a charm. I then dumped those values into the database, where I was able to use conditional formatting to push those values out of the database into the field.

After a large amount of work, it works like a charm.

VBA is....interesting

VBA is powerful. I'm in an intro to VBA class at BYU this semester which has been pretty awesome. There's a bit of stuff you can do in VBA to make Excel and other apps pretty smooth. The IDE sucks though. Pretty bad.

Maybe I'm spoiled, having learned C# in VS2008. Maybe I'm just a sucker for a pretty face, and VS definitely has that. Eiter way, using the built-in IDE and looking at the standard forms, the only thing I can think is "This would look SO much better in C#." Problem is that my final has to be done in VBA. I considered importing a C# project as an activex control and sneaking around it that way, but decided that would probably earn me a great grade for creativity, and a bad grade for everything else.

I mean, who wants a form that looks this bland?

Oh well. This is a screenshot from my final project, a tax client tracking tool for my dad. It can invoice, create/email reports, email reminders, etc.