• Ablebits blog
  • Excel macro

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

Alexander Frolov

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 .

  • Open your workbook in Excel.

Excel Visual Basic Editor window

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.

The following features cannot be saved in macro-free workbook

  • 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.

Press Alt+F8 to run the VBA macro

You may also be interested in

  • How to count and sum cells by color in Excel - VBA macro
  • How to enable macros in Excel
  • How to record a macro in Excel
  • Two best ways to convert numbers to words in Excel
  • How to merge Excel files with VBA
  • Custom user-defined functions vs. VBA macros

Table of contents

Ablebits.com website logo

245 comments

add macro using vba

Hi, my code was working previously but now isn't. I am not sure why. It gives me runtime error of 13

Sub Get_Plate_A_Data()

Dim FileToOpen As Variant Dim OpenBook As Workbook Application.ScreenUpdating = False

FileToOpen = Application.GetOpenFilename(Title:="Browse for your raw data file to import", FileFilter:="Excel Files (*.xls*), *xls*")

If FileToOpen False Then Set OpenBook = Application.Workbooks.Open(FileToOpen) OpenBook.Sheets(1).Range("C126:AP221").Copy

ThisWorkbook.Worksheets(Sheet1).Range("C39").PasteSpecial xlPasteValues OpenBook.Close False

End If Application.ScreenUpdating = True

add macro using vba

Hi! Your request goes beyond the advice we provide on this blog. We don't help with writing VBA code.

add macro using vba

this feature is not available how to solve

Hi! I can't see your workbook, but perhaps these instructions will help you: How to enable and disable macros in Excel .

add macro using vba

I’d like to take names from a column and have the entire row copied in a separate sheet. Data example: List of names in column D If the name is “Cody Hall” then I want the entire row copied (not deleted) to a sheet named “Cody”

I’ve attempted to use VBA code to accomplish this but to no avail.

Hi! To get a row from the table based on the value in a column, you can try using the FILTER function. Read more: Excel FILTER function - dynamic filtering with formulas .

add macro using vba

I want to delete A2 to A35 column using VB in excel, please help me

add macro using vba

i'm a registered user of ablebits excel ultimate suite. is there a way to run a certain ablebits tool [in my case, transpose] from vba or in a recorded macro? if it is not possible, this would be great addition to the ultimate suite

Hi! Unfortunately, the capabilities of VBA do not allow you to do this.

add macro using vba

Hey, can you tell me about the basis of VBA apply in Excel. I want to make my future in this field.

add macro using vba

Hello, thank you for posting this. I'm currently trying to use it and I have followed the steps as written but when I go to the run the macro I get a syntax error on this line:

Set cellsColorSample = Application.InputBox( _ "Select sample color:", "Select a cell with sample color", _ Application.Selection.Address, Type:=8)

Any ideas what I need to change? I'm sorry but I know little of VB Scripting so I'm just trying to follow directions here. This is what I have:

Sub SumCountByConditionalFormat() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim indRefColor As Long Dim cellsColorSample As Range Dim cntRes As Long Dim sumRes Dim cntCells As Long Dim indCurCell As Long

On Error Resume Next

cntRes = 0 sumRes = 0

cntCells = Selection.CountLarge

If Not (cellsColorSample Is Nothing) Then indRefColor = cellsColorSample.Cells(1, 1).DisplayFormat.Interior.Color

For indCurCell = 1 To (cntCells) If indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color Then cntRes = cntRes + 1 sumRes = WorksheetFunction.Sum(Selection(indCurCell), sumRes) End If Next MsgBox "Count=" & cntRes & vbCrLf & "Sum= " & sumRes & vbCrLf & vbCrLf & _ "Color=" & Left("000000", 6 - Len(Hex(indRefColor))) & _ Hex(indRefColor) & vbCrLf, , "Count & Sum by Conditional Format color" End If Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub

I am trying to sum cells that are conditionally formatted red. Any help you can give would be GREATLY appreciated!!!

Hi! Copy the VBA code carefully from the article, or use the sample file linked at the end of the article.

add macro using vba

Hi there, thanks for posting this. I'm currently trying to use it and have followed the steps as outlined but when I go to the run the macro there is nothing to select. Any idea why this might be?

Hi! If you have correctly inserted the macro code and saved the file correctly as described in the article above, try these guidelines: How to enable and disable macros in Excel .

add macro using vba

Hello everyone, I would like to ask you for help with my problem. I think it's possible to solve it with VBA, but I'm not sure how to do it.

There is an application named Qtest. I want to link it with excel with the help of VBA macros , so that I can upload data from Excel to Qtest automatically.

Hi! Sorry, your request goes beyond the advice we provide on this blog.

add macro using vba

