in a macro
One of the great things about Excel is its ability to automate processes with macros.
In this example, we have set up a macro to run when a button is clicked.
This will then trigger a dialog box that asks for the user to input a number between 1 and 3.
Each number is assigned to a color. The number 1 is red, the number 2 is green and the number 3 is blue.
The code also has cell B1 populate with the appropriate color that we have assigned to each number.
It populates with the actual text string of the color name and it also fills the cell with that color.
We use the ‘CHOOSE’ function in the VBA code itself to achieve this.
This article isn’t about how to write VBA code. It’s just a quick example showing you the possibilities of combining CHOOSE with VBA.
Here we’ve made use of the ‘CHOOSE’ function to achieve the outcome of placing the color value, as well as the color itself, in cell ‘B2’.
That said, let’s look at the code…
The main difference in the ‘CHOOSE’ function of this VBA code is that it uses the ‘R1C1’ style.
Under more simple circumstances, you’d be using literal cell references.
This means that we are using a cell reference style that is based on a single reference location on the worksheet.
‘R’ stands for row – and as you may have guessed, ‘C’ stands for column.
Let’s say our reference cell location was ‘D10’, ‘R[-1]C’.
This would be the equivalent of ‘E9’ (one row above…’R[-1]’… and one column to the right…’C’… of ‘D10’).
Another thing to note is that if we are using ‘R1C1’ style, a zero is not required for no change in row or column.
That would just be RC.
Another example would be that the ‘R1C1’ notation for cell ‘D11’ in relation to ‘D10’ would be ‘RC’. No number is required to the column reference since it would be 0.
The line in our VBA code, ActiveCell.FormulaR1C1 = _ “=CHOOSE(RC[-1],””Red””,””Green””,””Blue””)”, basically tells Excel to place the ‘CHOOSE’ formula between the double quotes into the active cell (which happens to be ‘B1’).
Knowing that ‘RC[-1]’ in relation to ‘B1’ (again, the active or selected cell) is ‘A1’, the ‘index_num’ of our formula is cell ‘A1’.
This is an example of how we can use VBA to insert the ‘CHOOSE’ function (or any other function!) into the cell of our choosing.
The use of VBA provides the flexibility to have Excel do this based on other events – such as the selection of a number value from an input box.