Tuesday, October 27, 2009

Variables in VBA Post 2.0

Variables in VBA:


1)Variables in VBA are declared by using Dim statement. VBA like any other programing language as many variable types. To list a few Integer,Double,String,Long,Variant,Range etc.
If a string variable needs to be declared it can be declared as


Dim strTest as String


The above style of declaring the variable is called Hungarian notation. In this variable name is prefixed by the variable type. Another variable is Range which is different from other programming languages. Range variable is declared similar to any other variable like Dim rngTest as Range. Any range in excel can be used in VBA by setting that range to this range variable. To play with this range first set this range as :
Set rngTest=Range("A1:B3")
Now the range variable rngTest can be used to access the variables in this range.
rngTest(1,1)->This will give you the value in cell A1.


2)Variant is one powerful variable in VBA. It can hold different variable types. So Variant is used when the user is not sure about the variable type. Do not try to use variant as the preferred variable. It might seem to be  easy and obvious choice but it can be dangerous considering the code performance.
If you use Variant as data type the compiler will take more time in identifying the Variable as compared to a variable of specific type.
If you are using Variant as variable type for variable which stores always one integer value. Then there is one is disadvantage that you might not be able to see the value of the variable while debugging. There might also Issue where lot of "type mismatch" errors will be missed if you use Variant as datatype.


An array variable can be declared as variant type.
Dim arrTest as variant.


The lower bound of this array is 0. To change the upper the bound of this array it can be redeemed.
Redim arrTest(1,4)


Now keep in mind one thing that  lower bound always starts with 0. In case you have to change the lower bound you can
Option base 2
This will take the lower bound as 2. By default the lower bound is 2.

Sunday, October 25, 2009

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.

Saturday, October 24, 2009

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.

Followers

About Me

My photo
Hello World!. I am an aspiring blogger, striving towards knowing more about various Technologies and innovating about it. I am full of Energy, Enthusiasm to learn more and more....