Excel VBA Basics

Document Sample
Excel VBA Basics Powered By Docstoc
					Excel VBA Basics
Outline
   Excel – Basic Elements
   Using Macros
   Excel VBA Basics
   Excel VBA Advanced
       Excel – Basic Elements
                           Column - characters
   Each Row is             uniquely designate
  designated by            each column.
 integer number




Cell – intersection of
row and column.
In the example the
ID of the cell: B4
    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 “=”
       =5+3, =45*7-3
Excel Spreadsheet Example
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:
Solution I

             We have inserted
             absolute constants
             and invoked
             AVERAGE excel
             function
After update of cell “B2”
     Can you see
   anything wrong?
Solution II – Using Cell
References
Solution II – Using Cell
References

                       Now let‟s add
                       a constant
                       factor! “$A$8”
      Now let‟s continue the
      calculations… - using “copy”

   Select cell E2 and click
    <Ctrl> + C
   Starting from E3 and till
    E5 drag the mouse and
    select the needed group
    of cells
   Press <Ctrl> + P
   That is all!
Solution II – using Excel
Graphical User Interface


               2. Click this
                  button
                               1. Select a
                               cell to be
                               updated
Solution II – using Excel
Graphical User Interface
                     3. In the opened
                     dialogue select the
                     needed function
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”
Finally,
    Using “If” Expression in Excel

   =If(A2>1,”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”
        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
Formatting Cells
Formatting Cells – cont.
Adding Graphs/Charts
Outline
   Excel – Basic Elements
   Using Macros
   Excel VBA Basics
   Excel VBA Advanced
Using Macros
   Now let‟s create a simple macro that
    formats a single cell
       Changes its background
       Changes its font
Recording a new Macro
Recording a new Macro –
cont.
Recording the new Macro –
cont.
                     Working with
                     Excel while
                     recording the
                     macro
Finishing the Macro
Running the Macro
Running the Macro – cont.
The Output!
Looking inside the VB code of
our Macro
What does the row
    mean???
Guess what does this Macro
do? What is different now?
Outline
   Excel – Basic Elements
   Using Macros
   Excel VBA Basics
   Excel VBA Advanced
VB example – “Hello World!”
Running the Example
The Output
Creating User From
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
 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
Do you
remember the
code?
Running the code
The Output!!
Using Combo-Box



                         Select
                       The Combo-
       Add Source of       Box
       range for the
        combo-box
Add Code to the Combo Box
 The output
  after user
makes combo
     box
  selection
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
                           The procedure places
                             the current time
Sub ShowTime)(                 inside cell C1
    Range("C1") = Now)(
End Sub
                          The function returns sum
Function sumNo(x, y)        of two input numbers,
     sumNo = x + y         whose values are in the
                           parameter variables x &
End Function                          y
Calling procedures vs. calling
                             If there are few
functions                    sumNo functions,
                                    the full name of
                                    the function is
                                    needed
  Sub z(a)               Sub ShowSum()
       MsgBox a
                            MsgBox _
  End Sub
                           Module1.sumNo(3,5)
  Sub x()                End Sub
       Call z("ABC")
  End Sub
                         Function sumNo(x, y)
                              sumNo = x + y
  Sub y()
       z "ABC“           End Function
  End Sub
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()               public Sub TestPassing2()
     Dim y As Integer                  Dim y As Integer
     y = 50                            y = 50
     AddNo1 y                          AddNo3 y
     MsgBox y
     AddNo2 y                          MsgBox y
     MsgBox y                    End Sub
End Sub
                                 private Sub AddNo3(ByVal x _
Sub AddNo1(ByRef x As Integer)      As Integer)
     x = x + 10                        x = x + 10
End Sub                          End Sub

Sub AddNo2(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       In VB the end of
                     statement is in the end of
                     line.
                     To write the same

Sub NoVariable()
                     statement in few lines
                     use “_” at the end of line!   Sub WithVariable() Dim _
Range("A1").Value = _                                iValue as Integer
  Range("B2").Value                                  iValue = _
Range("A2").Value = _                                     Range("B2").Value
  Range("B2").Value * 2                              Range("A1").Value = _
Range("A3").Value = _                                     iValue
  Range("B2").Value * 4                              Range("A2").Value = _
Range("B2").Value = _                                     iValue * 2
  Range("B2").Value * 5                              Range("A3").Value = _
End Sub                                                   iValue * 4
                                                     Range("B2").Value = _
                                                          iValue * 5
                                                   End Sub
        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 Integer        Procedure level variables

         x=5
    End Sub
   Dim y as Integer            Module level variables

    „all the module procedures are here…
   Public z as Integer          Project level variables

    „all the module procedures are here…
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
             Workbook




A Cell

                         A Range
                          E2:E5

          A current
          Worksheet
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              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       The range of two
         refer Sheet1                cells
The Output   Which Workbook was
                   Used?
  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
                                   See how we
                                   calculate cell 12
                                   In the given
Range(“B1:F5”).Cells(12) = “XYZ”   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 Range
Set 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
                    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”
    Multidimensional Arrays
Dim WeekTasks(7,2)

WeekTasks(1,1) = “To buy milk”
WeekTasks(7,1) = “To dance”             What will the
…                                        code print?

MsgBox WeekTasks(1,1) & ” ” &    WeekTasks(1,2) _
 & vbCrLf & WeekTasks(2,1)…
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”
        Else
             Status = “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=1
     Do 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 i
  End Sub
        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(office.11).aspx
       MSDN online help, a good place to learn about Excel
        classes (their data and functions set)
         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)