introduction to excel vba unc charlotte cpe/pdh series december 17, 2009
TRANSCRIPT
Introduction to Excel VBAIntroduction to Excel VBA
UNC Charlotte
CPE/PDH Series
December 17, 2009
Lesson 1 ObjectivesLesson 1 ObjectivesRecord a MacroModulesExamine the VBE window
– Project Explorer, Object Browser, PropertiesSub Procedures
– Write a Sub procedureExamine statement code
– Define objects properties– Edit code
Personal Macro WorkbookPersonal 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 EditorVisual Basic Editor
Project VBA Project
Properties
Sub Procedure in Module 1
Module
Record a MacroRecord a Macro
Display the Developer Tab
Click on the Macro tool
Proceed through steps to complete the macro
Stop Recording ToolStop Recording Tool
Click on the Stop Recording Tool
Edit a MacroEdit a Macro
Open the Visual Basic Editor Make the changes needed Save and Close the VBE
Absolute Cell ReferencesAbsolute Cell References
Relative Cell ReferencesRelative Cell References
DebugDebug Open the VBE and Set a Breakpoint Start running the procedure Step through the code
Debug continuedDebug continued
Open the VBE and Set a Breakpoint Start running the procedure 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 continuedDebug 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 Adding a Tool to the Quick Access ToolbarAccess Toolbar
Click on the Customize button on the Quick Access ToolbarSelect Macros
Adding a Tool to the Quick Adding a Tool to the Quick Access Toolbar continuedAccess Toolbar continued
Click on Create ReportClick Add
Adding a Tool to the Quick Adding a Tool to the Quick Access Toolbar continuedAccess Toolbar continued
Click on Create ReportClick Add and OK
Customize the New ToolCustomize 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 ObjectivesLesson 2 ObjectivesObjects
– Range– Selection– Active Sheet
Methods– Select
Properties– CurrentRegion– Sort– Name
Objects, Methods, & PropertiesObjects, 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
VariablesVariables
A variable is a name given to hold data.
When a procedure uses a variable the current ‘value’ is used
Option Explicit and Dim StatementsOption 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
LoopsLoops
For Next LoopsIf ThenIf Then ElseCaseDo Loops
– Do While– Do Until
For NextFor Next
Sub ForNext Loop ()For Counter = 1 to 10 [Step 1]
Code is written hereNext Counter – continues to collect iterations as a countEnd Sub
If…Then SyntaxIf…Then Syntax
If…Then…Else SyntaxIf…Then…Else Syntax
Use a Do Until…LoopUse a Do Until…Loop
Sub DoUntilLoop ()Do Until ActiveCell = “”
Code is written here
LoopEnd Sub
Case Is… SyntaxCase Is… Syntax
Find first blank row at the bottom Find first blank row at the bottom of a listof a listOpen the file Our CompanyCreate 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 PropertiesOffset and Count Properties
ActiveCell.Offset(3,2)(row,column)
Rows.CountThe 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 ObjectivesLesson 3 Objectives
Dialog boxInput boxMessage boxCommand buttons
Dialog box and User InputDialog box and User Input
SortBy ProcedureSortBy Procedure
DateTime ProcedureDateTime Procedure
RepSort ProcedureRepSort Procedure
Lesson 4 ObjectivesLesson 4 Objectives
Insert, Copy and Delete WorksheetsRename worksheetsChange worksheet orderPrint worksheets
More MethodsMore Methods
Add Method– Worksheets.Add
Delete Method– Worksheets(2).Delete
Copy Method– Worksheets(2).Copy After:=Worksheets(2)
Name PropertyName Property
More MethodsMore Methods
PrintPreview– Worksheets(2).PrintPreview
PrintOut– Worksheets(2).Printout– Worksheets(“SE Sales”).PrintOut
Unit 5 ObjectivesUnit 5 Objectives
Create a User Defined Function (UDF)– Declare a variable– Perform a calculation
User Defined FunctionsUser 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)Function Commission(Sales)If Sales >= 50000 Thenpct = 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 * pctEnd Function
PriceLevel FunctionPriceLevel Function
Automate Sum FunctionsAutomate Sum Functions
Wishing you a Wonderful Holiday Wishing you a Wonderful Holiday SeasonSeason