Excel Worksheet Post 1.0

Some of us know Excel for doing some simple calculations. These simple calculations can be adding ,subtracting set of values in Excel cells. But Excel actually is powerful platform for solving complex calculations and presenting results. We can go even a step further and make Excel even more Powerful. We can program Excel with VBA & .Net.

1)When do we see limitation of Excel:What if we have huge data in Excel and we need to apply several formula's to arrive at final result. To handle this alone in Excel will be next to impossible. We can take help of VBA for same. At the back of Excel we can use VBA programming language to code several procedures and functions. These procedures and functions can make use of Excel formula's internally in the looping structures and perform complex calculations. These results can again be populated back on the Excel surface. The input required for calculations in VBA can taken from the Excel surface. The Inputs can be played by user on the Excel surface.

2)When you write a formula in Excel and you try to use so many Excel functions in the formula, at one point you will see that formula will start throwing error. From the error it will appear to you that formula is wrong but it is not the case actually. So in this you will have to break the formula into two part. Put one part of formula in different cell (Intermediate cell). Now you can refer this cell in the main formula. Your formula is all set now.

Basics of VBA/What is VBA Post 1.0

Unlike Visual Basic not many people know about Visual Basic for applications(VBA).  Excel has so many features but do you know that you can even add to these features by going behind Excel. To start programming you need a VBA editor. You can arrive at editor by pressing ALT+F11 from key board. Once you press this you will see similar kind of interface as Visual Basic. You will see the VBA project similar to VB. In this project you will have Tool box similar to VB. You can add a Form and can embed different tools such as Text box, radio button etc. You can also insert Module and Class.
VBA is  flexible and you can write various functions, procedures. You can declare variables like integer,double etc like in any other programming language. I will restrict this post to basic and will take up more as we move further.
Let us try to write a simple Hello World program in VBA.
Once you press ALT+F11 you are in VBA editor.
1)Go to 'Insert->Module' on menu bar.
2)Click on the Module1 which is on left side of the page.
3)Go to Tools->Options->Editor->Check the check box 'Require Variable Declaration. To explain why is this required. This is not one of mandatory step, even if you do not check this the code will work fine. This step though provides you an additional feature. If you try to use a variable in a procedure without declaring a variable, code will throw compile error. So you know that you need to declare a variable before using it. In short this helps you to find a defect in code.
4)See program below:


Sub test()
Dim strHelloWorld As String


strHelloWorld = "Hello World"
MsgBox strHelloWorld


End Sub


5)In the above program strHelloWorld  is a string variable which holds the string variable.
Sub stands for procedure. Msgbox will output the string hello world.
6)Compile the code by going to Menu bar Debug->Compile VBA Project. Ensure that code should not throw any error.
7)O/P:Go to Menu Bar Run->Run Sub/UserForm. This pops up form saying Macro. Select the Macro which you want to run. Here you need select sub Test and click on Run.
It displays the output as HelloWorld.


Summary
The above was a simple way to explain about first program in VBA. If anyone has any comment or additional input please go ahead and post comment or initiate discussion.