Excel VBA Basics

1
Excel VBA Basics
2
Outline

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

3
Excel Basic Elements
Column - characters uniquely designate each
column.
Each Row is designated by integer number
Cell intersection of row and column. In the
example the ID of the cell B4
4
Excel Basic Data Types

• Label anything that is just a text
• My daughter is 3 years old!
• Constant any type of number
• 50, 3.5, 775, 10, -7.8
• Formula any math equation, always starts with
  an equal sign
• 53, 457-3

5
Excel Spreadsheet Example
6
Using Formulas in Excel

• Assignment
• Suppose, we have a class of four students and we
  need to calculate an average of the three
  assignments they had for each one of them. Given
  the following spreadsheet

7
Solution I
We have inserted absolute constants and invoked
AVERAGE excel function
8
After update of cell B2
Can you see anything wrong?
9
Solution II Using Cell References
10
Solution II Using Cell References
Now lets add a constant factor! A8
11
Now lets continue the calculations - using
copy

• Select cell E2 and click C
• Starting from E3 and till E5 drag the mouse and
  select the needed group of cells
• Press P
• That is all!

12
Solution II using Excel Graphical User Interface
2. Click this button
1. Select a cell to be updated
13
Solution II using Excel Graphical User Interface
3. In the opened dialogue select the needed
function
14
Solution II using Excel Graphical User Interface
5.Then with mouse select the needed cells
4. Go with mouse to the first argument (here
Number1)
See how we refer to a range!
6. Finally click OK
15
Finally,
16
Using If Expression in Excel

• If(A21,Yes,No)
• If it is true that the value in the cell A2 is
  greater then 1, then the value of current cell is
  Yes
• Otherwise (else), the value is No

17
Using Sin/Cos/Tan Expression in Excel

• Sin, Cos, Tan formats
• for degrees formula sin (angle pi()/180),
  the argument angle is in degrees
• for radians formula sin (angle), the argument
  angle is in radians

18
Formatting Cells
19
Formatting Cells cont.
20
Adding Graphs/Charts
21
Outline

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

22
Using Macros

• Now lets create a simple macro that formats a
  single cell
• Changes its background
• Changes its font

23
Recording a new Macro
24
Recording a new Macro cont.
25
Recording the new Macro cont.
Working with Excel while recording the macro
26
Finishing the Macro
27
Running the Macro
28
Running the Macro cont.
29
The Output!
30
Looking inside the VB code of our Macro
31
What does the row mean???
32
Guess what does this Macro do? What is different
now?
33
Outline

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

34
VB example Hello World!
35
Running the Example
36
The Output
37
Creating User From
38
Using Toolbox
This is a label
This is a button
Using the Toolbox select a GUI element and by
mouse-click place it on the frame
39
(No Transcript)
40
Adding Code to the Button
In the open Window fill-in the function

• The name of the method was automatically
  generated
• CommandButton1 is the name of the button object
• Click type of the event of the object
• The method will be invoked whenever user clicks
  on the CommandButton1
• button

41
Do you remember the code?
42
Running the code
43
The Output!!
44
Using Combo-Box
Select The Combo-Box
Add Source of range for the combo-box
45
Add Code to the Combo Box
46
The output after user makes combo box selection
47
Outline

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

48
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

49
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
50
Calling procedures vs. calling functions
If there are few sumNo functions, the full name
of the function is needed

• 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
51
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

52
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
53
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)

54
VBA Variables

• A variable is used to store temporary information
  within a Procedure, Module
• A variable name
• Must start with letter and cant contain spaces
  and special characters (such as , , \)
• Cant be any excel keyword (if, while)
• Cant have identical name to any existing class
  (Wroksheet, Workbook)

55
VBA Data Type

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

56
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

57
The Variables Advantage by Example
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!
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

• Sub NoVariable()
• Range("A1").Value _ Range("B2").Value
• Range("A2").Value _
• Range("B2").Value 2
• Range("A3").Value _ Range("B2").Value 4
• Range("B2").Value _ Range("B2").Value 5
• End Sub

58
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!!!)

59
Variant Data Type

• In VB you dont 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!

60
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"

61
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

62
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

63
VBA Variables Scope Lifecycle cont.

• Sub scopeExample()
• Dim x as Integer
• x 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
64
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

65
VBA Entities by Example
A Current Workbook
A Cell
A Range E2E5
A current Worksheet
66
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

67
Referencing the Objects - Examples
This will take the whole square between the two
cells

• Sub Test1()
• Worksheets("Sheet1").Range("A10", "B12")
  "Hello
• Worksheets(1).Range("A13,B14") "World!"
• End Sub

Two equal ways to refer Sheet1
The range of two cells
68
The Output
Which Workbook was Used?
69
What does this procedure do?

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

70
The Output!
How many times the user will click on the button?
71
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)

72
Referencing Cells with Offset
See how we calculate cell 12 In the given range!

• Range(B1F5).Cells(12) XYZ

73
Referencing Cells with Offset cont.

• ActiveCell.Offset(4, 5) 1

This is the currently active cell
The assignment result
74
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(AB).AutoFit
• Worksheets(1).Range(A1A10).Sort_
• Workbooks.Open fileNameHello.xls,
  passwordkukuriku

75
Defining and Assigning a new Object of type Range

• Dim myRange as Range
• Set myRange Range(A1A10)

76
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

77
Declaring object of type Array
The array declaration. The size must be defined
here!

• Dim LoanBooks(3)
• LoanBooks(1) Winnie The Pooh
• LoanBooks(2) Adventures of Huckleberry Finn
• LoanBook(3) Frankenstein

78
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?
79
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

80
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

81
VBA Control Structures - If

• If Age 18 Then Status "Adult" End If
• If Age 18
• Then
• Status Adult
• Vote Yes
• Else
• Status Child
• Vote No
• End If

82
VBA Control Structures - If

• If Age 18
• Then MsgBox "You can vote" ElseIf Age 22 and
  Age
• Then MsgBox You can drive
• End If

83
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

84
VBA Control Structures Loops

• For i 10 to 1 Step -2        Cells(i, 1)
  AB
• Next i
• i 1
• Do While i
• Loop
• i 1    Do         Cells(i, 1) i        i 
  i 1    Loop While i

85
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

86
(No Transcript)
87
References

• http//www.usd.edu/trio/tut/excel/13.html
• Tutorial on Excel
• http//www.anthony-vba.kefra.com/index_011.htm
• Great place to learn VBA basics!
• http//msdn.microsoft.com/en-us/library/aa224506(o
  ffice.11).aspx
• MSDN online help, a good place to learn about
  Excel classes (their data and functions set)

88
Assignment 1

• Create Excel file with grades
• The data
• There are 4 students with ids names
• There are 4 assignments and two exams
• Each student has grades for each one of the
  assignments and exams, the grades are from 20 to
  100
• Some cell in the worksheet keeps factor of 10
• Create VBA module that will calculate final grade
  for every student and places it in the new column
  allocated to keep the final grade
• 20 for the assignments average and 80 - for the
  maximal grade of the two exams plus factor
• If the grade becomes higher than 100 it should
  be 100
• Create VBA that accepts a column name from user
  and sorts the whole file according to the given
  column
• Create VBA that adds additional column with
  grades translated to A, B, C, D, E, F.
• Next week in class I will collect your solutions
• You should submit Excel file, and three VBA
  modules (only hardcopy)