excel – basic elements using macros excel vba basics excel vba advanced

OUTLINE

Excel – Basic Elements Using Macros Excel VBA Basics Excel VBA Advanced

MODULES & PROCEDURES

Module – collection of logically related procedures grouped together

Procedure – a group of ordered statements enclosed by Sub and End Sub

Function – the same as a procedure, but also returns some value and is closed between Function and End Function key words

PROCEDURE & FUNCTION EXAMPLES

Sub ShowTime()    Range("C1") = Now()

End Sub

Function sumNo(x, y)     sumNo = x + y

End Function

The procedure places the current time inside cell C1

The function returns sum of two input numbers, whose values are in the

parameter variables x & y

CALLING PROCEDURES VS. CALLING FUNCTIONS

Sub z(a)    MsgBox a

End Sub

Sub x()    Call z("ABC")

End Sub

Sub y()    z "ABC“

End Sub

Sub ShowSum() MsgBox _ Module1.sumNo(3,5)

End Sub

Function sumNo(x, y)     sumNo = x + y

End Function

If there are few sumNo functions, the full name of the function is needed

PASSING ARGUMENTS BY VALUE OR BY REFERENCE

Passing arguments by reference – Is the VBA default Means, if any changes happened to the argument

variables, they will be preserved after the function/procedure finishes

Passing arguments by value – Is possible in VBA (by explicit definition) Means, the pre-calling state of the argument

variables will be preserved after the procedure/function finishes

ARGUMENTS BY REF/BY VAL. EXAMPLES

Sub TestPassing1()    Dim y As Integer    y = 50    AddNo1 y    MsgBox y AddNo2 y MsgBox y

End Sub

Sub AddNo1(ByRef x As Integer)    x = x + 10

End Sub

Sub AddNo2(x As Integer)x = x + 10

End Sub

public Sub TestPassing2()    Dim y As Integer    y = 50    AddNo3 y    MsgBox y

End Sub

private Sub AddNo3(ByVal x _ As Integer)    x = x + 10

End Sub

FUNCTIONS/PROCEDURE SCOPE

Use public to allow any module to call the function/procedure

Use private to make limited access to the function/procedure (only from the owning module)

VBA VARIABLES

A variable is used to store temporary information within a Procedure, Module…

A variable name Must start with letter and can’t contain spaces and

special characters (such as “&”, “%”, “\”) Can’t be any excel keyword (“if”, “while”…) Can’t have identical name to any existing class

(“Wroksheet”, “Workbook”…)

VBA DATA TYPE

Byte – positive integer numbers (0:255) Integer – integers (-32,768 : 32,767) Long – 4-byte integer Currency – for fixed-point calculations Single – 2-byte floating-point numbers

VBA DATA TYPE

Double – double-precision floating-point numbers

Date – used to store dates and times as real numbers.

String – contains a sequence of characters

THE VARIABLES ADVANTAGE BY EXAMPLE

Sub NoVariable()Range("A1").Value = _

Range("B2").Value Range("A2").Value = _

Range("B2").Value * 2Range("A3").Value = _

Range("B2").Value * 4 Range("B2").Value = _

Range("B2").Value * 5 End Sub

Sub WithVariable() Dim _ iValue as Integer iValue = _ Range("B2").Value Range("A1").Value = _

iValue Range("A2").Value = _

iValue * 2 Range("A3").Value = _

iValue * 4 Range("B2").Value = _

iValue * 5 End Sub

In VB the end of statement is in the end of line.To write the same statement in few lines use “_” at the end of line!

USING VARIABLES

Declaring Variables Format: Dim varibaleName AS dataType Examples:

Dim myText As String Dim myNum As Integer Dim myObj As Range

The default value of any numeric variable is zero any string variable – “” (empty string) an Object variable – is nothing (still the declaration will

store space for the object!!!)

VARIANT “DATA TYPE”

In VB you don’t have to declare variable before its usage Then, VB will by itself declare such variable as “Variant”

You can also declare variable as “Variant” Dim myVar as Variant

Variant – means that the variable may contain any data type The price is very high!!! – any time VB access

such variable, it will spend time on “deciding” what is its “current” type!

VARIABLES ASSIGNMENT

To assign a value to a Numeric or String type Variable, you simply use your Variable name, followed by the equals sign (=) and then the String or Numeric

To assign an Object to an Object type variable you must use the key word "Set"

VARIABLES ASSIGNMENT – CONT.

Sub ParseValue() Dim sWord as String Dim iNumber as Integer Dim rCell as Range Set rCell = Range("A1") sWord = Range("A1").Text iNumber = Range("A1").Value

End Sub

VBA VARIABLES SCOPE & LIFECYCLE

The scope & lifecycle of a variable defines the code where the variable can be accessed and time when the stored data is kept inside the variable

Procedure-Level Variables defined inside procedures Can be accessed only inside the procedure and keep their data

until the End statement of the procedure Module-Level

Defined in the top of a Module Any procedure inside the Module can access the variable The variable retains the values unless the Workbook closes

Project-Level, Workbook Level, or Public Module-Level Defined as “Public” in the top of a Module Can be accesses by any procedure in any module The variable retains the values unless the Workbook closes