I have an RFID reader putting value in active Excel cell. Then nothing more happenes, i want Excel to automaticly move active cell to the cell below and calculate the sheet :-(

Hi! Unfortunately, I cannot reproduce your problem. Set the cursor to move automatically after entering a value in a cell: Options - Advanced - Editing options. Also check in the menu on the ribbon: Formulas - Calculation options - Automatic. Add to VBA code the first executable statement: Application.Volatile.

add macro using vba

I was able to do this using Office 2003, but after closing and then opening, i get the message that my VB project was removed. Ive spent hrs googling and experimenting but I cant find the solution. I have no problems if I use Office 2013, unfortunately, the old program im using will not work with newer versions of excel. Any ideas?

Unfortunately, code created in Excel 2013 and later does not work in Excel 2003.

Im not using excel 2013, im trying to get excel 2003 to stop removing my macro after closing/opening. I meant my old lintool program will not work with new versions of excel so im stuck using the 2003 excel.

Unfortunately, your old program doesn't work with modern VBA macros.

add macro using vba

Thanks so much Alexander! that is very helpful.

add macro using vba

Fails at Step 2. When I Press Alt-F11, I get the editor screen, but not the left-hand panel shown in the image. :-(.

Press Ctrl + R

add macro using vba

This is so good & helpful for first time user like me - Thanks so much

add macro using vba

Can someone help me with the following script? There is something wrong with the last call function of this.

Sub Admin() ' Defines variables Dim Cell As Range, sRange As Range, Rng As Range ' Defines LastCol as the last column with a header on the Run sheet LastCol = Sheets("SABER").Cells(1, Columns.Count).End(xlToLeft).Column ' Sets the search range as A1 to the last column with a header on the Run sheet Set sRange = Sheets("SABER").Range("A1", Cells(1, LastCol)) ' With the search range With sRange ' Set Rng as the cell where "Admin No." is found Set Rng = .Find(What:="Admin No.", _ After:=.Cells(1), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False) ' If Rng exists then If Not Rng Is Nothing Then ' Define LastRow as the last row of data under the Due Date header lastRow = Sheets("SABER").Cells(Rows.Count, Rng.Column).End(xlUp).Row ' Copy from the Systme Consition header down to the last row of that column and paste to A1 of Paste Report Here sheet Sheets("SABER").Range(Rng, Cells(lastRow, Rng.Column)).Copy _ Destination:=Sheets("Status").Range("A1") End If End With End Sub

What I'm trying to accomplish here is to display the "Admin No." Column from sheet 3 "Saber" (no matte where it is in sheet 3) to a sheet called "Status". This works really well HOWEVER The issue is when a row is deleted from Saber and I run the Macro in status the row remains where there like I never deleted it.

Any help would be apricated.

Dose anyone one know if the above process can be accomplished without the Marco? ie. search sheet 2 for a certain column regardless of where it is and populate it in sheet 1.

We apologize, but we cannot offer assistance beyond the scope of this blog.

add macro using vba

I am trying to create a macro that will unlock a worksheet and spellcheck the sheet and lock the worksheet again. But I also want to still be able to format rows. When I use the generic macro I found online it defaults to not allowing the worksheet to allow for row formatting? And when I try to use the record macro function to do the same functions it want the password and leaves it protected with no password. Does anybody have a solution?

add macro using vba

I want to creat a sequence from 1 to infonity and skip all ending zero number such that 10,20,30,.... Also no skip any indices

add macro using vba

Hello can VBA code be inserted into Excel 365? if so how?

Hi! You can record and run VBA macros with all Excel desktop versions. In the online version of Excel, VBA does not work.

add macro using vba

I'm trying to add a blank row between every row. After running the VBA code, I get an error 6 - overflow.

When debugging, it looks like there's an issue with the following string: CountRow = rng.EntireRow.Count

Please help me resolve this <3

add macro using vba

Hello, I need to use VBA in a worksheet_change event instead of formula, so you can leave the cell empty ready for user interface, or when the conditions are met the value can be put in the cell automatically and protected at the same time. I am looking to autopopulate previously entered data based on the client's unique identifier. This is my formula, which works/allows for the formula to auto-populate/return data BUT it doesn not allow for data entry if there is no match/data to return once the sheet is protected? =IFERROR(INDEX($C$7:C7,MATCH($A$7:$A$6001,$A$7:$A$6001,0))," ")

Hi! Unfortunately, here we can only help you with Excel formulas.

add macro using vba

Hi, I'm looking for a solution that create a button to print excel worksheet with print range A2:AA73 from a workbook into pdf and save a copy of pdf in specific file location and file name will be based on each sheet cell value at AD2

Can you help me with how to code that part? I'll be really appreciated your help.

add macro using vba

Hi everyone, can you please help me on writing VBA code in excel as per my requirement: Requirement: In Cell: D23 (containing formula value automatically generated on every 15 minutes) In column c23=current time value is showing In Cell "A30:A54" (value started from 9.30, 9.15, 10.00, 10.15, 10.30, 10.45, 12.00, 12.15, 12.30, 12.45, 13.00, 13.15, 13.30, 13.45, 14.00, 14.15, 14.30, 14.45, 15.00, 15.15, 15.30 respecitively each time value in each row)

Now I want to copy cell D23 value and Paste value only inside the Cell Range : B30:B54 where Cell C23=matches with Cell A30:A54).

Kindly help to write code on VBA in excel.

Thank you very much

add macro using vba

Hi, I'm looking for a solution that is I created a report on excel and I also created a button to export that report excel file into a pdf and its location is on Sharepoint, not in some folder only on Computer. Can you help me with how to code that part? I'll be really appreciated your help.

Is there a code set or technique to get an excel sheet to read all the documents in a folder, their headers (if they are word documents) and then build a hyperlink list with colums that display data fields from the header like dates/vital-info/etc.

Is this possible?

add macro using vba

Hi Is there a code or tool in Excel to enable a cell pop up a number previously entered in another cell immediately that number is deleted?

add macro using vba

I've written a whole script which works in extracting all the information from a Questionnaire in word to excel. However, the only problem I've run into is when questions within the Questionnaire has tick box options. My current VB macro script just pulls in the box symbol, but not the words associated with the ticked box/next to the tick boxes. Is there a VB macro script you can recommend I can write which allows me to pick up the text associated with the ticked box instead?

add macro using vba

Hello, I need to learn coding in excell. Actually I want to use coding in excell for getting expenditures year wise. In excell sheet im going to fill cells with expenditures pertain to different years but i want excell to use codes and give those expenditures years wise as an easy reporting. Kindly guide me through this how could i get desired results, please.

add macro using vba

I have 2 Dropdown List with the same options (Included and Excluded) in different sheets. If I select " Included" in one dropdown list, it should also select "Included" on another dropdown list and vice versa. I need VBA code to crack this.

add macro using vba

I have an excel excel workbook .It has four sheets A, B, C, D .A, sheet information .Rowes' information is categorized in C column. VBA code so that the information contained in the Rowes can be found on the B sheet, the information on the C on the C sheet and the information on the D on the D sheet. want to .

Hi I have 3 sheets work book. sheet1 'E,sheet 2 'N anf Sheet 3 'D'. It has 1000 rows in sheet 1. "H" column data catagary 'N' and 'D' two data type .I want to link data by N or D to other two sheets .

add macro using vba

this deleted active names as well so my entire excel has #name error now. is there a VBA that only deleted names that are not used

add macro using vba

I am having an excel wherein i have done some conditional formatting with color coding and i just want to copy the color codes to another fields, can you suggest a macro VBA so that i can run it?

add macro using vba

May I request the expertise of those who are professionals in VBA. I'm planning to improve my procurement monitoring in excel using the VBA wherein I want to simplify the way I can search for the status of purchases. By simply typing the reference number in the search bar then the status would appear. Would that be possible in excel VBA. Thank you in advance for your help

how i can give command in execel

add macro using vba

I need a code to color the sheet tab to red if T43 in that sheet is > 0, no change to sheet color for all other cases. I'd like this to run for the entire workbook of 160 sheets automatically. Can someone help?

i like your question as well. Can someone help us on this?

Hi! Unfortunately, we can't help writing a VBA macro. Only Excel formulas.

add macro using vba

please can I have a cod to calculate the average for each 29 number of excel column with 184450 row

add macro using vba

Hello everyone, I would like to ask you for help with my problem. I think it's possible to solve it with VBA, but I'm not sure how to do it. Also, if there is a way to do it without VBA, even better. I exported the tasks from the Planner to Excel (did some work to filter and format the data I needed, etc.) and finally, I have a list of tasks that belong to a person. For each person, I have to manually enter the approximate time needed to complete the task, during the task that person should enter each week how many hours he spends solving the task and when the task is completed I can compare in the table the time he spends and the time I set for this assignment. This table needs to be used for a long time, and the task list changes almost every week, so I need to export new data from the scheduler every week, but save the data previously entered for some tasks. Each time an export is performed, the order of tasks in Excel changes and this is the point when a problem occurs. The time I need to specify for each task (forecast) is entered manually, for example, for the task "Task1" in A1 the forecast is entered in C1. The next time I export tasks from the Planner, it is possible that "Task 1" will no longer be in A1 (ie I added another task in the Planner and now that task is the first, so "Task 1" moves to A2), but the forecast for " Task 1 "remained in C1 (because column C is not included in exports). How to ensure that cell C1 follows the task in A1, no matter where the data from A1 is transferred? In this case, when a new export is made, the forecast from C1 should be automatically moved to C2, because the task from A1 is now to A2. I hope someone can help me. Thanks in advance, Los

add macro using vba

I Want use VBA code flash data on return on blog

add macro using vba

Hello, thank you for your help. If possible, could you please help me with an additional problem? I have around 1000+ xml files and I want to convert them to excel or csv file. Is it possible to do that as well? If so, can you show me how?

Thank you for your time.

Hey! I am looking for a little help with a code. I am a beginner it is a little confusing..

Change the application so that now there is no limit.

For EG, if the strategy says to buy 30% more shares but there is not enough cash on hand to do so, the investor will now borrow the cash they need. Now the cash positions in columns H and J of the Model worksheet can be negative, indicating that the investor owes money to the lender.

Capture the maximum the investor ever owes during the year in an extra output cell, keep track of it, and summarize it (including a histogram), just like all of the other outputs, with your VBA code.

add macro using vba

Hi Sir i want to count diffent names in coloum wise what is the formula or code?

add macro using vba

i am new vb in excel and need some assistance with the following macro. any help greatly appreciated. i need to create a macro which will take value from sheet 1 cell A1 value ,(example: CD-600500 is available in sheet 1 cell A1) then increment the value by 1 in sheet 2 cell range A5:A50,also A5:A50 if the cell is blank try goto next row and increment the value by 1.

add macro using vba

I need a code to convert half of my numbers to variables. example if the number is 12345, i need to convert it as ABC45. (A=1, B=2 Etc..) Someone Please help.

add macro using vba

hi there, im quite new at programming but uses excel alot so what im looking for would help me quite alot. i would like to create a macro or a button that takes the value i a cell and multiplies it with negative one. Example: i have multiple sheets and plots in a number from sheet one to sheet two, then in sheet two i need the number to be multiplied with negative one, whilst still being traceble to where the value came from. is this possible?

sorry if the explenation is bad, English is my second language.

Post a comment

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Getting started with VBA in Office

  • 10 contributors

Are you facing a repetitive clean up of fifty tables in Word? Do you want a particular document to prompt the user for input when it opens? Are you having difficulty figuring out how to get your contacts from Microsoft Outlook into a Microsoft Excel spreadsheet efficiently?

You can perform these tasks and accomplish a great deal more by using Visual Basic for Applications (VBA) for Office—a simple, but powerful programming language that you can use to extend Office applications.

This article is for experienced Office users who want to learn about VBA and who want some insight into how programming can help them to customize Office.

The Office suite of applications has a rich set of features. There are many different ways to author, format, and manipulate documents, email, databases, forms, spreadsheets, and presentations. The great power of VBA programming in Office is that nearly every operation that you can perform with a mouse, keyboard, or a dialog box can also be done by using VBA. Further, if it can be done once with VBA, it can be done just as easily a hundred times. (In fact, the automation of repetitive tasks is one of the most common uses of VBA in Office.)

Beyond the power of scripting VBA to accelerate every-day tasks, you can use VBA to add new functionality to Office applications or to prompt and interact with the user of your documents in ways that are specific to your business needs. For example, you could write some VBA code that displays a pop up message that reminds users to save a document to a particular network drive the first time they try to save it.

This article explores some of the primary reasons to leverage the power of VBA programming. It explores the VBA language and the out-of-the-box tools that you can use to work with your solutions. Finally, it includes some tips and ways to avoid some common programming frustrations and missteps.

Interested in developing solutions that extend the Office experience across multiple platforms ? Check out the new Office Add-ins model . Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML.

When to use VBA and why

There are several principal reasons to consider VBA programming in Office.

Automation and repetition

VBA is effective and efficient when it comes to repetitive solutions to formatting or correction problems. For example, have you ever changed the style of the paragraph at the top of each page in Word? Have you ever had to reformat multiple tables that were pasted from Excel into a Word document or an Outlook email? Have you ever had to make the same change in multiple Outlook contacts?

If you have a change that you have to make more than ten or twenty times, it may be worth automating it with VBA. If it is a change that you have to do hundreds of times, it certainly is worth considering. Almost any formatting or editing change that you can do by hand, can be done in VBA.

Extensions to user interaction

There are times when you want to encourage or compel users to interact with the Office application or document in a particular way that is not part of the standard application. For example, you might want to prompt users to take some particular action when they open, save, or print a document.

Interaction between Office applications

Do you need to copy all of your contacts from Outlook to Word and then format them in some particular way? Or, do you need to move data from Excel to a set of PowerPoint slides? Sometimes simple copy and paste does not do what you want it to do, or it is too slow. Use VBA programming to interact with the details of two or more Office applications at the same time and then modify the content in one application based on the content in another.

Doing things another way

VBA programming is a powerful solution, but it is not always the optimal approach. Sometimes it makes sense to use other ways to achieve your aims.

The critical question to ask is whether there is an easier way. Before you begin a VBA project, consider the built-in tools and standard functionalities. For example, if you have a time-consuming editing or layout task, consider using styles or accelerator keys to solve the problem. Can you perform the task once and then use CTRL+Y (Redo) to repeat it? Can you create a new document with the correct format or template, and then copy the content into that new document?

Office applications are powerful; the solution that you need may already be there. Take some time to learn more about Office before you jump into programming.

Before you begin a VBA project, ensure that you have the time to work with VBA. Programming requires focus and can be unpredictable. Especially as a beginner, never turn to programming unless you have time to work carefully. Trying to write a "quick script" to solve a problem when a deadline looms can result in a very stressful situation. If you are in a rush, you might want to use conventional methods, even if they are monotonous and repetitive.

VBA Programming 101

Using code to make applications do things.

You might think that writing code is mysterious or difficult, but the basic principles use every-day reasoning and are quite accessible. Microsoft Office applications are created in such a way that they expose things called objects that can receive instructions, in much the same way that a phone is designed with buttons that you use to interact with the phone. When you press a button, the phone recognizes the instruction and includes the corresponding number in the sequence that you are dialing. In programming, you interact with the application by sending instructions to various objects in the application. These objects are expansive, but they have their limits. They can only do what they are designed to do, and they will only do what you instruct them to do.

For example, consider the user who opens a document in Word, makes a few changes, saves the document, and then closes it. In the world of VBA programming, Word exposes a Document object. By using VBA code, you can instruct the Document object to do things such as Open, Save, or Close.

The following section discusses how objects are organized and described.

The Object Model

Developers organize programming objects in a hierarchy, and that hierarchy is called the object model of the application. Word, for example, has a top-level Application object that contains a Document object. The Document object contains Paragraph objects and so on. Object models roughly mirror what you see in the user interface. They are a conceptual map of the application and its capabilities.

The definition of an object is called a class, so you might see these two terms used interchangeably. Technically, a class is the description or template that is used to create, or instantiate, an object.

Once an object exists, you can manipulate it by setting its properties and calling its methods. If you think of the object as a noun, the properties are the adjectives that describe the noun and the methods are the verbs that animate the noun. Changing a property changes some quality of appearance or behavior of the object. Calling one of the object methods causes the object to perform some action.

The VBA code in this article runs against an open Office application where many of the objects that the code manipulates are already up and running; for example, the Application itself, the Worksheet in Excel, the Document in Word, the Presentation in PowerPoint, the Explorer and Folder objects in Outlook. Once you know the basic layout of the object model and some key properties of the Application that give access to its current state, you can start to extend and manipulate that Office application with VBA in Office.

In Word, for example, you can change the properties and invoke the methods of the current Word document by using the ActiveDocument property of the Application object. This ActiveDocument property returns a reference to the Document object that is currently active in the Word application. "Returns a reference to" means "gives you access to."

The following code does exactly what it says; that is, it saves the active document in the application.

Read the code from left to right, "In this Application, with the Document referenced by ActiveDocument, invoke the Save method." Be aware that Save is the simplest form of method; it does not require any detailed instructions from you. You instruct a Document object to Save and it does not require any more input from you.

If a method requires more information, those details are called parameters. The following code runs the SaveAs method, which requires a new name for the file.

Values listed in parentheses after a method name are the parameters. Here, the new name for the file is a parameter for the SaveAs method.

You use the same syntax to set a property that you use to read a property. The following code executes a method to select cell A1 in Excel and then to set a property to put something in that cell.

The first challenge in VBA programming is to get a feeling for the object model of each Office application and to read the object, method, and property syntax. The object models are similar in all Office applications, but each is specific to the kind of documents and objects that it manipulates.

In the first line of the code snippet, there is the Application object, Excel this time, and then the ActiveSheet , which provides access to the active worksheet. After that is a term not as familiar, Range, which means "define a range of cells in this way." The code instructs Range to create itself with just A1 as its defined set of cells. In other words, the first line of code defines an object, the Range, and runs a method against it to select it. The result is automatically stored in another property of the Application called Selection .

The second line of code sets the Value property of Selection to the text "Hello World", and that value appears in cell A1.

The simplest VBA code that you write might simply gain access to objects in the Office application that you are working with and set properties. For example, you could get access to the rows in a table in Word and change their formatting in your VBA script.

That sounds simple, but it can be incredibly useful; once you can write that code, you can harness all of the power of programming to make those same changes in several tables or documents, or make them according to some logic or condition. For a computer, making 1000 changes is no different from making 10, so there is an economy of scale here with larger documents and problems, and that is where VBA can really shine and save you time.

Macros and the Visual Basic Editor

Now that you know something about how Office applications expose their object models, you are probably eager to try calling object methods, setting object properties, and responding to object events. To do so, you must write your code in a place and in a way that Office can understand; typically, by using the Visual Basic Editor. Although it is installed by default, many users don't know that it is even available until it is enabled on the ribbon.

All Office applications use the ribbon. One tab on the ribbon is the Developer tab, where you access the Visual Basic Editor and other developer tools. Because Office does not display the Developer tab by default, you must enable it by using the following procedure:

To enable the Developer tab

On the File tab, choose Options to open the Options dialog box.

Choose Customize Ribbon on the left side of the dialog box.

Under Choose commands from on the left side of the dialog box, select Popular Commands .

Under Customize the Ribbon on the right side of the dialog box, select Main Tabs in the drop down list box, and then select the Developer checkbox.

Choose OK .

In Office 2007, you displayed the Developer tab by choosing the Office button, choosing Options , and then selecting the Show Developer tab in Ribbon check box in the Popular category of the Options dialog box.

After you enable the Developer tab, it is easy to find the Visual Basic and Macros buttons.

Figure 1. Buttons on the Developer tab

Buttons on the Developer tab

Security issues

To protect Office users against viruses and dangerous macro code, you cannot save macro code in a standard Office document that uses a standard file extension. Instead, you must save the code in a file with a special extension. For example you cannot save macros in a standard Word document with a .docx extension; instead, you must use a special Word Macro-Enabled Document with a .docm extension.

When you open a .docm file, Office security might still prevent the macros in the document from running, with or without telling you. Examine the settings and options in the Trust Center on all Office applications. The default setting disables macro from running, but warns you that macros have been disabled and gives you the option to turn them back on for that document.

You can designate specific folders where macros can run by creating Trusted Locations, Trusted Documents, or Trusted Publishers. The most portable option is to use Trusted Publishers, which works with digitally signed documents that you distribute. For more information about the security settings in a particular Office application, open the Options dialog box, choose Trust Center , and then choose Trust Center Settings .

Some Office applications, like Outlook, save macros by default in a master template on your local computer. Although that strategy reduces the local security issues on your own computer when you run your own macros, it requires a deployment strategy if you want to distribute your macro.

Recording a macro

When you choose the Macro button on the Developer tab, it opens the Macros dialog box, which gives you access to VBA subroutines or macros that you can access from a particular document or application. The Visual Basic button opens the Visual Basic Editor, where you create and edit VBA code.

Another button on the Developer tab in Word and Excel is the Record Macro button, which automatically generates VBA code that can reproduce the actions that you perform in the application. Record Macro is a terrific tool that you can use to learn more about VBA. Reading the generated code can give you insight into VBA and provide a stable bridge between your knowledge of Office as a user and your knowledge as a programmer. The only caveat is that the generated code can be confusing because the Macro editor must make some assumptions about your intentions, and those assumptions are not necessarily accurate.

To record a macro

Open Excel to a new Workbook and choose the Developer tab in the ribbon. Choose Record Macro and accept all of the default settings in the Record Macro dialog box, including Macro1 as the name of the macro and This Workbook as the location.

Choose OK to begin recording the macro. Note how the button text changes to Stop Recording . Choose that button the instant you complete the actions that you want to record.

Choose cell B1 and type the programmer's classic first string: Hello World. Stop typing and look at the Stop Recording button; it is grayed out because Excel is waiting for you to finish typing the value in the cell.

Choose cell B2 to complete the action in cell B1, and then choose Stop Recording .

Choose Macros on the Developer tab, select Macro1 if it is not selected, and then choose Edit to view the code from Macro1 in the Visual Basic Editor.

Figure 2. Macro code in Visual Basic Editor

Macro code in Visual Basic Editor

Looking at the code

The macro that you created should look similar to the following code.

Be aware of the similarities to the earlier code snippet that selected text in cell A1, and the differences. In this code, cell B1 is selected, and then the string "Hello World" is applied to the cell that has been made active. The quotes around the text specify a string value as opposed to a numeric value.

Remember how you chose cell B2 to display the Stop Recording button again? That action shows up as a line of code as well. The macro recorder records every keystroke.

The lines of code that start with an apostrophe and colored green by the editor are comments that explain the code or remind you and other programmers the purpose of the code. VBA ignores any line, or portion of a line, that begins with a single quote. Writing clear and appropriate comments in your code is an important topic, but that discussion is out of the scope of this article. Subsequent references to this code in the article don't include those four comment lines.

When the macro recorder generates the code, it uses a complex algorithm to determine the methods and the properties that you intended. If you don't recognize a given property, there are many resources available to help you. For example, in the macro that you recorded, the macro recorder generated code that refers to the FormulaR1C1 property. Not sure what that means?

Be aware that Application object is implied in all VBA macros. The code that you recorded works with Application. at the beginning of each line.

Using Developer Help

Select FormulaR1C1 in the recorded macro and press F1. The Help system runs a quick search, determines that the appropriate subjects are in the Excel Developer section of the Excel Help, and lists the FormulaR1C1 property. You can choose the link to read more about the property, but before you do, be aware of the Excel Object Model Reference link near the bottom of the window. Choose the link to view a long list of objects that Excel uses in its object model to describe the Worksheets and their components.

Choose any one of those to see the properties and methods that apply to that particular object, along with cross references to different related options. Many Help entries also have brief code examples that can help you. For example, you can follow the links in the Borders object to see how to set a border in VBA.

Editing the code

The Borders code looks different from the recorded macro. One thing that can be confusing with an object model is that there is more than one way to address any given object, cell A1 in this example.

Sometimes the best way to learn programming is to make minor changes to some working code and see what happens as a result. Try it now. Open Macro1 in the Visual Basic Editor and change the code to the following.

Use Copy and Paste as much as possible when working with code to avoid typing errors.

You don't need to save the code to try it out, so return to the Excel document, choose Macros on the Developer tab, choose Macro1 , and then choose Run . Cell A1 now contains the text Wow! and has a double-line border around it.

Figure 3. Results of your first macro

Results of your first macro

You just combined macro recording, reading the object model documentation, and simple programming to make a VBA program that does something. Congratulations!

Did not work? Read on for debugging suggestions in VBA.

Programming tips and tricks

Start with examples.

The VBA community is very large; a search on the Web can almost always yield an example of VBA code that does something similar to what you want to do. If you cannot find a good example, try to break the task down into smaller units and search on each of those, or try to think of a more common, but similar problem. Starting with an example can save you hours of time.

That does not mean that free and well-thought-out code is on the Web waiting for you to come along. In fact, some of the code that you find might have bugs or mistakes. The idea is that the examples you find online or in VBA documentation give you a head start. Remember that learning programming requires time and thought. Before you get in a big rush to use another solution to solve your problem, ask yourself whether VBA is the right choice for this problem.

Make a simpler problem

Programming can get complex quickly. It's critical, especially as a beginner, that you break the problem down to the smallest possible logical units, then write and test each piece in isolation. If you have too much code in front of you and you get confused or muddled, stop and set the problem aside. When you come back to the problem, copy out a small piece of the problem into a new module, solve that piece, get the code working, and test it to ensure that it works. Then move on to the next part.

Bugs and debugging

There are two main types of programming errors: syntax errors, which violate the grammatical rules of the programming language, and run-time errors, which look syntactically correct, but fail when VBA attempts to execute the code.

Although they can be frustrating to fix, syntax errors are easy to catch; the Visual Basic Editor beeps and flashes at you if you type a syntax error in your code.

For example, string values must be surrounded by double quotes in VBA. To find out what happens when you use single quotes instead, return to the Visual Basic Editor and replace the "Wow!" string in the code example with 'Wow!' (that is, the word Wow enclosed in single quotes). If you choose the next line, the Visual Basic Editor reacts. The error "Compile error: Expected: expression" is not that helpful, but the line that generates the error turns red to tell you that you have a syntax error in that line and as a result, this program will not run.

Choose OK and change the text back to"Wow!".

Runtime errors are harder to catch because the programming syntax looks correct, but the code fails when VBA tries to execute it.

For example, open the Visual Basic Editor and change the Value property name to ValueX in your Macro, deliberately introducing a runtime error since the Range object does not have a property called ValueX. Go back to the Excel document, open the Macros dialog box and run Macro1 again. You should see a Visual Basic message box that explains the run-time error with the text, "Object doesn't support this property of method." Although that text is clear, choose Debug to find out more.

When you return to the Visual Basic Editor, it is in a special debug mode that uses a yellow highlight to show you the line of code that failed. As expected, the line that includes the ValueX property is highlighted.

You can make changes to VBA code that is running, so change ValueX back to Value and choose the little green play button underneath the Debug menu. The program should run normally again.

It's a good idea to learn how to use the debugger more deliberately for longer, more complex programs. At a minimum, learn a how to set break-points to stop execution at a point where you want to take a look at the code, how to add watches to see the values of different variables and properties as the code runs, and how to step through the code line by line. These options are all available in the Debug menu and serious debugger users typically memorize the accompanying keyboard shortcuts.

Using reference materials well

To open the Developer Reference that is built into Office Help, open the Help reference from any Office application by choosing the question mark in the ribbon or by pressing F1. Then, to the right of the Search button, choose the dropdown arrow to filter the contents. Choose Developer Reference . If you don't see the table of contents in the left panel, choose the little book icon to open it, and then expand the Object Model Reference from there.

Figure 5. Filtering on developer Help applies to all Office applications

Filtering on developer Help applies to all Office applications

Time spent browsing the Object Model reference pays off. After you understand the basics of VBA syntax and the object model for the Office application that you are working with, you advance from guesswork to methodical programming.

Of course the Microsoft Office Developer Center is an excellent portal for articles, tips, and community information.

Searching forums and groups

All programmers get stuck sometimes, even after reading every reference article they can find and losing sleep at night thinking about different ways to solve a problem. Fortunately, the Internet has fostered a community of developers who help each other solve programming problems.

Any search on the Web for "office developer forum" reveals several discussion groups. You can search on "office development" or a description of your problem to discover forums, blog posts, and articles as well.

If you have done everything that you can to solve a problem, don't be afraid to post your question to a developers forum. These forums welcome posts from newer programmers and many of the experienced developers are glad to help.

The following are a few points of etiquette to follow when you post to a developer forum:

Before you post, look on the site for an FAQ or for guidelines that members want you to follow. Ensure that you post content that is consistent with those guidelines and in the correct section of the forum.

Include a clear and complete code sample, and consider editing your code to clarify it for others if it is part of a longer section of code.

Describe your problem clearly and concisely, and summarize any steps that you have taken to solve the problem. Take the time to write your post as well as you can, especially if you are flustered or in a hurry. Present the situation in a way that will make sense to readers the first time that they read the problem statement.

Be polite and express your appreciation.

Going further with programming

Although this article is short and only scratches the surface of VBA and programming, it is hopefully enough to get you started.

This section briefly discusses a few more key topics.

In the simple examples in this article you manipulated objects that the application had already created. You might want to create your own objects to store values or references to other objects for temporary use in your application. These are called variables.

To use a variable in VBA, must tell VBA which type of object the variable represents by using the Dim statement. You then set its value and use it to set other variables or properties.

Branching and looping

The simple programs in this article execute one line at a time, from the top down. The real power in programming comes from the options that you have to determine which lines of code to execute, based on one or more conditions that you specify. You can extend those capabilities even further when you can repeat an operation many times. For example, the following code extends Macro1.

Type or paste the code into the Visual Basic Editor and then run it. Follow the directions in the message box that appears and change the text in cell A1 from Wow! to Yes! and run it again to see the power of looping. This code snippet demonstrates variables, branching and looping. Read it carefully after you see it in action and try to determine what happens as each line executes.

All of my Office applications: example code

Here are a few scripts to try; each solves a real-world Office problem.

Create an email in Outlook

Be aware that there are situations in which you might want to automate email in Outlook; you can use templates as well.

Delete empty rows in an Excel worksheet

Be aware that you can select a column of cells and run this macro to delete all rows in the selected column that have a blank cell.

Delete empty text boxes in PowerPoint

Be aware that this code loops through all of the slides and deletes all text boxes that don't have any text. The count variable decrements instead of increments because each time the code deletes an object, it removes that object from the collection, which reduces the count.

Copy a contact from Outlook to Word

Be aware that this code copies the currently open contact in Outlook into the open Word document. This code only works if there is a contact currently open for inspection in Outlook.

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Was this page helpful?

Submit and view feedback for

Additional resources

#1 Excel tutorial on the net

  • Create a Macro

Developer Tab   |  Command Button   |  Assign a Macro   |  Visual Basic Editor

With Excel VBA you can automate tasks in Excel by writing so called macros. In this chapter, learn how to create a simple macro which will be executed after clicking on a command button. First, turn on the Developer tab.

Developer Tab

To turn on the Developer tab, execute the following steps.

1. Right click anywhere on the ribbon, and then click Customize the Ribbon.

Customize the Ribbon in Excel

2. Under Customize the Ribbon, on the right side of the dialog box, select Main tabs (if necessary).

3. Check the Developer check box.

Turn on the Developer Tab

4. Click OK.

5. You can find the Developer tab next to the View tab.

Developer Tab

Command Button

To place a command button on your worksheet, execute the following steps.

1. On the Developer tab , click Insert.

2. In the ActiveX Controls group, click Command Button.

Insert a command button control

3. Drag a command button on your worksheet.

Assign a Macro

To assign a macro (one or more code lines) to the command button, execute the following steps.

1. Right click CommandButton1 (make sure Design Mode is selected).

2. Click View Code.

View Code

The Visual Basic Editor appears.

3. Place your cursor between Private Sub CommandButton1_Click() and End Sub.

4. Add the code line shown below.

Add Code Line

Note: the window on the left with the names Sheet1 (Sheet1) and ThisWorkbook is called the Project Explorer. If the Project Explorer is not visible, click View, Project Explorer. If the Code window for Sheet1 is not visible, click Sheet1 (Sheet1). You can ignore the Option Explicit statement for now.

5. Close the Visual Basic Editor.

6. Click the command button on the sheet (make sure Design Mode is deselected).

Excel Macro Result

Congratulations. You've just created a macro in Excel!

Visual Basic Editor

To open the Visual Basic Editor, on the Developer tab , click Visual Basic.

Open the Visual Basic Editor

Learn more, it's easy

  • Swap Values
  • Run Code from a Module
  • Macro Recorder
  • Use Relative References
  • FormulaR1C1
  • Add a Macro to the Toolbar
  • Enable Macros
  • Protect Macro

Download Excel File

  • create-a-macro.xlsm

Next Chapter

Follow excel easy.

Excel Easy on Facebook

Become an Excel Pro

  • 300 Examples

Create a Macro • © 2010-2024 Popular Excel Topics: Pivot Tables • Vlookup • Formulas • Charts • Conditional Formatting

Learn VBA Macro Coding Basics In Excel [2024 Guide]

An introduction to Learn VBA coding to quickly get the basic understanding

No Coding Experience? No Problem!

First I want to congratulate you because if you are taking the time to read this guide I am assuming two things: 

  • You have realized that Excel and the rest of Microsoft's Office Suite have an enormous amount of power that is just waiting for you to unlock
  • You have noticed that most of your peers don't understand these capabilities and you want to differentiate yourself from them

Understanding those two points really gave me the motivation to dig deep into the Excel universe (and eventually into Word and PowerPoint) and add every technique I could find to my virtual tool belt.  

In this introductory guide, I’m going to walk you through 5 brief lessons to give you a basic understanding of what VBA is and how you can get started automating tasks in Microsoft Excel.

I’m going to assume that you are in the same situation as I was when I first started learning about VBA. I had absolutely zero computer coding experience and was just looking for a way to save time on boring, repetitive tasks I was having to do in Excel every month.

Here is the lesson plan we will walk through together:

Before we get our hands messy and try to build a foundation that will allow you to do anything you could ever imagine within these programs, I think it is really important to take some time and define what exactly VBA and those “Macro things” are that you've probably heard or seen floating around.

LESSON ONE: What Is VBA? What Are Macros?

The acronym VBA stands for Visual Basic for Applications .  This is essentially an offshoot of the Visual Basic computer language that Microsoft created way back in the 90s that allows Microsoft programs to communicate with each other based on events or actions that take place within those programs.  

This language is not only used in Office programs like Excel and PowerPoint, but also in programs like NotePad and Paint.  Since Microsoft created this language to go along with its own applications, the code is very user intuitive.  

For example, if you read a code line in Excel that states  Range("A1:B4").ClearContents  you can make an educated guess that the line of code tells Excel to clear the contents of cells A1 through B4.  This is HUGE because it allows users with very limited or no computer programming knowledge to easily pick up on how the VBA language works.

What Is A Macro?

Macros are what most people who write VBA code use to automate their tasks.  

Definition: A macro (also can be referred to as a  Procedure or  Subroutine ) is a grouping of code that performs a series of tasks or commands within a targeted computer program (aka Application).  

Macros can contain code that performs calculations, copy & pastes, changes formatting, and a bunch of other nifty things; all within milliseconds!  Most Office users use macros to automate routine tasks that take them a long period of time to perform manually (by keyboard & mouse).

What The Function Is "Dim"?

I threw this section into this guide for a friend of mine who was frustrated with the lack of introductory information on VBA.  He said, "Chris, I've been searching everywhere and I wish there was a simple introduction to VBA that could get me started.  I mean, I can't even figure out what the heck a Dim is !"  

So below I will list out a couple of terms you might have come across if you have ever recorded a macro or seen VBA code.  Leave a comment at the very bottom of this guide if there are any other terms you have come across and would like defined.

Dim  - This stands for Dimension and is a statement used to declare a variable name and type that you want to create.

Sub  - This is short for Subroutine and is your opening statement for your code. Every time Sub is typed a new macro or procedure is created.  The words "End Sub" must be placed as the last line of code for your macro in order to tell VBA that your procedure is finished

Module  - This is an area where you can write function and macro codes.  This is also where any macros that you record are stored.

Class Module  - This is an area for really advanced VBA users.  In this area, you can write your own custom classes, methods, and collections into the VBA library.  If you are a beginner I would stay away from these for now.  Most VBA writers won't ever use this type of functionality during their career but it is a very powerful option to have if needed.

Function  - VBA gives you the ability to create your own custom functions.  These can either be used by your macros to obtain a certain output or they can be used in the  Excel Formula Bar  to perform calculations on your cell's values.

Userforms  - These are pop-up boxes that allow users to enter inputs or choose options.  Microsoft uses these all the time in their applications. Some examples of these are:

  • Error Message Boxes
  • Dialog Boxes
  • The Macro Recorder

The cool thing is that VBA gives you the ability to create your own custom Userforms from scratch!  You get to design the look (user interface) and code for all the buttons, inputs, and actions as your user interacts with the form.

LESSON TWO: The Visual Basic Editor

The Visual Basic Editor is your workspace for creating your VBA code.  

The editor can be accessed through your Developer Tab or by using the shortcut Alt + F11 .

The editor will display in a completely separate window from your Office Application and each one of the programs in the Office Suite has its own VBA Editor (so you can have the Excel and PowerPoint VBA Editors open at the same time).

I will not be going into too much detail about all of the Visual Basic Editor's capabilities in this article but I do want to show you enough so that you can understand its setup and how it functions.  

Below I have a screenshot of some of the main windows that can be shown within the  Visual Basic Editor. Some of these may not be showing on your editor by default and I will explain how to get them showing in the descriptions below.  I will be describing Excel's Visual Basic Editor but much of the information can easily be translated over to PowerPoint, Access, or any of the other Office program's Visual Basic Editors.

An overview of Visual Basic Editor for Excel

Project Window

This window will show you all the files that you have opened.  The Project Window uses a tree view where you can drill down into each file that you have open and see the areas in which you can insert VBA code.

Notice that in my screenshot above that there are two files that are open in my Excel application: Book1 (a workbook) and VBHTMLMaker (an add-in).  In Book1 you can see 3 subfolders:

  • Microsoft Objects  -This folder houses a code area for your workbook (ThisWorkbook) and each of your workbook’s spreadsheet tabs (Sheet1).
  • Forms  - This folder stores any userforms that you create.  I touched a little bit on this in Lesson One so I won't repeat myself here.  If you do not see this folder you can add it by right-clicking anywhere within the projects folder tree and going to  Insert  ->  Form .
  • Modules  - A Module folder stores your macro and function code.  If you do not see this folder then that means that the project most likely does not have any macro code in it (note: there can still be code stored in the forms folder or in the Objects folder).  You can add this folder by right-clicking anywhere within the projects folder tree and going to  Insert  ->  Module .

Properties Window

The Properties Window will allow you to modify certain aspects of whatever object, form, or module you have highlighted.  Typically the only thing I change using this window is the  Name  field.  This is a good idea because you can give your modules or forms a more meaningful name than the default names that the Visual Basic Editor provides.  Custom names in the  Name  field can only be one word in length. 

Code Writing Area

This is where the magic happens!  In this area, you can actually write and edit your VBA code.  Each macro must begin with a  Sub  statement (which is opened with  Sub  [ insert your macro name ]  ( )  and closed with  End Sub ).  Notice also that the VBA Editor color-codes some keywords in a few different colors.  This helps make your code more organized.  I have two major tips that I like to share with people when they are first learning to write VBA code:

  • Use Indentations  - Always try to use indentations (via the Tab key) within your code.  There are various methodologies for tabulating code but as long as you are consistent and it makes sense it will help you enormously when you are trying to add to or debug your code.  It also helps when someone else is trying to help you with your code.
  • Write in Lowercase  - If you haven't noticed already, every word in the VBA language has at least one capitalized letter.  How is this an advantage?  Well, the Visual Basic Editor is not case sensitive and it likes to correct you when it can.  This means that if you type in "workbook", the editor will automatically change it to "Workbook".  My rule of thumb is to type everything in lowercase and if the VB editor doesn't capitalize at least one letter, I know that I either misspelled that word or that word is not defined.  Having the Visual Basic Editor correct every word I type has really made my code less buggy and prevented a lot of frustration over the years.

Immediate Window

I like to refer to this area as my piece of scratch paper.  The Immediate window lets you do all sorts of tests while writing and running your code.  You can use the code  Debug.Print  to tell VBA to send the information that follows to the Immediate window.  This could be the output value of a function, the value of a cell, or what a current application property is set to.  When I first began writing VBA code I had no idea the Immediate window ever existed (it's usually hidden by default but you can use the shortcut  Ctrl + g  to view it), but once learned everything it could do I never stopped incorporating its functionality into my code writing and testing processes.

Watch Window

The Watch Window is kind of like an X-ray machine.  It will show you all the data that is stored inside a variable!  Some variables (like the ones you create in your code) will not have very much data stored in them.  However, if you were to "watch" a variable that was assigned to a cell, you would see a whole bunch of data (font color, value, height, fill color, etc...).  This is mostly useful when you are trying to debug your code and want to understand what value your variable has at any given point in your code.

In order to watch a variable, you need to highlight your variables text and click the Add Watch button (this is the eyeglasses icon located on the debugging toolbar).  You should then see your variable appear in the Watch Window.  Once you start running through your code and load a value to your variable, you should see an option (plus sign) to drill down or expand out the contents that are now stored in the variable.  

Are You Confused Yet?

I know this is a lot to take in but you really should use this as a reference sheet that you can refer back to as you learn.  You will not use all the features of the Visual Basic Editor when you are first learning but it is handy to know what they are in case someone who is trying to help you refers to a feature in the VB Editor.  For example, I first learned about the Immediate Window when I kept seeing the Debug function used in code that community members on Excel forums would include in their VBA code solutions.  I had no clue what Debug did and since my Immediate Window was hidden, it took a lot of research for me to understand how and why the forum members were using that functionality.

I will continue to update this page as I do have more detailed blog posts that I am planning to write about the Visual Basic Editor and I will make sure to link to them when the articles are posted!

LESSON THREE: The Macro Recorder

What is the macro recorder.

The Record Macro feature in Excel and Word is hands down the best programming tool you could ask for.  You can visualize it as a tape recorder with 2 buttons: record and stop.  When the Record Macro functionality is set to record, the program writes the code and stores every action that you do within either Excel or Word in a VBA module.  You can then go back and look at the code that was written and do various things with it. The macro recorder may seem like a VBA beginner’s tool but even Excel gurus will use it all the time because they understand the efficiency the recorder can add.

How To Turn Your Onscreen Actions Into Code

The  Record Macro  feature can be accessed through the  Developer  tab and resides in the  Code  section.

How to launch Macro Recorder in Excel

You can also access it by clicking on the Record Macro icon in the bottom left-hand corner of your window (I did not know this for the longest time!)

 After clicking the icon you should see the  Record Macro  dialog box

Example of Macro Record dialog box

In this dialog box you have a couple of options:

Macro Name  [Required]: The recorder will always default a name in the dialog box but you are free to change it to a more meaningful name which I always recommend.  If you forget to change the name or want to change it later, don’t worry you can always change your Macro name after you’re done recording.  The naming convention allowed has to follow the below criteria:

  • The name must start with a letter
  • No spaces are allowed (use an underscore “_” if you want to put a space between words)

Shortcut Key  [Optional]:  This allows you to trigger or run the code you recorded via a keyboard shortcut.  As a word of caution, this does overwrite the default Excel shortcuts so as a best practice I always use the capital letter instead of the lowercase one.  For example, instead of making my macro shortcut Ctrl + c (which of course is the universal shortcut for copying), I would use Ctrl + Shift + C or Ctrl + C.

Store Macro In  [Required]: This lets you determine the location of where the recorder will store the code.  All currently open workbooks will show as options in the dropdown along with your Personal Macro file (if you have one set up)

Description  [Optional]:  I don’t think I have ever used this field as I always go back to my recorded code and immediately add comments to describe what the code does, but if you want you can type in a brief description of what you are about to record and the recorder with code that for you too.

Once you have filled out the dialog box to your liking and can click OK.  Your every move is now being recorded #BigBrother!  After you are done performing whatever action(s) you want to code just it the Stop Recording button (located in the Developer tab or down in the left-hand corner of your window)

Not Just For Excel

The macro recorder can also be found and used in  Microsoft Word . 

This feature used to be available in PowerPoint a few software iterations ago however it is believed that the Microsoft Office team, unfortunately, axed the feature because it was too buggy.  This was a huge mistake as even a buggy macro recorder would have been useful to users trying to teach themselves how to write VBA for PowerPoint (especially since there is not a whole lot of information on PowerPoint VBA out there on the web).  Fortunately for you, I have taken it upon myself to cover VBA for PowerPoint on this site so you won’t have to pull your hair out trying to figure it out!

Weakness Of The Macro Recorder

Like most things, the Recorder does have some weaknesses.  I will attempt to list some of the ones I run into on a regular basis.

  • Some Actions Aren't Recordable  -  There are some instances where I have tried to record a command and it would not get coded by the recorder.  This can get frustrating sometimes!
  • Some Programs Don't Have A Recorder  - Unfortunately, only Excel and Word currently have the Macro Recorder capability.  For the rest of the Office programs, you will have to teach yourself how to write the VBA code (or just read my blog) as each program has its own unique quirks and functionality.
  • Records Scrolls & Clicks  - This is probably more of an annoyance than a downfall but when I say the Macro Recorder records everything, I mean EVERYTHING!  That means every scroll your make and every cell you click on.  This usually causes the recorded macro code to be extremely lengthy.  I always recommend going in and deleting those extraneous lines of code as it will make your code slower, harder to read, and could potentially cause errors.

Can you think of any other downfalls to the Macro Recorder?  Leave a comment below and I will continue to update this list!

LESSON FOUR: Writing Code On Your Own

Where do i begin....

I'm sure there are many ways to teach people how to code but I am going to use my own methods that I have developed while explaining VBA to my co-workers over the years.  There is no way I can explain everything about coding here in this post but I will try to cover the basics and hopefully provide you with a good foundation that you can build on going forward.

How VBA is Structured

When explaining VBA code to someone who has no experience with computer languages, I like to use the analogy of a computer's folder hierarchy.  A typical folder structure on a PC might look like this:

Processes for beginners to write VBA code.

Hopefully everyone is comfortable seeing a structure like above and I am here to tell you that VBA coding is kind of setup like your computer's folder structure.  

Walking Through The Hierarchy

I am a visual learner myself and I always find it easiest to explain how to write a line of code with an example.  So let's write a VBA line that tells Excel to clear the contents of range A1:C50.

Instead of “drives” on a computer, VBA starts out with a reference to the computer program or in its terms "Application" that you are wanting to target. This can be Word, Excel, NotePad, Internet Explorer, or whatever program you want to access that also speaks the Visual Basic language.  

VBA assumes that whatever program you are storing your code in is the application you writing for.  Because of this, most people do not add the program name to their line of code but for purpose of this example, we will.  

With this in mind, the first part of our line of code will be as follows since we are wanting to work with the Excel Application:

'User' Folder

After we specify the Application we want to use, we then need to tell Excel which Workbook we want to modify.  You can do this in a couple of different ways:

This references the workbook that your VBA code is written in.  Typically you use this when you want the macro to only affect one workbook.  The benefit to this is that if you are viewing another workbook and you accidentally run your code it will not affect that workbook.   REMEMBER YOU CAN NOT UNDO A MACRO'S COMMANDS ONCE IT HAS RUN!

This refers to the workbook that you are currently viewing on your screen.  You will mainly use this type of reference when you create code that is aimed at affecting any workbook (for example if you write a macro to format text a specific way)

Writing your workbook level reference this way allows you to specify the name of the workbook you want to take action with.  I rarely refer to workbooks in this way because if I or someone else changes the name of the workbook, the macro will not be able to find it because it is referencing the old name.

I don't believe I have ever used this form of referencing a workbook but I will throw it out there just as an FYI.  When you use a number to reference a workbook you are referencing the order in which the workbooks were opened by Excel.  This can get tricky because if you have add-in files active, those are going to be the first workbooks opened when your Excel Application starts up.  You can determine which position your workbook is in by referencing its order in the VBA Project Window (location is shown in Lesson Three).

For this example, I am going to decide that I only want the scope of the macro to be within our workbook, so we are going to use a ThisWorkbook reference in our code:

'My Docs' Folder

After we specify the workbook we want our code to target we can then move on to the next level and specify the tab or worksheet that we want to get our code to point to.  Like the Workbook level, the Worksheet level has a few different referencing options:

This is very similar in functionality to ActiveWorkbook.  ActiveSheet points you to the tab that you are currently viewing.  This is great to use when you want to give your code the flexibility to run on a worksheet of your choosing.

In this form, you can spell out the name of the worksheet you want to target.  Because your sheet name is considered text and is not part of the VBA language, you will need to make sure your tab name is surrounded by quotes.  This lets the Worksheet know that you are spelling out a name of a tab.  If your name does not have quotes then VBA will think that Sheet1 is a variable that you created instead of text.

You can also target the order of your worksheets.  The above code is bringing you to the second worksheet from the left inside your workbook.

'Finances' Folder

Once we've specified the worksheet we want to modify, we then need to specify which cells we want to do something to (in this case, clear their contents).  There are a bunch of nifty ways to tell VBA which range we want but here I am going to cover the two main ways for referencing a single cell and a range of cells.

Reference A Single Cell

Range("A4")  - You can reference a cell by inserting its string name (range name surrounded with quotes). 

Cells( 4, 1)  - You can also reference a cell by using Cells( ) and inputting the row number and then the column number.  Note that Cells( ) will not accept a letter as an input for the column number.

Reference A Range of Cells

Range("A1:E5")  - Similar to referencing a single cell by name, you can also reference multiple cells by inserting the top-left cell of your range, a colon, and then the bottom right cell of the range.

Range(Cells(1,1),Cells(5,5))  - By embedding two Cells( ) references inside a Range( ) function, you can refer to multiples cells.  To do this, use a numerical reference (row number, then column number) for the top-left cell in your range and then do the same for the bottom-right cell of the range.

'Budget' Excel File

Congratulations! We have made it to the lowest level of what we wanted to modify or if you want to keep with the analogy we finally clicked on the folder that is storing our file.  Now in this folder, there are hundreds of files we can select so we need to specify which unique file we want to open.  

There are lots of different properties you can change and actions you can take at the Range level.  You can change a color, you can perform a calculation.....you can do pretty much anything you can do manually on a spreadsheet.  A benefit to Microsoft being the creator of both Office and Visual Basic is that most of the computer language is used in the program functionality.  So in our example, if we wanted to delete the values in our selected cells while in Excel, we would simply go to the  Home  tab, click on the  Clear  drop-down in the  Editing  section, and click  Clear Contents .  In VBA language to simulate doing the same thing all we have to add to our line of code " ClearContents ".  Pretty easy, right?!

What's Up With All the Periods?

Now that we have written a fully actionable line of code, you might be asking yourself what is the significance of putting a period in between each level in VBA? I like to compare the use of periods in VBA to the use of backward slashes in a folder's address.  What these two symbols do in their respective areas is connect the previous level to the next level.  Another way of picturing this is that they are the stairs from one floor to another in a home.  You can't go from the basement to the first floor without using the stairs to get there.

The VBA Defaults

The VBA Language creators at Microsoft realized that we as computer coders want to type as little as possible.  They found it in their hearts to program in some defaults or assumptions of which levels we may want to be targeting.  Below is a list of some of the major defaults that VBA will assume in Excel

  • Excel.Application
  • ActiveWorkbook
  • ActiveSheet

So if we leave out any of these levels in our code, the VBA language is going to assume the respective level reference above.  Let’s look at a few examples of how we can shorten our code and understand what that means.

  • Starting at the  Excel Application  level
  • ThisWorkbook.Worksheets("Sheet2").Range("B3").Value = 10
  • Starting at the  ActiveWorkbook  level
  • Worksheets("Sheet2").Range("B3").ColumnWidth = 4.3
  • Starting at the  ActiveSheet  level
  • Range("B3").Interior.Color = RGB(75, 172, 198)

The Visual Basic Editor Wants To Help You!

Last but not least I want to leave you with a tip and you may have noticed this while typing in the Visual Basic Editor .  Every time you type a period after one of your 'levels' you may have noticed that a little window pops up with a bunch of words.

What this floating box ends up displaying is every possible word that can come after your previous code phrase. This is a feature that I often ignored when first learning how to write VBA code because I really didn't understand what it was.  Eventually, I took the time to figure out what it was showing me and the below little box has helped prevent errors in my code ever since!

Microsoft VBA drop down menu while typing codde

LESSON FIVE: Where Do You Go From Here?

Building on your foundation.

Hopefully after reading the previous lessons I've provided you with enough information to give you a good start in taming the beast that is VBA.  I threw this last lesson into the mix with the hope of giving you some solid resources that you can use as you continue to learn how to write VBA code.  

Challenge Yourself!

The number one reason I have the knowledge I do about Microsoft Office is that I kept challenging myself on a regular basis.  I have taught so many people who had taken a few of my classes and got absolutely nowhere afterward because they weren't putting into practice what they had learned.  

The BEST way to learn this stuff is to keep using it on a regular basis.  I can guarantee that if you use Excel that there are literally hundreds of automating macros that you could create to make your work faster and more accurate.  

I have a co-worker that I have written a bunch of VBA procedures for and jokingly says that pretty soon all he is going to have to do in Excel is press a bunch of buttons that run macros and his work for the day will be done in 15 minutes.  The real funny thing is that through VBA I have saved him hours of manual work per day that he spent copying & pasting, reformatting, and double-checking his numbers.  

This is the power of Visual Basic for Applications and why knowing how to use it can almost guarantee you success in the future!

Resources On The Web

The following resources are sites that I used on a daily basis for education, inspiration, and troubleshooting while teaching myself Visual Basic for Applications.  Hopefully, this list will save you the time and effort of digging through the web to find your answers.

Google  - Google is my go-to search engine and I find that its results provide the best sites to answer my questions.  When I search for a question, I always begin my search by typing in "VBA Excel" or "VBA PowerPoint".  I have found that by starting your Google search with the words  VBA  and the program you are trying to target,  you can usually find the answer you are looking for within the first couple of results.

Blogs  - I tend to prefer written content while trying to learn something because it is typically the most efficient way for me to understand how to accomplish the technique I am trying to learn. I can quickly scan through an article and within seconds determine if the article is likely to have what I need or if I need a try another. There are many great blogs out there like Chandoo , How To Excel , ExcelJet , Excel Off The Grid , & Excel Campus (just to name a few). I use an RSS Feed Manager called Feedly to keep track of new posts from my favorites and this allows me to keep learning new techniques on a regular basis.

Forums  - There are many fantastic forums out there with lots of amazing folks who are willing to share their knowledge for free! My personal favorite is a forum called MrExcel has an awesome forum that is used all over the world.  If you have a question and can't find the answer, post on this forum and you will get multiple people trying to answer your question (usually within minutes)

YouTube  - The video tutorials being created over the past years has really become valuable. With the introduction of sections in YouTube videos, it is now easier to jump to a certain section of the video to get your answer without having to sit through the whole thing. There are also videos that show you how to build incredible interfaces and models within Excel. Some of my favorite channels include: Other Levels , MrExcel , Leila Gharani , Excel Is Fun , & The Office Lab .

Take A Class/Course!

I can't stress enough how much taking a 2-day long class exponentially grew my knowledge of VBA and its power.  While it was a lot of information to take in, it opened my eyes as to what VBA is truly capable of.  

I would definitely recommend going to a live class whether it’s in person or over the web.  The benefits of attending a live class are that you can ask questions that pop into your head right then and there and usually the teacher is willing to tailor the lecture to hit some areas that pertain to your specific line of work.  

While I do not currently offer any classes, this form of education is definitely on my long-term goals list as I believe this can be a very important resource in helping people understand coding in VBA.

What Do You Recommend?

I'm excited to hear from you and discover what has helped you become a VBA Guru!  Drop me a note in the comments section letting me know your most valuable VBA resources and I will try to put together a community list of everyone's suggestions in this post.  I will keep checking back to make sure the list contains the most current and best resources available.

I Hope This Helped!

Hopefully, I was able to provide you with a solid foundation where you understand the purpose of VBA and can start exploring the many strengths it has to offer. I want to make this the best introduction to VBA I can as I think this is a very important functionality in Excel that is far too overlooked. If you have any questions about a certain section or suggestions on how to improve this guide, please let me know in the comments section below.

After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!

Hidden Hacks For VBA Macro Coding

Keep Learning

How To Use A VBA Macro You Found On The Internet

How To Use A VBA Macro You Found On The Internet

You Finally Found It! Yes, I’ve been there! You’ve spent days scouring the internet to find a solution to a...

Quickly Learn Excel's Power Query Tool [Guide 2024]

Quickly Learn Excel's Power Query Tool [Guide 2024]

Quickly Learn Power Query Now! The goal of this guide/tutorial is to provide you with a great starting point in...

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.

Copy Excel VBA Code to a:

--- Regular Module

--- Worksheet Module

--- Workbook Module

--- Different Workbook

Allow Macros to Run in Your Workbook

Run an excel macro, create a macro shortcut, add macro to quick access toolbar, create a worksheet event macro, modify copied excel vba code.

--- Check the Sheet Names and Ranges

--- Add and Name Objects

--- Specify the Target Columns or Rows

Get the Sample File

Copy excel vba code to a regular module.

The programming language in Excel, and other Microsoft Office programs, is Visual Basic for Applications (VBA). This page shows how to copy VBA code into your Excel files, and store it in the correct location.

To see the steps for pasting a macro into a workbook, and running the macro, please watch this short video tutorial. The written instructions are below the video .

Instead of starting from scratch, if you need an Excel macro, you can often find sample code at reputable sites on the internet.

  • Copy the sample code that you want to use
  • Open the workbook in which you want to add the code
  • Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
  • Choose Insert | Module
  • Where the cursor is flashing, choose Edit | Paste

To run the code:

  • On the Excel Ribbon, click the View tab
  • At the far right, click Macros
  • Select a macro's name in the list, and click the Run button

Copy Excel VBA Code to a Worksheet Module

Another type of Excel code is Event code, which runs automatically when something specific occurs in the workbook.

For example, if you enter a number in a spreadsheet cell, or select an entry in a cell's drop down list, the worksheet has been changed. This could trigger the Worksheet_Change event.

Worksheet event code is stored on a worksheet module. To add worksheet event code to your worksheet, do the following:

  • Copy the code that you want to use
  • Select the worksheet in which you the code to run

worksheet code module

Copy Excel VBA Code to a Workbook Module

Another type of code is Workbook Event code, which should be added to the workbook code module:

  • Select the workbook in which you want to store the code
  • In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects
  • Right-click on the ThisWorkbook object, and choose View Code

Copy Excel VBA Code From a Different Workbook

To see the steps for copying a macro from one workbook to another, in any version of Excel, please watch this short video tutorial. The written instructions are below the video .

You may find code in a sample workbook online, and decide to add it to one of your workbooks. You can copy all the code in a module by doing the following:

  • Open both workbooks
  • In the Project Explorer, find your workbook, and the workbook with the code that you want to copy. The screenshot at the right, the code is in VBACodeCopy.xls and will be copied to MyForm.xlsm
  • In the workbook with the code, click the + sign to view the list of Modules
  • Click on the module that you want to copy, and drag it over the project where you'd like the copy placed.
  • Release the mouse button, and a copy of the module will appear in the workbook.

copy code module from different workbook

  • Select a macro in the list, and click the Run button

To use macros in Excel, you might need to enable them when the file opens. If you are using macros for the first time on your current computer, you might also need to adjust the macro security settings.

Follow the instructions below, to make these changes.

Enable Macros When Opening the File

When you open a workbook that contains macros, you might see a security warning, at the top of the worksheet, above the Formula Bar.

  • Click the Options button.
  • Click Enable This Content, to allow the workbook's macros to run, and click OK.

Check Your Macro Security Settings

If you haven't run macros before, you might need to change your macro security level. (You may have to clear this with your IT department.)

  • On the Ribbon, click the Developer tab, and in the Code group, click Macro Security.
  • NOTE: If the Developer tab is not visible, follow the steps here to display it.
  • In the Macro Settings category, under Macro Settings, click Disable all macros with notification
  • If you changed the setting, close the workbook, and then reopen it

After you copy a macro to a regular module, follow the steps below, to run the macro. If the macro does not run, check your macro settings .

To run an Excel macro, follow these steps:

  • Copy the macro code to a regular code module in your file.
  • Then, on the Ribbon's View tab, click the top part of the Macro button, to open the Macro window
  • In the list of macros, click on the macro that you want to run
  • Click the Run button

run an Excel macro

To make it easier to run your macros, you can create keyboard shortcuts for them.

Later, when you want to run one of your macros, just press the keyboard shortcut keys that you set up.

Create the Macro Shortcut

Follow these steps, to set up the keyboard shortcut for running a macro:

  • On the Ribbon, click the Developer tab, and in the Code group, click Macros.
  • In the Macro dialog box, click the macro for which you want to create a shortcut- CopyDailyRecords.
  • At the bottom right, click the Options button.

click Options

  • In the Macro Options window, click in the Shortcut Key box
  • NOTE : Using the Shift key is not required , but I recommend using it
  • This will help you avoid overwriting one of the built-in Excel shortcuts.

click Options

  • Click OK to complete the shortcut
  • Click Cancel in the Macros window, to close it, without running the macro

Run Macro With Keyboard Shortcut

When you want to run one of your Excel macros, just press the keyboard shortcut keys that you set up.

For example, press Ctrl+Shift+R , to run the macro named CopyDailyRecords, shown in the "Create a Macro Shortcut" example, above.

If you use a macro frequently, you can add its icon to the Quick Access Toolbar (QAT). This short video shows the steps, and the written instructions are below the video.

See more tips for using the Quick Access Toolbar with your macros , such as adding QAT buttons that run macros for a specific workbook only.

How to Add a Macro to the QAT

For example, here are the steps to add a macro to the QAT. The macro is named ToggleR1C1 and is stored in the Personal Macro Workbook, which is named Personal.xlsb.

  • At the right end of the QAT, click the drop down arrow

QAT More Commands

  • PERSONAL.XLSB is the file name for the Personal Macro Workbook

QAT add macro

  • In the QAT list, click the PERSONAL.XLSB!ToggleR1C1 macro

QAT modify button

  • Click OK, to close the Excel Options window.

The macro icon now appears on the QAT, and you can click it to run the macro.

macro icon on the QAT

To see the steps for creating an Excel Worksheet Change Event macro, watch this short video.

There are written steps on the Contextures Blog , and you can download the sample file used in this video .

If you copy VBA code into your Excel file, you might need to make changes to the object names, or other settings, so that the code works correctly in your file. Here are three things to check, before you try to run the code in your file:

Check the Sheet Names and Ranges

Add and name objects, specify the target columns or rows.

If there are sheet names or range references in the code, you can modify them, to match your workbook.

  • In the code, look for references to "Worksheets" to "Sheets", and change those to the sheet names in your workbook.
  • Also look for "Range" references, such as Range("A1:G100"), and adjust those to match the location of your data.

These references might be at the top of the procedure, in a Set statement:

or elsewhere in the code.

If you run the code without modifying the reference, you might see an error message: Run-time error '9': Subscript out of range

run-time error 9

To see where the problem is, click the Debug button, and a line of code will be highlighted in yellow.

highlighted code

To stop the code, click the Run menu, then click Reset.

vba run reset

Change the sheet name in the line that was highlighted, save the changes, and try the code again.

If the code refers to objects on the worksheet, be sure to add those objects in your workbook, and use the correct object name in the code.

For example, in the code for the Data Validation Combo Box , you'll need to add a combo box to the worksheet, and name it as TempCombo. Or, if your combo box has a different name, change the code references to match.

vba run reset

Some code is designed to run when a cell in a specific row or column is changed.

For example, in the sample code shown below, there is a red dot on the line that says column 3 is the only one where the change will occur.

NOTE: In all of these examples, you could use Row instead of Column , to limit the target to specific rows.

A) In your workbook, if you want the code to run when a cell in column E is changed, you could change the 3 to a 5.

B) Or, add more columns in the code. For example:

