How to Write Excel Macros via VBA

You can easily get started with writing the visual basic code by enabling the feature in Microsoft Excel. By writing VBA excel macro, you can automate a number of tasks which otherwise might take a lot of your time. For example, changing the formatting of a whole document in a specific way, etc.

Follow this step by step tutorial where we will guide you on how to write Microsoft excel macros using Visual Basic.


Step 1 – Enabling the developer tab

First of all we have to enable the Developers Tab in order to access Microsoft Excel macros. For that, we will open up the Excel options window and over here enable the “Show Developer Tab” option.

choosing the show developer tab option


Step 2 – Opening up Visual Basic

Once you enable that option, you will notice the Developer tab will be appearing in the menu no top. Simply move to the tab and you can work with a number of options related to adding and editing macros. Over here, we will click on the “Visual Basic” button to open up the VBA editor.

clicking on the visual basic option


Step 3 – General Layout

Over here, you have the project explorer on the upper left side of the screen, the properties window, which will show the properties of the selected item and the window where the code is displayed.

Simply double click on “This Workbook” module from the Project Explorer menu on the left corner of the window. This is because over here, we want the code to be applied on the whole workbook, instead of any particular sheet.

the VBA editor Layout


Step 4 – Getting into the code

Once opened, select the “General” option from the Object drop down list and then create an event handler by selecting the “Sheet Change” option. The Sheet change event handler basically activates the code once any change is made on the sheet.

We will write a basic code that will fetch the data from a cell and use that as the name for the sheet that would be currently activated. For doing this, we will use a range for the target address. Over here, we have used the “if” condition and fetched the value entered in Cell A1.

After that, we will input the Active Sheet keyword which will basically return an object that will represent the sheet which is currently active. Over here, the object that we will be using is the name of the sheet.

To use the data in cell 1 as the sheet name, we will include the range along with the value property. This particular property is used for the selected value within the control here.

writing the VBA code


Step 5 – Saving the document

Now let’s save this workbook using the “Excel macro Enabled” option. It’s very important to save the document in this particular format otherwise the vba code will not be saved.

saving as a macro enabled workbook


Step 6 – Testing the code

With that done, let’s test if the code works. We will enter a name in Cell A1 and you will notice that it will automatically change the Active sheet name here. That means the vba excel macro works perfectly.

And that’s basically how you can get started with writing excel macros using visual basic. There are many online repositories which can assist you in writing vba code.

Once such example would be:

msdn.microsoft.com

Over here, you can move to the Library page, expand the office and SharePoint development section, choose which version of office you are using and go through the extensive documentation present here.

checking the output