Programming Macros in Excel: The Complete Step-by-Step Guide

Informatec Digital » Programme » Programming Macros in Excel: The Complete Step-by-Step Guide

If you repeat tasks in Excel over and over again, macros are your best ally to stop wasting time. With them, you can automate complete sequences of actions (learn more about VBA programming), from formatting to importing data or running calculations, and trigger them whenever you want with a click or a keyboard shortcut.

When you create a macro, Excel records your clicks and keystrokes and executes them faithfully. You can then tweak the code to fine-tune the execution and consult resources for ExcelIt is common to start with the recorder and then adjust with VBA, getting more control, flexibility and zero errors due to carelessness.

What exactly is a macro in Excel?

A macro is a list of instructions that Excel executes in the defined order. It can be as simple as applying a percentage style or as complex as prepare a report with pivot tables, format it, create spreadsheets, and send an email in Outlook. Macros are saved in your workbook, and you can run them whenever you need.

You don't need to know how to program to get started: the recorder translates your actions into VBA code. However, knowing some VBA will allow you to Create custom functions, forms, and even applets within Excel.

Why use macros? Real advantages

The main purpose of a macro is avoid repetitive work and minimize errorsIf you prepare the same report every month, a macro saves you the mechanical steps and lets you focus on what's important.

Among its benefits are time savings, consistent execution (without forgetting), and the possibility of creating shortcuts and buttons to speed up processesIf you use Excel occasionally, it might not be worth it; but if you use it daily, you'll notice the difference from day one.

Enable the Developer tab (Windows and Mac)

The Developer ribbon is hidden by default. In Windows, right-click the ribbon and select "Customize the Ribbon." Select "Developer" and accept. After that, You will see the Developer tab next to Vista.

On a Mac, go to Excel > Preferences > Toolbars & Ribbon. Under "Main Tabs," select "Developer" and save. This will give you access to the toolbar. the recorder, access to VBA and controls. If you are just starting out, check out the basic Excel handling to guide you with the ribbon and options.

How to create macros with the Recorder

The recorder is a great place to start. Under Programmer > Record Macro (or Alt+T+M+R on Windows), assign a descriptive name. Remember: the name must begin with a letter. without spaces or symbols.

Choose where to save: "This workbook" will work in most cases. If you want to always have it available, select "Personal Macro Workbook" and Excel will create/save it to Personal.xlsb, which opens hidden with Excel.

Optionally add a shortcut. To avoid conflicts (e.g. losing Ctrl+Z), it is recommended to use combinations with Ctrl+Shift. You can also type a shortcut. short and clear description (very useful when you have a lot of macros).

Click OK and perform the actions you want to record. For example, select A1 and apply the "Percentage" format. Avoid unnecessary clicks or selection changes that aren't part of the intended process, because the recorder records almost everything.

When you're done, stop recording. To run it, go to Developer > Macros (or Alt+F8), choose your macro, and press Run. You'll see how Excel repeat the steps in exactly the same order.

Run, edit, and manage your macros

From Programmer > Macros (Alt+F8) you can run, edit, or delete macros. If you choose Edit, the Visual Basic Editor will open with the recorded code, which you can edit. clean and optimize (usually includes redundant steps).

Assigning a macro to a button or shape is very convenient: insert a shape, right-click > Assign Macro, choose the macro, and that's it. When you click the shape, will be executed instantly.

To always have it handy in the Quick Access Toolbar, open the bar menu (down arrow) > More Commands > under "Commands available in", choose "Macros", add yours and change the icon if you want. Now you'll have a permanent button even when you're not in Programmer.

If you need to reuse the macro in another workbook, you can copy the module from the Visual Basic Editor. Open VBE (Alt+F11), drag the module to another open workbook, or use Export/Import .bas file.

Programming macros in Excel with the Visual Basic (VBA) Editor

To go beyond the recorder, open the Editor with Alt+F11. Insert a Module (Insert > Module) and create your first subroutine. The basic pattern is Sub Name() … End Sub.

For example, to display a message and write a value to A1, you could start with something simple. This way, you understand the structure and how to call the macro from Excel (Alt+F8) or assign it to a button, with very few lines:

Sub Primera_Macro()
    Range("A1").Value = "Esto de las macros mola."
    MsgBox "Hola, querido amigo."
End Sub

Another quick example to apply the percentage style to the current selection. This macro replicate what you did with the recorder, but written directly:

Sub FormatoPorcentaje()
    Selection.Style = "Percent"
End Sub

The ideal combination is: record to speed things up, then open the VBE to clean up unnecessary steps, add variables, conditions, or loops. This way, you turn a "verbatim" recording into a macro. robust and reusable.

Absolute vs. relative references

By default, the recorder creates macros in absolute mode: it always acts on the same cells (e.g., B3) regardless of where you are when executing. If the data changes position in your process, this can not being what you need.

With "Use relative references," the macro works relative to the active cell when it starts running. For example, if you started recording in B3 and typed "First Name," "Last Name," etc., moving to the right, when you run from C4, it will write it from C4, D4, E4… relatively.

A typical example: titling a table that sometimes starts in B3 and sometimes in C5. With absolute mode, you would always write in B3. With relative mode, it will adapt to the selected start cell.

Common mistakes when recording processes

A common pitfall is recording a formula drag for, say, three rows, and thinking it will work for longer tables. The recorder will record something equivalent to "fill three cells down", not "to the end of the table."

The solution is to change the approach: use dynamic ranges (e.g., detect last row), loops, or the CurrentRegion method, or adjust the macro in VBA after recording it so that scales to any size.

Good practices when recording