C) If you don't want to limit the code to a specific column, you could delete the two rows (If...End If) that are marked with red circles. In that case, the code will run for a change in every column.

D) If you want code to run on any column EXCEPT a specific column, use the Not Equal To operator -- <> -- instead of the equal sign. For example:

To see examples of workbook modules, worksheet modules and regular code modules, download the Add Code to a Workbook sample file . The zipped file is in xlsm format, and contains macros. Be sure to enable macros when you open the file, if you want to test the macros.

Related Tutorials

Create an Excel UserForm

Macro Troubleshooting Tips

UserForm with ComboBoxes

Edit Your Recorded Macro

About Debra

Last updated: December 27, 2023 4:21 PM

Excel Off The Grid

Useful VBA codes for Excel (30 example macros + Free ebook)

With macros, we can automate Excel and save time; big tasks or small tasks, it doesn’t matter.  All that matters is that we’ve become more efficient.

In this post, I share 30 of the most useful VBA codes for Excel that you can use today.

If you’ve never used VBA before, that’s fine.  Part 1 contains instructions of how to use the codes and part 2 contains the code sample themselves.

PART ONE: How to use VBA Macros

What is vba, setting up excel, using the visual basic editor, running a macro, part two: 30 useful vba codes for excel, hide all selected sheets, unhide all sheets, protect all selected worksheets, unprotect all worksheets, lock cells containing formulas, hide formulas when protected, save time stamped backup file, prepare workbook for saving, convert merged cells to center across, fit selection to screen, flip number signage on selected cells, clear all data cells, add prefix to each cell in selection, add suffix to each cell in selection, reverse row order, reverse column order, transpose selection, create red box around selected areas, delete all red boxes on active sheet, save selected chart as an image, resize all charts to same as active chart, refresh all pivot tables in workbook, turn off auto fit columns on all pivot tables, get color code from cell fill color, create a table of contents, excel to speak the cell contents, fix the range of cells which can be scrolled, invert the sheet selection, assign a macro to a shortcut key, apply single accounting underline to selection.

