Excel Macros and VBA Excel Macros and VBA / 01

40+ Most Used Macros for Daily Use

What does "Macro Code" mean?
A macro is a piece of code that automates tasks in Excel. In simpler terms, it's like a set of
instructions the computer can follow to perform repetitive tasks, saving your time and effort.
In Excel, macros are written in VBA (Visual Basic for Applications), a programming language
designed for automating tasks within Microsoft Office applications.
Instead of picking the range > File Tab > Print > Print Select > OK Button, you might use
a code to print just a certain range of cells with a single click.
What is VBA, VB, VBA?
VBA (Visual Basic for Applications): VBA (Visual Basics for Application) is the programming
language used to create macros in Excel. It is a subset of the Visual Basic (VB) programming
language and is integrated into Microsoft Office applications. With VBA, you can interact
with Excel's objects, such as worksheets, ranges, charts, and pivot tables, to automate
processes and add custom functionalities to your workbooks.
VB (Visual Basic): VB is a general-purpose programming language, and it is used to create
tandalone applications that can run independently on a computer. Unlike VBA, VB is not
integrated into Microsoft Office applications nor designed for automation within Excel or
other Office programs.
VBS (Visual Basic Script): VBS is a variant of the Visual Basic programming language, but
it is specifically designed for internet applications and can be used for client-side scripting
within web pages. VBS files have the extension ".vbs" and can be executed on Windows
machines without needing a compiler.

Why need Macros?

Save Time: One of the main reasons for using macros is to save time. With macros, you
can automate repetitive tasks, reducing the need for manual intervention. Tasks that usually
take minutes or even hours to complete can be accomplished in a matter of seconds with
the help of a well-written macro.
Repetitive Tasks: Many tasks in Excel involve performing the same actions repeatedly on
different datasets. Macros can be used to standardize and automate these processes,
ensuring consistency and accuracy in data manipulation.
Develop New Formulas: Macros provide the flexibility to create custom functions and
formulas that may not be available in Excel by default. You can build complex calculations
tailored to your specific needs, empowering you to solve unique problems efficiently.
Complex Tasks: Excel macros are excellent for handling complex tasks involving multiple
steps and interactions between worksheets or workbooks. By splitting complex tasks into
smaller, manageable pieces, macros can simplify otherwise daunting processes.

How do I utilise an Excel macro code?

Enable the Developer Tab: Before using macros, you need to make sure the Developer tab
is visible on your Excel ribbon. To enable, go to "File" > "Options" > "Customize Ribbon."
In the right column, check the box next to "Developer" and click "OK."
Open the Visual Basic for Applications (VBA) Editor: Once the Developer tab is visible, click
on it, and then click on "Visual Basic" to open the VBA Editor. Alternatively, you can also use
the shortcut "ALT + F11" to access the VBA Editor.
 Excel Macros and VBA / 02

Create a New Macro: In the VBA Editor, click on "Insert" in the menu and choose "Module."
It will create a new module where you can write your macro code.

Write the Macro Code: Now, you can start writing your macro code in the empty module.
The code should be written in VBA. If you are new to VBA, don't worry; it follows a
straightforward syntax.

Save the Macro: After writing the macro code, close the VBA Editor and save your
workbook as a macro-enabled workbook with the extension ".xlsm." This file format
allows macros to be saved within the workbook.

Run the Macro: To run the macro, go back to the worksheet where you want the macro to
take effect. Press "ALT + F8" to open the "Macro" dialog box. Select the macro you
created and click "Run."
 Excel Macros and VBA / 03

Assign a Shortcut (Optional): If you plan to use the macro frequently, you can assign a
keyboard shortcut for quick access. To do this, go back to the VBA Editor, select the
macro from the left pane, and click on "Tools" > "Macro" > "Options." Here, you
can assign a shortcut key.

Best practices for writing excel macros code:

When writing Excel macros, it's essential to follow best practices to ensure that the
code is efficient, maintainable, and error-free:
Use Explicit Variable Declaration: Always declare your variables explicitly using the
"Dim" statement. This helps prevent variable scope issues and enhances code readability.

Avoid Select and Activate: Refrain from using the "Select" and "Activate" methods as
much as possible. Instead, work directly with objects and ranges in VBA without selecting
them first. This speeds up the code execution and reduces the risk of errors.

Error Handling: Implement error-handling routines to deal with unexpected situations

gracefully. Use "On Error" statements to catch and manage errors appropriately.

Optimize Loops and Code: If your macro involves loops or extensive calculations,
optimize the code to run efficiently. For instance, consider turning off screen updating
and calculation during the macro execution.

