XL n CAD

Excel isn’t just a tool – it's a way of Life!

Macros (VBA for Excel)

Table of Contents

Introduction to VBA for Excel

VBA stands for Visual Basic for Applications. It is a custom version of the Visual Basic programming language that has powered Microsoft Excel’s macros since the mid-1990s. The programming interface of MS Excel will let you,

  • Create and Execute Macros (explained in the next section). Anything that the user can do in Excel from the user interface can be done by writing code in VBA for Excel.
  • Create User Defined Functions (UDFs).
  • Integrate Excel with other applications such as Microsoft Word, PowerPoint, Outlook, Notepad, etc.

What is a Macro ?

Macros are codes written in VBA (Visual Basic for Applications), or recorded in MS Excel (or other MS applications) which helps to reduce the manual effort required for a job. Macros save your time and headaches by automating common, repetitive tasks. They reduce the possibility of human error that increases with many, repetitive keystrokes and tasks. The most important feature of an Excel macro is, it can be saved for future use, shared among multiple users.

In a layman’s language, a macro is a recording of your routine steps in Excel that you can replay using a single button

The best part is You don’t have to be a programmer or know Visual Basic Applications (VBA) to use Macros. You just need to copy the codes shared here and hit the run button to execute them. Save the file as a Macro enabled workbook for future use.


How to use codes shared here?

  • Step 1: Copy the Code shared here.
  • Step 2: Click on Visual Basic in the Developer Tab of Excel Ribbon. ALT + F11 is the Shortcut for Visual Basic Editor.
  • Step 3: Right click on the tree view and Insert a Module.
  • Step 4: Paste the Code given here into a module.
  • Step 5: Close the editor and Click on Macros in Developer Tab. You will get a dialogue box with the list of Macros ready for execution. Click on Run button to execute the macro.

51 Ready to use Macros


Add a new Worksheet


Sub AddNewSheet()
Call Sheets.Add
End Sub


Add two new Worksheets into a Workbook


Sub AddMultipleSheets ()
Call Sheets.Add(, , 2)
End Sub
‘created by Ajay Anand, xlncad.com


Add Worksheets based on user input


Sub AddSheetsUserInput ()
Dim i, j As Integer
i = Val(InputBox(“Enter the number of sheets to be added”))
For j = 1 To i
Worksheets.Add
Next j
End Sub


Add and Name the Worksheets based on User Input


Sub AddAndNameWorksheets()
Dim i, j As Integer
i = Val(InputBox(“Enter the number of sheets to be added”))
For j = 1 To i
Worksheets.Add.Name = “XL n CAD ” & j
Next j
End Sub

Create worksheets from the names present in a Worksheet


Sub CreateSheetsUsingNames()
Range(“A1”).Select
Do Until ActiveCell.Value = “”
Sheets.Add.Name = ActiveCell.Value
Sheets(“Names”).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub


Delete Worksheet


Sub DeleteSheet()
Sheets(“Sheet1”).Delete
End Sub


Delete Worksheet based on user input


Sub DeleteSheetUserInput()
Dim XLnCAD As String
XLnCAD = Val(InputBox(“Enter the name of the sheet to be deleted”))
Sheets(XLnCAD).Delete
End Sub


Insert a row (one row each) in between each row containing data


Sub InsertRows()
Range(“A2”).Select
Do Until ActiveCell.Value = “”
Selection.EntireRow.Insert
ActiveCell.Offset(2, 0).Select
Loop
End Sub

Insert 2 Rows in between each row containing data


Sub Insert2Rows()
Range(“A2”).Select
Do Until ActiveCell.Value = “”
Selection.EntireRow.Insert
Selection.EntireRow.Insert
ActiveCell.Offset(3, 0).Select
Loop
End Sub


Insert Rows in between each row containing data, based on user Input


Sub InsertNRows()
Range(“A3”).Select
Dim d, e As Integer
e = Val(InputBox(“Enter the number of Rows to be inserted”))
Do Until ActiveCell.Value = “”
For d = 1 To e
Selection.EntireRow.Insert
Next d
ActiveCell.Offset(e + 1, 0).Select
Loop
End Sub


Delete blank Rows in between rows containing data


Sub DeleteRows()
Range(“A2”).Select
Do Until ActiveCell.Offset(1, 0).Value = “”
Selection.EntireRow.Delete
ActiveCell.Offset(1, 0).Select
Loop
End Sub


Delete blank Rows (2 rows each) in between rows containing data