Visual Basic for Applications (VBA) is the programming language created by Microsoft to control parts of their applications. Most things which you can do with the mouse or keyboard in the Microsoft Office suite, you can also do using VBA. For example, in Excel, you can create a chart; you can also create a chart using VBA, it is just another method of achieving the same thing.

Advantages of using VBA

Since VBA code can do the same things as we could with the mouse or keyboard, why bother to use VBA at all?

Saves time:

VBA code will operate at the speed your computer will allow, which is still significantly faster than you can operate. For example, if you have to open 10 workbooks, print the documents, then close the workbook, it might take you 2 minutes with a mouse and keyboard, but with VBA it could take seconds.

Reduces errors:

Do you ever click the wrong icons or type the wrong words? Me too, but VBA doesn’t. It will do the same task over and over again, without making any errors. Don’t get me wrong, you still have to program the VBA code correctly. If you tell it to do the wrong things 10 times, then it will. But if we can get it right, then it can remove the errors created by human interaction.

Completes repetitive actions without complaining:

Have you ever had to carry out the same action many times? Maybe creating 100 charts, or printing 100 documents, or changing the heading on 100 spreadsheets. That’s not fun, nobody wants to do that. But VBA is more than happy to do it for you. It can do the same thing in a repetitive way (without complaining). In fact, repetitive tasks is one of the things VBA does best.

