Macros in Excel with VBA code examples

*Click images to enlarge

After we introduce the famous Microsoft Application Excel and we mentioned that Macros is a powerful little program that can help you a lot to save time and organize your data, in this post we gonna go deep and explain Macro Excel with some basic examples.

Exactly what’s macros in Excel?

A macro is the diminutive of macro-command. It is a kind of computer program that most often executes actions such as those that the user would perform using the menu and It allows performing several commands automatically at once, without tedious user intervention.

Macros can be used to automate a wide variety of tasks, including tasks that are difficult or impossible to do manually. For example, a macro Excel example can be used to automatically generate reports, format data, and perform calculations.

Macros can be run manually or they can be set to run automatically when certain conditions are met. For example, a macro Excel example can be set to run when a workbook is opened or when a worksheet is activated.

Often macros also contain control instructions that allow, for example, the same action to be performed several times. Those instructions are written in a programming language called Visual Basic for Applications (VBA).Macros can be written by anyone who is familiar with Visual Basic for Applications. There are also many software programs that can be used to create macros.

Why do we need to use macros?

Exactly Macros are basically shortcuts that can be used to automate tasks in Excel. By recording a macro, you can tell Excel to perform a series of steps that you typically do by hand.

For instance, if you find yourself constantly reformatting a particular worksheet, then basically you can record a macro to do it for you. Once you’ve recorded a macro, you can play it back as many times as you want, with just a few clicks.

By using a macro, you can reduce the amount of time you spend performing a task, and you can avoid errors that can occur when a task is performed manually.

And When to use macros?

Using macros in Excel is related to your need , perhaps to automate tasks in a spreadsheet, or to speed up data entry or data analysis.

You can use macros any time you want but in some cases you don’t have another choice to save time and effort you need to use them. Some of those times are:

• If a file becomes too large because of formulas
• Some of the formulas are no longer sufficient and/or become too complex (you can’t figure it out!)
• When you need a more advanced user interface
• Or simply when you like to write macros

Next we gonna illustrate how to record and how to use macros in Excel with examples for all version of Excel.

Create Excel macro with examples

Well when you want to create or use a macro in Excel 2016 or other versions of Excel, you need some basics concept. Here are the most common things you should know:

• The Developer Tab and The Ribbon or the menu bar
• VBA programming language

And also you need to know the difference between recording a macro in Excel and creating or writing a macro:

1. Record Excel macro : the automatic way

To begin, we will perform only simple operations to familiarize you with the macro recorder and starting with some macro Excel example.

1. Open an empty workbook and launch the Macro Recorder:
   • Excel version 2003: menu Tools ➝ Macro ➝ Record New Macro
     

     Macro Excel 2003

   • For Excel 2007 to 2019 and Office 365: menu View ➝ Macros ➝ Record Macro…
     (Or From the menu Developer ➝ Record Macro (Don’t forget to add The Developer Tab to the Ribbon))
     

     Macro Excel 2007 to 2019
2. The Macro Record Window
   

   Macro Record Window

   NB: The macro name must not contain spaces. You can remove them or replace them with allowed characters (the underscore _ for example).
3. Click OK, the recording will start. From this moment on, all your manipulations in Excel are translated and saved.
4. Do some manipulations in the spreadsheet:
   • Click on cell A1 to select it,
   • Enter the text “Hello” in cell A1 and put the cell in Bold,
   • Then cut it (menu Edit / Cut or key combination Ctrl+x),
   • Paste it into cell B1 (Edit / Paste or key combination Ctrl+v).

   That’s a little simple Macro Excel example 🙂 but you can do any instruction you want and it will be recorded.

5. Stop recording Macro
   • Excel 2003: using the menu Tools ➝ Macro ➝ Stop Recording
   • And Excel 2007 to 2019 and Office 365: using the menu View ➝ Macro ➝ Stop Recording or the Developer Tab ➝ Stop Recording
   

   Stop Recording Macro In Excel

Congratulation your first Macros in Excel is created! Now let’s see how to execute it.
There are various ways to access a macro, we have already seen above that it is possible to associate a shortcut to them when recording.
To run the macro in Excel:

• Excel 003: Tools ➝ Macro ➝ Macros…
• Excel 2007 to 2019 and Office 365: menu View ➝ Macros ➝ View Macros or the Developer Tab➝ Macros

You should find it in the list (here with my example under the name “myFirstMacro”).

Select it then click on “Run“.

It is not that complicated right! This is the automatic way to create a macro where you don’t need to write any codes. The interest is now to know how to modify it and create your own macros. So we gonna move to the second method and its little advanced for beginner with Excel.

1. How to write macros in Excel? : the manually way (using VBA programming language)

