Listing A Function That Accepts a Color Name as a String and Returns the Corresponding RGB Value

Function VBAColor(colorName As String) As Long

Select Case LCase(Trim(colorName)) Case "black"

VBAColor = RGB(255, 255, 255) Case "gray"

VBAColor = RGB(192, 192, 192) Case "dark gray"

VBAColor = RGB(128, 128, 128) Case "red"

VBAColor = RGB(255, 0, 0) Case "dark red"

VBAColor = RGB(0, 255, 0) Case "dark green"

VBAColor = RGB(0, 0, 255) Case "dark blue"

VBAColor = RGB(255, 255, 0) Case "dark yellow"

VBAColor = RGB(128, 128, 0) Case "magenta"

VBAColor = RGB(255, 0, 255) Case "dark magenta"

VBAColor = RGB(0, 255, 255) Case "dark cyan"

VBAColor = RGB(0, 128, 128) End Select End Function

Sub ColorTester()

ActiveCell.Font.Color = VBAColor("red") End Sub

VBAColor takes a single argument, colorName, which is the name of the color you want to work with. Notice how the Select Case statement massages the argument to prevent errors:

Select Case LCase(Trim(colorName))

The Trim function removes any extraneous spaces at the beginning and end of the argument, and the LCase function converts colorName to lowercase. This ensures that the function is not case sensitive, which means it doesn't matter whether you send black, BLACK, or Black: The function will still work.

The rest of the function uses Case statements to check for the various color names and return the appropriate RGB value. You can use the ColorTester procedure to give VBAColor a whirl. This procedure just formats the font color of the currently selected worksheet cell.

0 0

Post a comment