Sunday, 9 December 2012

Excel VBA Error Handling and Debugging Techniques

 What is Use of Locals Window , Watch Window ,Immediate Window,Debug.Print and Debug.Assert Techniques.
The most important step in programming is testing and debugging. Once you finished writing the VBA Procedures and compile , the next step you need to take is debugging the code to check the Macro is giving the correct result or not using the below techniques.

This post describes the various debugging resources available in the VBA Editor (VBE) and how to use them.

Step by Step Through Code 
Normally, your code runs unattended. It executes until its logical end. However, when you are testing code, it is often useful to step through the code line by line, watching each line of code take effect. This makes it easy to determine exactly what line is causing incorrect behavior. You can step through code line by line by pressing the F8 key to start the procedure in which the cursor is, or when VBA is paused at a break point. Pressing F8 causes VBA to execute each line one at a time, highlighting the next line of code in yellow. Note, the highlighted line is the line of code that will execute when you press F8. It has not yet been executed. 

If your procedure calls another procedure, pressing F8 will cause VBA to step inside that procedure and execute it line by line. You can use SHIFT+F8 to "Step Over" the procedure call. This means that the entire called procedure is executed as one line of code. This can make debugging simpler if you are confident that the problem does not lie within a called procedure. 

When you are in a called procedure, you can use CTRL+SHIFT+F8 to "Step Out" of the current procedure. This causes VBA to execute until the end of the procedure is reached (an End Sub or Exit Sub statement) and then stop at the line of code immediately following the line which called the procedure. 

Locals Window 
The Locals Window displays all the variables in a procedure (as well as global variables declared at the project or module level) and their values. This makes it easy to see exactly what the value of each variable is, and where it changes, as you step through the code. You can display the Locals Window by choosing it from the View menu. The Locals Window does not allow you to change the values of variables. It simply displays their names and values.

Watch Window 
The Watch Window allows you to "watch" a specific variable or expression and cause code execution to pause and enter break mode when the value of that variable or expression is True (non-zero) or whenever that variable is changed. (Note, this is not to be confused with the Watch object and the Watches collection). 

There are three types of watches, shown in the Watch Type group box. "Watch Expression" causes that watch to work much like the Locals Window display. It simply displays the value of a variable or expression as the code is executed. "Break When Value Is True" causes VBA to enter break mode when the watch variable or expression is True (not equal to zero). "Break When Value Changes" causes VBA to enter break mode when the value of the variable or expression changes value. 

You can have many watches active in your project, and all watches are displayed in the Watch Window. This makes is simple to determine when a variable changes value. 