As we have seen, Excel’s macro recorder tools are very useful for creating macros in Excel 2016 or older versions without special knowledge. But the power of the language used is not limited to the reconstruction of simple repetitive tasks. You will now discover what programming is and all the advantages it can bring to your Excel workbooks with of course some VBA Examples Excel for beginners.

From the previous section its truly that we record a macro but in the real world exactly when we did the record Excel translate our click into VBA Codes and store them using the Visual Basic Editor
You have recorded and executed a macro, now let’s see where it is stored…

• From the window that runs the macro, View ➝ Macros ➝ View Macros you can also delete and edit the Macro selected.
• click the Edit button a new software will be opened it’s the Visual Basic Editor (Visual Basic is the programming language used by Excel for macros.)
• The recorded macro must be stored in Module1.
• Double-click on this module to display its contents and discover what your macro really looks like!

That’s a simple basic VBA Examples for Excel that print a “Hello World” words :).

So to create your own macros in Excel you just need to :

1. open the Visual Basic Editor 
2. Create new module
3. write or paste your VBA Code in the editor
4. Click the button run the menu bar of the Visual Basic Editor

NB: You can access the Visual Basic Editor directly from the developer tab check out how to do this : How to add developer tab in Excel?

And here is some other simple VBA Codes examples that’s very useful when you work with Excel:

[mks_accordion]
[mks_accordion_item title= “1. Except the Active Sheet Hide All Worksheets “]

Sub HideAllNoActiveSheet()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden
Next ws
End Sub

[/mks_accordion_item]

[mks_accordion_item title= “2. Alphabetically Sort Worksheets Using VBA”]

Sub SortSheetsAlpha()
Application.ScreenUpdating = False
Dim ShCount As Integer, i As Integer, j As Integer
ShCount = Sheets.Count
For i = 1 To ShCount - 1
For j = i + 1 To ShCount
If Sheets(j).Name < Sheets(i).Name Then
Sheets(j).Move before:=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub

[/mks_accordion_item]

[mks_accordion_item title= “3. Show All Rows and Columns Even the hidden Ones”]

Sub ShowAllRowsColumns()
Columns.EntireColumn.Hidden = False
Rows.EntireRow.Hidden = False
End Sub

[/mks_accordion_item]

[mks_accordion_item title= “4. Add the TimeStamp to WorkBook Name when Saving”]

Sub SaveTimeStampToWorkbookName()
Dim timestamp As String
timestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss")
ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & timestamp
End Sub

[/mks_accordion_item]

[mks_accordion_item title= “5. Export All Worksheet as a Separate PDF”]

Sub ExportWorksheetAsPDF()
Dim ws As Worksheet
For Each ws In Worksheets
ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf"
Next ws
End Sub

[/mks_accordion_item]

[mks_accordion_item title= “6. Highlight Blank Cells With VBA”]

Sub HighlightBlankCells()
Dim Dataset as Range
Set Dataset = Selection
Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed
End Sub

[/mks_accordion_item]

[/mks_accordion]

Or you can  search and download many others VBA Codes examples and macros with our Excel Tools Search Engine For Free

If you wanna go deep into the Visual Basic programming language and how to create advanced macros in Excel , check out those post that will extend your knowledge about VBA Programming and Macros in Excel:

• Macro Excel Example for beginners
• Advanced VBA Examples Search and download
• How to add developer tab in Excel?
• Excel Add-ins Step by Step Tutorial

Excel Macro not working : The solution is here!

If your Excel macro isn’t working, there could be a few reasons why.

Maybe the macro is outdated and no longer works with the latest version of Excel. Maybe the macro was written for a specific purpose and no longer serves its original purpose.

Maybe the macro is just plain broken.

There are a few things you can do to troubleshoot your Excel macro issue.

First, check to see if the macro is compatible with the version of Excel you’re using.

If it’s not, you may need to update the macro or find a different macro to use.

Next, check to see if the macro is still needed.

If it’s no longer needed, you can delete it.

If it is needed, try to figure out what has changed that is causing the macro to no longer work. This could be a change in data, a change in the structure of your workbook, or something else.

Finally, if you can’t figure out why the macro isn’t working, you can try to repair it. This is usually a last resort, but if you can’t figure out the issue any other way, it may be the only option.

To repair a broken macro, you’ll need to edit the macro code. This can be done in the Visual Basic Editor, which is located in the Developer tab in Excel.

If you’re not sure how to fix your macro, you can always search the internet for help or ask a colleague for assistance. With a little troubleshooting, you should be able to get your Excel macro up and running again in no time.

Conclusion

At the end of this article you should now be familiar with Macros in Excel and how to create , use and run them.

Also you have a good idea about VBA Codes and how to run them in Excel with practice of course and using macros in excel and VBA code examples you will become a genious with Excel Macros :).