Integration with other applications:

You can use VBA in Word, Access, Excel, Outlook and many other programs, including Windows itself. But it doesn’t end there, you can use VBA in Excel to control Word and PowerPoint, without even needing to open those applications.

What is programming?

Programming is simply writing words in a way which a computer can understand. However, computers are not particularly flexible, so we have to be very specific about what we want the computer to do, and how we tell it to do it. The skill of programming is learning how to convey the request to the computer as clearly, as simply and as efficiently as possible.

What is the difference between a Macro and VBA?

This is a common question which can be confusing. Put simply, VBA is the language used to write a macro – just in the same way as a paragraph might be written using the English language.

The terms ‘macro’ and ‘VBA’ are often used interchangeably.

The golden rule of learning VBA

If you are still learning to write VBA, there is one thing which will help you. While it may be common practice, to copy and paste code, it will not help you to learn VBA quickly. Here is the one rule I am going to ask you to stick to… type out the code yourself .

Why am I asking you to do this? Because it will help you learn the VBA language much faster.

Let’s get started

Now you know what VBA is, why you should use it, and the golden rule, so there is only one thing left to do… let’s get started!

Before you can get stuck in with using the code in this post, you must first have Excel set up correctly. This involves:

  • Ensuring the correct macro security settings have been applied
  • Enabling the Developer ribbon.

