Introduction to Excel VBA

1
Introduction to Excel VBA

• UNC Charlotte
• CPE/PDH Series
• December 17, 2009

2
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

3
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

4
Visual Basic Editor
Project VBA Project
Module
Sub Procedure in Module 1
Properties
5
Sub Procedures
Keyword
Procedure Name (arguments, optional)
Sub Enter_Text() Enters the text
Commission in selected cell Range
(E1).Select Active Cell.FormulaR1C1Commissi
on End Sub
Comment
Statements
6
Record a Macro
Display the Developer Tab Click on the Macro
tool Proceed through steps to complete the macro
7
Stop Recording Tool
Click on the Stop Recording Tool
8
Edit a Macro

• Open the Visual Basic Editor
• Make the changes needed
• Save and Close the VBE

9
Absolute Cell References
10
Relative Cell References
11
Debug

• Open the VBE and Set a Breakpoint
• Start running the procedure
• Step through the code

12
Debug continued

• Open the VBE and Set a Breakpoint
• Start running the procedure
• Step through the code

Bellerephon Investment Advisors Bellerephon Investment Advisors Bellerephon Investment Advisors Bellerephon Investment Advisors Bellerephon Investment Advisors Bellerephon Investment Advisors Bellerephon Investment Advisors
Stock Prices Stock Prices 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

13
Debug continued

• Procedure InsertRowsCols
• Correction Rows(14)
• Procedure InsertTxt
• Corrections A1
• Procedure FmtTxt
• Correction Columns(HJ) Percent
• Move EntireColumn.AutoFit above End Sub

14
Adding a Tool to the Quick Access Toolbar
Click on the Customize button on the Quick Access
Toolbar Select Macros
15
Adding a Tool to the Quick Access Toolbar
continued
Click on Create Report Click Add
16
Adding a Tool to the Quick Access Toolbar
continued
Click on Create Report Click Add and OK
17
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

18
Lesson 2 Objectives

• Objects
• Range
• Selection
• Active Sheet
• Methods
• Select
• Properties
• CurrentRegion
• Sort
• Name

19
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

20
Variables

• A variable is a name given to hold data.
• When a procedure uses a variable the current
  value is used

21
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
22
Loops

• For Next Loops
• If Then
• If Then Else
• Case
• Do Loops
• Do While
• Do Until

23
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
24
IfThen Syntax
25
IfThenElse Syntax
26
Use a Do UntilLoop
Sub DoUntilLoop () Do Until ActiveCell Code
is written here Loop End Sub
27
Case Is Syntax
28
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
29
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

30
Lesson 3 Objectives

• Dialog box
• Input box
• Message box
• Command buttons

31
Dialog box and User Input
32
SortBy Procedure
33
DateTime Procedure
RepSort Procedure
34
Lesson 4 Objectives

• Insert, Copy and Delete Worksheets
• Rename worksheets
• Change worksheet order
• Print worksheets

35
More Methods

• Add Method
• Worksheets.Add
• Delete Method
• Worksheets(2).Delete
• Copy Method
• Worksheets(2).Copy AfterWorksheets(2)

36
Name Property
37
More Methods

• PrintPreview
• Worksheets(2).PrintPreview
• PrintOut
• Worksheets(2).Printout
• Worksheets(SE Sales).PrintOut

38
Unit 5 Objectives

• Create a User Defined Function (UDF)
• Declare a variable
• Perform a calculation

39
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

40
Function Commission(Sales)

• Function Commission(Sales)
• If Sales gt 50000 Then
• pct 0.15
• ElseIf Sales gt 40000 Then
• pct 0.12
• ElseIf Sales gt 25000 Then
• pct 0.1
• ElseIf Sales gt 10000 Then
• pct 0.08
• Else
• pct 0.05
• End If
• Commission Sales pct
• End Function

41
PriceLevel Function
42
Automate Sum Functions
43
Wishing you a Wonderful Holiday Season