Get documents about "Excel and VBA
Document Sample
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
