Excel VBA Macros for Beginners – 15 Examples File download

Home/Excel VBA/Excel VBA Macros for Beginners – 15 Examples File download

Excel VBA Macros for Beginners – These 15 novice macros provides the easiest way to understand and learn the basics of VBA to deal with Excel Objects. Learning Basic Excel VBA By Examples is the easiest way to understand the basics of VBA to deal with Excel Objects, in this tutorial we will not covering any programming concepts, we will see how to access the different Excel Object using VBA.


Excel VBA Macros for Absolute Beginners - 15 Examples File download

Excel VBA Macros for Beginners- Novice Example 1: How To Access The Excel Range And Show The Value Using Message Box

While automating most of the Excel Tasks, we need to read the data from Excel spread sheet range and perform some calculations. This example will show you how to read the data from a worksheet range.

'Example 1: How To Access The Excel Range And Show The Value Using Message Box
Sub sbExample1()
'It will display the A5 value in the message Box
MsgBox Range("A5")
'You can also use Cell Object to refer A5 as shwon below:
MsgBox Cells(5, 1) 'Here 5 is Row number and 1 is Column number
End Sub

Excel VBA Macros for Beginners- Novice Example 2: How To Enter Data into a Cell

After performing some calculations using VBA, we generally write the results into worksheet ranges. This example will show you how to write the data from VBA to Spread sheet range or cell.

'Example 2: How To Enter Data into a Cell
Sub sbExample2()
'It will enter the data into B5
Range("B5") = "Hello World! using Range"
'You can also use Cell Object as shwon below B6:
Cells(6, 2) = "Hello World! using Cell" 'Here 6 is Row number and 2 is Column number
End Sub

Excel VBA Macros for Beginners- Novice Example 3: How To Change The Background Color Of A Particular Range

The following example will help you in formatting a cell or range by changing the background color of a range. We can use ColorIndex property of a Ranger Interior object to change the fill color of a range or cell.

'Example 3: How To Change The Background Color Of A Particular Range
Sub sbExample3()
'You can set the background color using Interior.ColorIndex Property
Range("B1:B5").Interior.ColorIndex = 5 ' 5=Blue
End Sub

Excel VBA Macros for Beginners- Novice Example 4: How To Change The Font Color And Font Size Of A Particular Range

You may need to change the font color of range or cell sometimes. We can differentiate or highlight the cell values by changing the text color of range in the worksheet. The following method will use the font ColorIndex property of a range to change the font color.

'Example 4: How To Change The Font Color And Font Size Of A Particular Range
Sub sbExample4()
'You can set the background color using Interior.ColorIndex Property
Range("B1:B10").Font.ColorIndex = 3 ' 3=Red
End Sub

Excel VBA Macros for Beginners- Novice Example 5: How To Change The Text To Upper Case Or Lower Case

This example will help you to change the text from lower case to upper case. We use UCase Function to do this. If you wan tot change the text or string from upper case to lower case, we can use LCase function.

'Example 5: How To Change The Text To Upper Case Or Lower Case
Sub sbExample5()
'You can use UCase function to change the text into Upper Case
Range("C2").Value = UCase(Range("C2").Value)
'You can use LCase function to change the text into Upper Case
Range("C3").Value = LCase(Range("C3").Value)
End Sub

Excel VBA Macros for Beginners- Novice Example 6: How To Copy Data From One Range To Another Range

This example will help you to copy the data from one particular range to another range in a worksheet using VBA.

'Example 6: How To Copy Data From One Range To Another Range
Sub sbExample6()
'You can use Copy method
Range("A1:D11").Copy Destination:=Range("H1")
End Sub

Excel VBA Macros for Beginners- Novice Example 7: How To Select And Activate Worksheet

This example will help you to select one particular worksheet. To activate one particular sheet we can use Activate method of a worksheet.

'Example 7: How To Select And Activate Worksheet
Sub sbExample7()
'You can use Select Method to select
Sheet2.Select
'You can use Acctivate Method to activate
Sheet1.Activate
End Sub

Excel VBA Macros for Beginners- Novice Example 8: How To Get The Active Sheet Name And Workbook Name

We can use Name property of a active sheet to get the worksheet name. We can use name property of active workbook to get the name of the active workbook.

