VBA Code Excel Examples Macros Useful 100+ How Tos for Basic and Advanced Users

VBA Codes Excel Examples Macros Useful 100+ How To Codes explained for Basic Beginners to Advanced VBA users. Tutorials to learn Excel 2003, 2007, 2010, 2013 Macros and Mastering in VBA. Selected examples to deal with different objects, methods and properties in Excel. Numerous free most useful VBA codes and tips will help you to deal with various Excel Objects like Cell, Range, Worksheets, Workbooks, Application, Charts, Pivot Tables, Hyperlinks, functions, User Forms, MsgBox, ListBox, ComboBox. Also provided free example codes to deal with MS Word, PowerPoint, Outlook, Access, Other Applications and File Handling. If you think that I missed any useful code, please feel free to write us. We will respond with a solution with in couple of days and publish here to make it available for all VBA Users.

Most Useful Excel VBA Codes - 100+ Examples for Basic and Advanced Users

The Most Useful VBA Codes Excel Examples Macros! Learning Path

Learn VBA Codes Excel Examples Macros and do almost everything! We have explained verity of examples to cover most frequently used codes. Start learning…

Cells and Range Objects : VBA Codes Excel Examples Macros

Reading data from a Cell was my first exciting VBA Code when I stared learning Excel VBA. Then I successfully write data to Excel Cells. That was my first happy moment which motivated me to engage with VBA from last 10 years.

Here are the common VBA Codes Excel Examples Macros to deal with Cell and Range Objects of Worksheet.
You can find examples on reading and writing the data, selecting, copying and pasting the data.
Clearing, changing font color, font to bold, background color of cells or range. We can also see the examples on merging cells, adding comments and changing the font case to lower or upper using Excel VBA.


Top

Rows and Columns Objects: VBA Codes Excel Examples Macros

Cell and Range objects helps to reading and writing the data from worksheet. Now we will look into Rows and Columns of the Worksheet, helps to show or hide the data. Here you can find the VBA Codes Excel Examples Macros on delete rows, columns, change row height, column width. Hiding or un-hiding columns or rows. Inserting or deleting Rows or Columns. And finding the Last row, or columns in the worksheet in different situations.


Top

Worksheet and Workbook Objects: VBA Codes Excel Examples Macros

Now we will see the VBA Codes Excel Examples Macros on Worksheet and Workbook Objects. Here you can find creating new workbook, opening, closing and saving workbook. And example to show running a macro on opening or staring the workbook. We will also see how to protecting or unprotecting Excel workbooks or worksheets, Copying the data from one worksheet to another worksheet.
Hiding and unhiding worksheets. Changing the tab color of worksheet. Activating workbooks or worksheet, etc.


Top

Hyperlink: VBA Codes Excel Examples Macros

Hyperlinks are most widely used concepts in Excel. We generally use hyperlinks to navigate or open a file, link or folder. But we can do many other things using Hyperlinks. Examples in this topic will show you the power of Hyperlinks in Excel VBA. We have covered the following examples in this tutorial:

  • Add Create Hyperlinks in Excel VBA
  • Removing Hyperlinks in Excel VBA
  • VBA Open File Folder Website Using FollowHyperlink method in Excel
  • VBA Create Send Emails Using FollowHyperlink Method – Send Keys in Excel

Hyperlinks in Excel VBA – Explained with Examples!

Charts : VBA Codes Excel Examples Macros

Another powerful tool in Excel is charting. You can create rich visualized dashboards using Excel Charts and VBA. We have covered most commonly used Charting VBA Codes Excel Examples Macros. In this topic will show you how to deal with different chart objects to automate the charting process using VBA. Examples on creating charts, changing chart types. Changing chart axes titles, chart title, axis format, primary and secondary axes. We have provided more than 33 example to cover A-z of Excel Chart VBA.

  • Example tutorials on Creating Charts using Excel VBA
  • Example tutorials on Chart Type using Excel VBA
  • Example Tutorials on Formatting Chart Objects using Excel VBA
  • Example Tutorials on Chart Collection in Excel VBA
  • Other useful Examples and tutorials on Excel VBA Charting
  • Excel VBA Charting Constants and Enumeration

Excel Chart VBA Examples and Tutorials

Tables: VBA Codes Excel Examples Macros

Tables in Excel help to manage our data in Excel and give the more control over the data. In this example we will see the different VBA Codes Excel Examples Macros like creating tables, sorting tables data and applying filters in the data. We have provided following example to deal with Tables using Excel VBA.

  • Create Tables in Excel VBA
  • Sorting Tables in Excel VBA
  • Filtering Tables in Excel VBA
  • Clear Toggle Table Filters in Excel VBA

Tables in Excel VBA – Explained with Examples!


Top

Pivot Tables: VBA Codes Excel Examples Macros

Pivot tables help us to summarize the data and analyze it. VBA Codes Excel Examples Macros provided for creating pivot tables, pivot charts, adding calculated, changing row fields, column fields, value field fields in pivot tables using VBA. We have provided the following example to deal with pivot tables using Excel VBA, we will add some more examples to do more tasks using pivot tables.

  • Creating Pivot Tables in Excel VBA
  • Create Pivot Chart using Excel VBA
  • Create Pivot Column Chart using Excel VBA
  • Create Calculated Pivot Field in Excel VBA

Pivot Tables in Excel VBA – Explained with Examples!

Names: VBA Codes Excel Examples Macros

Names are another time saving feature in the Excel. We can define the names to Cells, Ranges and Tables and use them across the worksheets in the workbook. Her we will see how to add or remove the Names using VBA. Hiding and un-hiding names from the users. Follwing examples covered in this topic to deal with Names using Excel VBA.

  • Adding Names in Excel VBA
  • Deleting Names in Excel VBA
  • Hide UnHide Names in Excel VBA

Names in Excel VBA – Explained with Examples!


Top

Other Applications: VBA Codes Excel Examples Macros

VBA is powerful, it can interact with the other applications like MS Word, PowerPoint, Access, Outlook, Internet Explorer, VBScript etc. Here are examples to deal with other application from Excel. Below examples are covered in this tutorial:

  • Interact with PowerPoint from Excel VBA
  • Dealing with MS Word From Excel VBA
  • Interact with MS Access from Excel VBA
  • Interact with Outlook from Excel VBA
  • Dealing with Internet Explorer
  • Dealing with Other Applications from Excel VBA – Calculator
  • Run VBScript from Excel VBA
  • VBA to Attach Send An Excel Chart to Outlook Email

Excel VBA to Interact with Other Applications

Application Object: Excel VBA Codes Examples Macros

Mastering the Application Objects Examples help you to fasten your VBA programs and writing optimized code in Excel VBA. Examples for stopping or displaying application alerts, stopping or enabling screen updating, stopping or enabling application events. The following example procedures and functions are covered in this topic to fasten and speed up VBA code processing.

  • Stop Screen Updating
  • Stop Events in Excel VBA – Disable Enable
  • Stop Application Alerts in Excel VBA – Disable Enable
  • Display Progress on Statusbar in VBA Excel
  • Set Windows State in Excel VBA – Minimize Maximize Normal
  • Toggle Full Screen in Excel VBA
  • Get User Name in VBA Excel
  • Stop Calculations in Excel VBA – Manual Automatic
  • Open Visual Basic Editor (VBE) – Open Module with VBA
  • VBA to Exit from Procedure or Function

Fasten VBA Code – Application Objects Explained with Examples

File Handling: Excel VBA Examples Macros Codes

File handling examples are provided to creating deleting files, folders. Copying Files and Folders and Moving from one location to another location. And displaying file or folder dialog boxes to browse the files or folders. And check if files exists in a folder using VBA.

  • Check if Folder Exists using Excel VBA
  • Opening Folders using VBA Excel
  • Creating Folders in Excel VBA
  • Copying Folders From One Location to Another in Excel VBA
  • Move Folder From One Location to Another in Excel VBA
  • Deleting Folders in VBA Excel
  • Make File Read Only in VBA Excel
  • Copy all Excel Files One Folder to Another in VBA Excel
  • Opening Files Using File Dialog Box in Excel VBA
  • Customize File or Folder Dialog Box in VBA Excel
  • Excel VBA File Dialog Box – Displaying Vanilla Dialog Box to Pick Files

Folders and File Handling in Excel VBA

Some more VBA examples are added to deal with Files and Folders using Excel VBA:


Top

Miscellaneous Excel VBA Examples Macros Codes




Valli

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

You may also like...

