Lesson 3 : Writing Your Own VBA Code - 35 Examples - ListenData

Menu

• Home
• SAS
  • Tutorials
  • SAS Certification
  • Interview Questions
  • Resumes
  • Clinical SAS
• R
• Python
• Data Science
• Credit Risk
• AI
• Excel
  • Functions
  • Advanced Excel
  • Charts
  • VBA / Macros
  • Statistical Analysis
  • Resumes
• SQL
• Calculators
• More
  • Infographics
  • SPSS
  • Humor

Go Home » 4 Simple VBA Lessons » Lesson 3 : Writing Your Own VBA Code - 35 Examples Deepanshu Bhalla 10 Comments 4 Simple VBA Lessons In the previous post, we have covered the following topics :

• 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() 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 toInsert > 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 Share Tweet About Author:

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.

While I love having friends who agree, I only learn from those who don't Let's Get Connected Email LinkedIn Post Comment 10 Responses to "Lesson 3 : Writing Your Own VBA Code - 35 Examples"

1. UnknownOctober 15, 2016 at 9:46 AM

   Thanks for the use full codes

   ReplyDeleteReplies
   Reply
2. vipul the ultimate oneNovember 10, 2016 at 9:53 PM

   Thanks for your help

   ReplyDeleteReplies
   Reply
3. UnknownMarch 9, 2017 at 6:08 AM

   Thank you very much for the great tutorials.

   ReplyDeleteReplies
   Reply
4. UnknownMarch 29, 2018 at 8:22 AM

   Thanks Sir

   ReplyDeleteReplies
   Reply
5. AnonymousJune 4, 2019 at 2:39 PM

   Thanks very much. This article is very helpful to me.

   ReplyDeleteReplies
   Reply
6. UnknownSeptember 16, 2019 at 9:23 PM

   Thank You so much

   ReplyDeleteReplies
   Reply
7. Technical knowledgeMarch 17, 2020 at 9:25 PM

   Great details

   ReplyDeleteReplies
   Reply
8. UnknownMay 5, 2020 at 10:09 PM

   how 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....)

   ReplyDeleteReplies
   Reply
9. UnknownJune 24, 2020 at 10:56 AM

   excellent help for beginner

   ReplyDeleteReplies
   Reply
10. UnknownJanuary 13, 2021 at 5:16 PM

    Thank you for that beautiful examples.

    ReplyDeleteReplies
    Reply

Add commentLoad more... Next → ← Prev Home Subscribe to: Post Comments (Atom) Follow us on Facebook