How to insert and run VBA code in Excel - tutorial for beginners

This is a short step-by-step tutorial for beginners showing how to add VBA code (Visual Basic for Applications code) to your Excel workbook and run this macro to solve your spreadsheet tasks.

Most people like me and you are not real Microsoft Office gurus. So, we may not know all specificities of calling this or that option, and we cannot tell the difference between VBA execution speed in different Excel versions. We use Excel as a tool for processing our applied data.

Suppose you need to change your data in some way. You googled a lot and found a VBA macro that solves your task. However, your knowledge of VBA leaves much to be desired. Feel free to study this step-by-step guide to be able to use the code you found:

Insert VBA code to Excel Workbook

For this example, we are going to use a VBA macro to remove line breaks from the current worksheet.

  1. Open your workbook in Excel.
  2. Press Alt + F11 to open Visual Basic Editor (VBE).
    Excel Visual Basic Editor window
  3. Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu.
    Insert a new VBA module to the Excel workbook
  4. Copy the VBA code (from a web-page etc.) and paste it to the right pane of the VBA editor ("Module1" window).
    Paste the code to the VBA module

  5. Tip: Speed up macro execution

    If the code of your VBA macro does not contain the following lines in the beginning:

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Then add the following lines to get your macro to work faster (see the screenshots above):

    • To the very beginning of the code, after all code lines that start with Dim (if there are no "Dim" lines, then add them right after the Sub line):
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
    • To the very of the code, before End Sub:
      Application.ScreenUpdating = True
      Application.Calculation = xlCalculationAutomatic

    These lines, as their names suggest, turn off screen refresh and recalculating the workbook's formulas before running the macro.

    After the code is executed, everything is turned back on. As a result, the performance is increased from 10% to 500% (aha, the macro works 5 times faster if it continuously manipulates the cells' contents).

  6. Save your workbook as "Excel macro-enabled workbook".

    Press Crl + S, then click the "No" button in the "The following features cannot be saved in macro-free workbook" warning dialog.
    The following features cannot be saved in macro-free workbook

    The "Save as" dialog will open. Choose "Excel macro-enabled workbook" from the "Save as type" drop-down list and click the Save button.
    Save your workbook as Excel macro-enabled workbook

  7. Press Alt + Q to close the Editor window and switch back to your workbook.

How to run VBA macros in Excel

When you want to run the VBA code that you added as described in the section above: press Alt+F8 to open the "Macro" dialog.

Then select the wanted macro from the "Macro Name" list and click the "Run" button.
Press Alt+F8 to run the VBA macro

Table of contents

256 comments

  1. please explain me how to enable or disable .bas in excel

  2. Good afternoon

    I have a spreadsheet with about 49,000 lines in it, I need to insert 9 lines between each line of text and am having some issues with the Macro to use. There is only 1 column of text and I just need to insert 9 blank lines between each line of text.

    Is someone able to point me in the right location to get the code to create the macro to do this please?

    Thank you in advance
    Rob Garven

  3. Less tension more work - more work less tension

    How.. ?

  4. hi, i'm jey i want some help i want the exell sheets chenge in between some time limit how to develop it plese send me the code or tips

  5. I will like to code my page in excell pla how do I do it.

  6. Hi,

    I'm trying to make it so that only one person can access the workbook at once and if anyone else tries do use it they will get a message saying that it's in use by another user.

    I have found the code below on another forum but it doesn't seem to be working - The Macro name won't appear when I try to run it. I think it's to do with the beginning section of code? Your help would be much appreciated!

    Many thanks, Tamsyn

    Private Sub Workbook_Open()
    With Me
    If .ReadOnly Then
    Call MsgBox(Prompt:="The workbook is already open by another user!", _
    Buttons:=vbExclamation + vbOKOnly, _
    Title:="Closing...")
    Call .Close(SaveChanges:=False)
    End If
    End With
    End Sub

  7. can i ask one code in excel macro how can we "enter" using a macro code

  8. If I create a VBA code in Excel version 2010 will it work by someone who has other versions of Excel including 2013 as 2007?
    I want to send a form where the letters automatically change to UPPERCASE when anyone with any kind of excel version types in the cell.

    • Hello Christophe,

      Yes, of course. Your code should work in all the versions of Excel you mentioned and even in Excel 2003.
      It won't work in earlier versions only if you are calling new functions of Excel 2010 – 2013 there, but it doesn't look like your case.

  9. Thank you for this! You saved me a ton of work of having to do this manually! Very simple instructions with amazing results.

  10. How can I create the custom toolbar with VBA code as addins sothat it can work for any computer.
    or there is any installation file that I need to create.
    Please help me??????????

  11. Hello, This is Chetan
    I have created one custom toolbar as addins that has so many buttons with assigned macros and my query is When I am giving to the client he is not able to use that toolbar???? Here I am able to export the customization that i did but unable to export the VBA code that I have written, after some R&D I came to the point there is one Personal.xlsb file that I need to copy to the client machine on specific XLSTART folder but it's not working because the Psersonal.xlsb is specific to me (specified user who has created )on other system it is asking for my computer location..
    So How can i resolve the issue so that it can work for any computer
    please help me???????????

  12. What is the desired output of the above Macro file?
    Please let us know since we are new to this.

  13. what if i already have an VB code and i just want to run this code using excel !
    do you have any idea about how to do this ?

    • Hello Lina,

      This is exactly what is told in the article. Could you please specify on what step you have difficulties?

  14. I want to appended 50 excel files on daily basis. please tell me how to do that on micro level

  15. Alexander, great post.
    I have a doubt about sending a "macroed" excel worksheet to execute in another computer.
    The end user will need to have any special permission to execute macros? (no ActiveX included)
    Because i am planning to distribute an excel file (made in 2010 or 2013), with macros/forms, but i am afraid that it will not be compatible with other excel versions or missing configuration/installed apps in users computers (and i don't have control over those as the file will be to open public).

    Do you know where i can get more information about this?
    Thanks in advance!

  16. Hi,

    I want to lock some of cells ( in all worksheet within a workbook including adding new worksheet, and also do not want to move excel cursor on that cells.
    Where should I type code i.e in module or at workbook.

    Example:
    Lock cell (A1:C2) and (D2:F4) and (B1:B12) in worksheet as well as adding new worksheet withing a workbook.

  17. I am trying to input VBA code to determine the indentation in an excel 2010 row.

    the followin was taken from a different forum but When I insert the the code I get and run error message compile error ambuguous name detected: indenture

    this is what I pasted into the window
    Sub indenture()
    Function indenture(r As Range) As Integer
    indenture = r.IndentLevel
    End Function

    • Hello, Paul,

      The first line is redundant, you just need:

      Function indenture(r As Range) As Integer
      indenture = r.IndentLevel
      End Function

  18. Excellent article, I don't think it could have been made any more simple. Thanks

  19. I can't understand properly.

  20. Is cut and paste between worksheets disabled in macro-enabled worksheets? I thought this would be an easy answer for the internet, but can't find it.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)