Immediate Window 
The Immediate Window is a window in the VBE in which you can enter commands and view and change the contents of variables while you code is in Break mode or when no macro code is executing. (Break mode is the state of VBA when code execution is paused at a break point (see Breakpoints, below). To display the Immediate Window, press CTRL+G or choose it from the View menu. 

In the Immediate Window, you can display the value of a variable by using the ? command. Simply type ? followed by the variable name and press Enter. VBA will display the contents of the variable in the Immediate Window. For example, 

?ActiveCell.Address
$A$10 

You can also execute VBA commands in the Immediate Window by omitting the question mark and entering the command followed by the Enter key:
Application.EnableEvents=True
or
Range("A1").Value = 1234

The Immediate Window won't let you enter VBA code snippets and execute them together because the Immediate Windows executes what you enter when you press the Enter key. However, you can combine several "logical" lines of code in to a single "physical" line of code using the ':' character, and execute this entire command. For example, to display each element of the array variable Arr use the following in the Immediate Window.

or N= LBound(Arr) To UBound(Arr): Debug.Print Arr(N) : Next N 

The Immediate Window always acts as if there were no Option Explicit statement in the active code module; that is, you don't have to declare variables you might use in Immediate Window commands. In fact, this is prohibited and you'll receive an error message if you attempt to use Dim in the Immediate Window. 

Debug.Print 
You can use the Debug.Print statement anywhere in your code to display messages or variable values in the Immediate Window. These statements don't require any confirmation or acknowledgement from the user so they won't affect the operation of your code. For example, you can send a message to the Immediate Window when a particular section of code is executed.
' some code

Debug.Print "Starting Code Section 1"

Unfortunately, there is no way to programmatically clear the Immediate Window. This is a shortcoming that has frustrated many programmers. 

Debug.Assert 
In Excel 2000 and later, you can use Debug.Assert statements to cause the code to break if a condition is not met. The syntax for Debug.Assert is: 

Debug.Assert (condition) 

where condition is some VBA code or expression that returns True (any numeric non-zero value) or False (a zero value). If condition evaluates to False or 0, VBA breaks on that line (see Breakpoints, below). For example, the following code will break on the Debug.Assert line because the condition ( X < 100) is false. 

Dim X As Long
X = 123
Debug.Assert (X < 100)

Debug.Assert is a useful way to pause code execution when special or unexpected conditions occur. It may seem backwards that Debug.Assert breaks execution when condition is False rather than True, but this peculiarity traces its roots back to early C-language compilers. 

Remember, your end users don't want the code to enter break mode under any circumstances, so be sure to remove the statements before distributing your code, or use Conditional Compilation (see below) to create "release" and "debug" versions of your project. Note that Debug.Assert is not available in Excel97 or earlier versions. 




Excel VBA Macro To Sum The Individual Digits of Number

Excel VBA Macro To Sum The Individual Digits of Number

'Suppose In Column 'A' having Numbers Then You Can find the Sum of Individual Digits of Numbers in Column 'B'
'Eg: A1=147 then B1=1+4+7=11 will return.

Sub SumDigitz()
Dim i As Integer
Dim j As Integer
For i = 1 To 100 'Rows Having Numbers

Cells(i, 2).Clear
If Cells(i, 1) = "" Then Exit For

For j = 1 To Len(Cells(i, 1))
Cells(i, 2) = Cells(i, 2) + Mid(Cells(i, 1), j, 1)
Next j

Next i
End Sub


Saturday, 8 December 2012

How To Import Data from a Text File using Excel VBA Macro

Excel VBA Macro To Import Data from a Text File and Keep in One Cell
 Sub ImpTxtFile()
 Dim A As Integer
 Dim B As Integer

    With ActiveSheet.QueryTables.ADD(Connection:= _
    "TEXT;C:\Documents and Settings\Administrator\Desktop\code1.txt" _
    , Destination:=Cells(1, 1))
    .Refresh BackgroundQuery:=True
    End With

For A = 1 To 100
Cells(1, 1) = Cells(1, 1) & Cells(A, 1)
Next A

For B = 2 To 100
Cells(B, 1).Clear
Next B

End Sub

'Macro to Join all the imported text from all rows to first row'
'Pull out the search item from the cell'
Sub JoinRows()
Dim A As Integer
Dim B As Integer

For A = 1 To 100
Cells(1, 1) = Cells(1, 1) & Cells(A, 1)
Next A

For B = 2 To A
 Cells(B, 1).Clear
Next B

Cells(4, 2) = Mid(Cells(1, 1), InStr(Cells(1, 1), "True") - 10, 30)
End Sub

How To Group the Records of the Same Date in Main Sheet

Excel VBA Macro To Grouping Records of the Same Date in Main Sheet
'The Following Macro Pull Out The Records From Sub Sheet and Insert Into Main Sheet
'Based On Date Matching'

 Sub GroupingTransactions()
 Dim X As Integer
 Dim Y As Integer
 Dim z As Integer
 Dim A As Integer

 z = 0

 For X = 2 To 100
 If Sheets("Sub").Cells(X, 1) = "" Then Exit For

 For Y = 2 To 100
 If Sheets("Main").Cells(Y, 1) = "" Then Exit For

 If Sheets("Main").Cells(X + z, 1) = Sheets("Sub").Cells(Y, 1) Then
 z = z + 1
 Sheets("Main").Cells(X + z, 1).EntireRow.Insert
 For A = 1 To 26
 Sheets("Main").Cells(X + z, A) = Sheets("Sub").Cells(Y, A)
 Next A
 End If

 Next Y
 Next X

 End Sub

Thanks,
TPR

Excel VBA Macro To ShiftUp Data Which In Between Empty Cells in a Entire Sheet

Excel VBA Macro To ShiftUp Data Which In Between Empty Cells in a Entire Sheet
'This Macro is Very Usefull to Shift Up The Data Of a From All Rows and Columns , Where Data Is In Between Empty Rows
'Data In Between Empty Rows Means Some Rows Having Data and Some Rows are Empty
'This Macro Does not Delete Any Cells So That the Data in Other Columns Would Not Distrub
'This Macro Shifts Up Data From All Rows and Columns as It is In Same Order.

Sub Shift_Data_Upp()
Dim X As Long
Dim Y As Long
Dim C As Long

Dim RC As Long
Dim cc As Long

On Error GoTo TPR:

RC = Cells(1, 1).EntireColumn.Cells.COUNT
cc = Cells(1, 1).EntireRow.Cells.COUNT

'RC Is The Total No.Of Rows Count(1048576)
'CC Is The Total No.Of Columns Count(16384)

'If You Know The Rows Range(Eg: 100) Define It So That You Can Speed Up Macro Process
'If You Know The Columns Range(Eg: 26) Define It So That You Can Speed Up Macro Process

Y = 0

For X = 1 To RC  'Instead of 'RC' You Can Give Your Desired Range
If Cells(X, 1) <> "" Then
Y = Y + 1

For C = 1 To 26 'Instead of '26' You Can Give Your Desired Range
'You Can Use 'CC' For All Columns But It Takes So Much Time

Sheets(1).Cells(Y, C) = Sheets(1).Cells(X, C)
'Shifts Up Data From All Rows of Sheet1  As It Is.
Next C
End If
Next X

'Range(Cells(Y + 1, 1), Cells((Y + 1) + (RC - (Y + 1)), 26)).Clear
'Clears The Data In Following Cells Of Shifted Data

MsgBox "Data Success Fully Shifted Up", vbInformation, "Macro Process Completed"

TPR:
End Sub

How To ShiftUp Data In Between Empty Cells in a Desired Column using Excel VBA Macro

Excel VBA Macro To ShiftUp Data Which In Between Empty Cells in a Desired Column
'This Macro is Very Usefull to Shift Up The Data Of a Desired Column , Where Data Is In Between Empty Cells
'Data In Between Empty Cells Means Some Cells Having Data and Some Cells are Empty
'This Macro Does not Delete Any Cells So That the Data in Other Columns Would Not Distrub
'This Macro Shifts Up Data From All Cells(1048576 Rows)of a Desired Column


Sub Shift_Data_Up()
Dim X As Long
Dim Y As Long
Dim z As Long

Dim C As Long
On Error GoTo TPR:

C = InputBox("Enter Desired Column No.In Which Data To Be Shift UP", _
"Shift Up Data In Between Empty Cells", _
"Enter Here As 1 or 2 or 3.....")

z = Cells(1, C).EntireColumn.Cells.COUNT


'Z Is The Total No.Of Rows(1048576)
'If You Know The Row Range(Eg: 100) Define It So That You Can Speed Up Macro Process

Y = 0

For X = 1 To z  'Instead of 'Z' You Can Give Your Desired Range
If Cells(X, C) <> "" Then
Y = Y + 1
Sheets(1).Cells(Y, C) = Sheets(1).Cells(X, C)
'Shifts Up Data From Desired Column of Sheet1
End If
Next X

'Range(Cells(Y + 1, C), Cells((Y + 1) + (Z - (Y + 1)), C)).Clear
'Clears The Data In Following Cells Of Shifted Data

MsgBox "Data Success Fully Shifted Up", vbInformation, "Macro Process Completed"

TPR:
End Sub


Saturday, 1 December 2012

Loop Structures-For Next Loop-Do While Loop-Do Until Loop

Loop Structures-For Next Loop-Do While Loop-Do Until Loop 

For ... Next
Use For ... Next loop if the number of loops is already defined and known. A For ... Next loop uses a counter variable that increases or decreases in value during each iteration of the loop.

Example:

For i = 1 to 10
Cells(i, 1) = i
Next i




In this example, i is the counter variable from 1 to 10. The looping process will send value to the first column of the active sheet and print i (which is 1 to 10) to row 1 to 10 of that column.

Note that the counter variable, by default, increases by an increment of 1.


For ... Next Loop With Step
You can use the Step Keyword to sepcify a different increment for the counter variable. 

Example:


For i = 1 to 10 Step 2
Cells(i, 1) = i
Next i

This looping process will print values with an increment of 2 on row 1, 3, 5, 7 and 9 on column one.


You can also have decrement in the loop by assign a negative value afte the Step keyword.
Example:

For i = 10 to 1 Step -2
Cells(i, 1) = i
Next i

This looping process will print values with an increment of -2 starts from 10 on row 10, 8, 6, 4 and 2 on column one.



Do While ... Loop

You can use the Do While ... Loop to test a condition at the start of the loop. It will run the loop as long as the condition is ture and stops when the condition becomes false. For Example:

i = 1
Do While i =< 10
Cells(i, 1) = i
i = i + 1
Loop

This looping process yields the same result as in the For ... Next structures example.

One thing to be caution is that sometimes the loop might be a infinite loop. And it happens when the condition never becomes false. In such case, you can stop the loop by press [ESC] or [CTRL] +[BREAK].


Do Until ... Loop 
You can test the condition at the beginning of the loop and then run the loop until the test condition becomes true.

Example:

i = 1
Do Until i = 11
Cells(i, 1) = i
i = i + 1
Loop

This looping process yields the same result as in the For ... Next structures example.


Do ... Loop While 
When you want to make sure that the loop will run at least once, you can put the test at the end of loop. The loop will stop when the condition becomes false. (compare this loop structure to the Do ... While Loop.)

Example:

i = 1
Do
Cells(i, 1) = i
i = i + 1
Loop While i < 11

This looping process yields the same result as in the For ... Next structures example.


Do ... Loop Until
This loop structure, like the Do ... Loop While, makes sure that the loop will run at least once, you can put the test at the end of loop. The loop will stop when the condition becomes true. (compare this loop structure to the Do ... Until Loop.)

Example:

i = 1
Do
Cells(i, 1) = i
i = i + 1
Loop Until i = 11

This looping process yields the same result as in the For ... Next structures example.


Thanks
Tamatam

Excel VBA Macros-Decision Structures - IF-Then-Else-ElseIf and Select Case

Decision Structures - IF-Then-Else-ElseIf and Select Case

IF ... Then Statement
The IF ... Then is a single condition and run a single statement or a block of statement.

Example:

The following statement set variable Status to "Adult" if the statement is true:

If Age >= 18 Then Status = "Adult"

You can also use multiple-line block in the If statement as followed:

If Ago >= 18 Then
Status = "Adult"
Vote = "Yes"
End If

Note that in the multiple-line block case, End If statement is needed, where the single-line case does not.
IF ... Then ... Else
The If ... Then ... Else statement is used to define two blocks of conditions - true and false.

Example:

If Age >=22 Then
Drink = "Yes"
Else
Drink = "No"

End If

Again, note that End If statement is needed in this case as well since there is more than one block of statements.


IF ... Then ... ElseIf
The IF ... Then ... ElseIf is used to test additional conditions without using new If ... Then statements.

For Example:

If Age >= 18 and Age < 22 Then
Msgbox "You can vote"
ElseIf Age >=22 and Age < 62 Then
Msgbox "You can drink and vote"
ElseIf Age >=62 Then
Msgbox "You are eligible to apply for Social Security Benefit"
Else
Msgbox "You cannot drink or vote"
End If

Note that the last condition under Else is, implicitly, Age < 18.
Select Case
Select Case statement is an alternative to the ElseIf statement. This method is more efficient and readable in coding the the If ... Then ... ElseIf statment. 

Example:


Select Case Grade
Case Is >= 90
LetterGrade = "A"
Case Is >= 80
LetterGrade = "B"
Case Is >= 70
LetterGrade = "C"
Case Is >= 60
LetterGrade = "D"
Case Else
LetterGrade = "Sorry"
End Select

Excel VBA Macro to Find The Size of an Array

How to Find The Size of an Array
The largest available subscript for the indicated dimension of an array can be obtained by using theUbound function. In our one-dimensional array example, Ubound(arr) is 5.

In our two-dimensional array example above, there are two upper bound figures - both are 2.
UBound returns the following values for an array with these dimensions*:

Dim A(1 To 100, 0 To 3, -3 To 4)

Statement            Return Value
UBound(A, 1)          100
UBound(A, 2)            3
UBound(A, 3)            4

* Example taken from Excel VBA Help section.

The UBound function is used with the LBound function to determine the size of an array. Use theLBound function to find the lower limit of an array dimension.

Statement            Return Value
LBound(A, 1)            1
LBound(A, 2)            0
LBound(A, 3)           -3

To get the size of an array, use the following formula:

UBound(Arr) - LBound(Arr) + 1

For example:

Ubound(A,1) - LBound(A,1) + 1
= 100 - 1 + 1
= 100

Ubound(A,2) - LBound(A,2) + 1
= 3 - 0 + 1
= 4

Ubound(A,3) - LBound(A,3) + 1
= 4 - (-3) + 1
= 8

Excel VBA -Multi-Dimensional Array Example

How to Create a Multi-Dimensional Array in VBA
An array can also store multiple dimensional data. To simplify our tutorial, example on a two-dimensional array is used. Assume you have data of a local store's yearly sale in the following table and you want to store the data in a two-dimensional array:

Year 2003 Year 2004
CD Sale 1,000 1,500
DVD Sale 1,200 2,000

First we create the array as follow:


Dim Arr(2,2)

Then we assign the values into the array. We treat the first dimension as the year and the second dimension as the product sale:

arr(1,1) = 1000
arr(1,2) = 1200
arr(2,1) = 1500
arr(2,2) = 2000

We now display the values of the array with a message box:
Msgbox "Sale of CD in 2003 is " & arr(1,1) & vbCrLf & "Sale of CD in 2004 is " _
& arr(2,1) & vbCrLf & "Sale of DVD in 2003 is " & arr(1,2) & vbCrLf _
& "Sale of DVD in 2004 is " & arr(2,2)

The complete precedure is as followed:

Option Base 1
Sub multDimArray( )
Dim Arr(2,2)

arr(1,1) = 1000
arr(1,2) = 1200
arr(2,1) = 1500
arr(2,2) = 2000

Msgbox "Sale of CD in 2003 is " & arr(1,1) & vbCrLf & "Sale of CD in 2004 is " _
& arr(2,1) & vbCrLf & "Sale of DVD in 2003 is " & arr(1,2) & vbCrLf _
& "Sale of DVD in 2004 is " & arr(2,2)
End Sub




* vbCrLf stands for VB Carriage Return Line Feed. It puts a return and a new line as shown in the
message box above. The underscore "_" on the back of the first line of the message box means
"continue to the next line"

How to use Redim Preserve keyword in VBA Arrarys

How to Declare a Dynamic Array with Redim Preserve in VBA Macros

The Redim Statement is used to resize an array. When we resize an Array it will erase the elements already stored in it. In the Example-I, all the values assigned prior to resize the array are erased. Only the value assigned to the array after resize remains. 


To keep/preserve already stored values we need to use the 'Redim Preserve' keyword as explained in Example-II.
---------------------------------------------------------------------------------------
Example-I :Resizing an Arrary with 'Redim ' keyword :
---------------------------------------------------------------------------------------
Option Base 1         ------ ' Allows the Arrary index starts from '1' otherwise by default from '0'
Sub Redim_Array( )
Redim Arr(5)

Arr(1) = “Jan”
Arr(2) = “Feb”
Arr(3) = “Mar”
Arr(4) = “Apr”
Arr(5) = “May”

Redim Arr(6)

Arr(6) = “Jun”

Msgbox Arr(1) & "-" & Arr(2) & "-" & Arr(3) & "-" & Arr(4) & "-" & Arr(5) & "-" & Arr(6)
End Sub


---------------------------------------------------------------------------------------
Example-II : Resizing an Arrary with 'Redim Preserve' keyword :
--------------------------------------------------------------------------------------- By replace the Redim Arr(6) with Redim Preserve Arr(6), all values will remain. 

Option Base 1
Sub Redim_Preserve_Array( )
Redim Arr(5)

Arr(1) = “Jan”
Arr(2) = “Feb”
Arr(3) = “Mar”
Arr(4) = “Apr”
Arr(5) = “May”

Redim Preserve Arr(6)

Arr(6) = “Jun”

Msgbox Arr(1) & "-" & Arr(2) & "-" & Arr(3) & "-" & Arr(4) & "-" & Arr(5) & "-" & Arr(6)
End Sub





To Clear or Erase an Array :
You can clear or erase an array with Erase keyword, which will clears all index values in an Array.

Erase Arr()


Thanks,
TAMATAM

How to Resize an Array With Redim Statement in VBA Arrarys

VBA Arrarys : Resize an Array With Redim Statement
The ReDim statement is used to size or resize a dynamic array that has already been formally declared.

For example, if you have already declared an array with an Index value of 5 and decided to change the number of Index to 6, you can do as shown in below example Macro :
Sub Redim_My_Array( )
Dim Arr(5) 

As you declared an array with index size 5 , you can store the values as follows :
Arr(0) = "Months"
Arr(1) = “Jan”
Arr(2) = “Feb”
Arr(3) = “Mar”
Arr(4) = “Apr”
Arr(5) = “May”

Msgbox Arr(0) & "-" Arr(1) & "-" & Arr(2) & "-" & Arr(3) & "-" & Arr(4) & "-" & Arr(5)

At any point of time if you want to resize your , that means if you want to increase index size , you can use Redim statement as follows.

Redim Arr(6)

Arr(0) = "Months"
Arr(1) = “Jan”
Arr(2) = “Feb”
Arr(3) = “Mar”
Arr(4) = “Apr”
Arr(5) = “May”
Arr(6) = “Jun”

Msgbox  
Arr(0) & "-" Arr(1) & "-" & Arr(2) & "-" & Arr(3) & "-" & Arr(4) & "-" & Arr(5) & "-" & Arr(6)
End Sub 

Note :
Please note that when you use Redim() statement in between of the Macro , already stored values in Array will be lost , you need to restore again.
To avoid this problem , you need use 'Redim Preserve' Keyword.

Please go through the below link for better understanding :


Thanks,
TAMATAM



How to Declare an Array with Dim Statement in VBA Arrarys

Basic syntax of Declaring an Array with Dim Statement
An array is a set of sequentially indexed elements having the same intrinsic data type. Each element of an array has a unique identifying index number. Changes made to one element of an array don't affect the other elements. 

Before signing values to an array, the array needs to be created. You can declare the array by using the Dim statement.

For example, to declare a one-dimensional array with 5 elements, type the following:
Dim Arr(4)

The element’s index of the array starts from 0 unless Option Base 1 is specified in the public area (area outside of the sub procedure). If Option Base 1 is specified, the index will start from 1.

The following example assigns values to the array and displays all values in a message box :
Option Base 1
Sub Declare_Array( )
Dim Arr(5)

Arr(1) = “Jan”
Arr(2) = “Feb”
Arr(3) = “Mar”
Arr(4) = “Apr”
Arr(5) = “May”

Msgbox Arr(1) & "-" & Arr(2) & "-" & Arr(3) & "-" & Arr(4) & "-" & Arr(5)
End Sub




* The number inside the array, i.e. Arr(1), is the index. One (1) is the index of the first element in the array.

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts