Mention any two operations that can be performed using macros in a spreadsheet

Introduction This is a tutorial about writing code in Excel spreadsheets using Visual Basic for Applications (VBA). Excel is one of Microsoft’s most popular products. In 2016, the CEO of Microsoft said "Think about a world without Excel. That's just impossible for me.” Well, maybe the world can’t think without Excel. • In 1996, there were over 30 million users of Microsoft Excel ( • Today, there are an estimated 750 million users of Microsoft Excel. That’s a little more than the population of Europe and 25x more users than there were in 1996. We’re one big happy family! In this tutorial, you’ll learn about VBA and how to write code in an Excel spreadsheet using Visual Basic. Prerequisites You don’t need any prior programming experience to understand this tutorial. However, you will need: • Basic to intermediate familiarity with Microsoft Excel • If you want to follow along with the VBA examples in this article, you will need access to Microsoft Excel, preferably the latest version (2019) but Excel 2016 and Excel 2013 will work just fine. • A willingness to try new things Learning Objectives Over the course of this article, you will learn: • What VBA is • Why you would use VBA • How to get set up in Excel to write VBA • How to solve some real-world problems with VBA Important Concepts Here are some important concepts that you should be familiar with to fully understand this tutorial. Objects: Excel is object-oriented, which means everything is an object - the Excel window, ...

• • • • • • • • • • How to Record a Macro Recording a macro in Excel allows you to bundle several actions into a single action, which is handy if you find yourself performing certain action sequences often. With a macro you can automate common minor tasks, or entire workflows. While recording a macro is easy to do, some extra knowledge about how recording works and some “best practices” can help you quickly master macros and increase your productivity. Record a Macro To begin recording a macro, click the “Record Macro” button on the bottom-left of the Excel window. This will show the “Record Macro” dialog box . In the “Record Macro” dialog, enter the following: • Macro name: type a unique name for your macro (so you can identify it later) • Shortcut key (optional): allows you to set a keyboard shortcut to run the macro later. Type a single character to assign this keyboard shortcut (for best results, use SHIFT when you type the character to avoid conflicts with other Excel shortcuts) • Store macro in: This sets what workbook the macro will be stored in, which affects how you can use it • “This Workbook” (most common option) means the macro will only be usable in the currently active workbook • “Personal Macro Workbook” make the macro usable at any time in any workbook, by storing it in a hidden workbook that Excel always has open • “New Workbook” (least common) stores the macros in a brand new workbook; useful if you’re making macros that you can’t save to “This Workbook” ...

In this article 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 ...

This Tutorial Covers: • • • • • • • • • • • • • • • • Difference between Worksheets and Sheets in VBA In VBA, you have two collections that can be a bit confusing at times. In a workbook, you can have worksheets and as well as chart sheets. The example below has three worksheets and one chart sheet. In Excel VBA: • The ‘Worksheets’ collection would refer to the collection of all the worksheet objects in a workbook. In the above example, the Worksheets collection would consist of three worksheets. • The ‘Sheets’ collection would refer to all the worksheets as well as chart sheets in the workbook. In the above example, it would have four elements – 3 Worksheets + 1 Chart sheet. If you have a workbook that only has worksheets and no chart sheets, then ‘Worksheets’ and ‘Sheets’ collection is the same. But when you have one or more chart sheets, the ‘Sheets’ collection would be bigger than the ‘Worksheets’ collection Sheets = Worksheets + Chart Sheets Now with this distinction, I recommend being as specific as possible when writing a VBA code. So if you have to refer to worksheets only, use the ‘Worksheets’ collection, and if you have to refer to all sheets (including chart sheets), the use the ‘Sheets’ collection. In this tutorial, I will be using the ‘Worksheets’ collection only. Referencing a Worksheet in VBA There are many different ways you can use to refer to a worksheet in VBA. Understanding how to refer to worksheets would help you write better code, especially when you...

If you regularly work with Excel spreadsheets, you probably find yourself repeating the same steps over and over. Wouldn’t it be nice to click a button and have those tasks happen automatically? That’s where Excel macros come in. You can use macros to automate repetitive tasks, which can save you a lot of time and effort. What is an Excel macro? An Excel macro is a recorded sequence of Excel commands and actions that you can play back as many times as you want. Macros can be used to automate just about any sequence of tasks in Excel, from something as simple as entering your company’s name and address into a spreadsheet to something as complex as creating a custom report. If you can do it in Excel, you can probably automate it with a macro. To use a macro, you first need to record it. You perform the sequence of steps you want to automate, and Excel tracks them all and saves them in the macro. Once you’ve recorded a macro, you can run it again at any time. You can even assign a keyboard shortcut to a macro, so you can run it with just a few keystrokes. Excel macros are based on Microsoft’s Visual Basic for Applications (VBA) programming language. When you record a macro, Excel translates your actions into VBA code under the hood. So in addition to creating macros by recording them, you can also write them manually in VBA code. In this article, I will focus primarily on creating macros by recording them — the simplest and quickest method. After that, I will discuss how you ...

“Macro” by its name, is a programming language that a machine understands. It is a set of computer language which is understood by the computer and it can perform various operation. In Microsoft Office, we have a facility to access the programming by using MS Office Visual Basic for Application (VBA). We can use VBA in other office applications like Word, Power Point etc. But in this tutorial, we are focusing on use of Macro in MS Excel. macro code example The key use of macro is to perform repetitive tasks in excel easily. If you have some task in excel that you have to repeat multiple times, then you can use the Macro. By using macro, you can record your task, save the task and reuse it. It dramatically saves your time. Now lets learn step by step how to enable macro, how to record macro and how to run the macro. Step 1: Enable Macro in Excel & Save Excel Workbook as Macro Enabled Macro tool you can see in the Developer Menu of Excel. By default, developer menu is not visible in excel. We need to first enable the developer menu. To enable developer menu, click on File > Options > Customize Ribbon, then check the box of “Developer” and click Ok. Now you will be able to see Developer menu and macro options. developer menu in excel As you see in the image above, developer menu contains of Visual Basic, Macros, Record Macro etc. Using this menu, we can customize excel and automate excel. If you want to see video tutorial on how to enable developer menu, please click the link...

There are several ways to run a macro in Microsoft Excel. A macro is an action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language. You can always run a macro by clicking the Macros command on the Developer tab on the ribbon. Depending on how a macro is assigned to run, you might also be able to run it by pressing a combination shortcut key, by clicking a button on the Quick Access Toolbar or in a custom group on the ribbon, or by clicking on an object, graphic, or control. In addition, you can run a macro automatically whenever you open a workbook. Before you run macros Before you start working on macros you should enable the Developer tab. • For Windows, go to File> Options> Customize Ribbon. • For Mac, go to Excel> Preferences...> Ribbon & Toolbar. • Then, in the Customize the Ribbon section, under Main Tabs, check the Developer check box, and press OK. • Open the workbook that contains the macro. • On the Developer tab, in the Code group, click Macros. • In the Macro name box, click the macro that you want to run, and press the Run button. • You also have other choices: • Options - Add a shortcut key, or a macro description. • Step - This will open the Visual Basic Editor to the first line of the macro. Pressing F8 will let you step through the macro code one line at a time. • Edit - This will open the Visual Basic Editor and let you edit the macro code as needed. Once you've made changes...