Sub Delete2Rows()
Range(“A2”).Select
Do Until ActiveCell.Offset(2, 0).Value = “”
Selection.EntireRow.Delete
Selection.EntireRow.Delete
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Insert Columns in between each column containing data


Sub InsertColumns()
Range(“B1”).Select
Do Until ActiveCell.Value = “”
Selection.EntireColumn.Insert
ActiveCell.Offset(0, 2).Select
Loop
End Sub


Delete Columns in between columns containing data


Sub DeleteColumns()
Range(“B1”).Select
Do Until ActiveCell.Offset(0, 1).Value = “”
Selection.EntireColumn.Delete
ActiveCell.Offset(0, 1).Select
Loop
End Sub


Display a Message using Message Box


Sub SimpleMessageBox()
MsgBox “Hi Everyone”
End Sub


Defining the Title of the Message Box


Sub MessageBoxTitle()
MsgBox “Hi Everyone. Greetings from XL n CAD”, , “XL n CAD”
End Sub


Display a Message using Value in an Excel Sheet


Sub DisplayValueFromSheet()
MsgBox “The value of cell A1 is ” & Range(“A1”).Value
End Sub


Display a Message using Text in an Excel Sheet


Sub DisplayTextFromSheet ()
MsgBox “The in the Cell C3 is ” & Range(“C3”).Text
End Sub


Receiving two values using an Input Box and displaying their sum using Message Box


Sub SumUsingInputBox()
a = InputBox(“Enter the value of A”)
b = InputBox(“Enter the value of B”)
c = a + b
MsgBox “Sum of A and B is ” & c
End Sub


Export data (Write) from a Worksheet to a Text File


Sub WriteToTextFile()
Open “D:\XLnCAD.txt” For Output As 1
Range(“A1”).Select
Do Until ActiveCell.Value = “”
Print #1, ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Loop
Close (1)
End Sub

Import data (Read) from a Text File to a Worksheet


Sub ReadFromTextFile()
Dim XLnCAD As String
Open “D:\XLnCAD.txt” For Input As 1
Range(“C1”).Select
For i = 1 To 3
Input #2, XLnCAD
Next i
ActiveCell.Value = XLnCAD
Close (1)
End Sub


Hide All worksheets in a workbook (Except Active sheet)


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


Unhide Every Hidden sheet in an Excel Workbook


Sub UnHideSheets()
Dim XLnCAD As Worksheet
For Each XLnCAD In ActiveWorkbook.Worksheets
XLnCAD.Visible = xlSheetVisible
Next XLnCAD
End Sub


Convert a worksheet to Very Hidden Sheet Mode


Sub VeryHiddenSheet()
Sheets(“XLnCAD”).Visible = xlVeryHidden
End Sub

Unhide a Very Hidden Sheet


Sub UnHideSheets()
Dim XLnCAD As Worksheet
For Each XLnCAD In ActiveWorkbook.Worksheets
XLnCAD.Visible = xlSheetVisible
Next XLnCAD
End Sub


Delete All Worksheets in a workbook (Except Active sheet)


Sub DeleteWorksheets()
Dim XLnCAD As Worksheet
For Each XLnCAD In ThisWorkbook.Worksheets
If XLnCAD.Name <> ThisWorkbook.ActiveSheet.Name Then
Application.DisplayAlerts = False
XLnCAD.Delete
Application.DisplayAlerts = True
End If
Next XLnCAD
End Sub


Create a PDF file from a Worksheet


Sub CreatePdf()
Dim ID As String
ID = Range(“D4”).Text
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=”E:\Payslip\” + ID + “.pdf”, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End Sub


Convert text into Upper Case


Sub UpperCase()
For Each XLnCAD In Selection
XLnCAD.Value = UCase(XLnCAD.Value)
Next
End Sub

Convert text into Lower Case


Sub LowerCase()
For Each XLnCAD In Selection
XLnCAD.Value = LCase(XLnCAD.Value)
Next
End Sub


Convert text into Proper Case


Sub ProperCase()
For Each XLnCAD In Selection
XLnCAD.Value = Application.Proper(XLnCAD.Value)
Next
End Sub


Join Text in present in different cells of a Column


Sub JoinStringsInColumn()
Dim XLnCAD As Range, Sonder As Range, XNC As String
Set XLnCAD = Selection
For Each Sonder In XLnCAD
XNC = XNC & ” ” & Sonder
Next
ActiveCell.Offset(0, 1).Select
Selection.Value = XNC
Selection.Font.Bold = True
End Sub


Join Text in present in different cells of a Row


