Category: VBA

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

1. Open Excel
2. Do one of the following:
   1. Click File, click new, select New Workbook
   2. Click File, click open, select your macro-enabled workbook
3. Click the View tab
4. The Macros button should appear on the right side of the Ribbon(top menu bar).

Hello World!

Recording

1. Click the Macros button on the View tab
2. Select Record Macro…
3. Click OK
4. Type Hello World! in cell A1
5. Click the Macros button again and select Stop Recording

Editing

1. Click the Macros button on the View tab
2. Select View Macros
3. Select your macro from the list
4. 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

1. Clear the contents of cell A1
2. Click the Macros button on the View tab
3. Select View Macros
4. Select your macro from the list
5. 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 VBA Tutorial

• Excel Easy’s VBA (Visual Basic for Applications) guide