About Excel Macros
The macro code language for most Office programs, including Excel, is Visual Basic for Applications (VBA). You may have recorded macros in Excel by stepping through actions that the program saves for you. When you record a macro, Excel records the VBA code describing your actions in a module attached to the workbook.
http://office.microsoft.com/en-us/training/introduction-to-terms-RZ001150634.aspx?section=3
Downloading Excel Macros
Try Office 365 Home
Office 365 gives you the power and flexibility to get things done from virtually anywhere.
http://office.microsoft.com/en-us/try/
Starting Excel Macros
IMPORTANT: This feature isn’t available in Office on a Windows RT PC.
Excel provides several ways to run a macro. A macro is an action or a set of actions that you can use to automate tasks.
http://office.microsoft.com/en-us/excel-help/run-a-macro-HA102748985.aspx
- Open Excel
- Do one of the following:
- Click File, click new, select New Workbook
- Click File, click open, select your macro-enabled workbook
- Click the View tab
- The Macros button should appear on the right side of the Ribbon(top menu bar).
Hello World!
Recording
- Click the Macros button on the View tab
- Select Record Macro…
- Click OK
- Type
Hello World!
in cell A1 - Click the Macros button again and select Stop Recording
Editing
- Click the Macros button on the View tab
- Select View Macros
- Select your macro from the list
- Click the Edit button
Your code should look something like this:
Sub Macro1() ' ' Macro1 Macro ' ' Range("A1").Select ActiveCell.FormulaR1C1 = "Hello World!" End Sub
"Hello World!"
Playback
- Clear the contents of cell A1
- Click the Macros button on the View tab
- Select View Macros
- Select your macro from the list
- Click the Run button
"Hello World!"
Variables
You often have to store values when you perform calculations with Visual Basic. For example, you might want to calculate several values, compare them, and perform different operations on them, depending on the result of the comparison. You have to retain the values if you want to compare them.
http://msdn.microsoft.com/en-us/library/cd6hcy37.aspx
Sub Macro1() Dim message As String message = "Hello World!" Range("A1").FormulaR1C1 = message End Sub
"Hello World!"
Functions
All executable code must be inside a procedure. Each procedure, in turn, is declared within a class, a structure, or a module that is referred to as the containing class, structure, or module.
To return a value to the calling code, use a Function procedure; otherwise, use a Sub procedure.
http://msdn.microsoft.com/en-us/library/sect4ck6.aspx
Sub Macro1() ' Call the function writeMessage End Sub Sub writeMessage() Dim message As String message = "Hello World!" Range("A1").FormulaR1C1 = message End Sub
"Hello World!"
Passing Variables to Functions
In this example, the function writeMessage
will accept a value and declare it as a string.
Sub Macro1() writeMessage ("Hello World!") End Sub Sub writeMessage(message As String) Range("A1").FormulaR1C1 = message End Sub
"Hello World!"
Error Handling
In Microsoft Excel, you can use the On Error statement in a Microsoft Visual Basic for Applications macro (Sub procedure) to trap errors and direct procedure flow to the error-handling statements in a procedure. By using error handling, you make your macros and your application easier to use by intercepting run-time errors before the user sees them.
http://support.microsoft.com/kb/141571
Sub Macro1() ' Run the Error handler "ErrHandler" when an error occurs. On Error GoTo Errhandler writeMessage ("Hello World!") ' Disable the error handler. On Error GoTo 0 ' Exit the macro so that the error handler is not executed. Exit Sub Errhandler: ' If an error occurs, display a message and end the macro. MsgBox "An error has occurred. The macro will end." End Sub Sub writeMessage(message As String) Range("A1").FormulaR1C1 = message End Sub
"Hello World!"
Further Reading
Get in the Loop with Excel Macros
- Microsoft’s training course about Excel Macros
- Excel Easy’s VBA (Visual Basic for Applications) guide