Before recording, perform the manual process a couple of times to polish it. Make recordings short and specific instead of one huge one; this makes them easier to maintain and combine.

Avoid unnecessary clicks, unnecessary scrolling, and inconsequential selections. Keep the macro name and description clear so you or any teammates can you identify its purpose instantly.

Run macros in many ways

In addition to the Macros box (Alt+F8), you can launch macros with a shape, a button on the Quick Access Toolbar, or when you open the workbook. You can even have Excel run the macro from other Office apps, allowing you to automate multi-application tasks (for example, updating a table and sending an email via Outlook).

Think carefully about keyboard shortcuts: if you assign Ctrl+Z to a macro, you will lose the ability to Undo while the workbook is open. That's why it's suggested to use combinations with Ctrl + Shift to avoid conflicts.

Saving workbooks with macros

When a file contains VBA, save it as .xlsm so the code is preserved and can be executed. If you save it as .xlsx, you will lose the VBA projectIf you want global macros, use the Personal Macro Workbook (Personal.xlsb).

Security and enabling macros

Macros may be blocked depending on your security settings. Check the Trust Center to selectively enable them and never activate macros from unknown sources. Knowing your security level helps you balancing protection and productivity.

Assign macros to buttons, shapes, and controls

To have a user execute them without thinking, assign a macro to a button, shape, or control. Right-click on the object > Assign Macro > select. You can also place an icon on the ribbon or quick access bar and link it to the macro.

If you use forms or ActiveX controls, you can assign macros or event procedures to them (for example, on click). This allows more user-friendly interfaces and guided processes.

Useful macro examples (VBA)

These examples illustrate common tasks. You can paste them into a module (Alt+F11 > Insert > Module) and customize them. Remember to sign the books or adjust security if your organization requires it. be able to run them without warnings.

Copy the selected content to another location on the same sheet: Selection.Copy Destination

Sub CopiarSeleccion()
    If TypeName(Selection) = "Range" Then
        Selection.Copy Destination:=Selection.Offset(0, 2)
    Else
        MsgBox "Selecciona un rango de celdas primero."
    End If
End Sub

Print the active sheet: ActiveSheet.PrintOut

Sub ImprimirHojaActual()
    ActiveSheet.PrintOut
End Sub

Save the file as a macro-enabled workbook (.xlsm): ActiveWorkbook.SaveAs

Sub GuardarComoXlsm()
    Dim ruta As String
    ruta = ThisWorkbook.Path & Application.PathSeparator & "LibroConMacros.xlsm"
    ActiveWorkbook.SaveAs Filename:=ruta, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

Format a range (adjust the range to your needs): With Range(…)

Sub FormatoRango()
    With Range("A1:D10")
        .Font.Bold = True
        .Interior.Color = RGB(230, 230, 250)
        .Borders.LineStyle = xlContinuous
    End With
End Sub

Find a value in a range and display the first match: Range(…).Find

Sub BuscarValor()
    Dim celda As Range
    Dim valor As String
    valor = InputBox("¿Qué valor quieres buscar?")
    If valor = "" Then Exit Sub
    Set celda = Range("A1:D1000").Find(What:=valor, LookIn:=xlValues, LookAt:=xlPart)
    If Not celda Is Nothing Then
        MsgBox "Encontrado en: " & celda.Address
        celda.Select
    Else
        MsgBox "No se encontró el valor especificado."
    End If
End Sub

Practical case: formatting as a percentage

Recording: Start the recording, name it, choose where to save it, and optionally assign a shortcut and description. Select the cells and apply "Percentage" from the Start menu. Stop the recording and then start it with Alt+F8. If you want to play it with minimalistic code, use Selection.Style = "Percent" in VBA.

Tip: Do not change cells without reason during recording. These comings and goings are recorded and can be slow down and dirty the macro.

Automate long tasks: small macros are better

If you have a process with many steps, consider breaking it down into several smaller macros and calling them in a chain. This makes maintenance easier, allows you to reuse blocks and simplifies debugging.

For example, one macro to prepare data, another to format, another to generate a print sheet and another to export to PDF or send by e-mailYou can call them one by one or all at once.

Edit and clean up the recorded code

The recorder captures "everything": selections, clicks, and tab changes. When you open the VBE, it removes unnecessary selections (Select/Selection) and replaces them with direct references (e.g., Range("A1").Value = …) and groups formats into With/End With blocks.

It also adds variables and basic error handling. A couple of If statements and simple checks prevent errors when the range is empty or the selection is not what was expected.

FAQs

How do I create a form (UserForm) in Excel? Open the Visual Basic Editor (Alt+F11), go to Insert > UserForm, and add controls (text boxes, buttons, etc.). You can display them with a macro:

Sub MostrarFormulario()
    UserForm1.Show
End Sub

From there, assign procedures to the events of the controls (for example, Click of a button) so that execute the logic you need.

How do I protect/unprotect a sheet with macros? Use these routines to change your password to your liking. They allow you to activate or remove protection with one click:

Sub ProtegerHoja()
    ActiveSheet.Protect Password:="segura", AllowFiltering:=True
End Sub

Sub DesprotegerHoja()
    ActiveSheet.Unprotect Password:="segura"
End Sub

How do I delete a macro? Go to Programmer > Macros, select the macro, and press Delete. If you want to delete all the code in a module, open the VBE (Alt+F11), locate the module in the project, right-click, and then select Delete. Remove Module (you can export it first if you want to save it).

One last practical note: if you format a monthly report where clients with debt should be in red and bold, record the sequence once, adjust the VBA code to apply it to the correct range (using last row detection or filters), and save the macro to your personal macro workbook. Next time, a shortcut or button will be enough to apply the formatting instantly. zero failures and in seconds.