VBA Macros

VBA Macros

How to create a Macro

To create a macro:
  1. Navigate to Developer > Create Macro
  2. In the dialog that appears, enter a name and a description for the macro, then click Create.
  3. Enter the code in the VBA editor.
  4. Click Save.

Sample macro codes to get started with:

 Hide and unhide row   

  1. Sub HideUnhideRowColumn()
  2.     Sheets("Sheet1").Columns("A:E").Hidden = True
  3.     Sheets("Sheet1").Columns("C:D").Hidden = False
  4.     Sheets("Sheet1").Rows("11:15").Hidden = True
  5.     Sheets("Sheet1").Rows("12:13").Hidden = False
  6. End Sub

Cut, copy and paste rows

  1. Private Sub CopyCutRow()
  2.     WorkSheets("Sheet1").Rows(2).Copy WorkSheets("Sheet1").Rows(4) //copy row 2 and paste it to row 4
  3.     WorkSheets("Sheet2").Rows(2).Cut WorkSheets("Sheet1").Rows(4)//cut row 2 and paste it to row 4
  4. End Sub

Send emails based on the change in cell value

  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Set watchRange = Range("B1:B100") 'Assuming cell range to be monitored is
  3. If Not Intersect(Target,watchRange) Is Nothing Then
  4.   If Target.Value = "Send Mail" Then
  5.     ThisWorkbook.HasRoutingSlip = True
  6.     With ThisWorkbook.RoutingSlip
  7.         .Recipients = Array("SendEmail@zoho.com") 'Can send mail to multiple recipients
  8.         .Subject = "Task completed"
  9.         .Message = "Mail content" & Target.Row 'Sending cell value in mail
  10.         .AttachWorkbook = True
  11.         'By default the file will be attached in the mail. If it is not needed then send
  12.         ' .AttachWorkbook as false.
  13.     End With
  14.     ThisWorkbook.Route
  15.     End If
  16. End If
  17. End Sub
   

Workbook open event and new sheet create event
   

  1. REM ThisWorkbook Module

  3. Private Sub Workbook_Open()
  4. Msgbox("I am a open event ",-1,"Welcome back!")
  5. End Sub
  6. Private Sub Workbook_NewSheet(ByVal sh As Object)
  7. Msgbox("I am a new sheet event , You have created a sheet !  ",-1," Name it ")
  8. End Sub

Worksheet change event
   

  1. REM Sheet Module

  3. Private Sub Worksheet_Change(ByVal Target As Range)
  4. Application.EnableEvents = false
  5. On Error Goto ENEV
  6. REM add your code below
  7. Msgbox(" I am a change event ",-1,"OH thats a good change!")
  8. ENEV:
  9. Application.EnableEvents = true
  10. End Sub

How to record a macro

Use macros to automate everyday tasks. If you are to apply the same formatting everyday, you can record macros. 

To record a macro:
  1. Go to Developer > Record Macros 
  2. Enter a name and a description in the input field.
  3. You can check or uncheck Use Relative Reference as needed.
  4. Click Record.
  5. To stop recording, click on the Stop icon 
  6. Click View Code to view the recorded macro in the VBA editor or click Macro List to view all the macros in the spreadsheet.
Notes
Absolute referencing - The cells are referenced $Column_Number$Cell_Number, meaning the macro is executed in the same cells used when recording the macro.
 
Relative referencing - The cells are referenced without the "$" constraint, meaning the macro is executed with cells relative from where the macro is executed.