In The Name Of Allah Introduction on VBA

In The Name Of Allah Introduction on VBA Lab 05 ins. Tahani Al_dweesh

Lab Objectives �Introduction �Calculation with VBA �Storing and Retrieving Variables in a Worksheet �Using Excel Functions �Checking for conditions �Loops �Object ins. Tahani Al_dweesh

Introduction Visual Basic for Applications Used to customize many Microsoft applications (Excel, Word, Access, Power Point) host applications. ins. Tahani Al_dweesh

Introduction : Cont �Visual Basic for Applications, Excel’s: Powerful built-in programming language , permits you to easily incorporate user-written functions into a spreadsheet. ins. Tahani Al_dweesh

Programming Languages �Three groups: �Low-level languages are used to manipulate the computer at a basic level: assembly language. �High-level languages are used to create stand-alone applications: C, C++, Fortran, Java, Visual Basic. �Application level languages are used to manipulate an application program: VBA. ins. Tahani Al_dweesh

Visual Basic Editor To open VBA Click on Alt+F 11 ins. Tahani Al_dweesh

Calculations with VBA Creating a simple function ins. Tahani Al_dweesh

�The Steps: 1 - Open a blank workbook using File | New. 2 - Select Developer | Visual Basic Editor (Alt+F 11) 3 - Within the VBA editor, select Insert | Module from the menu. ins. Tahani Al_dweesh

4 - Within the newly-created macro module, type the function statement. Function function_name (arg 1, arg 2, …) Function structure End Function ins. Tahani Al_dweesh

Example Function addtwo(x, y) addtwo = x + y End Function 5 - Return to Excel. In cell A 1 enter. = addtwo(3 ; 5) 6 - Hit <Enter> , to see the result. ins. Tahani Al_dweesh

USE Functions �The function you typed it appears in the function wizard, just as if it were a builtin Excel function. ins. Tahani Al_dweesh

cont To see this: üin the “function category” üscroll to and highlight “User Defined” üNote that “Addtwo” appears. ins. Tahani Al_dweesh

Subroutine �A subroutine (called a “sub” by VBA) �subroutine, which is a set of statements which execute when invoked. Subroutine_name() statement End Sub ins. Tahani Al_dweesh

Subroutine �A Simple Example of a Subroutine: Sub displaybox() Msg. Box(”Welcome”) End Sub �run the subroutine by using Developer | Macro Or (Alt+F 8) ins. Tahani Al_dweesh

-then double-clicking on “displaybox” out of the list. -Or Running. ins. Tahani Al_dweesh

Create Button to Invoke a Subroutine �Creating a Button to Invoke a Subroutine: - creating a button in the spreadsheet is a way to create a shortcut to the subroutine. ins. Tahani Al_dweesh

Create Button to Invoke a Subroutine �Move the mouse to the spreadsheet , hold down the left mouse button, and drag to create a rectangle. �dialog box will pop up and one of the choices will be “displaybox”. �Double click on it. ins. Tahani Al_dweesh

Create Button to Invoke a Subroutine When you are click on the button” show Message” , the message will be appears ins. Tahani Al_dweesh

Differences Between Functions and Subroutines: Invoked by button Insert into cell Change the display ins. Tahani Al_dweesh Subroutines Functions yes no no yes no

Object Model Includes such objects as: �workbooks, worksheets, cells, rows, columns, ranges, charts and pivot tables ins. Tahani Al_dweesh

Objects An object: -is something that is identified by its properties (what it is) and methods (what it can do). For example: -Range is an Excel VBA object and one of its properties is value. -We connect an object to its property by a period (a dot or full stop). Range("A 1"). Value = 10 ins. Tahani Al_dweesh

Using Ranges �A Range object represents a cell, a row, a column, a rectangular block of cells, or the union of many rectangular blocks (a noncontiguous range). ins. Tahani Al_dweesh

Storing and Retrieving Variables in a Worksheet There are three ways to read and write to cells: �“Range(“ “)” give cells a name, �“Range(“ ”). Activate” lets you easily get at cells by using traditional cell addresses (e. g. “A 1”). �“Cell( , )” lets you address cells using a row and column numbering scheme. ins. Tahani Al_dweesh

1. “Range” give cells a name -Using a named range to read and write numbers from the spreadsheet: 1 - created a named range : - to create a named range : ins. Tahani Al_dweesh

1. “Range” give cells a name 2 - in the Module Sub Read. Variable() x = Range(”test ”). Value Msg. Box (Str(x)) End Sub ins. Tahani Al_dweesh

1. “Range” give cells a name 3 -Enter the value “ 4” in the cell you just named “test”. 4 - run the subroutine by using : Developer |Macro Or (Alt+F 8) ins. Tahani Al_dweesh

The number on cell ins. Tahani Al_dweesh

2. traditional cell addresses -Reading from Cells Which are not Named: 1 -first have to “activate” the worksheet containing the cell. Sub Read. Variable 2() Worksheets(”Sheet 2”). Activate X=Range(”A 1”). Value Msg. Box (Str(x)) End Sub �notice that when you have finished calling this function, the cursor has moved to cell A 1 in Sheet 2 ins. Tahani Al_dweesh

2. traditional cell addresses -Writing to Cells Which are not Named: get number of cell (A 1) then write this number in cell (B 2) Sub Write. Variable 2() ‘Make ”Sheet 2” the active sheet Worksheets(”Sheet 2”). Activate ‘Make ”A 1” the active cell X=Range(”A 1”). Value ‘Make ”B 1” the active cell Range(”B 1”). Value =X End Sub ins. Tahani Al_dweesh

Example of unnamed range: ‘places text "AB" in range A 1: B 5, on Sheet 1 Worksheets("Sheet 1"). Range("A 1: B 5") = "AB" Another Way ‘places text "AB" in range A 1: B 5, on Sheet 1 Worksheets(“Sheet 1"). Activate Range ("A 1: B 5") = “AB” ins. Tahani Al_dweesh

ins. Tahani Al_dweesh

3. using a row and column numbering scheme -Using the “Cells” Function to Read and Write to Cells: put number (1) in cell (A 3) Then read this number and write it in cell (C 3) 1 -first have to “activate” the worksheet containing the cell. Sub Cells. Example() ’ Make ”Sheet 2” the active sheet Worksheets(”Sheet 2”). Activate ’ The first entry is the row, the second is the column ’ Write the number 1 into cell A 3 Cells (3, 1) = 1 ’ Copy the number from cell A 3 into cell C 3 Cells (3, 3) = Cells (3, 1) End Sub ins. Tahani Al_dweesh

Checking for Conditions • If. . . Then. . . Else -To effectively control the VB program flow, we shall use If. . . Then. . . Else statement together with the conditional operators and logical operators. -The general format for the if. . . then. . . else statement is ins. Tahani Al_dweesh

Example Function check( x ) If x > 0 Then Msg. Box (“Positive Number") Else Msg. Box (“Negative Number") End If End Function ins. Tahani Al_dweesh

Use the Function ins. Tahani Al_dweesh

Select Case: Select Case < Expression to test> Case Do something Case Else Do something else End Select Sub select. Test() Select Case Range("A 1"). Value Case 100 To 400 Range("B 1"). Value = "it's between" Case Else Range("B 1"). Value = "it's not between" End Select End Sub ins. Tahani Al_dweesh

Use the Case Select ins. Tahani Al_dweesh

Loops �A simple for loop : For. . Next Loop The format is: For counter=start to end (Step increment) One or more VB statements Next ins. Tahani Al_dweesh

- counter : A numeric variable used as the loop counter. - start and end : The initial and final values of the counter. - increment : The amount the counter is changed each time through the loop. #Notice that increment can be negative ins. Tahani Al_dweesh

Example Sub for_loop() Dim Sum As Integer Dim I As Integer Sum= 0 ‘ i , 0, 1, 2 For i = 0 To 2 Sum = Sum + i Next i Msg. Box (Str(Sum)) End Sub ins. Tahani Al_dweesh Result

Loops �Do While (condition true) Loop Runs while condition is true. �Do. . . Loop While (condition true) Runs at least one time, and while the condition is true subsequently. ins. Tahani Al_dweesh

The formats are: a) Do While condition Block of one or more VB statements Loop Count= 0 Do while count<100 Count=count+1 Loop ins. Tahani Al_dweesh

b) Do Block of one or more VB statements Loop While condition Do Count=count+1 Loop while count<100 ins. Tahani Al_dweesh

Any Question? ins. Tahani Al_dweesh

ﻭﻟﻜﻦ ﻻ ﺃﺤﺪ ﻳﻔﻜﺮ ﻓﻲ , ﺍﻟﺠﻤﻴﻊ ﻳﻔﻜﺮ ﻓﻲ ﺗﻐﻴﻴﺮ ﺍﻟﻌﺎﻟﻢ ﺗﻐﻴﻴﺮ ﻧﻔﺴﻪ Everyone thinks of changing the world, but no one thinks of changing himself ins. Tahani Al_dweesh