In the previous post, we have covered the following topics :
Let’s take a simple example multiplying a cell by 2.
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
- How to enable developer tab
- How to get started with VBA
- How to record your macro
- How to run macros
- How to insert a button to run macro
- Fundamentals of VBA Programming
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()What is Sub?
Range("B2").FormulaR1C1 = "=RC[-1]*2"
End 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.
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 :
- Open Excel Workbook
- Press ALT + F11 to open visual basic editor (VBE)
- 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
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 C8Sub Entervalues()
Range("B3:B5,C5:C8").Value = 12
End Sub
3. Select a cell
Task : Select cell B3
Sub macro1()4. Select a range
Range("B3").Select
End Sub
Task : Select range B3:B5
Sub macro2()5. Select multiple ranges
Range("B3:B5").Select
End Sub
Task : Select ranges B3:B5 and C5:C8
Sub macro3()6. Copy/Paste
Range("B3:B5,C5:C8").Select
End Sub
Task : Copy the data from range “B3:B5” and paste it into cells starting from G3
Sub copypaste()Another way to write the above program (copy and paste)
Range("B3:B5").Copy
Range("G3").Select
ActiveSheet.Paste
End Sub
Sub copypaste()7. Copy/Paste Cell Value Only (Not Formula)
Range("B3:B5").Copy _
Destination:=Range("G3")
End Sub
Task : Copy the data from range “B3:B5” and paste it into cells starting from G3
Sub copypaste()8. Copy the entire data from one sheet to another sheet
Range("B3:B5").Copy
Range("G3").PasteSpecial Paste:=xlPasteValues
End Sub
Task : Copy the entire data from “Sheet1” and paste it into “Sheet2”.
Sub entirecopy()9. Copy the entire row and paste it to another row
Sheets("Sheet1").UsedRange.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
End Sub
Task : Copy the fifth row from “Sheet1” and paste it to seventh row in the same sheet.
Sub entirerow()10. Clear contents only
Sheets("Sheet1").Rows(5).EntireRow.Copy
Rows(7).Select
ActiveSheet.Paste
End Sub
Task : You want to clear only contents of the cells (not formats).
Sub clearcontent()11. Clear all formats and values
Range("A2:B4").ClearContents
End Sub
Task : You want to clear all formats and values from cells in addition to clearing the contents of the cells.
Sub clearall()12. Select the left most non-empty cell in a row
Range("A2:B4").Clear
End Sub
It moves the cursor to the left most non-blank cell in a row
Sub leftmost()13. Select the right most non-empty cell in a row
Selection.End(xlToLeft).Select
End Sub
It moves the cursor to the right most non-blank cell in a row
Sub rightmost()14. Select the last non-empty cell in a column
Selection.End(xlToRight).Select
End Sub
It moves the cursor to the last non-blank cell in a column
Sub lastcell()15. Select the first non-empty cell in a column
Selection.End(xlDown).Select
End Sub
It moves the cursor to the first non-blank cell in a column
Sub firstcell()16. VBA Message Box
Selection.End(xlUp).Select
End Sub
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
In VBA message box, ‘buttons’ parameter can have any of the following values:Sub Msg3()Dim Popup As IntegerPopup = MsgBox("Are you a lazy guy?", vbYesNo, "User's Attitude")'If user clicks on Yes button then displays a messageIf Popup = vbYes ThenMsgBox "Thank you for answering honsestly!!"Else'If user clicks on No button then displays a messageMsgBox "Great!"End IfEnd Sub
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
The following program renames the active sheet to "Raw Data".
The following program deletes the specified worksheet.
The following program saves the active workbook.
The following program saves the active workbook with the specified name.
The following program closes the active 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.
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.
32. Select current region without headers
33. How To Merge and Unmerge Cells
Sub Macro1()21. SELECT CASE - Alternative to IF THEN ELSE
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
Sub Macro11()22. How to select a worksheet
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
Sub slctwrk()23. How to add new worksheet
'Select sheet2 worksheet
Sheet2.Select
End Sub
Sub macro99()24. How to rename a worksheet
Sheets.Add
End Sub
The following program renames the active sheet to "Raw Data".
Sub macro999()25. How to delete a worksheet
ActiveSheet.Name = "Raw Data"
End Sub
The following program deletes the specified worksheet.
Sub macro100()26. How to add new workbook
Sheets("Sheet2").Delete
End Sub
Sub macro101()27. How to save a workbook
Workbooks.Add
End Sub
The following program saves the active workbook.
Sub macro102()28. How to save a workbook with the specified name
ActiveWorkbook.Save
End Sub
The following program saves the active workbook with the specified name.
Sub macro103()29. How to close the workbook
ActiveWorkbook.SaveAs "FinalFile.xls"
End Sub
The following program closes the active workbook.
Sub macro104()30. Run a macro when opening a workbook
ActiveWorkbook.Close
End Sub
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
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()34. How To Insert and Delete Rows
'Merge Property
Range("A1:A3").Merge
'UnMerge Property
Range("A1:A3").UnMerge
End Sub
Sub macro999()35. How To Insert and Delete Columns
Rows(2).Insert 'It inserts a row at row 2
Rows(4).Delete 'It deletes the fourth row
End Sub
Sub macro9999()Previous: Record Your First Macro Next : Playing with Dynamic Ranges
Columns("B").Insert 'It inserts a column at column B
Columns("D")..Delete 'It deletes the column D
End Sub
Thanks for the use full codes
ReplyDeleteThanks for your help
ReplyDeleteThank you very much for the great tutorials.
ReplyDeleteThanks Sir
ReplyDeleteThanks very much. This article is very helpful to me.
ReplyDeleteThank You so much
ReplyDeleteGreat details
ReplyDeletehow to select for empty cell dynamically example if i am on A1 row and want to select next 5 or as per user (b,c,d,e,f,g....)
ReplyDeleteexcellent help for beginner
ReplyDeleteThank you for that beautiful examples.
ReplyDelete