Writing a Sweet VBA Code Post 8.0

Hi Everyone!

There are different ways of writing a same piece code which performs same task as the other. But one piece of code can make your and subsequent developers a cake walk while the other can make it really tough.

So not always the code which you write has the objective to just perform the function.

Error Handler in VBA 7.0

Handling Error in form of programing language is vital task. To protect your application from stoping/freezing in midway is important. If you handle the error properly then you will not only successfully execute the application but you can also make user aware of the error which was thrown during the execution.


Lets us take a simple example for error handler:



Sub prTest()
'Error Handler Starts
On Error GoTo Err_PrTest:
Dim rngTest     As Range
Dim intTest     As Integer
Dim intCounter  As Integer

If rngTest(1, 1) = 0 Then
    'Do Nothing
 End If


Exit_PrTest:
    Set rngTest = Nothing
    Exit Sub
Err_PrTest:
    MsgBox Err.Description
    GoTo Exit_PrTest
End Sub


Once this code goes into error below error is thrown to the user:

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.