Excel VBAMacros v1.1

8/3/2019 Excel VBAMacros v1.1

1/72

Excel VBA / Macros
http://images.google.co.in/imgres?imgurl=http://farm2.static.flickr.com/1346/1416877307_8885d7816d.jpg?v=0&imgrefurl=http://www.flickr.com/photos/aeptspo/1416877307/in/set-72157601596037194/&usg=__Wn0GdbLP-_-wcrngV-x8gDpkps8=&h=100&w=99&sz=6&hl=en&start=42&tbnid=6FGbmjQONOByNM:&tbnh=82&tbnw=81&prev=/images?q=excel+logo&start=40&gbv=2&ndsp=20&hl=en&sa=Nhttp://images.google.co.in/imgres?imgurl=http://www.mt-soft.com.ar/wordpress/wp-content/uploads/2007/10/ms_excel1.jpg&imgrefurl=http://www.mt-soft.com.ar/2007/12/21/dump-mssql-results-excel/&usg=___ioD8L_TzHxf10qDQcpRSF9TjlI=&h=128&w=128&sz=9&hl=en&start=12&tbnid=aKGqcoPtcR8xIM:&tbnh=91&tbnw=91&prev=/images?q=excel+logo&gbv=2&ndsp=20&hl=en&sa=N


2/72

Table of Contents Starting with Macros - Recording and Running a Macro

Introduction to Visual Basic Concepts of Object Oriented Programming

Data Types, Variables, Procedures & Operators

Range Selection

Control Statements

If Then

With End With

For... Next

Do Loop

Do Until Do While Loop

While Wend

Select Case

Arrays and Counter

String Handling

Input Box and Message Box Functions Working with Controls Text Box, Label, Scroll Bar and Check box

User-Defined Functions like

Selective summation

Check a prime number

Extract data on certain condition

Error Handling and Debugging


3/72

Starting with Macros

Visual Basic Macros Tool Bar

Setting security level of MacrosRecord Macro

1. Open xlmacros.xls, sheet 1

2. Go to Tools on Menu bar and select Macros Record macro

3. Fill in the required details in Macros dialog box.

4. Select cells A1 to D105. Apply the following formats to selected cells

6. Change font to Tahoma and font size to 12

7. Right align and bold

8. Change font color to red and pattern to yellow

9. Stop recording Macro

View Macro Code in Visual Basic Editor

Run Macro


4/72

Object Oriented Programming

VBA is a structured programming language where sentences (called statements) areconstructed of building blocks such as objects, methods, and properties. These VBAstatements are grouped in larger blocks called procedures. A procedure is a set of VBAstatements that performs a specific task or calculates a specific result. The first step tolearning how to create procedures is to learn about the building blocks.

VBA Procedure

Methods PropertiesObjects


5/72

Objects and CollectionsObjects:VBA is an object-oriented programming language, which means the statements you create in VBA act onspecific objects rather than begin general commands. Excel is made of objects that you can manipulate

through the VBA statements.

Collections:For example, a workbook is a collection of all the objects it contains like ex.sheets, cells, ranges, charts,VBA modules, etc.

Objects in ExcelWorksheet

Workbook

Cell

Range

Collection of ObjectsWorksheets

Rows

Workbooks

Cells


6/72

Properties and MethodsProperties:Properties are used to describe an object. Some properties are read-only, while others are read/write. These propertiesdescribe Excel objects. For example, an Excel workbook has a particular path on your hard drive or network where it issaved. That path is a property of the workbook. That path is read-only as it cannot be changed without saving the file to adifferent location. Properties are separated from objects by periods just as methods are. The following sentence willdisplay the current path of the Training.xls file in an onscreen message box: Msgbox Workbooks(Training.xls).Path

Methods:A method is an action that can be performed on an object. Excel VBA objects are separated from their methods by aperiod. For example, if you wanted to save a particular file as part of a VBA program you could include the followingsentence in the code: Workbooks(Training.xls).Save

Examples:

Object.Method

Ball.kick, Book.read,Book.write, Water.Drink, Food.Eat

Balls(soccer).kick Balls(soccer).kick Direction:=Left, Force:=Hard, Elevation:=High

Wordart.Add Left:=10,Top:=20,width:=100, Height:=200

Worksheets.Add Before:=Worksheets(1)Set MyWorksheet = Worksheets.Add (Before:=Worksheets(1))

Object.property ActiveCell.Interior.ColorIndex = 30 --- (Color the Cell Yellow)


7/72

Variables and ConstantsVariablesVariables are the memory locations which are used to store values temporarily. A defined naming strategy has to befollowed while naming a variable. A variable name must begin with an alphabet letter and should not exceed 255characters. It must be unique within the same scope. It should not contain any special character like %, &, !, #, @ or

Sub Add_Footer()CoName = InputBox("Name your Company?", "Add Company Name to Footer")With ActiveSheet.PageSetup

.LeftHeader = ""

.CenterHeader = ""

.RightHeader = ""

.LeftFooter = CoName

.CenterFooter = ""

.RightFooter = "&N"End WithEnd Sub

ConstantsLike a variable, a constant is a temporary holding place for some information that is used in a procedure. However, as thename implies a constant never changes. Constants must be declared. A declaration statement in a VBA macro is used todefine the value of a constant.

Sub Add_Footer()

Const CoName = Analytics Training Institute With ActiveSheet.PageSetup.LeftHeader = "".CenterHeader = "".RightHeader = "".LeftFooter = CoName.CenterFooter = "".RightFooter = "&N"

End With

End Sub


8/72

Types of Variables

Local VariablesA local variable is one that is declared inside a procedure. This variable is only available to the code inside the procedure

and can be declared using the Dim statements as given below.e.g.: Dim sum As Integer

Static Variables Static variables are not reinitialized each time a procedure is invoked and therefore retains or preserves value even whena procedure ends. A static variable is declared as given below.

e.g.: Static tot As Integer

Module level Variables A module level variable is available to all the procedures in the module. They are declared using the Public or the Private keyword.

e.g.: Public ans As Integer

Private Temp As Integer


9/72

