Lesson 3 : Writing Your Own VBA Code - 35 Examples

Deepanshu Bhalla 10 Comments
In the previous post, we have covered the following topics :


This tutorial gives you a plenty of hands-on examples to get you started with excel macros.

Let's understand a simple macro code.

Let’s take a simple example multiplying a cell by 2.

Sub Macro1()
Range("B2").FormulaR1C1 = "=RC[-1]*2"
End Sub
What is Sub?
To create a sub procedure, start with the Sub keyword.

What is Macro1?
It is a macro name. You can give your macro any name you want.

What is FormulaR1C1 ?
It is a formula reference style. The macro recorder always uses R1C1 reference style..

How does R1C1 differ from A1?
Instead of letters you get numbers. The numbers represent the relative distance from the current cell.

A1 - Columns followed by row number.
R1C1 - Rows followed by columns.


Example
Let’s take a simple example multiplying two columns.


A1 Style


R1C1 Style



What does RC[-2] mean?
It refers to the cell 2 columns to the left of current cell.

Similarly, R[3]C[3] is a cell 3 rows down and 3 columns to the right. R[-2]C[-4] is a cell 2 rows up and 4 columns to the left.

Positive numbers - Cells below and/or across to the right.
Negative numbers - Cells above and/or to the left.

What is End Sub?
To end a sub procedure, use the End Sub keyword.

Where to write VBA code?

Module is an area where we write VBA code.

Instructions :
  1. Open Excel Workbook
  2. Press ALT + F11 to open visual basic editor (VBE)
  3. To insert a module, go to Insert > Module




To view a module, just double click on its icon in the Project Explorer window in the VBA Editor

35 Examples

The following is a list of hands-on examples to help you write your first macro.

1. Write value into a cell
  Task : Write 12 into cell B3
Sub Enteravalue()
Range("B3").Value = 12
End Sub

2. Add the same value into multiple ranges
Task : Enter 12 into cells B3 through B5 and C5 through C8
Sub Entervalues()
Range("B3:B5,C5:C8").Value = 12
End Sub

3. Select a cell
Task : Select cell B3
Sub macro1()
Range("B3").Select
End Sub
4. Select a range
Task : Select range B3:B5
Sub macro2()
Range("B3:B5").Select
End Sub 
5. Select multiple ranges
Task : Select ranges B3:B5 and C5:C8
Sub macro3()
Range("B3:B5,C5:C8").Select
End Sub 
6. Copy/Paste
Task : Copy the data from range “B3:B5” and paste it into cells starting from G3
Sub copypaste()
Range("B3:B5").Copy
Range("G3").Select
ActiveSheet.Paste
End Sub
Another way to write the above program (copy and paste)
Sub copypaste()
Range("B3:B5").Copy _
Destination:=Range("G3")
End Sub
7. Copy/Paste Cell Value Only (Not Formula)
Task : Copy the data from range “B3:B5” and paste it into cells starting from G3
Sub copypaste()
Range("B3:B5").Copy
Range("G3").PasteSpecial Paste:=xlPasteValues
End Sub
8. Copy the entire data from one sheet to another sheet
Task : Copy the entire data from “Sheet1” and paste it into “Sheet2”.
Sub entirecopy()
Sheets("Sheet1").UsedRange.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
End Sub
9. Copy the entire row and paste it to another row
Task : Copy the fifth row from “Sheet1” and paste it to seventh row in the same sheet.
Sub entirerow()
Sheets("Sheet1").Rows(5).EntireRow.Copy
Rows(7).Select
ActiveSheet.Paste
End Sub
10. Clear contents only
Task : You want to clear only contents of the cells (not formats).
Sub clearcontent()
Range("A2:B4").ClearContents
End Sub
11. Clear all formats and values
Task : You want to clear all formats and values from cells in addition to clearing the contents of the cells.
Sub clearall()
Range("A2:B4").Clear
End Sub
12. Select the left most non-empty cell in a row
It moves the cursor to the left most non-blank cell in a row
Sub leftmost()
Selection.End(xlToLeft).Select
End Sub
13. Select the right most non-empty cell in a row
It moves the cursor to the right most non-blank cell in a row
Sub rightmost()
Selection.End(xlToRight).Select
End Sub
14. Select the last non-empty cell in a column
It moves the cursor to the last non-blank cell in a column
Sub lastcell()
Selection.End(xlDown).Select
End Sub
15. Select the first non-empty cell in a column
It moves the cursor to the first non-blank cell in a column
Sub firstcell()
Selection.End(xlUp).Select
End Sub
16. VBA Message Box

