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:
- Go to Tools > VBA Macros > Create Macro.
- In the Create Macro dialog that appears, give the preferred macro name and description.
- Once done, click Create.
- In the VBA Editor, you can write the macro code
- Once done, click on Save.
Sample macro codes to get started with:
Hide and unhide row
- Sub HideUnhideRowColumn()
- Sheets("Sheet1").Columns("A:E").Hidden = True
- Sheets("Sheet1").Columns("C:D").Hidden = False
- Sheets("Sheet1").Rows("11:15").Hidden = True
- Sheets("Sheet1").Rows("12:13").Hidden = False
- End Sub
Cut, copy and paste rows
- Private Sub CopyCutRow()
- WorkSheets("Sheet1").Rows(2).Copy WorkSheets("Sheet1").Rows(4) //copy row 2 and paste it to row 4
- WorkSheets("Sheet2").Rows(2).Cut WorkSheets("Sheet1").Rows(4)//cut row 2 and paste it to row 4
- End Sub
Send emails based on the change in cell value
- Private Sub Worksheet_Change(ByVal Target As Range)
- Set watchRange = Range("B1:B100") 'Assuming cell range to be monitored is
- If Not Intersect(Target,watchRange) Is Nothing Then
- If Target.Value = "Send Mail" Then
- ThisWorkbook.HasRoutingSlip = True
- With ThisWorkbook.RoutingSlip
- .Recipients = Array("SendEmail@zoho.com") 'Can send mail to multiple recipients
- .Subject = "Task completed"
- .Message = "Mail content" & Target.Row 'Sending cell value in mail
- .AttachWorkbook = True
- 'By default the file will be attached in the mail. If it is not needed then send
- ' .AttachWorkbook as false.
- End With
- ThisWorkbook.Route
- End If
- End If
- End Sub
Workbook open event and new sheet create event
- REM ThisWorkbook Module
- Private Sub Workbook_Open()
- Msgbox("I am a open event ",-1,"Welcome back!")
- End Sub
- Private Sub Workbook_NewSheet(ByVal sh As Object)
- Msgbox("I am a new sheet event , You have created a sheet ! ",-1," Name it ")
- End Sub
Worksheet change event
- REM Sheet Module
- Private Sub Worksheet_Change(ByVal Target As Range)
- Application.EnableEvents = false
- On Error Goto ENEV
- REM add your code below
- Msgbox(" I am a change event ",-1,"OH thats a good change!")
- ENEV:
- Application.EnableEvents = true
- End Sub
To record a macro:
- Go to Tools > VBA Macros > Record Macro.
- 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.
- Once done, click on Record.
- 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.
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.
Access your files securely from anywhere
Zoho CRM Training Programs
Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.
Zoho DataPrep Personalized Demo
If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.
- Zoho Workerly Home
- Forums
- Connect With Us:
- Zoho Recruit Home
- Forums
- Connect With Us:
New to Zoho Writer?
Create. Review. Publish.
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.
New to Zoho Survey?
Zoho Sheet Resources
Zoho Forms Resources
New to Zoho Sign?
Zoho Sign Resources
Sign, Paperless!
New to Zoho TeamInbox?
Zoho TeamInbox Resources
New to Zoho ZeptoMail?
Design. Discuss. Deliver.
New to Zoho Workerly?
New to Zoho Recruit?
New to Zoho CRM?
New to Zoho Projects?
New to Zoho Sprints?
New to Zoho Assist?
New to Bigin?
Related Articles
Create a new blank spreadsheet
To create a new blank spreadsheet, open the app and follow these steps: In the listing page, tap the + icon in the bottom bar. Choose the Blank Spreadsheet option from the listed gallery.Supported VBA modules in Zoho Sheet
Classes Properties Methods <Globals> All properties and modules under VBA are globals (i.e., they can be used directly without prefixing the respective module name) All properties and modules under VBA are globals (i.e., they can be used directly ...Create a new spreadsheet
To create a new spreadsheet: 1. Visit the file listing page and tap Blank Spreadsheet.Create a named range
To create a named range: Select the desired cell range. Go to Under the Insert menu, choose Define Name. You can choose to define a cell range, an expression, or a constant value. Enter the desired values and tap Done.Create spreadsheets from templates
To create a new spreadsheet from templates, open the app and follow these steps: In the listing page, tap the + icon in the bottom bar. Simply tap to open the desired template from the default template gallery. You can immediately start editing the ...
New to Zoho LandingPage?
Zoho LandingPage Resources