ProceduresSub ProceduresA sub procedure can be placed in standard, class and form modules. Each time the procedure is called, the statementsbetween Sub and End Sub are executed.

The syntax for a sub procedure is as follows:[Private | Public] [Static] Sub Procedurename [( arglist)][ statements]End Sub

Function ProceduresFunctions are like sub procedures, except they return a value to the calling procedure. They are especially useful for takingone or more pieces of data, called arguments and performing some tasks with them. Then the functions returns a value

that indicates the results of the tasks complete within the function.

Property ProceduresA property procedure is used to create and manipulate custom properties. It is used to create read only properties forForms, Standard modules and Class modules. Visual Basic provides three kind of property procedures-Property Letprocedure that sets the value of a property, Property Get procedure that returns the value of a property, and Property Setprocedure that sets the references to an object.


10/72

Event Procedures An event procedure is a procedure block that contains the control's actual name, an underscore(_), and the event name.The following syntax represents the event procedure for a Form_Load event.

Private Sub Form_Load()....statement blockEnd Sub

General Procedures A general procedure is declared when several event procedures perform the same actions. It is a good programmingpractice to write common statements in a separate procedure (general procedure) and then call them in the eventprocedure..

Procedures


11/72

Data Types1. Numeric

I. Byte - Store integer values in the range of 0 - 255

II. Integer - Store integer values in the range of (-32,768) - (+ 32,767)

III. Long - Store integer values in the range of (- 2,147,483,468) - (+ 2,147,483,468)

IV. Single - Store floating point value in the range of (-3.4x10-38) - (+ 3.4x1038)

V. Double - Store large floating value which exceeding the single data type value

VI. Currency - store monetary values. It supports 4 digits to the right of decimal point and 15 digits to the left

2. String Use to store alphanumeric values. A variable length string can store approximately 4 billion characters

3. Date Use to store date and time values. A variable declared as date type can store both date and time values and itcan store date values 01/01/0100 up to 12/31/9999

4. Boolean Boolean data types hold either a true or false value. These are not stored as numeric values and cannot beused as such. Values are internally stored as -1 (True) and 0 (False) and any non-zero value is considered as true

5. Variant Stores any type of data and is the default Visual Basic data type. In Visual Basic if we declare a variablewithout any data type by default the data type is assigned as default.


12/72

Referencing

Range : Top-Left Bottom- Right Selection

Range( A1:B5 ).Select Range( A1 , B5 ).Select Range( A1 , ActiveCell ).SelectRange( Activecell , ActiveCell.Offset (5,2) ).Select

Shortcuts

Range(D5) [D5]Range(A1:D5) [A1:D5]Range(A1:D5,G6:I17) [A1:D5,G6:I17]Range( MyRange ) [MyRange]


13/72

Referencing Ranges with Other Sheets

WorkSheets (Sheet1).Range(A1)

Workbooks(InvoiceData.xls).Worksheets(Sheet1).Range(A1)

WorksheetFunction.Sum (Worksheets(Sheet2).Range(Range(A1),Range(A7))) Wrong

WorksheetFunction.Sum (Worksheets(Sheet2).Range(Worksheets(Sheet2).Range(A1),Worksheets(Sheet2).Range(A7)))

With. End With

With Worksheets(Sheets2) WorksheetFunction.Sum (.Range(.Range(A1), .range(A7)))

End With


14/72

Cells Property to Select RangeCell Item PropertyCells.Item(Row,Column)

Cells.Item (5,c) or Cells.Item(5,3)

Cells property is especially useful to loop through rows and columns

Ex: FinalRow = Range (A65536).End( xlup).RowFor i=1 to Finalrow

Range(A & i & :E &i).Font.Bold = TrueNext i

FinalRow = Range (A65536).End( xlup).RowFor i=1 to FinalrowCells( i,A).Resize(,5). Font.Bold = True

Next i

Cells Property in Range Property

Range(Cells(1,1),Cells(5,5)) useful to specify variables with parameters as in looping


15/72

Resize Property to Change Size of Range

Enables to change the size of range based off the location of active cell

Syntax: Range.Resize(Rowsize, Columnsize)

To create a range B3:D13,Range(B3). Resize(11,3) Range(B3).Resize(,2) --- only ColumnRange(B3).Resize(2) ---- only Row

Ex: Cells(i,1).resize(1,8).interior. Colorindex = 4

Sub try()

Cells(1, 1).Resize(1, 8).Interior.ColorIndex = 4

End Sub

Column and Row Properties to select RangeFinalRow = Range(A65536).End( xlUp).Row


16/72

Offset Property to Refer to Range

Range.Offset (RowOffset, ColumnOffset)

To affect cell F5 from cell A1,Range(A1).Offset(4,5) ------ The count starts at A1, but does not include A1

Range(A1).Offset( Colunmoffset :=1) or Range(A1).Offset(,1) Range(A1).Offset( Rowoffset :=1) or Range(A1).Offset( -1)

Ex: Sub try()Cells(1, 1).Offset(1, 8).Interior.ColorIndex = 4End Sub

Sub try1()Set Rng = Range("B1:B16").Find(What:="0", Lookat:=xlWhole, LookIn:=xlValues)Rng.Offset(, 1).Value = "Low"End Sub

Cereals 45

Dals 0

Noodles 15

Masala 10

Biscuits 60


17/72

Union Method to Join Multiple Ranges

Application.Union (argument1, argument2,..)

-- this code joins two named ranges on the sheet, inserts the =RAND() formulas,and bolds them

Set UnionRange = Union(Range(A1), Range(B4)) With UnionRange

.Formula = =RAND()

.Font.Bold = TrueEnd With


18/72

Exercise Day 1:

Procedure to Find Sum of two numbers: Sub addition()Dim total As Integertotal = Add(1, 10)MsgBox "The answer is : " & totalEnd Sub

Function Add(Number1, Number2) As IntegerAdd = Number1 + Number2End Function

Procedure to find if a certain workbook is open Function BookOpen(Bk As String) As BooleanDim T As Excel.WorkbookErr.ClearOn Error Resume NextSet T = Application.Workbooks(Bk)BookOpen = Not T Is NothingErr.ClearOn Error GoTo 0End Function

