Excel VBA programming for dummies
Take your Excel programming skills to the next level. To take Excel to the next level, you need to understand and implement the power of Visual Basic for Applications (VBA). Excel VBA Programming For Dummies introduces you to a wide array of new Excel options, beginning with the most important tools...
| Otros Autores: | |
|---|---|
| Formato: | Libro electrónico |
| Idioma: | Inglés |
| Publicado: |
Hoboken, N.J. :
John Wiley & Sons
2018.
|
| Edición: | 5th ed |
| Colección: | --For dummies.
|
| Materias: | |
| Ver en Biblioteca Universitat Ramon Llull: | https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009630532806719 |
Tabla de Contenidos:
- Intro
- Title Page
- Copyright Page
- Table of Contents
- Introduction
- About This Book
- Obligatory Typographical Conventions Section
- Check Your Security Settings
- Foolish Assumptions
- Icons Used in This Book
- Sample Files Online
- Where to Go from Here
- Part 1 Getting Started with Excel VBA Programming
- Chapter 1 What Is VBA?
- Okay, So What Is VBA?
- What Can You Do with VBA?
- Inserting a bunch of text
- Automating a task you perform frequently
- Automating repetitive operations
- Creating a custom command
- Creating a custom button
- Developing new worksheet functions
- Creating custom add-ins for Excel
- Advantages and Disadvantages of VBA
- VBA advantages
- VBA disadvantages
- VBA in a Nutshell
- Excel Compatibility
- Chapter 2 Jumping Right In
- First Things First
- What You'll Be Doing
- Taking the First Steps
- Recording the Macro
- Testing the Macro
- Examining the Macro
- Modifying the Macro
- Saving Workbooks That Contain Macros
- Understanding Macro Security
- Revealing More about the NameAndTime Macro
- Part 2 How VBA Works with Excel
- Chapter 3 Working in the Visual Basic Editor
- What Is the Visual Basic Editor?
- Activating the VBE
- Understanding VBE components
- Working with the Project Window
- Adding a new VBA module
- Removing a VBA module
- Exporting and importing objects
- Working with a Code Window
- Minimizing and maximizing windows
- Creating a module
- Getting VBA code into a module
- Entering code directly
- Using the macro recorder
- Copying VBA code
- Customizing the VBA Environment
- Using the Editor tab
- Using the Editor Format tab
- Using the General tab
- Using the Docking tab
- Chapter 4 Introducing the Excel Object Model
- Excel Is an Object?
- Climbing Down the Object Hierarchy
- Wrapping Your Mind around Collections.
- Referring to Objects
- Navigating through the hierarchy
- Simplifying object references
- Diving into Object Properties and Methods
- Object properties
- Object methods
- Object events
- Finding Out More
- Using VBA's Help system
- Using the Object Browser
- Automatically listing properties and methods
- Chapter 5 VBA Sub and Function Procedures
- Understanding Subs versus Functions
- Looking at Sub procedures
- Looking at Function procedures
- Naming Subs and Functions
- Executing Sub procedures
- Executing the Sub procedure directly
- Executing the procedure from the Macro dialog box
- Executing a macro by using a shortcut key
- Executing the procedure from a button or shape
- Executing the procedure from another procedure
- Executing Function procedures
- Calling the function from a Sub procedure
- Calling a function from a worksheet formula
- Chapter 6 Using the Excel Macro Recorder
- Recording Basics
- Preparing to Record
- Relative or Absolute?
- Recording in absolute mode
- Recording in relative mode
- What Gets Recorded?
- Recording Options
- Macro name
- Shortcut key
- Store Macro In option
- Description
- Is This Thing Efficient?
- Part 3 Programming Concepts
- Chapter 7 Essential VBA Language Elements
- Using Comments in Your VBA Code
- Using Variables, Constants, and Data Types
- Understanding variables
- What are VBA's data types?
- Declaring and scoping variables
- Working with constants
- Premade constants
- Working with strings
- Working with dates
- Using Assignment Statements
- Assignment statement examples
- About that equal sign
- Smooth operators
- Working with Arrays
- Declaring arrays
- Multidimensional arrays
- Dynamic arrays
- Using Labels
- Chapter 8 Working with Range Objects
- A Quick Review
- Other Ways to Refer to a Range
- The Cells property.
- The Offset property
- Some Useful Range Object Properties
- The Value property
- The Text property
- The Count property
- The Column and Row properties
- The Address property
- The HasFormula property
- The Font property
- The Interior property
- The Formula property
- The NumberFormat property
- Some Useful Range Object Methods
- The Select method
- The Copy and Paste methods
- The Clear method
- The Delete method
- Chapter 9 Using VBA and Worksheet Functions
- What Is a Function?
- Using Built-In VBA Functions
- VBA function examples
- VBA functions that do more than return a value
- Discovering VBA functions
- Using Worksheet Functions in VBA
- Worksheet function examples
- Entering worksheet functions
- More about using worksheet functions
- Using Custom Functions
- Chapter 10 Controlling Program Flow and Making Decisions
- Going with the Flow, Dude
- The GoTo Statement
- Decisions, Decisions
- The If-Then structure
- The Select Case structure
- Knocking Your Code for a Loop
- For-Next loops
- Do-While loop
- Do-Until loop
- Using For Each-Next Loops with Collections
- Chapter 11 Automatic Procedures and Events
- Preparing for the Big Event
- Are events useful?
- Programming event-handler procedures
- Where Does the VBA Code Go?
- Writing an Event-Handler Procedure
- Introductory Examples
- The Open event for a workbook
- The BeforeClose event for a workbook
- The BeforeSave event for a workbook
- Examples of Activation Events
- Activate and deactivate events in a sheet
- Activate and deactivate events in a workbook
- Workbook activation events
- Other Worksheet-Related Events
- The BeforeDoubleClick event
- The BeforeRightClick event
- The Change event
- Events Not Associated with Objects
- The OnTime event
- Keypress events
- Chapter 12 Error-Handling Techniques
- Types of Errors.
- An Erroneous Example
- The macro's not quite perfect
- The macro is still not perfect
- Is the macro perfect yet?
- Giving up on perfection
- Handling Errors Another Way
- Revisiting the EnterSquareRoot procedure
- About the On Error statement
- Handling Errors: The Details
- Resuming after an error
- Error handling in a nutshell
- Knowing when to ignore errors
- Identifying specific errors
- An Intentional Error
- Chapter 13 Bug Extermination Techniques
- Species of Bugs
- Identifying Bugs
- Debugging Techniques
- Examining your code
- Using the MsgBox function
- Inserting Debug.Print statements
- Using the VBA debugger
- About the Debugger
- Setting breakpoints in your code
- Using the Watches window
- Using the Locals window
- Bug Reduction Tips
- Chapter 14 VBA Programming Examples
- Working with Ranges
- Copying a range
- Copying a variable-size range
- Selecting to the end of a row or column
- Selecting a row or column
- Moving a range
- Looping through a range efficiently
- Looping through a range efficiently (Part II)
- Prompting for a cell value
- Determining the selection type
- Identifying a multiple selection
- Changing Excel Settings
- Changing Boolean settings
- Changing non-Boolean settings
- Working with Charts
- AddChart versus AddChart2
- Modifying the chart type
- Looping through the ChartObjects collection
- Modifying chart properties
- Applying chart formatting
- VBA Speed Tips
- Turning off screen updating
- Turning off automatic calculation
- Eliminating those pesky alert messages
- Simplifying object references
- Declaring variable types
- Using the With-End With structure
- Part 4 Communicating with Your Users
- Chapter 15 Simple Dialog Boxes
- UserForm Alternatives
- The MsgBox Function
- Displaying a simple message box
- Getting a response from a message box.
- Customizing message boxes
- The InputBox Function
- InputBox syntax
- An InputBox example
- Another type of InputBox
- The GetOpenFilename Method
- The syntax for the GetOpenFilename method
- A GetOpenFilename example
- The GetSaveAsFilename Method
- Getting a Folder Name
- Displaying Excel's Built-in Dialog Boxes
- Chapter 16 UserForm Basics
- Knowing When to Use a UserForm
- Creating UserForms: An Overview
- Working with UserForms
- Inserting a new UserForm
- Adding controls to a UserForm
- Changing properties for a UserForm control
- Viewing the UserForm Code window
- Displaying a UserForm
- Using information from a UserForm
- A UserForm Example
- Creating the UserForm
- Adding the CommandButtons
- Adding the OptionButtons
- Adding event-handler procedures
- Creating a macro to display the dialog box
- Making the macro available
- Testing the macro
- Chapter 17 Using UserForm Controls
- Getting Started with Dialog Box Controls
- Adding controls
- Introducing control properties
- Dialog Box Controls: The Details
- CheckBox control
- ComboBox control
- CommandButton control
- Frame control
- Image control
- Label control
- ListBox control
- MultiPage control
- OptionButton control
- RefEdit control
- ScrollBar control
- SpinButton control
- TabStrip control
- TextBox control
- ToggleButton control
- Working with Dialog Box Controls
- Moving and resizing controls
- Aligning and spacing controls
- Accommodating keyboard users
- Testing a UserForm
- Dialog Box Aesthetics
- Chapter 18 UserForm Techniques and Tricks
- Using Dialog Boxes
- A UserForm Example
- Creating the dialog box
- Writing code to display the dialog box
- Making the macro available
- Trying out your dialog box
- Adding event-handler procedures
- Validating the data
- Now the dialog box works
- A ListBox Example.
- Filling a ListBox.