VBA VARIABLES SCOPE & LIFECYCLE – CONT.

Sub scopeExample()Dim x as Integerx = 5

End Sub Dim y as Integer‘all the module procedures are here…

Public z as Integer‘all the module procedures are here…

Procedure level variables

Module level variables

Project level variables

BASIC EXCEL CLASSES

Workbook: the class represents an Excel file

Worksheet: represents a single worksheet

Sheet: represents a single worksheet or chartsheet

Cell: represents a single cell

VBA ENTITIES BY EXAMPLE

A current Worksheet

A Range C4:D7A Cell

A CurrentWorkbook

EXCEL CONTAINERS

Workbooks: a collection of objects of class “Workbook”

Worksheets: a collection of objects of class “Worksheet”

Sheets: a collection of Sheet objects Range: a range of objects of class Cell

REFERENCING THE OBJECTS - EXAMPLES

Sub Test1() Worksheets("Sheet1").Range("A10", "B12")

= "Hello“ Worksheets(1).Range("A13,B14") = "World!"End Sub

This will take the whole

square between the two cells

The range of two cells

Two equal ways to refer Sheet1

THE OUTPUTWhich Workbook

wasUsed?

WHAT DOES THIS PROCEDURE DO?

Sub ShowWorkSheets()    Dim mySheet As Worksheet        For Each mySheet In Worksheets        MsgBox mySheet.Name    Next mySheet

End Sub

THE OUTPUT!How many times

the user will click on the

button?

REFERENCING CELLS

Cells indexing format: Cells(row, column), where both row and column

are given as integers (starting from 1) Cells(index) – see the next slide

Following expressions are equivalent and refer to the cell A1 in the currently active sheet: ActiveSheet.Range.Cells(1,1) Range.Cells(1,1) Cells(1,1)

REFERENCING CELLS WITH OFFSET

Range(“B1:F5”).Cells(12) = “XYZ”

See how we calculate cell 12In the given range!

REFERENCING CELLS WITH OFFSET – CONT.

ActiveCell.Offset(4, 5) = 1

This is the currently active cell

The assignment

result

FEW METHODS/PROPERTIES OF EXCEL CLASSES

Workbooks.Close – closes the active workbook

Workbooks.Count – returns the number of currently open workbooks

Range(“A1”) is the same as Range(“A1”).Value Worksheets(1).Column(“A:B”).AutoFit Worksheets(1).Range(“A1:A10”).Sort_ Workbooks.Open fileName:=“Hello.xls”,

password:=“kukuriku”

DEFINING AND ASSIGNING A NEW OBJECT OF TYPE RANGE

Dim myRange as RangeSet myRange = Range(“A1:A10”)

VBA ARRAYS

Suppose, we want to keep a collection of all the books that we loan,

Or we want to keep lists of tasks for all the days of the week The naïve solution is to keep a lot of

variables Another solution is to create array keeping

the whole collection together

DECLARING OBJECT OF TYPE ARRAY

Dim LoanBooks(3)

LoanBooks(1) = “Winnie The Pooh”LoanBooks(2) = “Adventures of Huckleberry

Finn”LoanBook(3) = “Frankenstein”

The array declaration.The size must be

defined here!

MULTIDIMENSIONAL ARRAYS

Dim WeekTasks(7,2)

WeekTasks(1,1) = “To buy milk”WeekTasks(7,1) = “To dance”…

MsgBox WeekTasks(1,1) & ” ” & WeekTasks(1,2) _ & vbCrLf & WeekTasks(2,1)…

What will the code print?

RESIZING THE ARRAYS

There are two ways to resize the existing array: ReDim LoanBooks(7) – will erase the old

values ReDim Preserve LoanBooks(7) – will

preserve values in indexes 1-3

UPPER & LOWER INDEX BOUNDS OF AN ARRAY

Dim A(1 To 100, 0 To 3, -3 To 4) UBound(A, 1) – will return “100” UBound(A, 2) – will return “3” UBound(A, 3) – will return “4” LBound(A, 1) – will return “1” LBound(A, 2) – will return “0” LBound(A, 3) – will return “-3”

Write code calculating the size of each one of the sub-arrays

VBA CONTROL STRUCTURES - IF

If Age >= 18 Then Status = "Adult" End If If Age >=18

Then Status = “Adult”Vote = “Yes”

ElseStatus = “Child”Vote = “No”

End If

VBA CONTROL STRUCTURES - IF

If Age >= 18 Then MsgBox "You can vote"ElseIf Age >=22 and Age < 62

Then MsgBox “You can drive”End If

VBA CONTROL STRUCTURES – SELECT 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 = “E"End Select

VBA CONTROL STRUCTURES – LOOPS

For i = 10 to 1 Step -2        Cells(i, 1) = “AB”

Next i

i = 1Do While i =< 10

 Cells(i, 1) = i i = i + 1

Loop

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

TEST YOURSELF! WHAT DOES THE PROCEDURE DO?

Sub CellsExample()   For i = 1 To 5        For j = 1 To 5            Cells(i, j) = "Row " & i & "   Col " & j        Next j   Next iEnd Sub