REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

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.

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

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

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

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

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

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

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

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

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

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:TempMyNewWorkbook.xls"
    ActiveWorkbook.SaveAs "MyNewWorkbook.xls"

    ActiveWorkbook.Close

End Sub

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

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

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

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

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

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

Download: Excel VBA Macros for Absolute Beginners Example Workbook:

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

ANALYSIS TABS – 15 Examples VBA Codes for Beginners

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)

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: Excel VBATags: , Last Updated: March 6, 2023

40 Comments

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

    A BIG THANK YOU FOR THIS!!!

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

    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.

  3. PNRao November 16, 2013 at 1:07 PM

    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!

  4. ben April 18, 2014 at 9:51 AM

    you have a spelling error on here, “finction”

  5. PNRao April 19, 2014 at 5:30 PM

    Thank you Ben- I corrected it.

  6. Jagga November 14, 2014 at 4:19 PM

    Hi PNRao,

    Thanks a lot for this wonderful tutorial

  7. PNRao November 16, 2014 at 5:14 PM

    Thanks and welcome to analysistabs Jagga!
    Regards-PNRao!

  8. Austin March 18, 2015 at 3:20 PM

    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

  9. PNRao March 21, 2015 at 2:59 PM

    Hi Austin,

    refer the below topic, we have provided # of example for verity of requirements to delete rows/cells.
    http://analysistabs.com/vba/delete-row-excel-example-macros/

    Thnaks-PNRao!

  10. Divya June 18, 2015 at 11:10 PM

    i need to execute certain vba macro code could u help me in guiding

  11. PNRao June 19, 2015 at 2:09 PM

    Hi Divya,

    Please let me know your requirement.
    You can send sample files(if any) to my email: info@analysistabs.com

    Thanks-PNRao!

  12. Abhishek June 27, 2015 at 1:48 PM

    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”)

  13. Abhishek June 27, 2015 at 1:52 PM

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

  14. PNRao June 27, 2015 at 9:12 PM

    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
    
  15. PNRao June 27, 2015 at 9:14 PM

    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
    
  16. Raghavendra July 30, 2015 at 12:01 AM

    Hi PNRao,

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

  17. PNRao July 30, 2015 at 11:07 AM

    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!

  18. AJ August 8, 2015 at 10:22 AM

    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

  19. PNRao August 8, 2015 at 3:07 PM

    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!

  20. Arshad August 16, 2015 at 3:52 PM

    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?

  21. PNRao August 17, 2015 at 8:59 PM

    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

  22. Atif Khan August 19, 2015 at 12:39 AM

    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

  23. PNRao August 19, 2015 at 12:44 AM

    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

  24. Atif khan August 20, 2015 at 10:13 AM

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

  25. PNRao August 20, 2015 at 12:52 PM

    You are welcome Atif khan! Thanks-PNRao!

  26. Sumida August 24, 2015 at 11:17 PM

    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

  27. PNRao August 25, 2015 at 12:11 AM

    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

  28. John November 28, 2015 at 7:08 AM

    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

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

    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

  30. PNRao January 21, 2016 at 9:33 PM

    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!

  31. SUGAPRIYA February 4, 2016 at 3:32 PM

    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

  32. jpau5148 February 27, 2016 at 12:13 AM

    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

  33. Sagar April 27, 2016 at 3:24 PM

    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

  34. RAMANA RAO August 14, 2016 at 4:32 PM

    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.

  35. PNRao August 14, 2016 at 11:30 PM

    Thanks and Welcome to Analysistabs.com!
    Join in our Forum and let’s get more interacted.

    http://ask.analysistabs.com/

    Thanks-PNRao!

  36. usama October 13, 2016 at 7:19 AM

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

  37. usama October 13, 2016 at 7:33 AM

    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

  38. PNRao October 13, 2016 at 8:34 PM

    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

  39. PNRao October 13, 2016 at 8:40 PM

    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

  40. Vivek Mahure March 21, 2019 at 2:43 AM

    Sir,
    i need a vba program in excel where i can simulate the motor start stop. Please help me

    1) I have to command buttons (Start & Stop) and an Oval shape as Motor
    2) When I press Start button the oval shape shall be filled Red color and then it should be continuously blinking.
    3) when I press Stop button it should stop blinking and shall be filled with Green color.

Leave A Comment