The syntax for VBA message box is as follows :


Simple VBA Message Box
Sub Msg1()
MsgBox "Hello Everyone"
End Sub

17. Message Box With Title
Sub Msg2()
'Display message box with title "Introduction"
MsgBox "Hello Everyone",  , "Introduction"
End Sub

18. Advanced Message Box



Sub Msg3()
Dim Popup As Integer
Popup = MsgBox("Are you a lazy guy?", vbYesNo, "User's Attitude")
'If user clicks on Yes button then displays a message
If Popup = vbYes Then
MsgBox "Thank you for answering honsestly!!"
Else
'If user clicks on No button then displays a message
MsgBox "Great!"
End If
End Sub 
In VBA message box, ‘buttons’ parameter can have any of the following values:



18. How to pass a value in message box
Sub Msg5()
MsgBox "Your final score is " & Range("A2").Value
End Sub

19. Find active cell positioning
Sub cellpos()
Rowf = ActiveCell.Row
Colf = ActiveCell.Column
MsgBox Rowf & "," & Colf
End Sub
 20. Conditional Statements - IF THEN ELSE
Sub Macro1()
If Range("A1") > 100 Then
Range("B1").Value = 1
ElseIf Range("A1") > 50 Then
Range("B1") = 0.5
Else
Range("B1") = 0
End If
End Sub
21. SELECT CASE - Alternative to IF THEN ELSE
Sub Macro11()
Select Case Range("A1").Value
   Case Is > 100
    Range("B1").Value = 1
   Case Is > 50
   Range("B1").Value = 0.5
   Case Else
   Range("B1").Value = 0
   End Select
End Sub 
22. How to select a worksheet
Sub slctwrk()
'Select sheet2 worksheet
Sheet2.Select
End Sub
 23. How to add new worksheet
Sub macro99()
    Sheets.Add
End Sub
 24. How to rename a worksheet
The following program renames the active sheet to "Raw Data".
Sub macro999()
    ActiveSheet.Name = "Raw Data"
End Sub
 25. How to delete a worksheet
The following program deletes the specified worksheet.
Sub macro100()
     Sheets("Sheet2").Delete
End Sub
26. How to add new workbook
Sub macro101()
    Workbooks.Add
End Sub
 27. How to save a workbook
The following program saves the active workbook.
Sub macro102()
   ActiveWorkbook.Save
End Sub
 28. How to save a workbook with the specified name
The following program saves the active workbook with the specified name.
Sub macro103()
   ActiveWorkbook.SaveAs "FinalFile.xls"
End Sub
 29. How to close the workbook
The following program closes the active workbook.
Sub macro104()
ActiveWorkbook.Close
End Sub
 30. Run a macro when opening a workbook
Excel has Auto_Open subroutine that requests a macro run each time you open a workbook . After entering the following program in module, the message box - "Welcome to Excel World" will appear each time you open the workbook.
Sub Auto_Open()
Msgbox ("Welcome to Excel World")
End Sub

 It is generally used to refresh database queries automatically in your workbook upon opening

 31. Select current region
The current region selects is a range consisting of blank and non-blank cells surrounded by the range you provide.
Sub macro107()
Range("A1").CurrentRegion.Select
End Sub


 32. Select current region without headers
Sub macro108()
Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select
End Sub


 33.  How To Merge and Unmerge Cells
Sub macro109()
 'Merge Property
    Range("A1:A3").Merge

    'UnMerge Property
    Range("A1:A3").UnMerge
End Sub
 34.  How To Insert and Delete Rows
Sub macro999()
    Rows(2).Insert 'It inserts a row at row 2
    Rows(4).Delete 'It deletes the fourth row
End Sub
 35.  How To Insert and Delete Columns
Sub macro9999()
   Columns("B").Insert 'It inserts a column at column B
   Columns("D")..Delete 'It deletes the column D
End Sub 
Previous: Record Your First Macro                                   Next : Playing with Dynamic Ranges 
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

Post Comment 10 Responses to "Lesson 3 : Writing Your Own VBA Code - 35 Examples"
Next → ← Prev