You might have used MS Excel a lot many time. But have you ever thought that this Excel can be programmed to make a very interactive application sheet. If no then see here how this could be done. If you are here in search of some basic start up article on Excel Macro then you are at absolutely right place.
Applications inside MS Office Package include a programming language called VBA ( Visual Basic for Applications). This is used for automation of repetitive tasks in MS Office Applications. You can open VBA editor by pressing Alt+F11 key on your main screen of Application.
Let start with a simple Excel Macro. This is the most conventional Hello World!! program in worship of every programming language. Just kidding. First of all you will need to enable your Developer Tab in MS Excel. For enabling Developer Tab click on File Button on left top of your Excel window. Click Options and click Enable Developer Tab.
Note: For Office 2010 user this option will be available under customize ribbon.
Figure below shows a screen shot of Options window.
Enable the developer tab by checking the respective option. Next figure shows you a view of Developer tab. The figure is also showing the insert options available.
Select Command Button from ActiveX Control and place it on the Excel Sheet. Now here comes the main task of making this button active (Rally active.). Right click on button and select “View Code”. Below is figure for reference.
This will open VBA Editor window. This is a place where your actual code is place. VBA is somewhat similar to the Visual Basic from Microsoft. If you are having previous experience with Visual Basic then you will find it very simple to understand. See the figure below for a view of VBA Editor.
Let start coding. The first and last line of code is self generated as you can also observe if you are following me with an Excel sheet. Put following line of code in between the auto generated lines.
Range(“A1”).Value=”Hello World!!”
Close the editor. Now close the design mode by clicking the Design Mode button under Developer Tab. Now click the button you just created. See the output in A1 cell of sheet. Figure shows a screenshot of the output.
Till now you might have understood some fraction of the code. Range is an object which communicates or makes changes to a number of cells in the sheet which are provided as option to it. In out case we provided “A1” as cell. Next is the “Value” property of object. In our code we are modifying the value property of A1 cell “Hello World!!”. So if you have experience with any Object Oriented programming language then you can understand that VBA is an object oriented language.
Now to change caption of our button again select design mode. Right click on the button and click on properties. In the opening box modify the value of “Caption” field to your desired value. Screenshot below shows an example.
I hope this might have helped you starting with Excel Macro VBA programming. Happy programming with rich featured MS Excel.