Microsoft Excel is a spreadsheet application as we all know, but what most of us don’t know is the hidden development platform which it offers. Using this you can create nice interfaces for your excel files for entering the data. If you want and are capable of doing so then you can even create whole database application using the VBA platform of excel. The VBA platform is available in almost all the applications of Microsoft Office suit and it can be used for enhancing or rather customizing the applications. Using the VBA platform in Excel you can perform calculation by writing your own code for formulas and would not even need to write functions in any cell. You can do it all by your self and Excel will then behave just a database for you. In this post I will discuss about this hidden platform in Microsoft Excel and how to use it.
By default the Developer Ribbon is hidden in Excel. This is the Ribbon which has all the option regarding Visual Basic coding, Macro, ActiveX controls etc. This Ribbon can be made visible by going to the Office button on the top left corner of the Window and then clicking on Excel Options. In the Excel option Window, under the Popular category you will find a check box titled “Show Developer tab in the Ribbon”. You need to first check this box which stays uncheck by default. As you make this change you will get the Developer tab in the Excel Window and you can work on all the options available over there.
In the Developer Tab there is a button called Visual Basic. This button can be used for opening the Visual Basic Editor which is similar to the Visual Studio and you can create your custom Windows and buttons in it. You can write code and actions for every command button, or other controls. Other than the Visual Basic button another most important button is the Insert button. If you do not want to have separate window popping out in front of your Excel spreadsheet then you can add ActiveX controls like Command Button, Drop Downs etc in your Excel Sheet directly. Click on the Insert button and then add the control that you want in your spreadsheet.
In the paragraphs above I have told you how to add the Developer tab and how to use the features in it, but you can not understand the purpose of this and its usage until and unless you have some programming experience. You should be aware of how the instruction flows in any written program code. You should be aware of the programming structure and the control statements like If –Else, While, For etc. you should be aware of the data type and its usage and initialization. Even if you are good with Visual Basic still you need to understand the way Excel Cells are used in it. Referring an Excel Cell, Row, Column, or range will be a complete new study for you. I would suggest you to have a good book with you when you start writing code in Excel for ready reference.