Excel and VBA

Document Sample
Excel and VBA Powered By Docstoc
					Introduction to Excel VBA

             UNC Charlotte
            CPE/PDH Series
           December 17, 2009
Lesson 1 Objectives
 Record a Macro
 Modules
 Examine the VBE window
    – Project Explorer, Object Browser,
      Properties
   Sub Procedures
    – Write a Sub procedure
   Examine statement code
    – Define objects properties
    – Edit code
Personal Macro Workbook
 A hidden workbook that is always open
 Stores global macros
  – Save in the Personal Macro Workbook
 Use the View tab to unhide the Personal
  Workbook
   Visual Basic Editor


Project VBA Project
                         Module
 Sub Procedure in
 Module 1




    Properties
Record a Macro
Display the Developer Tab

Click on the Macro tool

Proceed through steps to complete the macro
Stop Recording Tool
Click on the Stop Recording Tool
    Edit a Macro
 Open the Visual Basic Editor
 Make the changes needed
 Save and Close the VBE
Absolute Cell References
Relative Cell References
Debug
 Open the VBE and Set a Breakpoint
 Start running the procedure
 Step through the code
 Open the VBE and Set a Breakpoint
 Start running the procedure
  Debug continued
 Step through the code

    Bellerephon Investment Advisors
    Stock Prices


        Symbol:    Open:    High:   Low:    Close:    Net Chg:      Pct Chg:     Port Alloc:        Pct Prt Chg:
        BPAH       $ 12.53 $ 12.99 $ 9.23 $     10.35 $    (2.18)         -17%            10%                  -2%
        CHJD       $ 41.38 $ 46.45 $ 42.23 $    45.63 $     4.25          10%                  4%                  0%
        CPDH       $ 21.34 $ 25.33 $ 18.34 $    18.60 $    (2.74)         -13%                 8%              -1%
        EOWH       $ 24.32 $ 25.67 $ 21.35 $    21.45 $    (2.87)         -12%                 7%              -1%
        OBAH       $ 14.32 $ 16.43 $ 13.55 $    13.63 $    (0.69)          -5%                 5%                  0%
        PWHG       $ 23.45 $ 30.34 $ 28.34 $    29.53 $     6.08          26%                  5%                  1%
        QLHZ       $ 25.65 $ 29.45 $ 26.42 $    26.95 $     1.30           5%                  5%                  0%
        QUJG       $ 20.34 $ 30.21 $ 26.42 $    27.43 $     7.09          35%             10%                      3%
        SDJB       $ 33.14 $ 36.43 $ 32.65 $    32.65 $    (0.49)          -1%                 5%                  0%
        SJCK       $ 31.54 $ 35.65 $ 24.56 $    26.43 $    (5.11)         -16%                 4%              -1%
        SJGL       $ 19.34 $ 25.46 $ 23.64 $    24.56 $     5.22          27%                  8%                  2%
        SLHW       $ 49.03 $ 53.13 $ 42.53 $    42.53 $    (6.50)         -13%                 3%                  0%
        WKCH       $   1.56 $   2.34 $ 1.94 $   2.13 $      0.57          37%             10%                      4%
        WQOC       $ 13.56 $ 15.43 $ 11.23 $    14.56 $     1.00           7%                  5%                  0%
        ZXLQ       $   4.05 $   6.43 $ 3.56 $   5.25 $      1.20          30%                  6%                  2%
    Debug continued

   Procedure InsertRowsCols
    – Correction Rows(“1:4”)
   Procedure InsertTxt
    – Corrections A1
   Procedure FmtTxt
    – Correction Columns(“H:J”) … Percent
    – Move EntireColumn.AutoFit above End Sub
   Adding a Tool to the Quick
   Access Toolbar
Click on the Customize button on the Quick Access Toolbar
Select Macros
   Adding a Tool to the Quick
   Access Toolbar continued
Click on Create Report
Click Add
   Adding a Tool to the Quick
   Access Toolbar continued
Click on Create Report
Click Add and OK
    Customize the New Tool
   Click on the Customize menu
   Select the Macro
   Click on the Modify button
   Change the Tool image
   Change the Tool name
Lesson 2 Objectives
   Objects
    – Range
    – Selection
    – Active Sheet
   Methods
    – Select
   Properties
    – CurrentRegion
    – Sort
    – Name
Objects, Methods, & Properties
   Objects have properties that govern their
    appearance and behavior
    – Name of worksheet
   Methods govern the action the objects
    perform
    – Protect a worksheet
   Properties govern how the objects
    respond to a user action
    – Procedure is executed when the event occurs
Variables
A variable is a name given to hold
 data.
When a procedure uses a variable the
 current ‘value’ is used
Option Explicit and Dim Statements

  Using Option Explicit in a module
   helps maintain the integrity of each
   variable.

  Dim statements are a way to define
   variables in a procedure

       Ex. Dim NumberOfEmployees As Integer
Loops

  For Next Loops
  If Then
  If Then Else
  Case
  Do Loops
     – Do While
     – Do Until
For Next




     Sub ForNext Loop ()
     For Counter = 1 to 10 [Step 1]
            Code is written here
     Next Counter – continues to collect iterations as a count
     End Sub
If…Then Syntax
If…Then…Else Syntax
Use a Do Until…Loop




   Sub DoUntilLoop ()
   Do Until ActiveCell = “”
            Code is written here

   Loop
   End Sub
Case Is… Syntax
Find first blank row at the bottom
of a list
 Open the file Our Company
 Create a Sub procedure named
  Database
    Sub GoToBottom()

    ‘ Goes to the first blank row in an Excel list

      ActiveSheet.Cells(Rows.Count,1).End(xlUp).Offset(1).Select

    End Sub
Offset and Count Properties
 ActiveCell.Offset(3,2)
  (row,column)
 Rows.Count
  The number of active rows in a worksheet

 Cells(Rows.Count,1)
  1st column of the last row of the list

 End(xlUp.Offset(1)
  The cell below .. The first blank row in a list
Lesson 3 Objectives

 Dialog box
 Input box
 Message box
 Command buttons
Dialog box and User Input
SortBy Procedure
DateTime Procedure




RepSort Procedure
Lesson 4 Objectives

 Insert, Copy and Delete Worksheets
 Rename worksheets
 Change worksheet order
 Print worksheets
    More Methods
   Add Method
    – Worksheets.Add
   Delete Method
    – Worksheets(2).Delete
   Copy Method
    – Worksheets(2).Copy After:=Worksheets(2)
Name Property
    More Methods
   PrintPreview
    – Worksheets(2).PrintPreview
   PrintOut
    – Worksheets(2).Printout
    – Worksheets(“SE Sales”).PrintOut
Unit 5 Objectives
   Create a User Defined Function
    (UDF)
    – Declare a variable
    – Perform a calculation
User Defined Functions
   Begin with the keyword Function
    – The first line of code includes the function
      name as well as any arguments
    – Arguments are enclosed in parentheses and
      separated by commas
    – The code between the first and last lines
      perform the mathematical calculation
    – The last line of code must return the calculated
      answer to the name of the function
   End with the keywords End Function
Function Commission(Sales)
   Function Commission(Sales)
   If Sales >= 50000 Then
   pct = 0.15
   ElseIf Sales >= 40000 Then
   pct = 0.12
   ElseIf Sales >= 25000 Then
   pct = 0.1
   ElseIf Sales >= 10000 Then
   pct = 0.08
   Else
   pct = 0.05
   End If
   Commission = Sales * pct
   End Function
PriceLevel Function
Automate Sum Functions
Wishing you a Wonderful Holiday
Season

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:15
posted:8/8/2012
language:
pages:42