Macro security settings

Macros can be used for malicious purposes, such as installing a virus, recording key-strokes, etc. This can be blocked with the security settings. However, if the settings are set too high, you cannot run any macros, or too low, you will not be protected. Neither of these is a good option.

Let’s apply suitable settings which will give you the power to decide when to allow macros or not.

  • In Excel, click File > Options

Excel Options - Trust Centre

  • Click OK to close the Trust Centre, then OK again to close the Excel Options.

Workbooks containing macros will now be automatically disabled until you click the Enable Content button at the top of the screen.

Enable the Developer ribbon

The Developer ribbon is the place where all the VBA tools are kept. It is unlikely that this is already enabled, unless you or your IT department have already done so.

Look at the top of your Excel Window if you see the word ‘Developer’ in the menu options, then you are ready to go. You can skip straight ahead to the next part. However, if the ‘Developer’ ribbon is not there, just follow these instructions.

  • In the Excel Options dialog box, click Customize Ribbon

Enable Developer Ribbon

  • Click OK to close the Excel Options

The Developer ribbon should now be visible at the top of the Excel window.

File format for macro enabled files

Saving xlsx with a macro error

Generally, the .xlsm (Excel Macro-Enabled Workbook) file format should be used for workbooks containing macros. However .xlam (Excel Add-in), .xlsb (Excel Binary Workbook) and .xltx (Excel Macro-Enabled Template) are scenario specific formats which can also contain macros.

The legacy .xls and .xla file formats can both contain macros. They were superseded in 2007, and should now be avoided.

The basic rule is… if you don’t know, go for .xlsm .

Personal macro workbook

If we want macros to be reusable for many workbooks, often the best place to save them is in the personal macro workbook.

A personal macro workbook is a hidden file which opens whenever the Excel application opens.

How to create a personal macro workbook?

A personal macro workbook does not exist by default; we have to create it. There are many ways to do this, but the easiest is to let Excel do it for us.

Developer Ribbon - Record Macro

  • Do anything in Excel, such as typing your name into cell A1.

Developer Ribbon - Stop Recording

In the next part, we will learn how to use the Visual Basic Editor, which gives us access to the personal macro workbook.

The Visual Basic Editor (or VBE as it can be known) is the place where we enter or edit VBA code. The Visual Basic Editor is found within the Developer Ribbon

In Excel, click Developer > Visual Basic to open the VBE.

Alternatively, you could use the keyboard; press ALT+F11 (the + indicates that you should hold down the ALT key, press F11, then release the ALT key), which toggles between the Excel window and the VBE.

The Visual Basic Editor Window

Parts of VBE

Within the top left of the VBE, we will see a list of items which can contain VBA code (known as the project window)

Double-clicking any sheet name, workbook or module, will open the code window associated with that item. VBA code is entered into the code window.

Unless you have specific reasons, the best option is to enter the macro into a module. To create a module, click Insert > Module within the VBE.

There are many ways to run VBA code. This section is not exhaustive, but is intended to provide an overview of the most common methods.

Running a macro from within Visual Basic Editor

When testing VBA code, it is common to execute that code from the VBE.

Click anywhere within the code, between the Sub and End Sub lines, choose one of the following options:

  • Click Run > Run Sub/UserForm from the menu at the top of the VBE
  • Using the keyboard, you can press ALT+F5

Run macro from VBE

The code you entered will be executed.

Running a macro from within Excel

Once the code has been tested and in working order, it is common to execute it directly within Excel. There are lots of options for this too (including events, or user defined functions), however the three most common methods I will show you are:

Run from the Macro window

Developer Ribbon - Macros

Create a custom ribbon

Having macros always available in the ribbon is a great time saver. Therefore, learning how to customize the ribbon is useful.

  • Click New Tab to create a new ribbon tab, then click New Group to create a section within the new tab.
  • In the Choose commands from drop-down, select Macros. Select your macro and click Add >> to move the macro it into your new group.

Customize Ribbon - to run macro

  • Click OK to close the window.

Insert button for macro

Create a button/shape on a worksheet

Macros can be executed using buttons or shapes on the worksheet.

  • To create a button, click Developer > Insert > Form Control > Button
  • Draw a shape on the worksheet to show the location and size of the button

Assign Macro to button

  • Right-click on the button to change the description

Right-click Assign Macro

Alternatively, a macro can be assigned to a shape. After creating a shape, right-click on it and select Assign Macro… from the menu, then follow the same process as for a button.

What does it do?

Hides all the selected sheets.

Excel requires at least one active worksheet. If all the visible sheets are selected, to avoid an error, the VBA code will not hide the last sheet.

For other examples of hiding worksheets check out these posts:

  • Macro to hide all sheets except one
  • Hide all sheets except one with Office Scripts

Makes all worksheets visible.

Protects all the selected worksheets with a password determined by the user.

Unprotects all worksheets with a password determined by the user.

Password protects a single worksheet with cells containing formulas locked, all other cells are unlocked.

When the active sheet is protected, formulas will not be visible in the formula bar. Uses a predefined password of mypassword .

Save a backup copy of the workbook with a time stamp.

The macro will, for each worksheet:

  • Close all group outlining
  • Set the view to the normal view
  • Remove gridlines
  • Hide all row numbers and column numbers
  • Select cell A1

The first sheet is selected.

After running the macro, every worksheet in the workbook will be in a tidy state for the next use.

Changes all single row merged cells into center across formatting.

Zoom the screen on the selected cells.

Flips the number signage of all numeric values in the selected cells

Clears all cells in the selection which are constants (i.e. not formulas).

Adds a prefix to each cell in the selected cells (excludes formulas and blanks).

Adds a suffix to each value in the selected cells (excludes formulas and blanks).

Reverses the order of all rows of data in the selection.

Reverses the order of all column data in the selection.

Transposes the selected cells with a single click.

Draws a rectangle shape to fit around the selected cells.

Having created the red boxes in the macro above. This code removes all the red boxes on the active sheet with a single click.

Saves the selected chart as a picture to the file location contained in the macro.

Select the chart with the dimensions you wish to use, then run the macro. All the charts will resize to the same dimensions.

Refresh all the Pivot Tables in the active workbook.

By default, PivotTables resize columns to fit the contents. This macro changes the setting for every PivotTable in the active workbook, so that column widths set by the user are maintained.

Returns the RGB and Hex for the active cell’s fill color.

Creates or refreshes a hyperlinked table of contents on a worksheet called “TOC”, which is placed at the start of a workbook.

Excel speaks back the contents of the selected cells

Fixes the scroll range to the selected cell range. It prevents a user from scrolling into other parts of the worksheet.

If a single cell is selected, the scroll range is reset.

Select some worksheet tabs, then run the macro to reverse the selection.

Assigns a macro to a shortcut key.

Single accounting underline is a formatting style which is not available in the ribbon. The macro below applies single accounting underline to the selected cells.

Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere Start using Office Scripts and Power Automate to automate Excel in new ways.

14 thoughts on “Useful VBA codes for Excel (30 example macros + Free ebook)”

I appreciate your efforts to create this exhaustive compilation of VBA macros. Thanks you for sharing this with us all.

Thank you – I’m glad you found it useful 🙂

Much appreciated, this was very helpful, and demonstrates your commitment to Excel in our world, thank you !!!

Nice, big and very clean job, Thank you very much!

Very useful! Thank you!

Wonderful job. I hope I can find even more.

Hi Amelia – my eBook of VBA Macros contains 100 examples that you can follow. You can check it out here: https://exceloffthegrid.com/100-excel-vba-macros/

Hide all selected sheets (ALTERNATE CODE) ==================================================== Instead of looping the selected sheets in order to hide them one-at-a-time, you can hide all the selected sheets all at once using this single line of code. As for the case when all of the sheets are selected, I decided it might be better to tell the user that all of the sheets cannot be hidden instead of automatically assuming the last selected sheet should not be hidden.

Sub HideAllSelectedSheets() On Error GoTo CannotDoThat ActiveWindow.SelectedSheets.Visible = False Exit Sub CannotDoThat: MsgBox “You cannot hide all of the sheets!!!!”, vbCritical End Sub

Hi Rick – thank you. Yes I agree that is a nice solution.

Flip number signage on selected cells (ALTERNATE CODE) ==================================================== You can flip the sign of all numbers in the selection using just a single line of code…

Sub FlipNumberSignage() Selection.Value = Evaluate(“-” & Selection.Address) End Sub

Thanks Rick, nice use of Evaluate.

wonderful, great job

Thank Mazhar – I’m glad you found it useful 🙂

Leave a Comment Cancel reply

15 Ways to Run a VBA Macro in Microsoft Excel

Do you want to learn how to run a VBA macro in Microsoft Excel? This guide will show you all the methods you can use to run your VBA code.

Microsoft Excel is a powerful spreadsheet application that offers users a variety of features and capabilities. One of the most popular features of Excel is the ability to create and run VBA macros.

A macro is a small scripts written in the VBA (Visual Basic for Applications) programming language that can be run in your desktop Excel app.

Macros can save you a lot of time and energy when working in Excel. They can be used to automate tedious tasks and save you hours of work each week.

But in order to leverage this time saving tool, you will need to know how to run your VBA macros.

Follow this guide and you’ll be able to start running macros like a pro in no time!

Run VBA Macro from the Developer Tab

The most common method for running a macro is from the Developer tab in the Excel ribbon.

This tab is hidden by default, so you will need to enable the Developer tab in your desktop Excel app first.

add macro using vba

Follow these steps to run a VBA macro from the Developer tab.

  • Go to the Developer tab.
  • Press the Macros command in the Code section.

add macro using vba

This will open the Macro menu which lists all the macros available to run.

  • Select the macro which you want to run.
  • Press the Run button.