Sub OpenAWorkbook()Dim IsOpen As BooleanDim Bookname As StringBookname = "Excel_Macros1.xls"IsOpen = BookOpen(Bookname)If IsOpen Then

MsgBox Bookname & "is Already Open"Else

Workbooks.Open (Bookname)

End IfEnd Sub


19/72

More Examples

Sub Count()mycount = Selection.Rows.Count 'Change Rows to Columns tocount columnsMsgBox mycountEnd Sub

Count2()count number of sheets

mycount = Application.Sheets.CountMsgBox mycountEnd Sub

Sub CopyRange()Copy selected cell to a destination Range("A1:A3").Copy Destination:=ActiveCellEnd Sub

Sub MyPosition()Count number of Rows and Columns in a sheet myRow = ActiveCell.Row

myCol = ActiveCell.ColumnMsgBox myRow & "," & myColEnd Sub


20/72

Function Myname() As StringMyname = ThisWorkbook.NameEnd Function

Function MyFullName() As StringMyFullName = ThisWorkbook.FullNameEnd Function

Convert Week number into Date

Function ConvertWeekDay(Str As String) As DateDim Week as LongDim FirstMon As DateDim TStr As StringFirstMon = DateSerial(Right(Str,4),1,1)FirstMon = FirstMon FirstMon Mod 7 + 2Tstr = Right(Str, Len(Str) 5)Week = Left(TStr, Instr (1,Tstr, ,1)+ 0 ConvertWeekDay = FirstMon + (Week -1) * 7

End Function


21/72

Conditional StatementsIf...Then selection structureSyntax of the If...Then selectionIf Then

statementEnd If

e.g.: If A3>75 ThenRange(B3).value = "A" End If

Nested If...Then...Else selection structure

Method 1

If < condition 1 > ThenstatementsElseIf < condition 2 > ThenstatementsElseIf < condition 3 > ThenstatementsElseStatementsEnd If

If...Then...Else selection structureSyntax of the If...Then...Else selectionIf ThenstatementsElse

statementsEnd If

e.g.: If A3>50 ThenRange(B3).value = "Pass" ElseRange(B3).value = "Fail" End If

Method 2 If < condition 1 > ThenstatementsElseIf < condition 2 > Then

statementsElseIf < condition 3 > ThenstatementsElseStatementsEnd IfEnd IfEndIf


22/72

Select...Case selection structureSyntax of the Select...Case selection structureSelect Case IndexCase 0StatementsCase 1StatementsEnd Select

Example:Function testcase(x As Integer)Select Case xCase 1testcase = "1st entry"Case 2testcase = "2nd entry"

Case 3testcase = "3rd entry"Case 4testcase = "4th entry"Case Elsetestcase = "invalid entry"End SelectEnd Function


23/72

Do While... Loop StatementThe Do While...Loop is used to execute statements until a certain condition is met. The following Do Loop counts from 1to 100.

Dim number As Integernumber = 1Do While number


24/72

Do...Loop While StatementThe Do...Loop While statement first executes the statements and then test the condition after each execution. Thefollowing program block illustrates the structure:

Dim number As Longnumber = 0DoCells(number,1) = Advanced Excel number = number + 1

Loop While number < 201

The programs executes the statements between Do and Loop While structure in any case. Then it determines whether thecounter is less than 201. If so, the program again executes the statements between Do and Loop While else exits theLoop.


25/72

Do Until...Loop StatementUnlike the Do While...Loop and While...Wend repetition structures, the Do Until... Loop structure tests a condition forfalsity. Statements in the body of a Do Until...Loop are executed repeatedly as long as the loop-continuation testevaluates to False.

An example for Do Until...Loop statement.

Dim number As Longnumber=0Do Until number > 10

number = number + 1Cells(number,1) = Successfully executed Loop

Numbers between 1 to 1000 will be displayed on the form as soon as you click on the command button.


26/72

The For...Next LoopThe For...Next Loop is another way to make loops in Visual Basic. For...Next repetition structure handles all the details of

counter-controlled repetition. The following loop counts the numbers from 1 to 100:

Dim x As IntegerFor x = 1 To 50Cells(x,2) = xNext

In order to count the numbers from 1 yo 50 in steps of 2, the following loop can be used

For x = 1 To 50 Step 2Cells(x,2) = xNext

The following loop counts numbers as 1, 3, 5, 7..etc


27/72

Following example is a For...Next repetition structure which is with the If condition used.

Dim number As IntegerFor number = 1 To 10If number = 4 ThenCells(number,3) = "This is number 4"ElseCells(number,3) = numberEnd IfNext

In the output instead of number 4 you will get the "This is number 4".


28/72

Exit Loops

A For...Next loop condition can be terminated by an Exit For statement. Consider the following statement block.

Dim x As IntegerFor x = 1 To 10Cells(x,2) = xIf x = 5 ThenCells(x,1) = "The program exited at x=5"Exit ForEnd IfNext x

The preceding code increments the value of x by 1 until it reaches the condition x = 5. The Exit For statement is executedand it terminates the For...Next loop.

The Following statement block containing Do...While loop is terminated using Exit Do statement.

Dim x As IntegerDo While x < 10Cells(x,2) = xx = x + 1If x = 5 ThenCells(x,3) = "The program is exited at x=5"Exit DoEnd IfLoop


29/72

With. End With

When properties are set for objects or methods are called, a lot of coding is included that acts on the same object. It iseasier to read the code by implementing the With...End With statement. Multiple properties can be set and multiplemethods can be called by using the With...End With statement. The code is executed more quickly and efficiently as theobject is evaluated only once. The concept can be clearly understood with following example.

With Worksheets(Sheet1).Range(A10:A20) .Font.Size = 14.Font.Bold = True.ForeColor = vbRed.Height = 230.Text = "Hello World"End With


30/72

ExamplesFunction Comm(Sales_V As Variant) as VariantIf Sales_V =500 and Sales_V=1000 and Sales_V=200 and Sales_V=5000 ThenComm=Sales_V*0.15End If

End Function

/Subject to certain changes in Form1 statement/

Private Sub addName()Dim studentName(10) As StringDim num As IntegerFor num = 1 To 10studentName(num) = InputBox("Enter the student name","Enter Name", "", 1500, 4500)If studentName(num) "" ThenForm1.Print studentName(num)ElseEndEnd If

NextEnd Sub


31/72

ExamplesEx 1:Sub tryloop()For I = 1 to 10

cells(i,i).value = INext IEnd Sub

For I = 2 to 10if cells(I,6).value >0 then

Cells(I,8).value = service revenue Cells(I,1).resize(1,8).interior. Colorindex = 4

End ifNext i

FinalRow = Cells(65536, 1).end(xlup).rowFor i=2 to FinalRowif cells(I,6).value >0 then

Cells(I,8).value = service revenue Cells(I,1).resize(1,8).interior. Colorindex = 4End ifNext i


32/72

Delete all rows where column C has value s54

FinalRow = Cells(65536,1).end(xlup).rowFor I = FinalRow to Step -1if Cells(I,3).value = s54 then

Cells(I,1).EntireRow.DeleteEnd ifNext I

Ex: Looks for row in dataset where service rev in column F is positive and product

Rev in Column E is 0.FinalRow = Cells(65536, 1).end(xlup).rowProblemFound = FalseFor I = 2 to FinalRow

if Cells(I,6).value >0 thenif Cells(I,5).value = 0 then

Cells(I,6).selectProblemFound = True

Exit ForEnd ifEnd ifNext iIf ProblemFound then

MsgBox There is a problem at row & I Exit Sub

End if


33/72

Arrays

An array is a consecutive group of memory locations that all have the same name and the same type. To refer to aparticular location or element in the array, we specify the array name and the array element position number. Arrays haveupper and lower bounds and the elements have to lie within those bounds. Each index number in an array is allocatedindividual memory space and therefore users must evade declaring arrays of larger size than required. We can declare anarray of any of the basic data types including variant, user-defined types and object variables. The individual elements ofan array are all of the same data type.


34/72

Array Declaration

Declaring arraysArrays occupy space in memory. The programmer specifies the array type and the number of elements required by thearray so that the compiler may reserve the appropriate amount of memory. Arrays may be declared as Public (in a codemodule), module or local. Module arrays are declared in the general declarations using keyword Dim or Private. Localarrays are declared in a procedure using Dim or Static. Array must be declared explicitly with keyword "As".There are two types of arrays in Visual Basic namely:

Fixed-size array : The size of array always remains the same-size doesn't change during the program execution.

Dynamic array : The size of the array can be changed at the run time- size changes during the program execution.


35/72

Fixed-sized ArraysWhen an upper bound is specified in the declaration, a Fixed-array is created. The upper limit should always be within therange of long data type.

Declaring a fixed-arrayDim numbers(5) As Integer

In the above illustration, numbers is the name of the array, and the number 6 included in the parentheses is the upper limitof the array. The above declaration creates an array with 6 elements, with index numbers running from 0 to 5.If we want to specify the lower limit, then the parentheses should include both the lower and upper limit along with the Tokeyword. An example for this is given below.

Dim numbers (1 To 6 ) As Integer

In the above statement, an array of 10 elements is declared but with indexes running from 1 to 6.A public array can be declared using the keyword Public instead of Dim as shown below.

Public numbers(5) As Integer


36/72

Multidimensional ArraysArrays can have multiple dimensions. A common use of multidimensional arrays is to represent tables of values consistingof information arranged in rows and columns. To identify a particular table element, we must specify two indexes: The first(by convention) identifies the element's row and the second (by convention) identifies the element's column.Tables or arrays that require two indexes to identify a particular element are called two dimensional arrays. Note thatmultidimensional arrays can have more than two dimensions. Visual Basic supports at least 60 array dimensions, but mostpeople will need to use more than two or three dimensional-arrays.

The following statement declares a two-dimensional array 50 by 50 array within a procedure.

Dim AvgMarks ( 50, 50)

It is also possible to define the lower limits for one or both the dimensions as for fixed size arrays. An example for this is

given here.

Dim Marks ( 101 To 200, 1 To 100)

An example for three dimensional-array with defined lower limits is given below.

Dim Details( 101 To 200, 1 To 100, 1 To 100)


37/72

Dynamic ArraysThere will be a situation when the user may not know the exact size of the array at design time. Under suchcircumstances, a dynamic array can be initially declared and can add elements when needed instead of declaring the sizeof the array at design time.

Dynamic arrays are more flexible than fixed-arrays, because they can be resized anytime to accommodate new data. Likefixed-sized arrays, dynamic arrays have either Public (in code modules), module or local scope. Module dynamic arraysare declared using keyword Dim or Private. Local dynamic arrays are declared with either Dim or Static.

e.g.: Dim dynArray ( )

The actual number of elements can be allocated using a ReDim statement. This example allocates the number ofelements in the array based on the value of the variable, x.

ReDim dynArray ( x + 1 )

Each time on executing the ReDim statement, the current data stored in the array is lost and the default value is set. But ifwe want to change the size of the array without losing the previous data, we have to use the Preserve keyword with theReDim statement. This is shown in the example given below.

ReDim Preserve dynArray ( 50 To 200)


38/72

ControlsCreating and Using ControlsA control is an object that can be drawn on a Form object to enable or enhance user interaction with an application.Controls have properties that define aspects their appearance, such as position, size and colour, and aspects of theirbehavior, such as their response to the user input. They can respond to events initiated by the user or set off by thesystem. For instance, a code could be written in a CommandButton control's click event procedure that would load a file ordisplay a result.

In addition to properties and events, methods can also be used to manipulate controls from code. For instance, the movemethod can be used with some controls to change their location and size.I would like to stress that knowing how and when to set the objects' properties is very important as it can help you to writea good program or you may fail to write a good program. So, I advice you to spend a lot of time playing with the objects'properties

Here are some important points about setting up the properties You should set the Caption Property of a control clearly so that a user knows what to do with that command. For

example, in the calculator program, all the captions of the command buttons such as +, - , MC, MR are commonlyfound in an ordinary calculator, a user should have no problem in manipulating the buttons

A lot of programmers like to use a meaningful name for the Name Property may be because it is easier for them towrite and read the event procedure and easier to debug or modify the programs later. However, it is not a must to dothat as long as you label your objects clearly and use comments in the program whenever you feel necessary

One more important property is whether the control is enabled or not Finally, you must also considering making the control visible or invisible at runtime, or when should it become visible

or invisible


39/72

Control Properties

TabIndex property of ControlsVisual Basic uses the TabIndex property to determine the control that would receive the focus next when a tab key ispressed. Every time a tab key is pressed, Visual Basic looks at the value of the TabIndex for the control that has focus andthen it scans through the controls searching for the next highest TabIndex number. When there are no more controls withhigher TabIndex value, Visual Basic starts all over again with 0 and looks for the first control with TabIndex of 0 or higherthat can accept keyboard input.

By default, Visual Basic assigns a tab order to control as we draw the controls on the Form, except for Menu, Timer, Data,Image, Line and Shape controls, which are not included in tab order. At run time, invisible or disabled controls also cannotreceive the focus although a TabIndex value is given. Setting the TabIndex property of controls is compulsory indevelopment environment.


40/72

Text Box Property of ControlsA TextBox control is an edit field or edit control, displays information entered at design time, entered by the user, orassigned to the control using code at run time.

Setting properties to a TextBox Text can be entered into the text box by assigning the necessary string to the text property of the control If the user needs to display multiple lines of text in a TextBox, set the MultiLine property to True To customize the scroll bar combination on a TextBox, set the ScrollBars property Scroll bars will always appear on the TextBox when it's MultiLine property is set to True and its ScrollBars property is set

to anything except None(0) If you set the MultilIne property to True, you can set the alignment using the Alignment property. The test is left-justified

by default. If the MultiLine property is et to False, then setting the Alignment property has no effect

In order to work with the part of a text in a text box, the text can be selected using three properties:

SelLength Returns or sets the number of characters selected.

SelStart Returns or sets the starting point of selected text. When no text is selected, SelStart indicates the position ofthe inserted point.

SelText Returns or sets the string containing the currently selected text. If no text is selected, SelText consists of a zero-length string.


41/72

Using a LabelIts a graphical control user can use to display uneditable text.

Properties of a Label Control We can write code that changes the caption property displayed by a Label control in response to events at run time. Wecan also use a label to identify a control, such as TextBox control, That doesn't have its own Caption property

The Autosize and WordWrap properties should be set if the user wants the Label properly display variable-length linesvarying numbers of lines

Using a CommandButtonWe can begin, interrupt or end a process using the CommandButton control

Properties of a CommandButton control To display text on a CommandButton control, set its caption property An event can be activated by clicking on the CommandButton To set the background colour of the CommandButton, select a colour in the BackColor property To set the text colour set the Forecolor property Font for the CommandButton control can be selected using the Font property To enable or disable the buttons set the Enabled property to True or False To make visible or invisible the buttons at run time, set the Visible property to True or False Tooltips can be added to a button by setting a text to the Tooltip property of the CommandButton

A button click event is handled whenever a command button is clicked. To add a click event handler, double click thebutton at design time, which adds a subroutine like the one given below

Private Sub Command1_Click( )..................End Sub


42/72

Using OptionButton ControlOptionButon provides a set of choices from which a user can select only one button byClicking it at run timeAssigning the value of the OptionButton in to True. The code is to assign it to True Option1.Value = TrueUsing the shotcut keys specified in the Caption of a LabelYou can disable or enable the option button by setting the Enabled property to True os False. You can use the Visibleproperty to make the option button visible to invisible.The following example contains a Label, TextBox, CommandButton and three OptionButton controls.

ExampleOpen a new Standard EXE project and the save the Form as Option.frm and save the project as Option.vbp

The application responds to the following events

The click event of the optWithoutMeal displays the amount of 2500 in txtAmount.The click event of the optWithMeal displays the amount of 3500 in txtAmount.The click event of the optLuxuty displays the amount of 5000 in txtAmount.The click event of the cmdExit terminates the programFollowing code is typed in the click events of the option buttons and the Exit button.

The Application is run by pressing F5 or clicking on the Run icon in the tool bar. By pressing the Exit button the programis terminated.


43/72

List Box and Combo Box

ListBox and ComboBox controls present a set of choices that are displayed vertically in a column. If the number of itemsexceed the value that be displayed, scroll bars will automatically appear on the control. These scroll bars can be scrolledup and down or left to right through the list.

The following Fig lists some of the common ComboBox properties and methods.


44/72

Run Time : The AddItem method is used to add items to a list at run time. The AddItem method uses the following syntax.Object.AddItemitem, Index The item argument is a string that represents the text to add to the listThe index argument is an integer that indicates where in the list to add the new item. Not giving the index is not aproblem, because by default the index is assigned.Following is an example to add item to a combo box. The code is typed in the Form_Load event

Removing Items from a ListThe RemoveItem method is used to remove anitem from a list. The syntax for this is given below.Object.RemoveItem index The following codeverifies that an item is selected in the list and thenremoves the selected item from the list.


45/72

The Simple Combo box displays an edit area with an attached list box always visible immediately below the edit area. Asimple combo box displays the contents of its list all the time. The user can select an item from the list or type an item inthe edit box portion of the combo box. A scroll bar is displayed beside the list if there are too many items to be displayed inthe list box area.The Dropdown Combo box first appears as only an edit area with a down arrow button at the right. The list portion stayshidden until the user clicks the down-arrow button to drop down the list portion. The user can either select a value from thelist or type a value in the edit area.The Dropdown list combo box turns the combo box into a Dropdown list box. At run time , the control looks like theDropdown combo box. The user could click the down arrow to view the list. The difference between Dropdown combo &

Dropdown list combo is that the edit area in the Dropdown list combo is disabled. The user can only select an item andcannot type anything in the edit area. Anyway this area displays the selected item.ExampleThis example is to Add , Remove, Clear the list of items and finally close the application.Open a new Standard EXE project is opened an named the Form as Listbox.frm and save the project as Listbox.vbpDesign the application as shown below.

Sorting the ListThe Sorted property is set to True to enable a list to appearin alphanumeric order and False to display the list items inthe order which they are added to the list.

Using the ComboBoxA ComboBox combines the features of a TextBox and aListBox. This enables the user to select either by typingtext into the ComboBox or by selecting an item from thelist. There are three types of ComboBox styles that arerepresented as shown below.

Dropdown Combo (style 0)Simple Combo (style 1)Dropdown List (style 2)


46/72

The following event procedures are entered for the TextBox and CommandButton controls.

The click event of the Add button adds the text to the list box that was typed in the Text box. Then the text box is clearedand the focus is got to the text box. The number of entered values will is increased according to the number of itemsadded to the listbox.


47/72

Command Buttons

Remove button removes the selected item from the list as soon as you pressed the Remove button. The number of itemsis decreased in the listbox and the value is displayed in the label.The code for the clear button clears the listbox when you press it. And the number of items shown in the label becomes 0.


48/72

Using ScrollBar Control

The ScrollBar is a commonly used control, which enables the user to select a value by positioning it at the desired location.It represents a set of values. The Min and Max property represents the minimum and maximum value. The value propertyof the ScrollBar represents its current value, that may be any integer between minimum and maximum values assigned.Following example illustrates the ScrollBar control Open a new Standard EXE project and name the form as ScrollBar.frm and name the project as ScrollBar.vbp When the thumb's position of the ScrollBar is changed the value has to displayed in the TextBox. Design the application as shown below.


49/72

The following codes are entered in the vsb1_Change( ) and cmdExit_Click( ) procedures.

Save the project and run the application by pressing F5 or clicking the Run icon in the ToolBar. We can see the valuechanges as soon as you move the thumb's position of the vertical scroll bar.


50/72

Control Arrays

A control array is a group of controls that share the same name type and the same event procedures. Adding controls withcontrol arrays uses fewer resources than adding multiple control of same type at design time.

Creating Control Arrays at Design Time:

There are three ways to create a control array at design time:

Assigning the same name in the Name property for more than one control Copying an existing control and then pasting it to the Form. Setting the Index property to a value that is not null


51/72

Adding a Control Array at Run Time

Control arrays can be created at run time using the statementsLoad object (Index %)Unload object (Index %)Where object is the name of the control to add or delete from the control array. Index % is the value of the index in thearray. The control array to be added must be an element of the existing array created at design time with an index valueof 0. When a new element of a control array is loaded, most of the property settings are copied from the lowest existingelement in the array.Following example illustrates the use of the control array. Open a Standard EXE project and save the Form as Calculator.frm and save the Project as Calculater.vbp Design the form as shown below


52/72

The following variables are declared inside the general declaration

The following code is entered in the cmd_Click( ) (Control Array) event procedure

The following code is entered in the cmdAC_Click ( ) event procedure


53/72

Examples

The following code is entered in the click events of the cmdPlus, cmdMinus, cmdMultiply, cmdDevide controls respectively.

The below code is entered in the cmdNeg_Click( ) procedure


54/72

To print the result on the text box, the following code is entered in the cmdEqual_Click ( ) event procedure.

Save and run the project. On clicking digits of user's choice and an operator button, the output appears.


55/72

Using a CheckBox control

The CheckBox control is similar to the option button, except that a list of choices can be made using check boxes whereyou cannot choose more than one selection using an OptionButton. By ticking the CheckBox the value is set to True. Thefollowing example illustrates the use of CheckBox Open a new Project and save the Form as CheckBox.frm and save the Project as CheckBox.vbp Design the Form as shown below


56/72

Following code is typed in the Click() events of the CheckBoxesPrivate Sub chkBold_Click()If chkBold.Value = 1 Then

txtDisplay.FontBold = TrueElse

txtDisplay.FontBold = FalseEnd IfEnd SubPrivate Sub chkItalic_Click()If chkItalic.Value = 1 Then

txtDisplay.FontItalic = TrueElse

txtDisplay.FontItalic = FalseEnd IfEnd SubPrivate Sub chkUnderline_Click()If chkUnderline.Value = 1 Then

txtDisplay.FontUnderline = TrueElse

txtDisplay.FontUnderline = FalseEnd IfEnd SubFollowing code is typed in the Click() events of the OptionButtonsPrivate Sub optBlue_Click()

txtDisplay.ForeColor = vbBlueEnd Sub

Private Sub optRed_Click()txtDisplay.ForeColor = vbRedEnd SubPrivate Sub optGreen_Click()

txtDisplay.ForeColor = vbGreenEnd SubTo terminate the program following code is typed in the Click() event of the Exit buttonPrivate Sub cmdExit_Click()

EndEnd SubRun the program by pressing F5. Check the program by clicking on OptionButtons and CheckBoxes.


57/72

Visual Basic Functions, InputBox Function

Syntax : memory_variable = InputBox (prompt[,title][,default])memory_variable is a variant data type but typically it is declared as string, which accept the message input by the users.The arguments are explained as follows:Prompt String expression displayed as the message in the dialog box. If prompt consists of more than one line, you canseparate the lines using the vbCrLf constantTitle String expression displayed in the title bar of the dialog box. If you omit the title, the application name is displayedin the title bardefault-text The default text that appears in the input field where users can use it as his intended input or he maychange to the message he wish to key in.x-position and y-position the position or the coordinate of the input box.Following example demonstrates the use of InputBox function Open a new project and save the Form as InputBox.frm and save the Project as InputBox.vbp Design the application as shown below.

Displays a prompt in a dialog box, waits for the user to input text or click a button, and returns a String containing thecontents of the text box.

Following is an expanded InputBox


58/72

Here I have entered "Hello World" in text field. As soon as you click OK the output is shown as shown below

Following code is entered in cmdOK_Click ( ) eventPrivate Sub cmdok_Click()Dim ans As Stringans = InputBox("Enter something to be displayed in the label", "Testing", 0)

If ans = "" Thenlbl2.Caption = "No message"Elselbl2.Caption = ansEnd IfEnd SubSave and run the application. As soon as you click the OK button you willget the following InputBox


59/72

MessageBox Function

Syntax :MsgBox ( Prompt [,icons+buttons ] [,title ] )memory_variable = MsgBox ( prompt [, icons+ buttons] [,title] )Prompt String expressions displayed as the message in the dialog box. If prompt consist of more than one line, you canseparate the lines using the vbrCrLf constant. Icons + Buttons Numeric expression that is the sum of values specifying the number and type of buttons and icon to

display. Title String expression displayed in the title bar of the dialog box. If you omit title, the application name is placed in thetitle bar.

Displays a message in a dialog box and wait for the user to click a button, and returns an integer indicating which buttonthe user clicked.


60/72

Following code is entered in the txtName_Change ( ) event Private Sub txtName_Change()If Len(txtName.Text) > 0 ThencmdAdd.Enabled = TrueEnd IfEnd SubFollowing code has to be entered in the cmdAdd_Click ( ) eventPrivate Sub cmdAdd_Click()answer = MsgBox("Do you want to add this name to the list box?", vbExclamation + vbYesNo,"Add Confirm")If answer = vbYes ThenlstName.AddItem txtName.Text

txtName.Text = ""txtName.SetFocuscmdAdd.Enabled = FalseEnd IfEnd SubFollowing code is entered in the cmdDelete_Click ( ) eventPrivate Sub cmdDelete_Click()Dim remove As Integerremove = lstName.ListIndex

If remove < 0 ThenMsgBox "No names is selected", vbInformation, "Error"Else


61/72

answer = MsgBox("Are you sure you want to delete " & vbCrLf & "the selected name?",_ vbCritical + vbYesNo, "Warning")

If answer = vbYes ThenIf remove >= 0 ThenlstName.RemoveItem removetxtName.SetFocusMsgBox "Selected name was deleted", vbInformation, "Delete Confirm"

End IfEnd IfEnd If

End SubFollowing code is entered in the cmdExit_Click ( ) eventPrivate Sub cmdExit_Click()answer = MsgBox("Do you want to quit?", vbExclamation + vbYesNo, "Confirm")If answer = vbYes ThenEndElseMsgBox "Action canceled", vbInformation, "Confirm"End IfEnd SubSave and run the application. You can notice the different type of message box types are used to perform an action


62/72

Error-Handling and DebuggingNo matter how hard we try, errors do creep into our programs. These errors can be grouped into three categories:

Syntax errors

Run-time errors

Logic errors

Syntax errors occur when you mistype a command or leave out an expected phrase or argument. Visual Basic detectsthese errors as they occur and even provides help in correcting them. You cannot run a Visual Basic program until allsyntax errors have been corrected

Run-time errors are usually beyond your program's control. Examples include: when a variable takes on an unexpected

value (divide by zero), when a drive door is left open, or when a file is not found. Visual Basic allows you to trap sucherrors and make attempts to correct them

Logic errors are the most difficult to find. With logic errors, the program will usually run, but will produce incorrect orunexpected results. The Visual Basic debugger is an aid in detecting logic errors

Some ways to minimize errors:

Design your application carefully. More design time means less debugging time

Use comments where applicable to help you remember what you were trying to do

Use consistent and meaningful naming conventions for your variables, objects, and procedures


63/72

Run-Time Error Trapping and Handling Run-time errors are trappable. That is, Visual Basic recognizes an error has occurred and enables you to trap it and take

corrective action. If an error occurs and is not trapped, your program will usually end in a rather unceremonious manner Error trapping is enabled with the On Error statement: On Error GoTo errlabel. Yes, this uses the dreaded GoTo

statement! Any time a run-time error occurs following this line, program control is transferred to the line labeled errlabel.Recall a labeled line is simply a line with the label followed by a colon (:)

The best way to explain how to use error trapping is to look at an outline of an example procedure with error trapping.Sub SubExample()

[Declare variables, ...]

On Error GoTo HandleErrors

[Procedure code]

Exit SubHandleErrors:

Error handling code]

End Sub

Once you have set up the variable declarations, constant definitions, and any other procedure preliminaries, the On Errorstatement is executed to enable error trapping. Your normal procedure code follows this statement. The error handlingcode goes at the end of the procedure, following the HandleErrors statement label. This is the code that is executed if anerror is encountered anywhere in the Sub procedure. Note you must exit (with Exit Sub) from the code before reaching theHandleErrors line to avoid inadvertent execution of the error handling code.


64/72

Since the error handling code is in the same procedure where an error occurs, all variables in that procedure areavailable for possible corrective action. If at some time in your procedure, you want to turn off error trapping, that is donewith the following statement: On Error GoTo 0

Once a run-time error occurs, we would like to know what the error is and attempt to fix it. This is done in the errorhandling code

Visual Basic offers help in identifying run-time errors. The Err object returns, in its Number property (Err.Number), thenumber associated with the current error condition. (The Err function has other useful properties that we wont cover here - consult on-line help for further information.) The Error() function takes this error number as its argument andreturns a string description of the error. Consult on-line help for Visual Basic run-time error numbers and theirdescriptions

Once an error has been trapped and some action taken, control must be returned to your application. That control isreturned via the Resume statement. There are three options:

Resume Lets you retry the operation that caused the error. That is, control is returned to the line where the erroroccurred. This could be dangerous in that, if the error has not been corrected (via code or by the user), an infinite loopbetween the error handler and the procedure code may result

Resume Next Program control is returned to the line immediately following the line where the error occurred

Resume label Program control is returned to the line labeled label

Be careful with the Resume statement. When executing the error handling portion of the code and the end of theprocedure is encountered before a Resume, an error occurs. Likewise, if a Resume is encountered outside of the errorhandling portion of the code, an error occurs


65/72

General Error Handling Procedure

Development of an adequate error handling procedure is application dependent. You need to know what type of errorsyou are looking for and what corrective actions must be taken if these errors are encountered. For example, if a 'divideby zero' is found, you need to decide whether to skip the operation or do something to reset the offending denominator

What we develop here is a generic framework for an error handling procedure. It simply informs the user that an errorhas occurred, provides a description of the error, and allows the user to Abort, Retry, or Ignore. This framework is a goodstarting point for designing custom error handling for your applications.

The generic code (begins with label HandleErrors) is: HandleErrors:Select Case MsgBox(Error(Err.Number), vbCritical + vbAbortRetryIgnore, "Error Number" + Str(Err.Number))

Case vbAbortResume ExitLine

Case vbRetryResume

Case vbIgnoreResume Next

End SelectExitLine:Exit Sub

Lets look at what goes on here. First, this routine is only executed when an error occurs. A message box is displayed,using the Visual Basic provided error description [Error(Err.Number)] as the message, uses a critical icon along with theAbort, Retry, and Ignore buttons, and uses the error number [Err.Number] as the title. This message box returns aresponse indicating which button was selected by the user.If Abort is selected, we simply exit the procedure. (This is done using a Resume to the line labeled ExitLine. Recall allerror trapping must be terminated with a Resume statement of some kind.)If Retry is selected, the offending program line is retried (in a real application, you or the user would have tochange something here to correct the condition causing the error).If Ignore is selected, program operation continues with the line following the error causing line.