47 Responses

  1. Chitanya says:

    Hi,

    How to add excel chart to email body?

    Thanks,
    Chitanya

  2. PNRao says:

    Hi Chaitanya,

    Welcome to ANALYSISTABS!

    Please refer the following page:

    How to Add and Send an Excel Chart to an Outlook email

    Thanks-PNRao

  3. Chitanya says:

    Thnk you very much.

  4. PNRao says:

    You are most welcome!

  5. Spoorthi M says:

    Hi Team,

    A very intersting site to learn VB. The thing which i liked here most is user friendly & explanation of each n every concepts very precisely. Applause for the whole team. Great JOB!!!

    Regards
    Spoorthi M

  6. Lody says:

    Hi PNRao,
    It’s very interesting site to learn step by step VBA in easy way. Please allow me to copy your lesson learning. thank you

    • PNRao says:

      Hi Lody,
      Thanks for visiting us. Unfortunately you should not copy the content from our site as it is against our terms and policies. We are a team working in day and night to build this. You can feel free to use our site as a reference for your queries our for learning purpose. We don’t allow the people to copy and duplicate our content.

      For more details, please read our terms and conditions:

      Privacy Policy  |  Terms of Use

      Thanks-PNRao

  7. Amrut Parab says:

    Can we get 100+ Examples for Basic and Advanced Users in PDF format So that we can work or Refer while offline

    • Amrut Parab says:

      Ok I got it, I read last comment Just now, Thanks for Good Effort,This website is best for all freshers who want to learn VBA programing, you are Amazing Guys.
      May god Bless you.

      • PNRao says:

        Hi Amrut,

        Thanks for visiting our site.

        Yes, I am working on 100+ Excel VBA Examples and providing to my blog readers as soon as possible (with in a month).

        Thanks
        PNRao

  8. Mahendra says:

    Hi,

    I would like to know about the matrix multiplication,transpose and inverse of a matrix (mXn). Kindly help me on this.

    Thanks
    Maahendra

  9. Vivek says:

    Hi ,

    I am new to Excel VBA and my need is to create multiple worksheets on the same workbook based on the values from a range of cells(Text) from the Index Sheet and also i have to give hyperlink to the corresponding sheets.
    For Eg:
    cell A1 contains “Name”, a work sheets should be created on this name and Cell A1 should have hyper link to the created worksheet.
    cell A1 will be in Index or 1st page.

    Thanks

  10. Vivek says:

    Hi,
    I am very new to Excel VBA. I want to add multiple worksheets in the same workbook and i have to rename it with the values from a range of cells from the Index page. And also each sheet should be hyper linked to the corresponding Cell in the range.
    For Eg:
    Cell A1 has “Name”, i have to create a worksheet with that name and that created sheet has to be hyper linked with Cell A1.

    Thanks

    • PNRao says:

      Hi Vivek,

      Here is the example program for your requirement.

      Sub sbCreateTOCSheetHyperLinks()
           
          iCntr = 5 ' worksheets names starts from 5th row
          
          'loop until the cell is blank
          Do While Sheets("Index").Range("A" & iCntr) <> ""
           
              'If you want to add new worksheets from last worksheet
              Sheets.Add After:=Sheets(ActiveWorkbook.Worksheets.Count)
              ActiveSheet.Name = Sheets("Index").Range("A" & iCntr)
              
              Sheets("Index").Activate
              'delete if any existing hyperlink
              Range("A" & iCntr).Hyperlinks.Delete
              
              'add hyperlinks
              Sheets("Index").Hyperlinks.Add Anchor:=Range("A" & iCntr), Address:="", _
              SubAddress:="'" & Sheets("Index").Range("A" & iCntr).Value & "'!A1", _
              TextToDisplay:=Sheets("Index").Range("A" & iCntr).Value
              
              iCntr = iCntr + 1
          Loop
           
           
      End Sub
      

      You can download the example file form our downloads page: Download Now

      You can use our add-in to create better TOC in easy way.

      Hope this helps!

      Thanks-PNRao!

  11. Jaimin says:

    How to write vba macro to copy data from one sheet to another without using “copy” command ?

    • PNRao says:

      Hi Jaimin,

      You can use for loop to get the data from one sheet to another sheet. For instance, if you want to get the data of cells A1:A20 from Sheet2 to cells B1:B20 of sheet1.


      For iCntr=1 to 20
      Sheets1.Cells(iCntr,2).value=Sheets2.Cells(iCntr,1)
      Next

      Its depending up on your requirement. If you want to copy only few cells. you can do it without using for loop.

      However Copy command [Sheets2.Range("A1:A10").Copy Destination:=Sheets1.Range("B1)] works more faster, you can copy the data including formats.

      Hope this helps!
      Thanks – PNRao!

  12. Jagadesh.K says:

    Hi Team,

    I want to Split data into multiple worksheets based on column(this column will contain numbers)

    with VBA code. It will be more helpful if you would help on this.

    Thanks

  13. Mukund Joshi says:

    Hi,
    I am at a beginner level of learning VBA. So far it really helped me a lot at my workplace to drag my manager’s attention. I prefer referring your site and I really love the way you interpret useful info in the simplest way. Your work actually attracted me towards learning more & more of VB. Thank you for your hard work & knowledge sharing!

  14. nitin chugh says:

    how to send email through excel using macro or without macro

  15. Mark Paull says:

    Hi great site – i’m self learning VBA mostly through using the record macro function and analysing the coded output. Trying to make use of the text held within an object to drive an action based on the digit held within this object.

    Fantastically built side in terms of content and style

  16. Patrizio says:

    Hi,
    first of all congrats for the amazing helpful website.
    I am wondering if you could help me with this VBA issue:
    1) Hypothesize we have in “sheet1″ cell a1, a3, a5, a7 and so on with a gap of 2.
    2) I would like a Macro that links (instead of copy if possible) cell b1, b2, b3, b4 and so on of “sheet2″ to cell a1, a3, a5 … of “sheet1″ (basically removing the Gap)

    Hope I was clear.

    Thanks a lot for your help,

    Patrizio

    • PNRao says:

      Hi Patrizio,

      Thanks for your comments. Here are VBA macros for your requirement.

      Sub sbFill_OddRows()
      'Declaration
      Dim iCntr, jCntr, lastRow As Long
      'iCntr to iterate Sheet2
      'jCntr to iterate Sheet1
      'lastRow for storeing last row with data in Sheet2
      
      lastRow = 25
      'Assuming you have data up to 25th row in Sheet2
      'If the last row is not fixed, then check
      'our most useful vba examples to find Last row
      'in different scenarios
      
      jCntr = 1
      For iCntr = 1 To lastRow
          Sheet1.Cells(jCntr, 1) = Sheet2.Cells(iCntr, 1)
          jCntr = jCntr + 2 'increasing jCntr to skip one row
      Next
      
      End Sub
      
      '*To fill even rows: Same as above with one change
      Sub sbFill_EvenRows()
      Dim iCntr, jCntr, lastRow As Long
      lastRow = 25
      
      jCntr = 2 ' 1 to fill Odd rows, 2 to fill Even rows
      For iCntr = 1 To lastRow
          Sheet1.Cells(jCntr, 1) = Sheet2.Cells(iCntr, 1)
          jCntr = jCntr + 2
      Next
      
      End Sub
      

      Hope this helps, let me know if you need more clarification.

      Thanks-PNRao!

  17. E Sivakuamr says:

    Hi,
    I want to insert multiple objeats using VBA,Please help me on this.

    Ex:Coulamn c is having the 100 objects names,I want to search in my system and i want to insert repect to that coulmn.

    • PNRao says:

      Hi Sivakumar,
      Thanks for writing us! Could you please specify your problem in more detailed.

      I understand that you will have your object names in C column of on of the sheet and you want to insert them just right to that respective cell. The thing I could not understand is Object: What are you referencing as object? is this a image?… please elaborate your question.

      Thanks-PNRao!

  18. Ashish says:

    hi this is my query, could you please help
    i have these percentages
    35%,40%,25% and then i have a value say 50,000
    i have made a tab on which i click and it should calculate the percentages itself

    will the same macro run for multiple entries?

    • PNRao says:

      Hi Ashish,

      I am assuming you have your Value at A1 and Percentages at B1,B2,B3 and you want to print the percentage of that values in C1,C2,C3.

      for this you can use simple formula in C1=A1*B1/100 [Example, if you have 200 in A1 and 10 in B1, this will pront 20 in C1]

      Sorry, your question is not clear, please provide more information. So that I can help you.
      Thanks-PNRao!

  19. Anurag Mishra says:

    Hi Team,

    I want to extract data from MSSQL server between two date and this date must be insert by user in the text box of a vba form so give me an example where we take the date from text box n put it on SQL query and direct extract the data from the server.

    Thankyou

  20. sravan says:

    Hi,

    Can you please show me the code to insert alphabets from A to Z in Horizontally and vertically.

    Thanks,
    Sravan

    • PNRao says:

      Here you go…

      Sub sbPrintAlphabetsVertically()

      For iCntr = 1 To 26
      Cells(iCntr, 1) = Chr(64 + iCntr)
      Next

      End Sub

      Sub sbPrintAlphabetsHorizonatally()

      For iCntr = 1 To 26
      Cells(1, iCntr) = Chr(64 + iCntr)
      Next

      End Sub

      Thanks-PNRao!

  21. Aslam says:

    Hi

    I am new to VBA can u please show me the code to search amount with negative sign in particular column and move it next/ before that column.

    Thanks in advance,

    Aslam

    • PNRao says:

      Hi Aslam,
      You can just check if the Column A value is <0 then print into Column B. Here is the Example:

      Sub sbPrintNegativeColumnAValuesInToColumnB()
      Dim iCntr, jCntr, lastRow As Long
      lastRow = 50 ‘ Last Row of Column A with data:You can change this
      For iCntr = 1 To lastRow
      If Cells(iCntr, 1) < 0 Then Cells(iCntr, 2) = Cells(iCntr, 1)
      Next
      End Sub

  22. arc says:

    Similar to PNRao response. I want to selectively pick data out of a column (the picks will be stepped apart by 3) , select the picks and paste to another column in contiguous fashion (meaning continuous-no step). This works in manually recording a macro and running it but I cannot make it work with programmed VBA language. Excel 2003.
    Thanks
    arc

    • PNRao says:

      Hi Arc,
      You can use STEP statement. Here is the example code:

      Assuming you have data in Column A: A1,A4,A7,…..
      And you want to print in Column B: B1,B2,B3…

      Sub sbPrintColumnAByStep3InColumnB()
      Dim iCntr, jCntr, lastRow As Long
      lastRow = 50 ' Last Row of Column A with data:You can change this
      jCntr = 1 ' Counter for Column B
      For iCntr = 1 To lastRow Step 3
      Cells(jCntr, 2) = Cells(iCntr, 1)
      jCntr = jCntr + 1
      Next
      End Sub

      Thanks-PNRao!

  23. gc pathalla says:

    Great Site!!

    Very Helpful!!

    Thanks a Lot!!

  24. Satish Telgote says:

    Hi, I am beginner, please advise me how to create tracker in VB using excel sheet. In a Excel sheet there are data in 5 column with titles, wanted to create Title on VB template. If I search with 1st Co data rest 4 columns data should be reflect e.g Column A Contain Dates, Column B contains Days, Column C Contains Activity etc

    If the VB template if I input Dates Field all 4 Fields reflects relevant data

    Regards
    Satish Telgote

    • PNRao says:

      Hi Satish,

      You can use Vlookup formula to achieve this. If you want to build your own function, you can loop through the each row and display the column data wherever its matching the given criteria.

      The sample code looks like this:

      Dim dtAVal as Date
      Dim lastRow as Long
      Dim blnFound as Boolean
      blnFound =False
      dtAVal = cdate(InputBox1.value) ' Your input date to find the respective data
      lastRow=200 'Your last row in the worksheet

      'looping through all rows and trying to get match row number
      For iCntr=1 to lastRow
      If Cells(iCntr,1)=dtAVal then
      blnFound =True
      exit For
      End if
      Next

      if blnFound =True then
      'Fill the respective data into other columns
      TextBox2.Value=Cells(iCntr,2) 'Column B
      TextBox3.Value=Cells(iCntr,3) 'Column C
      TextBox4.Value=Cells(iCntr,4) 'Column D

      end if

      Hope this helps!
      Thanks-PNRao!

  1. March 24, 2014

    Good info

    I have a business blog through which I offer certain goods and services. It is also a general-information blog, however. I am seeking to move it to another blog host. I know that blogspot offers user-sponsored advertising, but are there any other free…

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>