Developer Tab In Excel

The Developer Tab in Excel is a tab that has some functions and tools used to generate Macros, create VBA applications, design forms, and import/export XML, that is not found in Excel by default. We have to enable the tab from the “Excel Options” window.

For example, when we open the Excel worksheet, by default, the Developer Tab will not be found, and the ribbon will be as shown below.

However, once we enable the “Developer” tab, the tab will be visible on the Excel ribbon always, even when we close and reopen the workbook, as shown below.

We can use the Developer Tab in Excel to write VBA programs, as follows:

First, open an Excel workbook - select the “Developer” tab - go to the “Code” group - click the “Visual Basic” option to open the “Visual Basic Editor”, as shown below.

Now, the “Microsoft Visual Basic for Applications – Working Test.xlsx” window opens. Click the “Insert” tab - and select the “Module” option, as shown below.

Once the “Module” window opens, write the required VBA Code, and Run/Execute it.

We will consider examples to use the Excel Developer tab to write VBA programs.

Example Example #1 – Writing a Simple Code for Displaying “Hi, Welcome to VBA”.

The steps to write a simple VBA program are as follows:

• First, navigate to the “Developer” tab, and select “Visual Basic.” A new window will pop up.
• Next, double-click on “Sheet1 (Sheet1)”. A blank document or pane appears on the right side.

• Next, write the code shown in the image below, and click the “Run” button.

A pop-up box would appear saying, “Hi, Welcome to VBA”, as shown above.

Example #1 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 that displays whatever is written in it here, for example (“Hi, Welcome to VBA”).

Save the above code as a .xlsm file to save the macro code.

Example #2 - Click on Button Feature

This particular feature enables anyone to run the code with the click of a button.

For better understanding, let us illustrate where a person dynamically enters their name and birthplace, which would be displayed as an output.

• First, we must go to the “Developer” tab, click on the Radio buttons in excel, and select “Button (Form Control).”

Now, drag it anywhere on the worksheet, and the following dialog box will appear. Rename the “Macro name.”

Click the “New” button, and the following code page will appear.

Example #2 - 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.

Now close the code page window.

Then, we must right-click on the button, select the “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 birthplace. Two prompts would pop up for name and birth (enter the name and birthplace, e.g., Dhrish and Pune).

After inserting the name, the following output appears.

Now, save the file as the “.xlsm” file.

We can record a Macro when we must perform a task repetitively, but want to save time on both the repetitive work and coding.

So, here is the earlier example that we took earlier. Consider the data given below. To color columns with formulae for every file, we can record the work by doing it first manually and then run it for other files.

The steps to color columns F, G, and row total as yellow as formula columns and rows are as follows:

• First, go to the “Developer” tab, and click on Record Macro in Excel.

Once we click “Record Macro,” a dialog box will appear. Rename it to “Color_Yellow.”

Now, color columns F, G, and row total as “Yellow”, as shown below.

After the coloring, go to the “Developer” tab, and now click “Stop Recording”.

Now, go to the “Developer” tab, click the “Visual Basic” option, and select “Module1”, as shown below.

Next time, if we want to repeat the task, we can click the (“Run Macro”) button by copying and pasting the same code into the new Excel sheet using the link. Writing a simple code for displaying.

To ensure Macro Security, we can enable password-protected macros if needed.

The steps to perform the Macro Security are as follows:

• First, go to the “Developer” tab, and open “Visual Basic.”

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

We must select “Tools” and “VBAProject Properties.”

Next, subsequently, a dialog box will appear. Here, select the “Protection” tab, check/tick the “Lock project for viewing”, enter the desired password, confirm it, and click “OK.”

• Finally, save it as a .xlsm file while saving and closing it.

To cross-verify for Macro security, open the file and repeat steps 1,2 and 3. As a result, it will ask for a password. Insert the password, and click “OK”, to view the Code.

• The Developer tab 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 by clicking the button in VBA.
• We can also create forms or basic UI in VBA. Please refer to the references for the same.