66/72

To use this generic code in an existing procedure, you need to do three things:Copy and paste the error handling code into the end of your procedure.Place an Exit Sub line immediately preceding the HandleErrors labeled line.Place the line, On Error GoTo HandleErrors, at the beginning of your procedure.

For example, if your procedure is the SubExample seen earlier, the modified code will look like this:Sub SubExample().. [Declare variables, ...].

On Error GoTo HandleErrors.. [Procedure code].

Exit SubHandleErrors:Select Case MsgBox(Error(Err.Number), vbCritical + vbAbortRetryIgnore, "Error Number" + Str(Err.Number))Case vbAbort

Resume ExitLineCase vbRetry

ResumeCase vbIgnore

Resume Next

End SelectExitLine:Exit SubEnd Sub

Again, this is a very basic error-handling routine. You must determine its utility in your applications and makeany modifications necessary. Specifically, you need code to clear error conditions before using the Retry option.


67/72

One last thing. Once you've written an error handling routine, you need to test it to make sure it works properly. But,creating run-time errors is sometimes difficult and perhaps dangerous. Visual Basic comes to the rescue! The VisualBasic Err object has a method (Raise) associated with it that simulates the occurrence of a run-time error. To cause anerror with value Number, use:

Err.Raise Number We can use this function to completely test the operation of any error handler we write. Dont forget to remove the Raise

statement once testing is completed, though! And, to really get fancy, you can also use Raise to generate your ownapplication -defined errors. There are errors specific to your application that you want to trap

To clear an error condition (any error, not just ones generated with the Raise method), use the method Clear:Err.Clear

Example Simple Error Trapping1. Start a new project. Add a text box and a command button.2. Set the properties of the form and each control:Form1: BorderStyle - 1-Fixed SingleCaption - Error GeneratorName frmError

Command1:

Caption - Generate ErrorDefault - TrueName - cmdGenError

Text1: Name - txtErrorText - [Blank]

The form should look something like this:


68/72

3. Attach this code to the cmdGenError_Click event.

Private Sub cmdGenError_Click()On Error GoTo HandleErrors

Err.Raise Val(txtError.Text)Err.ClearExit Sub

HandleErrors:Select Case MsgBox(Error(Err.Number), vbCritical + vbAbortRetryIgnore, "Error Number" + Str(Err.Number))

Case vbAbortResume ExitLine

Case vbRetryResume

Case vbIgnoreResume Next

End Select

ExitLine:

Exit SubEnd Sub

In this code, we simply generate an error using the number input in the text box. The generic error handler then displays amessage box which you can respond to in one of three ways.


69/72

4. Save your application. Try it out using some of these typical error numbers (or use numbers found with on-line help).Notice how program control changes depending on which button is clicked.

Error Number Error Description

6 Overflow9 Subscript out of range11 Division by zero

13 Type mismatch16 Expression too complex20 Resume without error52 Bad file name or number53 File not found55 File already open61 Disk full70 Permission denied92 For loop not initialized


70/72

Debugging Visual Basic Programs

We now consider the search for, and elimination of, logic errors. These are errors that dont prevent an application fromrunning, but cause incorrect or unexpected results. Visual Basic provides an excellent set of debugging tools to aid inthis search. Debugging a code is an art, not a science. There are no prescribed processes that you can follow to

eliminate all logic errors in your program. The usual approach is to eliminate them as they are discovered. What well do here is present the debugging tools available in the Visual Basic environment (several of which appear asbuttons on the toolbar) and describe their use with an example. You, as the program designer, should select thedebugging approach and tools you feel most comfortable with.

The interface between your application and the debugging tools is via three different debug windows: the ImmediateWindow, the Locals Window, and the Watch Window. These windows can be accessed from the View menu (theImmediate Window can be accessed by pressing Ctrl+G). Or, they can be selected from the Debug Toolbar (accessedusing the Toolbars option under the View menu):

All debugging using the debug windows is done when your application is in break mode. You can enter break mode bysetting breakpoints, pressing Ctrl+Break, or the program will go into break mode if it encounters an untrapped error or aStop statement.Once in break mode, the debug windows and other tools can be used to:Determine values of variablesSet breakpointsSet watch variables and expressionsManually control the applicationDetermine which procedures have been calledChange the values of variables and properties


71/72

Example Debugging1. Unlike other examples, well do this one as a group. It will be used to demonstrate use of the debugging tools. 2. The example simply has a form with a single command button. The button is used to execute some code. We wont be

real careful about proper naming conventions and such in this example.

3. The code attached to this buttons Click event is a simple loop that evaluates a function at several values.

Private Sub Command1_Click()Dim X As Integer, Y As IntegerX = 0DoY = Fcn(X)X = X + 1Loop While X


72/72

Thank You
http://www.mentaljokes.com/images/funny_thank_you.gif

Excel VBAMacros v1.1

Documents

Transcript of Excel VBAMacros v1.1