Developer Tab in Excel
Developer tab in excel is used for generating macros, creating VBA applications, designing form and Importing or Exporting XML. By default, the developer tab is disabled in excel, so it needs to be enabled from the Options Menu in excel.
Let’s deep dive and go through the various option the Developer tab offers.
How to Add Developer Tab in Excel?
- Open a new excel sheet and navigate to file. Go to the options.
- After clicking on Options, select Customize Ribbon from left Select Main Tabs on the right then check on the Developer checkbox and click on the OK button
- Developer Tab would appear in the Excel file
How to write VBA Programs using Developer Tab?
Developer Tab in Excel can be used to write VBA programs as shown below –
Example #1 – Writing a Simple Code for Displaying “Hi, Welcome to VBA”
Navigate Developer Tab and Select “Visual Basic” a new window would pop up
Double click on Sheet1 (Sheet1) and Blank Document or Pane will appear on the right side
Write the following code and click on the Run button, a pop box would appear saying, “Hi, Welcome to VBA.”
Sub and End Sub are used as procedures or say the main body of the program.
“Msgbox” is a functional feature in the VBA package which displays whatever is written in it here, for example (“Hi, Welcome to VBA”)
For saving the above code, save it as a .xlsm file so that the macro code gets saved.
Example #2 – Click on Button Feature
This particular feature enables anyone to run the code by the click of the button and for better understanding, let’s have an illustration where a person dynamically enters name and birthplace, which would be displayed as an output.
Go to the Developer tab and click on Radio buttons in excelRadio Buttons In ExcelIn Excel, radio buttons or options buttons record a user's input. They can be found in the developer's tab's insert section. and select Button (Form Control)
Drag it anywhere in the form, and the following dialog box would appear and rename the Macro name.
Click on the New Button, and the following code page would appear.
Coding part and Explanation
- “Dim” function is used for declaring the variable, which can be the string, numeric, etc.(Here Name and Birthplace are variables declared as string)
- “InputBox” is a functional feature in VBA wherein the user is asked for input
- “Msgbox” is the functional feature in the VBA package which displays whatever is written in it
Now close the code page window
Right-click on the Button and select edit text in the excel where one had created the button and rename it to “Name_Birth.”
Click on the button and Enter the Name and Birth Place.
Two prompts would pop up for Name and Birth (Enter the Name and Birthplace for E.g., Dhrish, and Pune)
After Entering the name following output would appear.
Save the file as the .xlsm file.
How to Record a Macro in Developer Tab?
It’s best suited when one has to do a task repetitively and wants to save time on both the repetitive work as well as coding.
So, here is the earlier example which I took earlier, suppose one wants to color column which are having formulae but for every file. So what one can do is record the work by doing it first manually and then run it for other files.
Suppose we have data as below.
So here we have to color yellow on Column F, G, and row total as yellow as they are formula columns and rows.
So before coloring, those yellow go to Developer Tab.
Click on Record Macro in ExcelRecord Macro In ExcelRecording macros is a method whereby excel stores the tasks performed by the user. Every time a macro is run, these exact actions are performed automatically. Macros are created in either the View tab (under the “macros” drop-down) or the Developer tab of Excel.
Once clicked on Record Macro, a dialog box will appear to rename it to Color_Yellow.
Color Column F, G and Row Total as Yellow
After the coloring, go to the Developer tab.
Click Stop Recording
Go to the Developer tab in excel and Visual Basic
Next time one wants to repeat the task, you can click on the (Run Macro) Button by copying and pasting the same code in the new excel sheet by using the link. Writing a simple code for displaying
How to ensure Macro Security using Developer Tab?
One can enable password protected macros if needed.
Go to the Developer tab.
Open Visual Basic
Open the Macro for which code needs enabling of the password (For E.g., we take Color_Yellow macro as in the above example)
Select Tools and VBAProject properties
A dialogue box will appear
Select the Protection tab
Check on the Lock Project for Viewing
Enter the Password one needs and confirm it and click Ok
Save it as .xlsm file while saving and close it
Open the file and repeat step 1,2 and 3
It will ask for a password and enter the password
Now will be able to see the code
Things to Remember
- It is a built-in Tab in Excel
- It is easy to automate everything with the use of Recording the macro
- The best part about this is run-time prompts, or the user prompts, which can be given through the click of the button in VBA
- One can also create forms or Basic UI in VBA; please refer references for the same
This has been a guide to Developer Tab in Excel. Here we discuss how to add and use Excel Developers Tab for writing codes in VBA, Recording Macro, and Security along with practical examples and downloadable templates. You may learn more about excel from the following articles –