Optimized FOR Loop in VBA:6.0

Hi Everyone!


Writting a For loop in vba is not a big deal but one common mistake which lot of developers tend to do. They tend to write the for as below:


InEfficient Method:

Sub prTest()


'Error Handler
On Error GoTo Err_PrTest:


Dim intStartCounter     As Integer
Dim intLoopCounter      As Integer
Dim rngTest             As Range


intLoopCounter = 5
Set rngTest = Range("Test")


For intStartCounter = 1 To intLoopCounter
    If rngTest(intStartCounter, 1) = "Test" Then
        'Do Nothing
    End If
Next intStartCounter


Exit_PrTest:
    Exit Sub
Err_PrTest:
    GoTo Exit_PrTest
End Sub


2)Efficient Method


Sub prTest()


'Error Handler
On Error GoTo Err_PrTest:


Dim intStartCounter     As Integer
Dim intLoopCounter      As Integer
Dim rngTest             As Range


intLoopCounter = 5
Set rngTest = Range("Test")


For intStartCounter = 1 To intLoopCounter
    If rngTest(intStartCounter, 1) = "Test" Then
        'Exit the for loop as soon as you find the Test. Other as soon as the purpose of for loop
        'is satisfied exit
        Exit For
    End If
Next intStartCounter


Exit_PrTest:
    Exit Sub
Err_PrTest:
    GoTo Exit_PrTest
End Sub


The difference between 1 & 2 is that in case of 2 you exit the for loop once the purpose of FOR loop is satisfied and therefore you save execution time. 2 will make code more optimized.


Cheers!

Excel/MDB 5.0

Do you know ?


I am not sure if you have noticed this with some of Excel older versions for Eg: 2003. When you have a mdb which has more than 65,536 rows lets say 65,537, now when you try to pull in these mdb records into Excel? What happens then. You will notice that there will not be any error message flashed by Excel. So it hard to catch it, but the cause is pretty simple. We are trying pull in more rows than the capacity of Excel which is not possible.


This limitation of Excel 2003 is not present in Excel2007.


Cheers!!

Excel declaring same range twice in the Worksheet Post 4.0

Today I would be sharing about a small & simple thing. But this is indeed an important thing and might lead to trouble in case you are not aware of it!!
This  limitation is with Excel 2003 and some lower versions of excel. Try declaring some range name Test in Sheet1!A1.
Now again go to Sheet2!A1 and give again same range name in this cell. Excel quietly gives this name to this cell without popping any Error message(That range name already exists). So in this process what happens is that the range name Test which was there in Sheet1!A1 is deleted implicitly. 


Impact:As a result of this wherever you were using Test (Sheet!A1) now will start using Sheet2!A1 so that calculation will start going wrong in the worksheet/Workbook.


Solution: To avoid this Issue whenever you declare any new range just check for the range name if already exists. To check this you can go to keyboard Ctrl+G and enter the new range name to see if it exits.


Now one good thing is that Excel higher versions like Windows Vista does not have this Issue and Excel throws a warning message explicitly.

Connect to DataBase from VBA/Excel Post 3.0

Through VBA we can connect Microsoft Database(MDB) and can access the data from tables in the MDB. This data can be populated in the Excel sheet from these tables.
I have already explained some of the basics regarding VBA in my earlier posts. So today we will see how we can connect to MDB through VBA.
Below is the code which can be used to pull data from MDB and populate in Excel:
Before moving the actual code we should understand what is .accdb file. The accdb file format was introduced with release of Microsoft Access 2007 to replace the older MDB file format. So when we use ACCDB format allow users to leverage enhanced fucntionality in access ,including the use of multivalued fields and strong cryptography.




Sub prConnecttoMDB()
On Error GoTo Err_prConnecttoMDB
Dim strConnection   As String
Dim strSQl          As String


strConnection = "ODBC; DSN=MS Access Database;DBQ=D:\LearnVBA\Test_01.accdb; Driver={Driver do Microsoft Access (*.accdb)}"
strSQl = "SELECT TestTable.SerialNo, TestTable.MemberName, TestTable.School,TestTable.NativePlace,TestTable.Income FROM TestTable"


With ActiveSheet.QueryTables.Add(Connection:=strConnection, Destination:=ActiveSheet.Range("Test"))
    .CommandText = strSQl
    .Name = "TestTable"
    .Refresh BackgroundQuery:=False
End With


Exit_prConnecttoMDB:
    Exit Sub


Err_prConnecttoMDB:
    GoTo Exit_prConnecttoMDB
End Sub

The below code pulls the data from the MDB file placed at D:\LearnVBA\Test_01.accdb to Excel active sheet in range Test.

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.

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.