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 Developer tab offers.
How to Add Developer Tab in Excel?
- Step 1: Open a new excel sheet and navigate to file go to the options
- Step 2: After clicking on “Options”, select Customize Ribbon from left & Select Main Tabs on the right then check on the Developer checkbox and click on OK button
- Step 3: 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 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 Developer tab and Click on Insert button and select Button (Form Control)
Drag it anywhere in the form and following dialog box would appear and rename the Macro name
Click on the New Button and 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 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 column and rows.
So before coloring, those yellow go to Developer Tab
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, 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 click of the button in VBA
- One can also create forms or Basic UI in VBA, please refer references for 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 –