Comment Your Code: Add comments to your code to explain the purpose of each
section and any complex logic. This will help you and other users understand the code
better when reviewing or modifying it later.

Test Thoroughly: Before deploying your macro, test it thoroughly on sample data to
ensure it performs as expected and doesn't produce any errors.

Backup Your Work: Always make a backup copy of your workbook before running a
new macro, especially if the macro performs significant changes to the data. By adhering
to these best practices, you can create macros that are robust, reliable, and easy to
maintain over time.

Documenting Your Code:

Good code documentation is crucial for understanding and maintaining your macros.
Use comments (indicated by a single quote ') to explain the purpose of your code.
Add a header comment with the macro's name, author, date, and a brief description.
Describe the logic behind complex sections of your code.
 Excel Macros and VBA / 04

Testing and Debugging:

Always test your macros on sample data to ensure they work as intended.
Use breakpoints and debugging tools in the VBA editor to identify and fix errors.
Debug. Print statements can be helpful for tracking variable values during execution.

Terms –VBA Excel

You will become familiar with the terms used frequently in Excel VBA.
Understanding each of these terms is crucial because they will all be utilized in
subsequent modules.

Modules:
The place where the code is written is in modules. There are no Modules because
this is a brand-new Workbook.
Navigate to Insert -> Module to insert a module. 'module1' is formed once a
module is inserted.
We can write VBA code inside the modules, and the code is written inside a
Procedure. A procedure or subroutine is a collection of VBA statements that tells
the programmed what to do.

Procedure
Procedures are collections of statements that Excel is told to execute collectively in
order to carry out a certain task. The activity completed may be relatively
straightforward or extremely challenging.
However, it is a good idea to divide difficult processes into simpler ones.
The two primary categories of Procedures are Function and Sub.

Function
Anywhere in your application, you can invoke a function, which is a collection of
reusable code. This avoids the need to repeatedly write the same code. This aids
programmers in breaking down a large programme into several small, manageable
functions.
VBA allows users to create custom functions in addition to built-in ones, and
statements are placed between Function and End Function.
 Excel Macros and VBA / 05

Sub-procedures
Functions and sub-procedures function similarly. Functions may or may not return a
value, but sub procedures DO NOT. It IS POSSIBLE to call sub-procedures without
the call keyword.
Sub and End Sub statements are required to surround sub processes.

Variables- VBA
In VBA, a variable serves as a named memory location that allows for the storage
of data that may change during the script's execution. To adhere to the correct naming
conventions for variables, it is essential to follow these fundamental guidelines:
The variable name must always begin with a letter.
Avoid using special characters such as spaces, periods, and exclamation points, as
well as symbols like @, &, $, or # in the variable's name.
Ensure that the length of the variable's name does not exceed 255 characters.
It is crucial to refrain from using reserved keywords specific to Visual Basic as variable
names.

Syntax:
Syntax in VBA, you need to declare the variables before using them.
Dim <variable_name> As <variable_type>>

Data Types
Numerous VBA data types can be categorised into two basic groups: numeric data
types and non-numeric data types.
Types of Numeric Data
The numeric data types and the permitted range of values are shown in the following table.
 Excel Macros and VBA / 06

Constant VBA
In VBA, a constant serves as a named memory location that stores a fixed value,
maintaining its unchangeable nature throughout the script's execution. Unlike variables,
constants retain their assigned value and cannot be altered during the program's
runtime. Any attempt to modify a constant's value results in an error, leading to the
termination of the script's execution. To declare a constant, the same syntax used for
variables is employed.
To ensure adherence to the naming conventions for constants, consider the
following guidelines:
The constant's name must commence with a letter.
Special characters, including spaces, periods (.), exclamation marks (!),
as well as @, &, $, #, are strictly prohibited in the constant's name.
The length of the constant's name should not exceed 255 characters.
To prevent conflicts with language-specific commands, it is crucial to avoid utilizing
reserved keywords in Visual Basic as constant names.
Operator –VBA excel
A straightforward formula can be used to define an operator: 4 + 5 equals 9. In this
case, the operands are 4 and 5, and the operator is +. The following operator types
are supported by VBA:
Arithmetic Operators
Comparison Operators
Logical (or Relational) Operators
Concatenation Operators

Input-Box VBA Excel

The InputBox function in VBA is a powerful tool that allows developers to interact
with users through a dialog box to receive input. This feature enables users to provide
information that can be utilized in various scenarios within the VBA code. The InputBox
function is highly customizable and offers several parameters to modify its behavior
and appearance.

Syntax:
InputBox(Prompt, [Title], [Default], [XPos], [YPos], [HelpFile], [Context])
Parameters:
Prompt: This is a mandatory parameter representing the message displayed in the
input box. It provides guidance to the user regarding the expected input. The prompt
can be up to 1024 characters long and can span multiple lines using the carriage return
character (Chr(13)) or linefeed character (Chr(10)).
Title (Optional): You can optionally specify a custom title for the input box dialog. If left
empty, the default title will display the application's name. The title should be a string
expression.
Default (Optional): This parameter allows you to suggest a default value that appears
in the input box, providing a user-friendly hint about the expected input format. The
default can be a string expression.
XPos (Optional): Represents the horizontal position of the input box relative to the left side
of the screen. If not specified, the input box will be horizontally centered. XPos is a
numeric expression.
YPos (Optional): Indicates the vertical position of the input box relative to the top of the
screen. If not specified, the input box will be vertically centered. YPos is also a numeric
expression.
 Excel Macros and VBA / 07

HelpFile (Optional): If you have context-sensitive help related to the input box, you can
provide the HelpFile parameter, specifying the help file to be used. HelpFile should be
a string expression.
Context (Optional): When providing the HelpFile parameter, you can also specify the
Context parameter, which represents the help context number relevant to the specific
input box. Context is a numeric expression.

Example:

How to record Macros in excel?

Discover how to create an Excel macro.

Let's create a very basic macro that simply picks a cell and enters the word "Excel" there.
While recording this macro, I am using the text "Excel," but feel free to substitute your
name or any other text you choose.

The steps to record this macro are as follows:

 Excel Macros and VBA / 08

On the Developer tab, click.

Select the Macro button in the Code group. The 'Record Macro' dialogue box will
then be displayed. the ribbon's Developer tab's Record Macro button
Give your macro a name in the Record Macro dialogue box. EnterText is the name I'm
using. When naming a macro, there are a few rules that you must abide by. You cannot,
for instance: you can not use spaces in between. I usually prefer to keep my macro
names as a single word, with different parts with a capitalized first alphabet. You can
also use underscore to separate two words – such as Enter_Text.

Simple Code: Create message box with message learn excel

1. Autofit Columns and Rows:

This macro will automatically adjust the width of all columns and the height of all rows
in the active worksheet to fit the content.

VBA Code:

Step-by-step:
Press ALT + F11 to open the VBA editor.
Click Insert > Module to add a new module.
Copy and paste the code into the module.
Close the VBA editor.
Press ALT + F8 to open the macro dialog box.
Select the "AutofitColumnsAndRows" macro from the list and click "Run."

2. Remove Duplicates:
This macro will remove duplicate values from the selected range in the active worksheet.
VBA Code:
 Excel Macros and VBA / 09

Step-by-step:
Open the VBA editor and add a new module.
Copy and paste the code into the module.
Close the VBA editor.
Select the range containing the data from which you want to remove duplicates.
Press ALT + F8 to open the macro dialog box.
Select the "RemoveDuplicates" macro from the list and click "Run."

3. Copy Data to Another Sheet

Task: Copy data from one sheet to another in the same workbook.
Open Microsoft Excel and press "Alt + F11" to open the VBA editor.
Click "Insert" in the menu and select "Module" to add a new module.

Write the following VBA code:

Close the VBA editor and go back to the Excel worksheet.

Press "Alt + F8" to open the "Macro" dialog box.
Select "CopyData" from the list and click "Run" to execute the macro.
The macro will copy the data from "SourceSheet" to "TargetSheet" based on
the specified range.
 Excel Macros and VBA / 10

4. Find and Replace

Task: Find specific text in a worksheet and replace it with new text.
Open Microsoft Excel and press "Alt + F11" to open the VBA editor.
Click "Insert" in the menu and select "Module" to add a new module.

Write the following VBA code:

Close the VBA editor and go back to the Excel worksheet.
Press "Alt + F8" to open the "Macro" dialog box.
Select "CopyData" from the list and click "Run" to execute the macro.
The macro will copy the data from "SourceSheet" to "TargetSheet" based on
the specified range.

Close the VBA editor and go back to the Excel worksheet.

Press "Alt + F8" to open the "Macro" dialog box.
Select "FindAndReplace" from the list and click "Run" to execute the macro.
The macro will find all occurrences of "old_text" in "Sheet1" and replace them
with "new_text."
5. Sort Data as per criteria:
Task: Sort data as per criteria.
Open Microsoft Excel and press "Alt + F11" to open the VBA editor.
Click "Insert" in the menu and select "Module" to add a new module.
 Excel Macros and VBA / 11

Close the VBA editor and go back to the Excel worksheet.

Press "Alt + F8" to open the "Macro" dialog box.
Select " FilterByProduct " from the list and click "Run" to execute the macro.
The macro will sort data as per product criteria.
Sample data:

Summarize sales data and create a pivot table.

Code:

Dim pivotCache As PivotCache

Dim pivotTable As PivotTable

How to Use:
Make sure that the source data is in "Sheet1," starting from cell A1 (as shown in
the sample dataset).
Run the "SummarizeDataAndCreatePivotTable" macro by pressing Alt + F8,
selecting the macro, and clicking "Run."
The macro will summarize the sales data and create a pivot table in "Sheet1."
 Excel Macros and VBA / 12

7. Macro to Filter Data Based on Criteria:

Filter the data based on specific criteria.

How to Use:
Make sure that the source data is in "Sheet1," starting from cell A1 (as shown in
the sample dataset).
Run the "FilterData" macro by pressing Alt + F8, selecting the macro, and clicking "Run."
The macro will apply an AutoFilter to the data in "Sheet1" and filter the data to
show only employees from the HR department.

8. Macro to Copy Filtered Data to Another Sheet:

Filter data based on a specific criteria and copy the filtered data to another sheet.
 Excel Macros and VBA / 13

How to Use:
Make sure that the source data is in "Sheet1," starting from cell A1 (as shown in
the sample dataset).
Run the "CopyFilteredData" macro by pressing Alt + F8, selecting the macro, and
clicking "Run."
The macro will apply an AutoFilter to the data in "Sheet1," filter the data to show only
employees from the HR department, and then copy the filtered data to a new sheet
named "FilteredData."

9. Macro to Create a Pivot Table:

Create pivot table

How to Use:
Press ALT + F11 to open the VBA editor.
Click Insert > Module to add a new module.
Copy and paste the code into the module.
Close the VBA editor.
Press ALT + F8 to open the macro dialog box.
Select the " createpivottable " macro from the list and click "Run."
 Excel Macros and VBA / 14

10. Insert Multiple Column:

Insert multiple column at same time as per your requirement.

Press ALT + F11 to open the VBA editor.

Click Insert > Module to add a new module.
Copy and paste the code into the module.
Close the VBA editor.
Press ALT + F8 to open the macro dialog box.
Select the " InsertMultipleColumns " macro from the list and click "Run."

11. Macro to Calculate Age from Date of Birth:

Calculate age based on the date of birth in the dataset.

How to Use:
Press ALT + F11 to open the VBA editor.
Click Insert > Module to add a new module.
Copy and paste the code into the module.
Close the VBA editor.
Press ALT + F8 to open the macro dialog box.
Select the " CalculateAge " macro from the list and click "Run."
 Excel Macros and VBA / 15

12. Apply Conditional Formatting:

How to Use:
Make sure that the source data is in "Sheet1," starting from cell A1 (as shown in the
sample dataset).
Run the "ApplyConditionalFormatting" macro by pressing Alt + F8, selecting the macro,
and clicking "Run."
The macro will apply conditional formatting to the "Salary" column and highlight
salaries greater than 50000 in red.

13. Macro to Create a Bar Chart :

Create a bar chart to visualize data.

Sample Dataset:
Assume the following table represents our sample dataset for product sales data:
 Excel Macros and VBA / 16

How to Use:
Make sure that the source data is in "Sheet1," starting from cell A1 (as shown in the
sample dataset).
Run the "CreateBarChart" macro by pressing Alt + F8, selecting the macro, and
clicking "Run."
The macro will create a new chart sheet named "SalesChart" and generate a bar chart
to visualize the quantity sold for each product.

14. Macros to data validation:

 Excel Macros and VBA / 17

How to Use:
Make sure that the source data is in "Sheet1," starting from cell A1 (as shown in the
sample dataset).
Run the "CreateDataValidationList" macro by pressing Alt + F8, selecting the macro,
and clicking "Run."
The macro will create a data validation list for the "Product" column, allowing users
to choose from a list of products in the cells.

15. Macro to Generate Random Numbers

Generate random numbers and populate a range with them.

How to Use:
Open your Excel workbook.
Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
In the VBA editor, go to Insert > Module to insert a new module.
Paste the provided VBA code into the module.
Close the VBA editor (Alt + Q) to return to Excel.
In your workbook, go to the sheet where you want to generate random numbers
(e.g., "Sheet1").
Run the "GenerateRandomNumbers" macro by pressing Alt + F8, selecting the
macro, and clicking "Run."
The macro will generate random numbers between 1 and 100 in column E, starting
from cell E2 (as shown in the code), for the specified range.
 Excel Macros and VBA / 18

16. Macro to Insert Current Date and Time:

This macro inserts the current date and time into the active cell.

17. Macro to Sum Selected Cells:

This macro displays a message box with the sum of the selected cells.

18. Macro to Find and Replace Text:

This macro prompts the user to enter text to find and its replacement. It then
performs a find and replace operation for the entire worksheet

19. Macro to Sort Data by Date:

This macro sorts the data by date in ascending order based on the "Date" column.

20. Highlight Cells with Misspelled Words.

When you fill hard to find misspelled word then use this code for find.
 Excel Macros and VBA / 19

21. Print Comments:

To turn on the option to print cell comments at the end of the page, use this macro.
If you have 10 pages to print, for example, this code will print all the comments on
the 11th and final page. To turn on the option to print cell comments at the end of
the page, use this macro. If you have 10 pages to print, for example, this code will
print all the comments on the 11th and final page.

22. VBA Macro to Summarize Data

Macro to summarize the sales data by calculating the total sales amount for each product.
Sample dataset:
 Excel Macros and VBA / 20

Instructions:
Open your Excel workbook that contains the sample dataset in "Sheet1."
Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
In the VBA editor, go to Insert > Module to insert a new module.
Paste the provided VBA code into the module.
Close the VBA editor (Alt + Q) to return to Excel.

How to Use:
Make sure that your dataset is in "Sheet1," starting from cell A1 (as shown in
the example dataset).
Run the "SummarizeSalesData" macro by pressing Alt + F8, selecting
"SummarizeSalesData," and clicking "Run."

23. Macro to Copy Filtered Data to Another Sheet:

Filter data based on a specific criteria and copy the filtered data to another sheet.
 Excel Macros and VBA / 21

24. Macro to Delete Blank Rows:

This code help you to delete all blank from your data.

25. Macro to Convert Text to Proper Case:

Convert text in a specified column to proper case.

26. Protect all Worksheets

If you want to protect all sheets use this code and enter password.
 Excel Macros and VBA / 22

27. Resize All Charts in a Worksheet:

If you want to resize or all chart in same size use this code with changing of
height and width.

28. Macros to Export excel data into CSV:

Export data to a CSV file.
Sample data:

Get the CSV file path

Dim csvFilePath As String
 Excel Macros and VBA / 23

29. Macro to Create a Sales Data Validation Drop-Down:

This macro creates a new worksheet named "SalesDataValidation" and sets up data
validation on the "Product" column with a drop-down list containing the
product names.

30. Macro to Highlight Duplicates:

 Excel Macros and VBA / 24

31. Macro to Send Email:

VBA macro that uses the provided code to send an email using Microsoft Outlook.
Please make sure you have Outlook installed and configured on your system before
running this code.

32. Unprotect sheet:

This code you can use to unprotect sheet which you protect with password.

33. Range to Image Conversion:

This code is made to take a specified range of Excel worksheet cells and turn them
into an image that will be automatically put into the same worksheet.
 Excel Macros and VBA / 25

34. Text to Speech:

This code will speak all the text what you have in that range, cell by cell.

35. Convert to Upper Case:

This code will convert selected range to upper case

36. Remove Decimals from Numbers:

This code will remove all the decimals from the numbers from the selected range.

37. Highlight Unique Values:

This code will highlight unique values from selected range.
 Excel Macros and VBA / 26

38. Highlight All Cells With Comments:

This code will highlight all the cells that have comments in it.

39. Highlight alternative rows in the selected range

This code will highlight alternative rows in the selected range.

40. Open Calculator

Using this code, you can launch a specific calculator from within Excel.

41. Highlight Top 10 Values

This code will highlight top 10 values with the color.
 Excel Macros and VBA / 27

42. Daily Task Reminder:

This code will send team members daily work reminders automatically.
Sample dataset:

43. Sales Forecasting:

This code will use macros to forecast daily sales based on historical data.
Sample dataset:
 Excel Macros and VBA / 28

44. Expense Report Generation:

This code will create macros to generate daily expense reports for employees.
Sample dataset:
 Excel Macros and VBA / 29

45. Data Entry Automation

Automate the data entry process for daily transactions.
Sample dataset:

46. Customer Feedback Analysis

This code will use macros to analyze daily customer feedback and generate reports.
Sample dataset:
 Excel Macros and VBA / 30

Conclusion
Excel macros, powered by VBA, provide an incredible way to automate tasks, save time,
and extend the functionality of Microsoft Excel. With the ability to write custom code,
users can easily tailor Excel to suit their specific needs and tackle complex data
processing challenges. Whether you are a beginner or an experienced user, learning
VBA and mastering the art of Excel macros can significantly boost your productivity
and proficiency with this widely-used spreadsheet software.