That’s it! Your chosen macro code will now execute!

💡 Tip : Use the Macros in dropdown option to select the location of macros to run. You can select a specific workbook, All Open Workbooks , or This Workbook .

Run VBA from the View Tab

add macro using vba

The Macro command is also available in the View tab.

Go to the View tab and press the Macros button to launch the Macros menu.

This opens the same Macro dialog box as before and you can select the macro and press the Run button.

Run VBA Macro from Macro Menu with a Keyboard Shortcut

There is an easier way to open the Macro menu! There’s no need to use the Developer or View tab since there is a dedicated keyboard shortcut to open the Macro menu.

You can use the Alt + F8 keyboard shortcut to open the Macro menu.

Run VBA Macro from a Keyboard Shortcut

You can entirely bypass the Macro dialog box by assigning a keyboard shortcut to your desired Macro.

If this is a macro that you want to use a lot, then assigning a shortcut is a good way to avoid the many clicks needed when running a macro through the Macro menu.

add macro using vba

Follow these steps to assign your macro a dedicated keyboard shortcut.

  • Open the Macro menu.
  • Select the macro to which you want to assign a keyboard shortcut.
  • Press the Options button.

add macro using vba

This will open up the Macro Options menu where you can add a description for the macro and assign a keyboard shortcut.

  • Add a character into the Shortcut key input box.
  • Press the OK button in the Macro Options menu.
  • Press the Cancel button in the Macros menu.

⚠️ Warning : This chosen shortcut key will override any existing keyboard shortcut, so you should avoid using keys taken by commonly used shortcuts such as copy, paste etc.

add macro using vba

A lot of Ctrl and single key combinations are already taken with commonly used commands, so you might want to create a Ctrl + Shift shortcut instead.

💡 Tip : Hold the Shift key while entering a key in the Shortcut key input to create a Ctrl + Shift shortcut.

Run VBA Macro from a Form Control Button

If other people are using your spreadsheet solution, they might not realise they can run your macros to help complete their work.

This is where a button is the preferred choice to run a macro. It makes the act of running your macro easy and obvious!

You can create a Form Control Button to run the macro when you click the button. This can be placed anywhere in the spreadsheet since is floats over top of the grid. This means it won’t interfere with the rest of your data or formulas.

add macro using vba

Follow these steps to insert a Form Control Button and assign a macro to it.

  • Click on the Insert command.
  • Choose the Button option found in the Form Controls section.

This will not actually insert a button yet. You will notice your cursor has now turned into a small black plus sign. This will allow you to draw a button in your sheet.

add macro using vba

  • Left click and drag anywhere in the sheet.

When you release the click and drag action, the Assign Macro menu will immediately pop up and you will be able to assign your macro to the button.

add macro using vba

  • Select your macro.
  • Press the OK button.

add macro using vba

Now you have a button in your sheet which will run your select VBA macro when clicked.

💡 Tip : Righ click on the button and select Edit Text to change the text displayed on the button.

Run VBA Macro from any Shape, Icon, or Image

Form Control buttons are pretty ugly and outdated. They also don’t have many options to customize the look, but thankfully they aren’t the only way to make a button to run your macros!

There are some much more stylish options like using an image, shape, or icon as a button to run your macros.

add macro using vba

Follow these steps to assign a macro to any object such as an image, shape, or icon.

  • Right click on the object.
  • Select Assign Macro option from the menu.
  • Select the macro from the Assign Macro menu.

add macro using vba

Now when you click on the shape, image, or icon it will execute the macro code!

Run VBA Macro from a Quick Access Toolbar Command

Another option is to add your most frequently used macros to the Quick Access Toolbar.

The Quick Access Toolbar is a customizable set of commands that are always visible so you can easily use them at any time. You can even add a macro so it can be run with a click.

add macro using vba

Follow these steps to add a macro to your Quick Access Toolbar .

  • Right-click anywhere on the Quick Access Toolbar.
  • Select the Customize Quick Access Toolbar option from the menu.

add macro using vba

This will open the Excel Options menu on the Quick Access Toolbar section.

  • Select Macros from the Choose commands from dropdown.
  • Select the macro you want to add to your Quick Access Toolbar.
  • Press the Add button.

When you press the Add button you will see the selected macro gets added to your list of commands. You can use the Up and Down arrow buttons to adjust the order this macro will appear in your commands.

💡 Tip : Press the Modify button in the Excel Options to change the icon and label of the macro that will appear in your Quick Access Toolbar!

add macro using vba

Now you should have a new icon available in the Quick Access Toolbar. Click on this to run your select macro.

💡 Tip : An easy way to use the commands in your Quick Access Toolbar is with the Alt hotkey shortcuts. In this example, the command is in the 8th position starting from the undo command, so you can press the Alt + 8 to run the macro.

Run VBA Macro from a Custom Ribbon Command

If you have an entire repertoire of macros and you’re running out of room in the Quick Access Toolbar, then adding a custom ribbon tab to organize your macros could be the ideal solution.

Excel allows you to add your own custom ribbons and fill them with your favorite macros as well as any other commands you frequently use.

add macro using vba

Follow these steps to add a macro to the Excel ribbon.

  • Right click anywhere on the Excel ribbon.
  • Select the Customize the Ribbon option from the menu.

add macro using vba

This will open the Excel Options menu on the Customize Ribbon section.

  • Press the New Tab button to create your new ribbon tab.

add macro using vba

  • Press the Rename button to give your tab a name.

Each tab will need at least one group, and this is automatically created when you create a new tab. You can also rename the group. Both of these names will be displayed in your Excel ribbon.

add macro using vba

Now you will be able to add a macro into the new tab and group.

  • Select the Macros option from the Choose commands from dripdown.
  • Select the macro which you want to add into the ribbon.

💡 Tip : Select the tab and use the Up or Down arrow buttons to adjust the position of the new tab in your ribbon.

add macro using vba

You now have a new custom tab that can hold all your most frequently used macros. 😃

Run VBA Macro from Visual Basic Editor Run Menu

The visual basic editor (VBE) is the environment where you write VBA code, so it makes sense that you should be able to also run your code from it.

A lot of people like to test their code as they develop their solutions and this means frequently running your macros from the the editor. Your current code can always be run from the Run menu in the VBE.

add macro using vba

Follow these steps to run your macro from the Run menu in the visual basic editor.

  • Select the macro you want to run.

You can select the macro by either placing the cursor in the code or selecting the macro name from the dropdown menu in the top right.

add macro using vba

  • Go to the Run menu.
  • Select the Run Sub/UserForm option from the menu.

This will run your selected macro!

Run VBA Macro from Visual Basic Editor Toolbar

add macro using vba

The visual basic editor comes with a toolbar for easy access to the most frequently used commands.

Follow these steps to run your macro from the toolbar.

  • Press the Play button in the toolbar.

📝 Note : If you don’t see this toolbar you might need to enable it. Go to the View menu then Toolbars and check the Standard option.

Run VBA Macro from Visual Basic Editor Keyboard Shortcut

There are a lot of very useful keyboard shortcuts for using the visual basic editor .

Running a macro from the VBE is a very common task, so it’s no surprise there is also a keyboard shortcut available for this.

Press the F5 key while in the VBE and the currently selected macro will run!

Run VBA Macro from Another Macro

You can easily run a macro from a macro in Excel.

This is a good practice when it comes to programming. Creating smaller procedures and then reusing them within your main macro can be more efficient to run and easier to maintain the code.

add macro using vba

You can easily run any macro from within a macro using a single line of code. The above example will run the ExampleCode macro from the MainCode macro.

Run VBA Macro from a Worksheet Event

Did you know you can automatically run a macro?

You can automatically run a macro based on events that happen in your Excel worksheet!

For example, you can have a macro run anytime someone changes a value in the sheet.

add macro using vba

Follow these steps to create a worksheet event-driven macro.

  • Select the Sheet in which you want to trigger the macro. All your workbook sheets will be listed in the Microsoft Excel Object folder of the VBE Projects.
  • Select the Worksheet options from the dropdown menu.
  • Select the event type that should trigger your macro.

When you select the type of event, it will insert a bit of code into the editor. For example, the Change event trigger will insert the above code.

  • Place any code you want to run when the event occurs inside the generated code.

If you have an existing macro that you want to run, you can call it using a Call YourMacroName single line of code.

This macro will now run anytime you make changes in any cell within Sheet1 .

You might want to limit the macro to only running when the change event occurs in a particular cell or range. This is possible by setting conditions for the Target in your code.

In the above example, the ExampleCode macro will only be called when changes are made to cell A1 .

Run VBA Macro from a Hyperlink

add macro using vba

Did you know you can trigger your macros to run when you click on a hyperlink in Excel?

This is particular worksheet event method is worth its own mention!

add macro using vba

The above code will run every time you click the hyperlink in cell B2 in Sheet2 .

The code will execute and then take you to the hyperlinked address!

Run VBA Macro from a Workbook Event

There is also the possibility to automatically run a macro based on workbook events such as when you open or close the file.

This is a great option to make sure a task is performed before you do anything else in your workbook.

add macro using vba

Follow these steps to run a macro automatically when you open your Excel file.

  • Select ThisWorkbook found in the Microsoft Excel Object folder of the VBE Projects.
  • Select Workbook from the dropdown menu.
  • Select Open from the event type dropdown menu.

This will insert the above code into the code editor. You can then add any code inside which you want run when you open the file. You can also call any macro here with the Call YourMacroName single line of code.

Conclusions

VBA macros can be used to automate your tasks in Excel, so it’s important you know how to run them.

There are many methods to run your desired macros depending on your situation.

You can use the Excel ribbon, a keyboard shortcut, or a customized quick access command when you want to manually run a macro. Also, you can attach your macros to run from buttons in the workbook to make it more user friendly.

You might need to occasionally run your VBA macros while developing your solutions to test them. This can be done several ways in the visual basic editor.

You can even trigger your macros based on certain worksheet or workbook events for the ultimate in automation.

Are you using macros in Excel. Did you know all these methods to run your macros? Do you know any others? Let me know in the comments below!

About the Author

John MacDougall

John MacDougall

Subscribe for awesome Microsoft Excel videos 😃

add macro using vba

I’m John , and my goal is to help you Excel!

You’ll find a ton of awesome tips , tricks , tutorials , and templates here to help you save time and effort in your work.

  • Pivot Table Tips and Tricks You Need to Know
  • Everything You Need to Know About Excel Tables
  • The Complete Guide to Power Query
  • Introduction To Power Query M Code
  • The Complete List of Keyboard Shortcuts in Microsoft Excel
  • The Complete List of VBA Keyboard Shortcuts in Microsoft Excel

add macro using vba

Related Posts

7 Ways to Disable Macros in Microsoft Excel

7 Ways to Disable Macros in Microsoft Excel

Jan 19, 2024

Excel VBA macros undoubtedly automate repetitive tasks, ensuring swift...

How To Record a VBA Macro in Microsoft Excel

How To Record a VBA Macro in Microsoft Excel

Jan 15, 2024

Do you want to automate highly repetitive yet simple tasks in Microsoft Excel?...

5 Ways to Edit Macros in Microsoft Excel

5 Ways to Edit Macros in Microsoft Excel

Aug 11, 2023

Do you start recording a macro from scratch if you need to modify a small...

Get the Latest Microsoft Excel Tips

add macro using vba

Follow us to stay up to date with the latest in Microsoft Excel!

Create New Sheet Using VBA in Excel (Sheets.Add)

A common Excel task when automating tasks in Excel using VBA is to add new sheets.

In VBA, this can easily be done using the Sheets.Add method.

In this article, I will cover various scenarios where you can use VBA to create a new sheet in Excel.

This Tutorial Covers:

Sheets.Add Method

Sheets.Add is a method that adds a new sheet. You can use this to add a regular worksheet, a chart sheet, or a macro sheet.

When a new sheet is added, it automatically becomes the active sheet.

Below is the syntax of the Sheets.Add method:

  • Before : Specify the sheet before which you want the new sheet to be added
  • After : Specify the sheet after which you want the new sheet to be added
  • Count : Specify the number of sheets you want to add
  • Type : Specify what type of sheet you want to add (worksheet, chart sheet, or macro sheet). If not specified, it would add a worksheet.

Note that all of these arguments are optional, and if you just use Sheets.Add , it would create one new worksheet before the active sheet.

Now that you know about the ‘ Sheets.Add ‘ method, let’s see it in action in some practical examples.

Create One New Sheet

Below is the VBA code that will add one new sheet in the current workbook (in which you are running the code)

The new worksheet is always added to the left of the active sheet.

In the above code, I have used Sheets.Add method. You can also use Worksheets.Add

Create Multiple New Sheet

If you want to insert multiple new sheets, you can use the Sheets.Add method multiple times.

Below is the VBA code that will add three new sheets to the workbook.

In the above, I have used the Sheets.Add method three times, so this adds three new worksheets.

You can also use a loop to add multiple new sheets, as shown below in the code that adds five new sheets.

The above code uses a For Next loop to add five new sheets. In case you want to run the loop any other number of times, you can change the value in the code.

Create a Sheet with a Specific Name

The above code first adds a new worksheet and assigns it to a variable called ws .

It then renames the sheet to “NewName”.

Add a New Sheet at the Beginning

Below is the VBA code that will add a new sheet at the beginning (so that it becomes the first sheet in the workbook)

In the above code, I have used the Before parameter of the Add method. I have used Before:=Sheets(1), so it adds the new sheet before the first sheet.

You can also tweak the code so that it adds a new sheet at the beginning and also renames it, using the name you specify in the code.

Below is the code that does this:

Running the code again will generate another sheet at the start, but Excel will automatically adjust the name (like “FirstSheet (2)”) to avoid any naming conflicts.

Add a New Sheet at the End (After the Last Sheet)

Below is the VBA code that will add a new sheet at the end of all sheets in the workbook.

The above code uses Sheets.Count to get the count of all the worksheets in the workbook. It then uses this count number to add the sheet after this last worksheet.

You can also use the below (slightly modified) code to add the sheet at the end and give it the specified name.

