Developer Tab in Excel

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?

  1. Open a new excel sheet and navigate to file. Go to the options.

    Developer Tab 1

  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 the OK button

    Developer Tab 2

  3. Developer Tab would appear in the Excel file

    Developer Tab 3

How to write VBA Programs using Developer Tab?

Developer Tab in Excel can be used to write VBA programs as shown below –

You can download this Developer Tab Excel Template here – Developer Tab Excel Template

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

Developer Tab Example 1

Write the following code and click on the Run button, a pop box would appear saying, “Hi, Welcome to VBA.”

Developer Tab Example 2

Code Explanation:

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 excel and select Button (Form Control)

Developer Tab Example 2

Drag it anywhere in the form, and the following dialog box would appear and rename the Macro name.

Developer Tab Example 2-1

Click on the New Button, and the following code page would appear.

Developer Tab Example 2-2

Coding part and Explanation

Developer Tab Example 2-3
  • 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.”

Developer Tab Example 2-4

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)

Developer Tab Example 2-5
Developer Tab Example 2-6

After Entering the name following output would appear.

Developer Tab Example 2-7

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.

Recording and Saving Macro 1

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 Excel

Recording and Saving Macro 1-1

Once clicked on Record Macro, a dialog box will appear to rename it to Color_Yellow.

Recording and Saving Macro 1-2

Color Column F, G and Row Total as Yellow

Recording and Saving Macro 1-4

After the coloring, go to the Developer tab.

Click Stop Recording

Recording and Saving Macro 1-5

Go to the Developer tab in excel and Visual Basic

Select Module1

Recording and Saving Macro 1-6

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

Macro Security 1

Open the Macro for which code needs enabling of the password (For E.g., we take Color_Yellow macro as in the above example)

Macro Security 1-1

Select Tools and VBAProject properties

Macro Security 1-2

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

Macro Security 1-3

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

Macro Security 1-4

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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *