Below are some commands that you may find in a Visual Basic macro file, with brief descriptions of what they do.
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
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.
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.
To go to a given cell:
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.