Tutorial with Excel examples about Macros

Many people think that it is extremely hard to learn Excel Macros, but it is not. This article contains some nice and simple Examples to automate your daily tasks in Excel.

A Macro is a program in Visual Basic used to automate Office Tasks. In this sample I am using Excel 2010, but you can use 2007 version as well.

Create Macros using Record Macro Button

  1. To begin with click the View tab on the Ribbon. Choose the Record Macro from the Macros drop-down list:
    Choose the Record Macro from the Macros drop-down list
    You will get the Record Macro window.
    Excel Record Macro window
  2. Write the Macro name (it does not accept spaces or special characters), the shortcut key and select where you want to store your macro in in the corresponding field. Optionally you can write the description.
  3. Now, the macro is recording an action. For example you can write the value Hello in the A1 cell.
  4. Then go to the Macros icon and select the Stop Recording option.
    Go to the Macros icon and select the Stop Recording option

The macros you save are stored on the Macros button. When you click on it you will see the Macro window where you can select the one you need. Double click on the name to execute a command.

You can also associate a Macro with a button:

Click File -> Options -> Quick Access Toolbar category, select All Commands in the Choose commands from list, select Option Button (Form Control) and Click Add.

And assign a Macro to the button:

Click Option Button on the Quick Access Toolbar, and then draw the outline of the button on the sheet, select any cell in the worksheet. In versions of Excel that are for Microsoft Windows, right-click the option button, and then click Assign Macro.

For clauses

The next example shows how to work with the For clauses. The For clauses let us work with multiple values inside a loop. Let's see how you can get numbers from 1 to 5 in the cell A1 until the cell A5.

To do this go to the Developer tab -> Visual Basic. Double click on the Excel Object where you need the Macro to see the Microsoft Visual Basic for applications window. Enter the following:

Sub Macro1 ()
			For n = 1 To 5
					Cells(n, 1) = n
			Next n
	End Sub

Click to save the file. To execute the macro you can go to View -> Macros -> select the name with the one you need from the list and click Run.

The following code sends the message Hello World in a windows message box:

Sub MacroName()
		 MsgBox ("Hello World!")
	End Sub

The following example is a yes/no message box. If you answer yes, the cell value is deleted.

Sub MacroName()
		 Dim Answer As String
	 
			Answer = MsgBox("Are you sure you want to delete the cell values ?", 
	 vbQuestion + vbYesNo, "Delete cell")
	 
			If Answer = vbYes Then
					ActiveCell.ClearContents
	End If
	End Sub

Let's verify the code. Select a cell and execute the macro with the code. You will receive the following message:

Run the macro to see this message

If you press yes, the selected cell values will be deleted. Otherwise, the values will remain.

The If sentence

You can also use the If sentence in Excel. In this code we are going to paint the cells according to the values. If the values are higher than 20 the value is red, otherwise, it is blue.

Sub MacroName()
		 Dim CellValue As Integer
	CellValue = ActiveCell.Value
	If CellValue > 20 Then
	 With Selection.Font
					.Color = -16776961
			End With
	Else
			With Selection.Font
					.ThemeColor = xlThemeColorLight2 '
					.TintAndShade = 0
			End With
	End If
	End Sub

To test this code, let's select a cell with a value higher than 20:

Select a cell with a value higher than 20

When you execute the Macro it is painted red:

Execute the Macro and the value will be painted red

Otherwise, it is painted blue:

Run the Macro to see the value painted blue

The sentence case

You can also use the sentence case in order to do actions according to specific values. The following example shows a message according to the people's age.

Sub MacroName()
		 Dim CellValue As Integer
	CellValue = ActiveCell.Value
	 
	Select Case CellValue
			 Case 60 To 200
	 
					 MsgBox "The person is old"
			Case 30 To 59
	 
					MsgBox "The person is adult"
	 
			Case 18 To 29
				 MsgBox "The person is young"
	 
			Case 0 To 17
			MsgBox "The person is a child"
	 
			Case Else
			MsgBox "Unknown age"
	 
	End Select
	End Sub

In order to test this example you just need to select a cell value and run the macro. For example, if the selected cell value is 44, the message displayed would be the following:

Do actions according to specific values

Conclusion

Macros are a great way to automate the Excel tasks. This article just shows some few examples of what you can do with it.

See also