1 / 42

Introduction to Excel VBA

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.

otylia
Download Presentation

Introduction to Excel VBA

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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. Record a Macro Display the Developer Tab Click on the Macro tool Proceed through steps to complete the macro

  6. Stop Recording Tool Click on the Stop Recording Tool

  7. Edit a Macro • Open the Visual Basic Editor • Make the changes needed • Save and Close the VBE

  8. Absolute Cell References

  9. Relative Cell References

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

  11. Debug continued • Open the VBE and Set a Breakpoint • Start running the procedure • Step through the code

  12. 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

  13. Adding a Tool to the Quick Access Toolbar Click on the Customize button on the Quick Access Toolbar Select Macros

  14. Adding a Tool to the Quick Access Toolbar continued Click on Create Report Click Add

  15. Adding a Tool to the Quick Access Toolbar continued Click on Create Report Click Add and OK

  16. 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

  17. Lesson 2 Objectives • Objects • Range • Selection • Active Sheet • Methods • Select • Properties • CurrentRegion • Sort • Name

  18. 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

  19. Variables A variable is a name given to hold data. When a procedure uses a variable the current ‘value’ is used

  20. 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

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

  22. 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

  23. If…Then Syntax

  24. If…Then…Else Syntax

  25. Use a Do Until…Loop Sub DoUntilLoop () Do Until ActiveCell = “” Code is written here Loop End Sub

  26. Case Is… Syntax

  27. Find first blank row at the bottom of a list • Open the file Our Company • Create a Sub procedure named Database SubGoToBottom() ‘ Goes to the first blank row in an Excel list ActiveSheet.Cells(Rows.Count,1).End(xlUp).Offset(1).Select End Sub

  28. 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

  29. Lesson 3 Objectives • Dialog box • Input box • Message box • Command buttons

  30. Dialog box and User Input

  31. SortBy Procedure

  32. DateTime Procedure RepSort Procedure

  33. Lesson 4 Objectives • Insert, Copy and Delete Worksheets • Rename worksheets • Change worksheet order • Print worksheets

  34. More Methods • Add Method • Worksheets.Add • Delete Method • Worksheets(2).Delete • Copy Method • Worksheets(2).Copy After:=Worksheets(2)

  35. Name Property

  36. More Methods • PrintPreview • Worksheets(2).PrintPreview • PrintOut • Worksheets(2).Printout • Worksheets(“SE Sales”).PrintOut

  37. Unit 5 Objectives • Create a User Defined Function (UDF) • Declare a variable • Perform a calculation

  38. 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

  39. 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

  40. PriceLevel Function

  41. Automate Sum Functions

  42. Wishing you a Wonderful Holiday Season

More Related