If you run the code more than once, Excel will automatically rename subsequent sheets (e.g., “LastSheet (2)”, “LastSheet (3)”, and so on) to prevent any naming conflicts.

Create a Sheet With the Name from a Cell

Below is the VBA code that creates a worksheets and names it using the value in cell A1 of the active sheet.

In the above code, I have used the variable sheetname to store the value in cell A1. This variable is then used to assign the name to the newly added sheet.

I have also placed some checks and balances in place in this code to handle potential errors.

In case the cell is empty or it has a name that can’t be used for a worsheet, the code will show an error and stop.

Add Sheet Before Or After a Specific Sheet

Below is the VBA code that adds a new sheet named “Summary” before the sheet named “Sales”

While this code works, it doesn’t check whether the ‘Sales’ sheet exists or not.

Below is a more robust code that checks for the sheet’s existence first and then adds a new sheet after the Sales sheet.

Add Sheets from Values in a List/Range

Sometimes, you may want to add new sheets in bulk and assign them names based on the values in a list in the worksheet.

Below, I have some values in the range A1:A10, and I want to insert ten new sheets where the names should be taken from this range.

Below is the VBA code that would do this.

The above code loops through each cell in the range and then adds a new sheet for each of these names at the end. If you have a blank cell in the specified range, it will be ignored.

Add a Chart Sheet

So far, we have been talking about worksheets in Excel. Sheets.Add method, by default, adds a worksheet.

But you can also use it to add a chart sheet (which is a sheet dedicated to holding and displaying a single chart/graph).

Below is the VBA code to add a chart sheet.

Create a New Sheet and Copy Data from Another Sheet

The below VBA code will add a new sheet, copy all the cells from a specified sheet (Sheet1), and then paste it into the newly added sheet.

When using this code, ensure you have a sheet named “SourceSheet” in the workbook, or replace “SourceSheet” in the code with the name of your source sheet.

Create a New Sheet in Another Workbook

You can also add new sheets to other workbooks that are open already. In case you want to create a new sheet in a closed workbook, then you need to first open that closed workbook and then add a new sheet to it.

Let’s see both of these examples.

VBA Code to Create Sheet in Already Open Workbook

Below is the code that will create a new sheet in an open workbook named Example.xlsx, and give this new worksheet a name (NewSheet)

This is the basic code you can use and then modify accordingly. For example, you can put some checks to make sure that the workbook is open and the sheet with the same name does not exists.

VBA Code to Create Sheet in a Closed Workbook

Below VBA code will open the workbook named Example.xlsx, add a new sheet to it, and then close it.

While this alone may not be useful in practical scenario, you can extend this code and use this to open a closed workbook, add a new sheet, and then copy data from some other sheet to this newly created sheet, and then close it. This would certainly be immensely helpful for some people.

While this code as is may not be useful in practical scenario, you can extend this code and use this to open a closed workbook, add a new sheet, and then copy data from some other sheet to this newly created sheet, and then close it.

This would certainly be immensely helpful for some people.

Remember to change the workbook name and the file path based on your requirements.

Create a New Sheet from Template

If you’re trying to create a new sheet in the same workbook using a template sheet (an existing sheet within that workbook acting as the template), you can do this with the code below:

This code starts by checking if there’s a worksheet named “Template” in the current workbook.

If it finds one, it creates a copy of that template and places it at the end of all worksheets.

It then renames this new sheet to “NewSheet”. If the template does not exist, a message box pops up notifying the user.

You can change the sheet name in the code accordingly,

Create a New Sheet if It Doesn’t Exist

Here’s a VBA code that will ask the user for a sheet name and then check if that sheet already exists in the workbook. If it does not exist, it will add one with the given name, else it will show you a message box .

Remember that the changes done by VBA can not be reversed. So it’s essential to ensure you don’t accidentally overwrite or duplicate sheets, especially in workbooks with many sheets.

This VBA code helps prevent such mishaps by checking for the existence of a sheet before trying to create one with the same name.

What is VBA in Excel?

Other Excel VBA articles you may also like:

  • VBA Copy Sheet to New/Existing Workbook
  • Delete Sheet in Excel Using VBA
  • Activate Sheet using VBA
  • Working with Worksheets using Excel VBA
  • VBA Protect and Unprotect Sheets

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

© TrumpExcel.com – Free Online Excel Training

Privacy Policy  | Sitemap

Twitter | Facebook | YouTube | Pinterest | Linkedin

add macro using vba

add macro using vba

February 2024 updates for Microsoft Office

Introduction.

Microsoft released the following security and nonsecurity updates for Office in February 2024. These updates are intended to help our customers keep their computers up to date. We recommend that you install all updates that apply to you.

To download an update, select the corresponding Knowledge Base article in the following list, and then go to the "How to download and install the update" section of the article.

List of Office updates released in February 2024

Microsoft office 2016, sharepoint server subscription edition, microsoft sharepoint server 2019.

Facebook

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

add macro using vba

Microsoft 365 subscription benefits

add macro using vba

Microsoft 365 training

add macro using vba

Microsoft security

add macro using vba

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

add macro using vba

Ask the Microsoft Community

add macro using vba

Microsoft Tech Community

add macro using vba

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

IMAGES

  1. VBA Macros

    add macro using vba

  2. Macro using VBA Editor

    add macro using vba

  3. 3 Tips for Writing Formulas with VBA Macros in Excel

    add macro using vba

  4. Excel VBA macro tutorial for beginners with examples

    add macro using vba

  5. How To Create A Vba Macro Or Script In Excel

    add macro using vba

  6. Excel VBA macro tutorial for beginners with examples

    add macro using vba

VIDEO

  1. VBA Macro for automated report

  2. Advantage of using VBA/macro to save UserForm as PNG/PDF during runtime #excel

  3. How Much VBA & Macro Are Required For Entry Level MIS Executive Job ? BigDataKB.com

  4. VBA1

  5. VBA| VBA & Macro

  6. VBA Macro Excel

COMMENTS

  1. Insert and run VBA macros in Excel

    Press Alt + F11 to open Visual Basic Editor (VBE). 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. Copy the VBA code (from a web-page etc.) and paste it to the right pane of the VBA editor (" Module1 " window).

  2. Getting started with VBA in Office

    06/07/2022 10 contributors Feedback In this article When to use VBA and why VBA Programming 101 Macros and the Visual Basic Editor Programming tips and tricks Show 2 more Are you facing a repetitive clean up of fifty tables in Word? Do you want a particular document to prompt the user for input when it opens?

  3. How to Create a Macro in Excel (In Simple Steps)

    1. Right click CommandButton1 (make sure Design Mode is selected). 2. Click View Code. The Visual Basic Editor appears. 3. Place your cursor between Private Sub CommandButton1_Click () and End Sub. 4. Add the code line shown below. Note: the window on the left with the names Sheet1 (Sheet1) and ThisWorkbook is called the Project Explorer.

  4. Quick start: Create a macro

    How? Before you record a macro Macros and VBA tools can be found on the Developer tab, which is hidden by default, so the first step is to enable it. For more information, see Show the Developer tab. Record a macro In the Code group on the Developer tab, click Record Macro.

  5. Writing VBA Macros From Scratch

    Start Here VBA VBA Tutorial Learn the essentials of VBA with this one-of-a-kind interactive tutorial. VBA Code Generator Essential VBA Add-in - Generate code from scratch, insert ready-to-use code fragments VBA Code Examples 100+ VBA code examples, including detailed walkthroughs of common VBA tasks. Excel Formulas Tutorial

  6. Learn VBA Macro Coding Basics In Excel [2024 Guide]

    LESSON TWO: The Visual Basic Editor. The Visual Basic Editor is your workspace for creating your VBA code.. The editor can be accessed through your Developer Tab or by using the shortcut Alt + F11.. The editor will display in a completely separate window from your Office Application and each one of the programs in the Office Suite has its own VBA Editor (so you can have the Excel and ...

  7. Dynamically insert macro into a new excel workbook

    vba - Dynamically insert macro into a new excel workbook - Stack Overflow Dynamically insert macro into a new excel workbook Ask Question Asked 12 years, 1 month ago Modified 9 years, 11 months ago Viewed 23k times 9 How can an existing macro be inserted into a new excel workbook?

  8. Create or run a macro

    To use this macro in any new documents you make, be sure the Store macro in box says All Documents (Normal.dotm). To run your macro when you click a button, click Button . Click the new macro (it's named something like Normal.NewMacros.<your macro name>), and click Add .

  9. Add or edit a macro for a control on a worksheet

    In the Visual Basic Editor, write a new macro or change the existing macro. For more information about how to write macros, see Visual Basic Help.

  10. How to Add Macro Code to Excel Workbook

    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor Choose Insert | Module Where the cursor is flashing, choose Edit | Paste To run the code: On the Excel Ribbon, click the View tab At the far right, click Macros

  11. Add a Button and Assign a Macro in Excel

    Steve Rynearson Last updated on October 24, 2020 Excel Buttons In Excel, Buttons are used to call Macros. This tutorial will cover how to create Excel buttons, assign Macros to them, adjust their properties, and more. By default, Excel macros are accessible in a list via the "Macros" button on the View ribbon.

  12. Useful VBA codes for Excel (30 example macros + Free ebook)

    VBA code. Sub FlipNumberSignage () 'Create variable to hold cells in the worksheet Dim c As Range 'Loop through each cell in selection For Each c In Selection 'Test if the cell contents is a number If IsNumeric (c) Then 'Convert signage for each cell c.Value = -c.Value End If Next c End Sub.

  13. 15 Ways to Run a VBA Macro in Microsoft Excel

    Run VBA Macro from the Developer Tab. The most common method for running a macro is from the Developer tab in the Excel ribbon. This tab is hidden by default, so you will need to enable the Developer tab in your desktop Excel app first. Press the Macros command in the Code section. This will open the Macro menu which lists all the macros ...

  14. How to Create Macro Buttons in Excel Worksheets

    Here are the steps to create the macro button: 1. Draw a shape on the sheet (Insert tab > Shapes drop-down > Rectangle shape). 2. Add text to the shape (Right-click > Edit Text | or double-click in the shape). 3. Assign the macro (Right-click the border of the shape > Assign Macro…) 4. Select the macro from the list.

  15. Excel Add VBA Button

    Scroll down to 1) select the macro you wish to assign to the button, and then, 2) click OK. Right click on the button, and select Edit Text to change the text on the button. Type an appropriate name for the button and then click off the button. Click the button to run the macro.

  16. How to dynamically add and run a VBA macro from Visual Basic

    First, create a new text file named KbTest.bas (without the .txt extension). This is the code module that we will insert into Excel at run-time. In the text file, add the following lines of code: Attribute VB_Name = "KbTest" ' Your Microsoft Visual Basic for Applications macro function takes 1

  17. Create a Macro with the VBA Editor

    Create a macro. On the Tools tab, select Visual Basic to open the Visual Basic Editor. In the Visual Basic Project Explorer, right click on the project folder, and choose Insert > Module. In the code window, add a subroutine by entering Sub followed by the name for the macro. For this example, enter Sub CopyTextMacro ().

  18. Create New Sheet Using VBA in Excel (Sheets.Add)

    Below is the VBA code that will add a new sheet at the end of all sheets in the workbook. Sub AddSheetAtEnd () ' Create a new worksheet at the end of existing worksheets Sheets.Add After:=Sheets (Sheets.Count) End Sub. The above code uses Sheets.Count to get the count of all the worksheets in the workbook.

  19. VBA Add New Sheet

    Word Bookmarks - VBA Macros to Add, Delete, Goto, Modify: Word VBA - Macro to Open Word Document: Word VBA Macros - Add New Document: Word VBA Macros - Count Words in Selection: Word VBA Macros - Find, Find & Replace: Word VBA Macros - SaveAs (PDF or New File Name) Word VBA Macros - Tables: Add, Select, Loop, Insert From Excel

  20. VBA Assistance

    Hello Everyone, I have a number of Excel Projects that were started using some VBA Coding and or Macros. I am in need of help to complete these projects and would like to know if any one would be so kind to assist me. Some code in most instances is in place but it either needs to be expanded upon or corrections are required to fix changes that ...

  21. Assign a macro to a Form or a Control button

    Windows macOS Macros and VBA tools can be found on the Developer tab, which is hidden by default. The first step is to enable it. For more information, see the article: Show the Developer tab. Add a button (Form control) On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Button .

  22. How to use Copilot in word to reference a source document. to create a

    Scenario. We have a template requirements document in word which we want to use as a template. This document has many sections with titles, and we want to be able to use another document with a captureTable in it (ideally this would be excel but word can't use excel as a reference) and use it to essentially complete the new document using the information in the capturedocument

  23. Macros triggers Clipboard error

    Hello, I've been using macros for almost a year to help me make invoices. It has recently started to trigger a "Clipboard Error" that.. Hello, I've been using macros for almost a year to help me make invoices.

  24. How to solve the Excel VBA Run-time error '13', type mismatch

    There are a couple of issues with your current Sub CommandButton_Click(). ws10.Buttons.Add(200, 5, 80, 18.75) - why do you create a new button each time you click on it? b.OnAction = - this is C# code, not VBA This simple code should be enough: Sub CommandButton_Click() Call test End Sub

  25. Create a form in Word that users can complete or print

    Go to Developer, and then choose the controls that you want to add to the document or form. To remove a content control, select the control and press Delete. You can set Options on controls once inserted. From Options, you can add entry and exit macros to run when users interact with the controls, as well as list items for combo boxes, .

  26. VBA

    To create a new workbook simply use Workbooks.Add: Workbooks.Add The newly added Workbook is now the ActiveWorkbook. You can see this using this code: Sub AddWB() Workbooks.Add MsgBox ActiveWorkbook.Name End Sub Create New Workbook & Assign to Object You can use the ActiveWorkbook object to refer to the new Workbook.

  27. February 2024 updates for Microsoft Office

    Introduction. Microsoft released the following security and nonsecurity updates for Office in February 2024. These updates are intended to help our customers keep their computers up to date.