'Example 8: How To Get The Active Sheet Name And Workbook Name
Sub sbExample8()
'You can use ActiveSheet.Name property to get the Active Sheet name
MsgBox ActiveSheet.Name
'You can use ActiveWorkbook.Name property to get the Active Workbook name
MsgBox ActiveWorkbook.Name
End Sub

Excel VBA Macros for Beginners- Novice Example 9: How To Add New Worksheet And Rename A Worksheet and Delete Worksheet

We can Name property of worksheet to rename or change the worksheet name. We can use Add method of worksheets to add a new worksheet. Use Delete method to delete a particular worksheet.

'Example 9: How To Add New Worksheet And Rename A Worksheet and Delete Worksheet
Sub sbExample9()
'You can use Add method of a worksheet
Sheets.Add
'You can use Name property of worksheet
ActiveSheet.Name = "Temporary Sheet"
'You can use Delete method of a worksheet
Sheets("Temporary Sheet").Delete
End Sub

Excel VBA Macros for Beginners- Novice Example 10: How To Create New Workbook, Add Data, Save And Close The Workbook

The following examples will help you to adding some data, saving the file and closing the workbook after saving the workbook.

'Example 10: How To Create New Workbook, Add Data, Save And Close The Workbook
Sub sbExample10()
'You can use Add method of a Workbooks
Workbooks.Add
'You can use refer parent and child object to access the range
ActiveWorkbook.Sheets("Sheet1").Range("A1") = "Sample Data"
'It will save in the deafult folder, you can mention the full path as "c:\Temp\MyNewWorkbook.xls"
ActiveWorkbook.SaveAs "MyNewWorkbook.xls"
ActiveWorkbook.Close
End Sub

Excel VBA Macros for Beginners- Novice Example 11: How To Hide And Unhide Rows And Columns

We can use hidden property of rows or columns of worksheet to hide or unhide the rows or columns.

'Example 11: How To Hide And Unhide Rows And Columns
Sub sbExample11()
'You can use Hidden Propery of Rows
Rows("12:15").Hidden = True 'It will Hide the Rows 12 to 15
Rows("12:15").Hidden = False 'It will UnHide the Rows 12 to 15
'You can use Hidden Propery of Columns
Columns("E:G").Hidden = True 'It will Hide the Rows E to G
Columns("E:G").Hidden = False 'It will UnHide the Rows E to G
End Sub

Excel VBA Macros for Beginners- Novice Example 12: How To Insert And Delete Rows And Columns

This example will show you how to insert or delete the rows and columns using VBA.

'Example 12: How To Insert And Delete Rows And Columns
Sub sbExample12()
'You can use Insert and Delete Properties of Rows
Rows(6).Insert 'It will insert a row at 6 row
Rows(6).Delete 'it will delete the row 6
'You can use Insert and Delete Properties of Columns
Columns("E").Insert 'it will insert the column at E
Columns("E").Delete 'it will delete the column E
End Sub

Excel VBA Macros for Beginners- Novice Example 13: How To Set The Row Height And Column Width

We can set the row height or column width using VBA. The following example will show you how to do this using VBA.

'Example 13: How To Set The Row Height And Column Width
Sub sbExample13()
'You can use Hidden Propery of Rows
Rows(12).RowHeight = 33
Columns(5).ColumnWidth = 35
End Sub

Excel VBA Macros for Beginners- Novice Example 14: How To Merge and UnMerge Cells

Merge method of a range will help you to merge or unmerge the cell of range using VBA.

'Example 14: How To Merge and UnMerge Cells
Sub sbExample14()
'You can use Merge Property of range
Range("E1:E5").Merge
'You can use UnMerge Property of range
Range("E1:E5").UnMerge
End Sub

Excel VBA Macros for Beginners- Novice Example 15: How To Compare Two Values – A Simple Example On If Condition

When dealing with Excel VBA, often we compare the values. A Simple Example On If Condition will help you to understand -How To Compare Two Values?

'Example 15:
'How To Compare Two Values – A Simple Example On If Condition
Sub sbExample15()
'A Simple Example On If Condition
'We will campare A2 and A3 value using If
If Range("A2").Value = Range("A3") Then
MsgBox "True"
Else
MsgBox "False"
End If

Excel VBA Macros for Beginners- Novice Example 16: How To Print 1000 Values – A Simple Example On For Loop

Another useful statement in VBA is For, helps to loop through the rows, columns or any number of iteration. See the belwo example to understand the For loop:

'How To Print 1000 Values – A Simple Example On For Loop
'A Simple Example On For Loop
'We will print 1- 1000 integers in the Column E
For i = 1 To 1000
Cells(i, 5) = i 'here 5= column number of E
Next i
'showing status once it is printed the 1000 numbers
MsgBox "Done! Printed 1000 integers in Column E"
End Sub

Excel VBA Macros for Absolute Beginners – Download file with 15 novice Examples:

You can download the below macro file and execute it to understand well.

ANALYSIS TABS – 15 Examples VBA Codes for Bigenners

Real-time Example Codes:

The below codes will help you to use VBA in real-time application while automating your tasks.

Most Useful Excel VBA Tips (100+ Most useful VBA Codes)

By |April 20th, 2013|Excel VBA|39 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

39 Comments

  1. Florin November 8, 2013 at 10:30 PM - Reply

    A BIG THANK YOU FOR THIS!!!

  2. stella November 16, 2013 at 10:14 AM - Reply

    HI PNRao,

    Thanks for sharing your wonderful knowledge, anyway I wonder if you can share real-life examples of VBA Macro project. And step-by-step guide for it.

    Thanks.

    • PNRao November 16, 2013 at 1:07 PM - Reply

      Hi Stella,

      Thanks for your commends and suggestions.
      Sure, I am working on real-time Excel VBA tools/projects and I can share as soon as possible.
      And Yes, I will prepare some step by step explained guides to explain the topics with real-time examples.

      Thanks-PNRao!

  3. ben April 18, 2014 at 9:51 AM - Reply

    you have a spelling error on here, “finction”

    • PNRao April 19, 2014 at 5:30 PM - Reply

      Thank you Ben- I corrected it.

  4. Jagga November 14, 2014 at 4:19 PM - Reply

    Hi PNRao,

    Thanks a lot for this wonderful tutorial

    • PNRao November 16, 2014 at 5:14 PM - Reply

      Thanks and welcome to analysistabs Jagga!
      Regards-PNRao!

  5. Austin March 18, 2015 at 3:20 PM - Reply

    Hello PNRao

    In a given data Table in two columns there are data’s, but along with those data’s there are blank cells in the columns. What would be the code to delete those blanks cells in the column of the data table.

    Regards,
    Austin

  6. Abhishek June 27, 2015 at 1:48 PM - Reply

    Hi Rao,

    Please correct me on this code to concatenate two text with space between them.

    Dim a$, b$
    a = InputBox(“Enter first name”)
    b = InputBox(“Enter last name”)

    MsgBox (“a” ,””, “b”)

    • PNRao June 27, 2015 at 9:12 PM - Reply

      Hi Abhishek,

      Here is the simple VBA Macro to accept the values and concatenate it:

      Sub sbConacatenateTwoStrings()
      Dim a$, b$
      a = InputBox("Enter first name")
      b = InputBox("Enter last name")
      MsgBox a & " " & b
      End Sub
      
  7. Abhishek June 27, 2015 at 1:52 PM - Reply

    Dim a$, b$, c$
    a = InputBox(“Enter first name”)
    b = InputBox(“Enter last name”)
    c = a + b
    MsgBox (c)

    • PNRao June 27, 2015 at 9:14 PM - Reply

      Yes! alternatively, we can use + symbol to concatenate:

      Sub sbConacatenateTwoStrings()
      Dim a$, b$, c$
      a = InputBox("Enter first name")
      b = InputBox("Enter last name")
      c = a + " " + b
      MsgBox (c)
      End Sub
      
  8. Raghavendra July 30, 2015 at 12:01 AM - Reply

    Hi PNRao,

    Thanks a lot for this wonderful tutorial. You are sharing valuable infromation for real learners. Thanks a lot.

    • PNRao July 30, 2015 at 11:07 AM - Reply

      Hi Raghavendra,
      Thanks for your valuable feedback. I am glad to know that you found our tutorials helpful.

      We are happy to hear such a great feedback from our readers.It motivates and helps us to work hard to meet our readers expectations.

      Thanks-PNRao!

  9. AJ August 8, 2015 at 10:22 AM - Reply

    Greetings Mr. Rao,

    Kindly accept our sincere regards to providing us such a wonderful platform to learn and understand the actual usage of VBA.

    Thank you – AJ

    • PNRao August 8, 2015 at 3:07 PM - Reply

      Hi AJ,

      Thanks for such a sweet feedback. We are glad to hear nice complements for our VBA users, it always motivates me to give my best and share more useful stuff for our users.

      Thanks-PNRao!

  10. Arshad August 16, 2015 at 3:52 PM - Reply

    Hi Rao,

    Need some help making a slightly complex tool. How much can you help me? Is there a way I could mail you? Where are you based?

    • PNRao August 17, 2015 at 8:59 PM - Reply

      Hi Arshad,

      Thanks for contacting us. We are based out of Bangalore, India.

      We are happy to help you in building tools using VBA, please let us know your requirement.

      Thanks
      PNRao
      email: info@analysistabs.com
      website: analysistabs.com

  11. Atif Khan August 19, 2015 at 12:39 AM - Reply

    Hi ,

    Hope you are doing good!

    Your website its really very good and helpful. I was trying to create an account at your website however, could not find an option to register. Please do help me out with path to create an account.

    I am learning how to create VBA Excel Macros. I have a question, no idea if this is the right way to ask..

    TransactionID Debit Credit
    NBC0010 51,878 0
    NBC0016 34,788 0
    NBC0033 42,356 0
    NBC0036 54,713 0
    NBC0040 0 33,584
    NBC0041 49,986 0
    NBC0045 0 49,986
    NBC0046 0 56,525
    NBC0048 0 40,434
    NBC0050 56,898 0
    NBC0052 0 56,898

    above is my data i want to highlight those debit transaction id which have equal credit amount. I am trying to solve it with IF condition.
    Awaiting for your reply.

    Thanks & Regards
    Atif Khan

    • PNRao August 19, 2015 at 12:44 AM - Reply

      Hi,

      Here is the macro for highlighting the matched credits with debits.

      Sub markCerditsForDebits()
      Dim i As Integer
      lastRow = 12
      For i = 2 To lastRow
      clrn = Application.WorksheetFunction.RandBetween(7, 50)
      For j = 2 To lastRow
      If Cells(i, 2) = Cells(j, 3) And Not (Cells(i, 2) = 0 Or Cells(j, 3) = 0) Then
      Cells(i, 1).Interior.ColorIndex = clrn
      End If
      Next
      Next
      End Sub
      

      Thanks
      PNRao

  12. Atif khan August 20, 2015 at 10:13 AM - Reply

    Thanks for your quick response. I really appreciate your assistance.

    • PNRao August 20, 2015 at 12:52 PM - Reply

      You are welcome Atif khan! Thanks-PNRao!

  13. Sumida August 24, 2015 at 11:17 PM - Reply

    HI,

    I have the below requirement :
    Need to extract the the table from particular table from database and need to analyse each column value such as how many null and special characters(i,e ?,#) in the column.
    Can we able to achieve this in VBA
    .Please advice

    • PNRao August 25, 2015 at 12:11 AM - Reply

      Hi Sumida,

      We can do this using ADO, please refer the below article to extract the data from database.

      http://analysistabs.com/excel-vba/ado-sql-macros-connecting-database/

      We are happy to provide our premium help to develop a tool (Excel VBA application) to achieve your requirement, please let us know if you want us to develop.

      Please feel free to contact us if in case of any questions.

      Thanks
      PNRao

  14. John November 28, 2015 at 7:08 AM - Reply

    Hi PNRao

    I am working on Excel to blink cells in a row, what i actually want is the text in a cell only to blink. I have this code which i got from the web and made some modification.

    i only have code on the module:

    Option Explicit

    ‘In a regular module sheet
    Public RunWhen As Double ‘This statement must go at top of all subs and functions

    Sub StartBlink()
    Dim cel As Range
    With ThisWorkbook.Worksheets(“Sheet1”)
    Set cel = .Range(“E2”)
    If cel.Value > .Range(“L2”).Value Then
    If cel.Font.ColorIndex = 3 Then ‘ Red Text
    cel.Font.ColorIndex = 2 ‘ White Text
    cel.Interior.ColorIndex = 3
    Else
    cel.Font.ColorIndex = 3 ‘ Red Text
    cel.Interior.ColorIndex = xlColorIndexAutomatic
    End If
    Else
    cel.Font.ColorIndex = 3 ‘Red text
    cel.Interior.ColorIndex = xlColorIndexAutomatic
    End If
    End With
    RunWhen = Now + TimeSerial(0, 0, 1)
    Application.OnTime RunWhen, “‘” & ThisWorkbook.Name & “‘!StartBlink”, , True
    End Sub

    Sub StopBlink()
    On Error Resume Next
    Application.OnTime RunWhen, “‘” & ThisWorkbook.Name & “‘!StartBlink”, , False
    On Error GoTo 0
    With ThisWorkbook.Worksheets(“Sheet1”)
    .Range(“E2”).Font.ColorIndex = 3
    .Range(“E2”).Interior.ColorIndex = xlColorIndexAutomatic
    End With
    End Sub

    Sub xStopBlink()
    On Error Resume Next
    Application.OnTime RunWhen, “‘” & ThisWorkbook.Name & “‘!StartBlink”, , False
    On Error GoTo 0
    ThisWorkbook.Worksheets(“Sheet1”).Range(“L2”).Font.ColorIndex = 3

    End Sub

    hope you can help with the code. thank you and more power. i love this site i can learn more on excel programming here. thanks

  15. Sandip Talware January 21, 2016 at 1:15 PM - Reply

    Thanks PNRao as I learnt so many new things for automations our day to day work. This is really a best website for VBA learning

    • PNRao January 21, 2016 at 9:33 PM - Reply

      Hi Sandip, Thanks for great feedback. I am glad that you are learning by visiting our blog.

      I have been using VBA from last 10 years. I recommend every IT person must learn to enjoy their work. It can finish most of your job and give you plenty of free time to relax and learn new things.

      Thanks-PNRao!

  16. SUGAPRIYA February 4, 2016 at 3:32 PM - Reply

    i have data in two different workbooks i wana to merge the data in these two worbooks in a new workbook using VBA CAN U PLS HELP MI

  17. jpau5148 February 27, 2016 at 12:13 AM - Reply

    Hi PNRao!

    After discovering this VBA tutorial website of yours, you are now my newest Excel Hero. Thank you and keep up the great work!

    ~ JP

  18. Sagar April 27, 2016 at 3:24 PM - Reply

    HI PN Rao,

    I have a requirement of reading hierarchical XML file to excel ( Reading entire xml or only particular nodes), can you provide the VBA script for this.

    Thanks,
    Sagar

  19. RAMANA RAO August 14, 2016 at 4:32 PM - Reply

    After discovering this VBA tutorial website of yours, you are now my newest Excel Hero. Thank you and keep up the great work!

    i am the great fan of your website.

  20. usama October 13, 2016 at 7:19 AM - Reply

    Range(“B1:B5”).Interior.ColorIndex = 5 ‘ 5=Blue
    what does this main 5 ‘ 5

    • PNRao October 13, 2016 at 8:40 PM - Reply

      Hi Usama,

      We are using single quote(‘) to write comments while writing macro in VBA editor window.

      The below statement 5 represents the index color number. ie Blue Color. You can change this number according to your wish.
      Range(“B1:B5”).Interior.ColorIndex = 5

      Hope you understand.

      Regards-Valli

  21. usama October 13, 2016 at 7:33 AM - Reply

    I do not understand this
    ‘Example 5: How To Change The Text To Upper Case Or Lower Case
    Sub sbExample5()

    ‘You can use UCase function to change the text into Upper Case
    Range(“C2”).Value = UCase(Range(“C2”).Value)

    ‘You can use LCase function to change the text into Upper Case
    Range(“C3”).Value = LCase(Range(“C3”).Value)

    End Sub

    • PNRao October 13, 2016 at 8:34 PM - Reply

      Hi Usama,

      If we want to change any text to either upper case letters or lowercase letters we using function UCase and LCase.

      Please run the macro for better understand.

      Here is the example.
      Lets say Range(“C2″)=”Analysistabs” in worksheet.

      To Convert to upper case:
      Range(“C2”).Value = UCase(Range(“C2”).Value)
      Range(“C2”).Value=UCase(“Analysistabs”)
      Output: Range(“C2”).Value=ANALYSISTABS

      To Convert to lower case:
      Range(“C2”).Value = LCase(Range(“C2”).Value)
      Range(“C2”).Value=LCase(“Analysistabs”)
      Output: Range(“C2”).Value=analysistabs

      Hope you understand the macro.

      Regards-Valli

Leave A Comment