Sub JoinStringsInRow()
Dim XLnCAD As Range, Sonder As Range, XNC As String
Set XLnCAD = Selection
For Each Sonder In XLnCAD
XNC = XNC & ” ” & Sonder
Next
ActiveCell.Offset(1, 0).Select
Selection.Value = XNC
Selection.Font.Bold = True
End Sub


Hide Specific Row/Rows of a Worksheet


Sub HideRows()
Rows(“5:10”).EntireRow.Hidden = True
End


Unhide Specific Row/Rows of a Worksheet


Sub UnHideRows()
Rows(“5:10”).EntireRow.Hidden = False
End Sub


Unhide Every hidden Row of a Worksheet


Sub UnhideAllRows()
Cells.EntireRow.Hidden = False
End Sub


Hide Specific Column/Columns of a Worksheet


Sub HideColumns()
Columns(“B:D”).EntireRow.Hidden = True
End Sub

Unhide Specific Column/Columns of a Worksheet


Sub UnHideColumns()
Columns(“B:D”).EntireRow.Hidden = True
End Sub


Unhide Every hidden Column of a Worksheet


Sub UnhideAllColumns()
Cells.EntireColumn.Hidden = False
End Sub


Select first 1000 cells from first 4 columns a Worksheet


Sub SelectCells()
Range(“A1:D1000”).Select
End Sub


Protect Every Worksheet in a Workbook with a password


Sub ProtectAllWorksheets()
Dim XLnCAD As Worksheet
Dim Sonder As String
Sonder = InputBox(“Enter a Password.”, “XL n CAD”)
For Each XLnCAD In ActiveWorkbook.Worksheets
XLnCAD.Protect Password:=Sonder
Next XLnCAD
End Sub


Unprotect Every protected Worksheet in a Workbook


Sub UnProtectAllSheets()
Dim XLnCAD As Worksheet
Dim Sonder As String
Sonder = “Password”
‘this password should be the same which you used to protect the sheets
For Each XLnCAD In Worksheets
XLnCAD.Unprotect password:=Sonder
Next XLnCAD
End Sub

Text to Speech


Sub TextToSpeech()
Selection.Speak
End Sub


Unmerge all merged cells in a Worksheet


Sub UnmergeAllCells()
ActiveSheet.Cells.UnMerge
End Sub


Auto Fit Columns


Sub AutoFitColumns()
Cells.EntireColumn.AutoFit
End Sub


AutoFit Rows


Sub AutoFitRows()
Cells.EntireRow.AutoFit
End Sub

Reverse A String


Sub ReverseString()
Dim XLnCAD As String
XLnCAD = Range(“A1”).Value
Range(“A2”).Value = StrReverse(XLnCAD)
End Sub


Copy the data from a Worksheet and Paste as Values to a New Sheet


Sub PasteAsValuesToNewSheet()
Cells.Select
Selection.Copy
Sheets.Add After:=ActiveSheet
Range(“A1”).Select
Selection.PasteSpecial Paste:=xlPasteValues
End Sub


Count the total number of Worksheets in a Workbook


Sub CountSheets()
MsgBox Application.Sheets.Count
End Sub


Get the name of every Worksheet present in a Workbook


Sub ListEverySheet()
Dim XNC As Worksheet
Dim x As Integer
x = 1
Sheets(“XL n CAD”).Range(“A:A”).Clear
For Each XNC In Worksheets
Sheets(“XL n CAD”).Cells(x, 1) = XNC.Name
x = x + 1
Next XNC
End Sub


Count number of Rows and Columns in a selection


Sub CountRowAndColumns()
MsgBox “Rows ” & Selection.Rows.Count _
& vbCrLf & “Columns ” & Selection.Columns.Count
End Sub


Find the largest number in a selection


Sub LargetstNumber()
Dim XLnCAD As Double, Sonder As Range
Set Sonder = Selection
XLnCAD = WorksheetFunction.Max(Sonder)
MsgBox XLnCAD
End Sub


Things to remember while using Macros

  • By default, the tab for developers is not displayed in excel. If you can’t see the Developer tab, use the quick access toolbar to activate it.
  • By default, Macros are disabled in excel. To execute macros, you will need to enable running macros.
  • We cannot Undo an action performed using Macro. Make sure that you have a backup of the data before executing the macro.
  • To save macros in a workbook, you must save your workbook in a macro-enabled format *.xlsm
  • Always fill in the description of the macro when creating one. This will help you understand your own work if you have to revisit it weeks or months later.

2 thoughts on “Macros (VBA for Excel)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.