Contents
Many people are afraid to use macros in Excel because they find them quite difficult to understand. All because they are related to programming. But in practice, everything turns out to be much simpler than it might seem at first glance. In programming, it is important to learn how to build algorithms. And if a person knows how to code in at least one language, it is much easier for him to learn all the others.
We will give some examples of Excel macros that will become faithful assistants in performing the most common tasks.
Concept of macros
The term “Macro” has been heard by many people. It is not uncommon to see a warning when starting a spreadsheet: “This document uses macros that can harm this computer, so they are disabled to protect against malicious actions.”
Macros are a powerful way to automate the most common actions you need to perform in spreadsheets. Macros are a form of programming. These routines are developed using the VBA language. However, some types of macros do not require programming skills. After all, there is still such a thing as a macro recorder. It is enough to turn it on and perform some actions, as they will then be repeated by pressing one button.
Macros can be really dangerous. Since a programming language is used when writing them, it can be used to create a real virus that can damage information, as well as collect data for intruders (it is especially dangerous if the table contains banking data, passwords, and so on).
Also, the macro can run a real Trojan on the computer. Therefore, in order to prevent malicious actions from a third-party macro, you should not run macros from third-party sources that are not trusted.
It is much easier to explain why macros are needed with a real example. For example, you might want to delete a few columns from a spreadsheet every day and then add new rows. This is an incredibly tedious and time-consuming task. If you use macros, there is a real opportunity to significantly save it.
Macros can be run by pressing a specific key combination. For example, if you press Ctrl+J, you can run a subroutine.
An interesting fact: the well-known 1C accounting program initially looked very much like Excel, but then its functionality expanded to the current one.
If you need to give the computer complex instructions, you can use the Visual Basic editor, in which we will consider code examples a little later.
When to use which type of macro recording?
If you need to automate the simplest actions, it is enough to use the built-in macro recording tool. That is, if you do not have to prescribe any conditions, variables and other similar things. Just the usual sequence of actions.
If you need to program complex actions, then you will have to use the built-in VBA environment. For example, if you need to write all the elements of a certain range of values into an array, determine its duration, and, provided that the number of array elements does not exceed a certain number, issue some kind of message. Here, a standard tool for recording macros will not be enough, you need to learn a programming language and record commands in a special environment. And the interpreter will continue to execute the written code.
Macro example #1
This code sample was first used to show comments in code written in VBA. But since it also includes other features of the language, it can be used to demonstrate the following features:
- Declaring variables.
- Specifying links to Excel cells.
- Using a For Loop.
- Applying a conditional operator.
- Display alert.
‘ Subroutine to search for cells with addresses A1-A100 of the current active sheet
‘ and search for cells that contain the desired string
Sub Find_String(sFindText As String)
Dim i As Integer ‘ An integer variable that is used in a “For” loop
Dim iRowNumber As Integer ‘ Integer variable to store the result
iRowNumber = 0
‘ Loop through cells A1-A100 until the string ‘sFindText’ is found
For i = 1 To 100
If Cells(i, 1).Value = sFindText Then
‘ Match found for given string
‘ Save the current line number and exit the loop
iRowNumber = i
Exit For
End If
Next i
‘ A popup message informing the user of the line found and its number
If iRowNumber = 0 Then
MsgBox «String » & sFindText & » not found»
else
MsgBox «String » & sFindText & » found in cell A» & iRowNumber
End If
End Sub
Example 2
This procedure lists all the values of the Fibonacci number sequence up to 1000. This example shows the following Excel macro features:
- Declaring variables.
- Do While loop.
- Links to cells in the current Excel sheet.
- Conditional operator.
‘ Routine to enumerate all values of the Fibonacci sequence for all values below a thousand
Sub Fibonacci()
Dim i As Integer ‘ counter for a position in a series of values
Dim iFib As Integer ‘ stores current value in series
Dim iFib_Next As Integer ‘ saves the next value in the series
Dim iStep As Integer ‘ stores the size of the next step
‘ Initialize variables i and iFib_Next
i = 1
iFib_Next = 0
‘ A Do While loop that runs as long as the number
‘ Numbers in the Fibonacci sequence are less than 1000.
Do While iFib_Next < 1000
If i = 1 Then
‘Special occasion for the first entry in the series
iStep = 1
iFib = 0
else
‘ Save the next step size before overwriting
‘ of the current entry in the series
iStep = iFib
iFib = iFib_Next
End If
‘ Print the current value of the Fibonacci sequence for column A
‘ current sheet
Cells(i, 1).Value = iFib
‘ Calculate the next value in the sequence and increment
‘ position marker by 1
iFib_Next = iFib + iStep
i = i + 1
loop
End Sub
Example 3
The following subroutine example reads values from a cell in column A of the active sheet until it finds an empty cell. All received information is stored in an array. This is a simple example of macros in spreadsheets that shows:
- How to declare variables.
- The work of a dynamic array.
- Cycle Do Until.
- Links to cells in the current Excel sheet.
- The built-in function Ubound, which is designed to determine the size of an array.
‘ Subroutine that stores the values of column A of the current sheet
‘ in array
Sub GetCellValues()
Dim iRow As Integer ‘ saves the current row number
Dim dCellValues() As Double ‘ an array that stores cell values
iRow = 1
ReDim dCellValues(1 To 10)
‘ A Do Until loop that retrieves the value of each cell in column A
‘ the active sheet until the cell is empty
Do Until IsEmpty(Cells(iRow, 1))
‘ Check if the dCellValues array is large enough
‘ If not, use ReDim to increase the size of the array by 10 elements.
If UBound(dCellValues) < iRow Then
ReDim Preserve dCellValues(1 To iRow + 9)
End If
‘ The current cell is stored in the CellValues array
dCellValues(iRow) = Cells(iRow, 1).Value
iRow = iRow + 1
loop
End Sub
Example 4
The following procedure “Sub” reads the contents of the cells from column A of another sheet called “Sheet2” and performs arithmetic operations with these values. The result of the calculation is written in column A of the current sheet.
This example shows:
- How to declare variables.
- Excel objects.
- Cycle Do Until.
- Access spreadsheet sheets and cell ranges from the current workbook.
‘ Subroutine that loops through the values in column A of the current sheet
‘ «Sheet2», perform arithmetic operations on each value, and write the
‘ result into Column A of the current Active Worksheet («Sheet1»)
Sub Transfer_ColA()
Dim i As Integer
Dim Col As Range
Dim dVal As Double
‘ Set variable Col to column A of sheet 2
Set Col = Sheets(«Sheet2»).Columns(«A»)
i = 1
‘ Loop through each cell in the ‘Col’ column until
‘ will not find an empty cell
Do Until IsEmpty(Col.Cells(i))
‘ Applying arithmetic operations to the value of the current cell
dVal = Col.Cells(i).Value * 3 — 1
‘ The command below copies the result to column A
‘ of the current active sheet – without specifying the name of the active sheet
Cells(i, 1) = dVal
i = i + 1
loop
End Sub
Example 5
This macro example provides an example of the VBA code associated with the event. Each time a person selects a cell or range of values, the event associated with the macro is fired.
‘ Code for displaying the dialog box of cell B1 of the current sheet in case it is selected
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
‘ Check if cell B1 is selected
If Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Then
‘ When cell B1 is selected, show the dialog box
MsgBox «You have selected cell B1»
End If
End Sub
Example 6
The following subroutine demonstrates how to handle errors using the statements OnError и Resume. This code also describes how to open and read data from a file.
‘ Subroutine, for setting certain values
‘ in cells A1 and B1 of the “Data.xls” document on drive C:
Sub Set_Values(Val1 As Double, Val2 As Double)
Dim DataWorkbook As Workbook
On Error GoTo ErrorHandling
‘ Opening a document with data
Set DataWorkbook = Workbooks.Open(«C:Documents and SettingsData»)
‘ Select variables Val1 and Val2 from data in Excel workbook
Val1 = Sheets(«Sheet1»).Cells(1, 1)
Val2 = Sheets(«Sheet1»).Cells(1, 2)
DataWorkbook.Close
Exit Sub
ErrorHandling:
‘ If the file is not found, prompt the user to find the correct directory
‘ then continue the execution of the subroutine
MsgBox «Data Workbook not found;» & _
«Please add the workbook to C:Documents and Settings and click OK»
DESCRIPTION
End Sub
After a detailed acquaintance with these examples, it will be much easier to apply your skills in practice.
Recommendations for using macros
There are several recommendations that can significantly increase the efficiency of using macros in spreadsheets:
- Before recording a macro using a recorder, you should think over all your actions in advance, since all actions (including erroneous ones) will be automated.
- Take your time, because pauses are not taken into account when recording macros. It is quite possible to start thinking through some actions along the way. And all recorded operations will be processed at one moment.
- Be sure to learn how to use macro debug mode. If there are any errors, it will help to find out what is the cause of the problem. At first, you cannot do without errors, because in a real program everything will not be as perfect as it might seem at first glance.
- Before using macros made by other people, you need to configure your antivirus program to detect them. Typically, this option is set by default.
- If documents are downloaded from dubious sources, you should select the “Disable macros” option when opening them. And it is not recommended to change the settings that are set by default in the Excel security settings.
Conclusions
Thus, macros are an effective tool for automating workflows in Excel. It allows you to automate even the most complex sequences of actions. If you need to make a simple program, then it is enough to use the built-in function for recording macros. For more complex ones, you need to master the VBA language, which is easy to learn and very flexible.
If third-party macros are used, be sure to take care of the safety of their use.