Excel Programming (Table of Contents)
Introduction to Programming in Excel
Have you ever been tired of doing a task in Excel which you feel can be automated and save your time? Most of the time, you must have encountered such tasks. However, in order to automate any task, you first need to have programming skills for that particular language. In Excel, you can do programming with the help of Visual Basic for Application (VBA) which is Excel’s own programming language that can help you to automate the tasks. In this article, we will see how we can do programming in Excel VBA. VBA can be used to write a program that can automate the task for you. The piece of lines we write under VBA is called Macro, which is written in such a way that they instruct the Excel system about what to be done.
How to Program in Excel?
Let’s understand how to Program in excel with few illustrations.
Enabling Developer Tab
The first thing that comes is enabling the developer tab that helps you to record and store a macro (VBA Code). Let us see how we can get that enabled.
- Navigate to the File menu in your excel file and click on it.
- Within the File menu, click on Options, and it will load a new window with all excel options available.
- In the new window that popped up named Excel Options, click on the Customize Ribbon tab. You can see all the customization options you can use for Excel Ribbon, which appears at the top of your Excel file.
- Enable Developer option under Main Tabs dropdown within Customize the Ribbon: section. You can check (tick-mark) the Developer tab to enable it. Click the OK button placed at the bottom right of the Excel Options tab, and that’s it.
- You have successfully enabled the Developer option within your excel. If you check the Excel Ribbon in your file now, you’ll be able to see a new tab added there with the name Developer on it.
This is the first step you need to follow before you start writing macros in Excel. Because the Developer tab is needed to record and run the macro, this option tab is not by default enabled, which is why we tried enabling it here first.
Recording a Macro
- Open the Excel file. Navigate towards the Developer tab you just enabled and then click on the Record Macro button, categorized and can be seen under the Code section.
- As soon as you click on the Record Macro button, you’ll see a window popping up; in that window, you must have to assign a name to the macro; you can also assign a shortcut key for this macro to run. Can you add the description, if any, for this macro you are creating? Once you are done with all this, you can click on the OK button placed at the right bottom of the window. See the screenshot below for your reference.
As soon as you click OK, the system starts recording the macro and all the tasks you perform will be recorded and converted to Excel Program in the backend.
- Try typing the sentence “This is my first VBA code, and I am very happy!” in cell A1 within the Excel sheet and press Enter key. These steps will be recorded in the backend of the macro.
- Under the Code section, you might have observed that the Record Macro button has changed to Stop Recording. This is like Play and Stop. Record Macro Works as Play button and Stop Recording work as Stop button. Click on the Stop Recording button to stop the recording.
The magic behind all this is, Excel has recorded my steps here and converted those into pieces of code so that this task can be automated. It means, every single step, selecting cell A1, inputting the text as “This is my first VBA code, and I am happy!”, clicking Enter to go to the next cell. All these steps are converted into a VBA code. Let’s check the code now.
- In order to go to Visual Basic Editor, you can click on the Visual Basic option under the Code category in the Developer tab, or you can use Alt + F11 as a shortcut for the same.
- Navigate towards the Modules section under VBAProject and click on the plus button under it to see the list of active modules in VBA.
- Inside the Modules folder, you can see Module1 as soon as you click on the plus sign. You can double click on Module1; it is where your code for the task we performed in previous steps (Step 3 and 4) are recorded. Save this code, and you can run it every time to get the same output. See the screenshot below:
- We can record a macro in Excel to automate day to day small tasks, which are simpler for the system to manipulate programmatically.
- The cool thing about it is you don’t need to dig your head deep for the logic behind each step you perform. Excel VBA does it for you.
- For some complex tasks, such as the one which involves looping and conditional statements, you need to write code manually under VBA.
Things to Remember About Programming in Excel
- The Developers tab is not by default enabled and visible to you in Excel Ribbon. You need to enable it through Excel Options.
- Recording a macro works on simple tasks that are repeating, and you need those to be automated. However, for complex tasks which involve looping or Conditional Inputs and Outputs are still need to be coded manually under VBA.
- You need to save the file as an Excel-Macro Enable file format in order to be able to read and run the code again on your excel.
This is a guide to Programming in Excel. Here we discuss how to Program in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –