How to Use Hex in VBA Excel
Using hexadecimal notation makes it easier to understand the numbers that certain VBA objects or properties use. One such property is color. For example, the base-10 notation "65025" doesn't reveal the three-part structure of the number, but using the hexadecimal notation for the same number, "&HFF00FF," reveals this structure: the color's red component is in the two rightmost characters, the blue component is in the two middle characters, and the green component is in the fifth and sixth characters from the right.
Click the "Developer" tab to expose the controls for working with VBA.
Click the "Visual Basic" button to enter the VBA programming environment.
Paste the following program in the window at screen center. This program uses VBA's hexadecimal notation symbol, "&H," to define two variables, "a," and "b." Variable "x" holds the sum of these two numbers, and the "MsgBox" statement displays the non-hexadecimal representations of "a," "b," and "x." Public Sub doHexMath()Dim x, a, ba = &H10b = &HAx = a + bMsgBox a & " plus " & b & " equals " & xEnd Sub
Click any of the program's statements to select the program.
Click the "Run" menu's "Run" command. Excel will display the message "16 plus 10 equals 26." In hexadecimal, "10" equals 16, and "A" equals 10.
Click the "Developer" tab, then click the "Visual Basic" button on the "Code" panel. This action will bring up the VBA programming environment.
Paste the following program into the programming window that appears. This program uses hexadecimal notation to assign a background color to the currently selected cell in the workbook. That notation appears in the assignments to the variables "red," "green," and "blue." The "ActiveCell" statement also uses hexadecimal notation, and adds the "red," "green," and "blue" components together to yield a number in the format needed to set colors in Excel. Notice that only the "red" variable is non-zero. This means that the color you'll see will be pure red. Public Sub colorCell()Dim blue, green, redred = &HFFgreen = &H0blue = &H0ActiveCell.Interior.Color = blue * &HFF00 + green * &HFF + redEnd Sub
Click any statement in the program to select the program for execution.
Click the "Run" menu's "Run" command to execute the program.
Click the "Excel" icon in the Windows taskbar to return to Excel. Notice that the current cell has the red color you specified in step 2.