VBA and macros : Microsoft Excel 2010
AUTOMATE REPORTS BUILD FUNCTIONS VISUALIZE DATA WRITE FAST, RELIABLE SCRIPTS Microsoft Excel 2010 VBA AND MACROS: SAVE TIME AND SUPERCHARGE EXCEL 2010 WITH VBA AND MACROS! Use Excel 2010 VBA and macros to automate virtually any routine task, and save yourself hours, days, maybe even weeks. Then lear...
| Otros Autores: | , |
|---|---|
| Formato: | Libro electrónico |
| Idioma: | Inglés |
| Publicado: |
[Place of publication not identified]
Que Pub
2010
|
| Edición: | 1st edition |
| Colección: | MrExcel library VBA and macros
|
| Materias: | |
| Ver en Biblioteca Universitat Ramon Llull: | https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009629120406719 |
Tabla de Contenidos:
- Cover
- Contents
- Introduction
- Getting Results with VBA
- What Is in This Book?
- Reduce the Learning Curve
- Excel VBA Power
- Techie Stuff Needed to Produce Applications
- Does This Book Teach Excel?
- The Future of VBA and Windows Versions of Excel
- Versions of Excel
- Special Elements and Typographical Conventions
- Code Files
- Next Steps
- 1 Unleash the Power of Excel with VBA
- The Power of Excel
- Barriers to Entry
- The Macro Recorder Doesn't Work!
- Visual Basic Is Not Like BASIC
- Good News: Climbing the Learning Curve Is Easy
- Great News: Excel with VBA Is Worth the Effort
- Knowing Your Tools: The Developer Tab
- Macro Security
- Adding a Trusted Location
- Using Macro Settings to Enable Macros in Workbooks Outside of Trusted Locations
- Using Disable All Macros with Notification
- Overview of Recording, Storing, and Running a Macro
- Filling Out the Record Macro Dialog
- Running a Macro
- Creating a Macro Button on the Ribbon
- Creating a Macro Button on the Quick Access Toolbar
- Assigning a Macro to a Form Control, Text Box, or Shape
- Using New File Types in Excel 2010
- Understanding the VB Editor
- VB Editor Settings
- The Project Explorer
- The Properties Window
- Understanding Shortcomings of the Macro Recorder
- Examining Code in the Programming Window
- Running the Macro on Another Day Produces Undesired Results
- Possible Solution: Use Relative References When Recording
- Never Use the AutoSum Button While Recording a Macro
- Three Tips When Using the Macro Recorder
- Next Steps
- 2 This Sounds Like BASIC, So Why Doesn't It Look Familiar?
- I Can't Understand This Code
- Understanding the Parts of VBA "Speech"
- VBA Is Not Really Hard
- VBA Help Files: Using F1 to Find Anything
- Using Help Topics
- Examining Recorded Macro Code: Using the VB Editor and Help.
- Optional Parameters
- Defined Constants
- Properties Can Return Objects
- Using Debugging Tools to Figure Out Recorded Code
- Stepping Through Code
- More Debugging Options: Breakpoints
- Backing Up or Moving Forward in Code
- Not Stepping Through Each Line of Code
- Querying Anything While Stepping Through Code
- Using a Watch to Set a Breakpoint
- Using a Watch on an Object
- Object Browser: The Ultimate Reference
- Seven Tips for Cleaning Up Recorded Code
- Tip 1: Don't Select Anything
- Tip 2: Cells(2,5) Is More Convenient Than Range("E2")
- Tip 3: Ride the Range from the Bottom to Find Last Row
- Tip 4: Use Variables to Avoid Hard-Coding Rows and Formulas
- Tip 5: R1C1 Formulas That Make Your Life Easier
- Tip 6: Learn to Copy and Paste in a Single Statement
- Tip 7: Use With...End With to Perform Multiple Actions
- Next Steps
- 3 Referring to Ranges
- The Range Object
- Syntax to Specify a Range
- Named Ranges
- Shortcut for Referencing Ranges
- Referencing Ranges in Other Sheets
- Referencing a Range Relative to Another Range
- Use the Cells Property to Select a Range
- Using the Cells Property in the Range Property
- Use the Offset Property to Refer to a Range
- Use the Resize Property to Change the Size of a Range
- Using the Columns and Rows Properties to Specify a Range
- Use the Union Method to Join Multiple Ranges
- Use the Intersect Method to Create a New Range from Overlapping Ranges
- Use the ISEMPTY Function to Check Whether a Cell Is Empty
- Use the CurrentRegion Property to Select a Data Range
- Use the Areas Collection to Return a Noncontiguous Range
- Referencing Tables
- Next Steps
- 4 User-Defined Functions
- Creating User-Defined Functions
- Sharing UDFs
- Useful Custom Excel Functions
- Set the Current Workbook's Name in a Cell.
- Set the Current Workbook's Name and File Path in a Cell
- Check Whether a Workbook Is Open
- Check Whether a Sheet in an Open Workbook Exists
- Count the Number of Workbooks in a Directory
- Retrieve USERID
- Retrieve Date and Time of Last Save
- Retrieve Permanent Date and Time
- Validate an E-mail Address
- Sum Cells Based on Interior Color
- Count Unique Values
- Remove Duplicates from a Range
- Find the First Nonzero-Length Cell in a Range
- Substitute Multiple Characters
- Retrieve Numbers from Mixed Text
- Convert Week Number into Date
- Separate Delimited String
- Sort and Concatenate
- Sort Numeric and Alpha Characters
- Search for a String Within Text
- Reverse the Contents of a Cell
- Multiple Max
- Return Hyperlink Address
- Return the Column Letter of a Cell Address
- Static Random
- Using Select Case on a Worksheet
- Next Steps
- 5 Looping and Flow Control
- For...Next Loops
- Using Variables in the For Statement
- Variations on the For...Next Loop
- Exiting a Loop Early After a Condition Is Met
- Nesting One Loop Inside Another Loop
- Do Loops
- Using the While or Until Clause in Do Loops
- While...Wend Loops
- VBA Loop: For Each
- Object Variables
- Flow Control: Using If...Then...Else and Select Case
- Basic Flow Control: If...Then...Else
- Conditions
- If...Then...End If
- Either/Or Decisions: If...Then...Else...End If
- Using If...Else If...End If for Multiple Conditions
- Using Select Case...End Select for Multiple Conditions
- Complex Expressions in Case Statements
- Nesting If Statements
- Next Steps
- 6 R1C1-Style Formulas
- Referring to Cells: A1 Versus R1C1 References
- Switching Excel to Display R1C1-Style References
- The Miracle of Excel Formulas
- Enter a Formula Once and Copy 1,000 Times
- The Secret: It's Not That Amazing
- Explanation of R1C1 Reference Style.
- Using R1C1 with Relative Reference
- Using R1C1 with Absolute References
- Using R1C1 with Mixed References
- Referring to Entire Columns or Rows with R1C1 Style
- Replacing Many A1 Formulas with a Single R1C1 Formula
- Remembering Column Numbers Associated with Column Letters
- Array Formulas Require R1C1 Formulas
- Next Steps
- 7 What Is New in Excel 2010 and What Has Changed
- If It Has Changed in the Front End, It Has Changed in VBA
- The Ribbon
- Charts
- Pivot Tables
- Slicers
- Conditional Formatting
- Tables
- Sorting
- SmartArt
- Learning the New Objects and Methods
- Compatibility Mode
- Version
- Excel8CompatibilityMode
- Next Steps
- 8 Create and Manipulate Names in VBA
- Excel Names
- Global Versus Local Names
- Adding Names
- Deleting Names
- Adding Comments
- Types of Names
- Formulas
- Strings
- Numbers
- Tables
- Using Arrays in Names
- Reserved Names
- Hiding Names
- Checking for the Existence of a Name
- Next Steps
- 9 Event Programming
- Levels of Events
- Using Events
- Event Parameters
- Enabling Events
- Workbook Events
- Workbook_Activate()
- Workbook_Deactivate()
- Workbook_Open()
- Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
- Workbook_BeforePrint(Cancel As Boolean)
- Workbook_BeforeClose(Cancel As Boolean)
- Workbook_NewSheet(ByVal Sh As Object)
- Workbook_WindowResize(ByVal Wn As Window)
- Workbook_WindowActivate(ByVal Wn As Window)
- Workbook_WindowDeactivate(ByVal Wn As Window)
- Workbook_AddInInstall()
- Workbook_AddInUninstall
- Workbook_Sync(ByVal SyncEventType As Office.MsoSyncEventType)
- Workbook_PivotTableCloseConnection(ByVal Target As PivotTable)
- Workbook_PivotTableOpenConnection(ByVal Target As PivotTable)
- Workbook_RowsetComplete(ByVal Description As String, ByVal Sheet As String, ByVal Success As Boolean).
- Workbook_BeforeXmlExport(ByVal Map As XmlMap, ByVal Url As String, Cancel As Boolean)
- Workbook_AfterXmlExport(ByVal Map As XmlMap, ByVal Url As String, ByVal Result As XlXmlExportResult)
- Workbook_BeforeXmlImport(ByVal Map As XmlMap, ByVal Url As String, ByVal IsRefresh As Boolean, Cancel As Boolean)
- Workbook_AfterXmlImport(ByVal Map As XmlMap, ByVal IsRefresh As Boolean, ByVal Result As XlXmlImportResult)
- Workbook Level Sheet and Chart Events
- Worksheet Events
- Worksheet_Activate()
- Worksheet_Deactivate()
- Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
- Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
- Worksheet_Calculate()
- Worksheet_Change(ByVal Target As Range)
- Worksheet_SelectionChange(ByVal Target As Range)
- Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
- Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
- Chart Sheet Events
- Embedded Charts
- Chart_Activate()
- Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
- Chart_BeforeRightClick(Cancel As Boolean)
- Chart_Calculate()
- Chart_Deactivate()
- Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
- Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
- Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
- Chart_Resize()
- Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
- Chart_SeriesChange(ByVal SeriesIndex As Long, ByVal PointIndex As Long)
- Chart_DragOver()
- Chart_DragPlot()
- Application-Level Events
- AppEvent_AfterCalculate()
- AppEvent_NewWorkbook(ByVal Wb As Workbook)
- AppEvent_ProtectedViewWindowActivate(ByVal Pvw As ProtectedViewWindow).
- AppEvent_ProtectedViewWindowBeforeClose(ByVal Pvw As ProtectedViewWindow, ByVal Reason As XlProtectedViewCloseReason, Cancel As Boolean).
