Excel 2013 & 2016: Visual Basic Commands

Updated 2013.08.07

Below are some commands that you may find in a Visual Basic macro file, with brief descriptions of what they do.

Getting input: the InputBox

You can make your macros much more flexible if you allow the user to provide some input values and then using those input values to carry out your macro instructions. Visual Basic has an InputBox function that allows a macro to receive input from the spreadsheet user. To get input from the user:

stuff = inputbox("Please enter some stuff:")

 

This instruction causes an input box to be displayed on the screen with the message Please enter some stuff: displayed. Whatever the user types in response to this message is saved in a variable called Stuff. Whenever we refer to Stuff in the future, Visual Basic will know that we are referring to the data typed in by the user. Note that there is nothing special about the word "Stuff". If we wanted the user to type in his name, we could have done this:

username = inputbox("Please enter your name:")

 

Or if we wanted the user to type in a major, we could have done this:

Major = inputbox("Please enter the desired major:")

 

To append text, use the "&" operator:

ActiveCell.FormulaR1C1 = stuff & stuff

Changing the active cell

Your macro commands must be applied to one of the pages of a workbook. This workbook must be selected before any other commands are executed. The following command may be inserted at the top of your macro automatically by Visual Basic. If your macro doesn't work at first, and this command does not appear in your macro, add it to the beginning of the macro.

Sheets("Sheet1").Select

 

The text "Sheet1" can be replaced with the name of any worksheet (the name on the tab at the bottom of the worksheet).

 

To select any range:

Range("A1").Select               'select cell A1

Range("A:A").Select              'select column A

Range("A1:D10").Select           'select range A1:D10

 

When you use Excel, at least one cell is always active (selected). To make the active cell bold:

Activecell.Font.Bold = true

 

And to turn bold formatting off for the active cell:

Activecell.Font.Bold = false

 

To make the active cell italic:

Activecell.Font.Italic = true

 

And to turn it off:

Activecell.Font.Italic = false

 

To set the font of the active cell:

Activecell.Font.Name = "Times New Roman"

Activecell.Font.Name = "Courier New"

Activecell.Font.Name = "Arial"

 

Note that you can put any font name between the quotation marks. However, you must be careful to spell it correctly. Note, for example, that there is a single blank between the words "Times" and "New" and another blank between the words "New" and "Roman". Capitalization is not important.

Moving the cursor to a specific cell

To go to a given cell:

Application.Goto Reference:="r5c2"

 

Instead of the usual cell name with the column letter followed by a row number, Excel uses both a row number and a column number designation. Examples:

r1c1 is cell A1

r5c5 is cell E5

 

To go to a given named cell (use any name that you have given to a range):

Application.Goto Reference:="range name"

 

To put text in a cell:

ActiveCell.FormulaR1C1 = "your text"

 

To put a formula in a cell:

ActiveCell.FormulaR1C1 = "=A1+1"

Don't forget the equal sign at the beginning of the formula, as well as the quotation marks around the entire formula.