Create, run, and record VBA macros | Zoho Sheet Help Guide

Create, run and record VBA macros

What is a macro?

Macros are special programs that will help you auto-execute specific tasks instead of performing manual tasks repeatedly. Visual Basic for Applications (VBA) is the programming language used to write macros. In Zoho Sheet, you can use the VBA editor to create, record, run, and manage macros.

To create a macro:

  1. Go to Tools > VBA Macros > Create Macro.
  2. In the Create Macro dialog that appears, give the preferred macro name and description.
  3. Once done, click Create.
  4. In the VBA Editor, you can write the macro code
  5. Once done, click on 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

To record a macro:

  1. Go to Tools > VBA Macros > Record Macro.
  2. In the Record Macro dialog that appears, enter the name of the macro you wish to record. You can check or uncheck "Use Relative Reference" based on your preference.
  3. Once done, click on Record.
  4. To stop recording the macro, click on the (stop icon). Further, you can click on View Code to view the recorded macro code in the VBA Editor. Or you can click on Macro List to view the list of macros you've created to date.


Info
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.