Advanced Macro with VBA Programming (MIS 2)

• 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 complete, macro-driven applications
• Creating custom add-ins for Excel
• Advantages and Disadvantages of VBA
• VBA advantages
• VBA disadvantages
• VBA in a Nutshell
• An Excursion into Versions
• TEAM LinG
  
  
• 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
• More about the Name And Time Macro
  
  
• Chapter 3:
• Working In the Visual Basic Editor
• What Is the Visual Basic Editor?
• Activating the VBE
• Understanding VBE components
• Working with the Project Explorer
• 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 the Object Hierarchy
• Wrapping Your Mind around Collections
• Referring to Objects
• Chapter 5:
• VBA Sub and Function Procedures
• 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
• Is It Live or Is It VBA
• 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
• Description
  
  
• 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
• Working with strings
• Working with dates
  
  
• Chapter 8:
• Working with Range Objects
• Other Ways to Refer to a Range
• The Cells property
• The Offset property
• Referring to entire columns and rows
• Some Useful Range Object Properties
• The Value property
• The Text property
• The Count property
• The Column and Row properties
• The Address property
• The Has Formula property
• The Font property
• The Interior property
• The Formula property
• The Number Format 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
• Looping through a Collection
  
  
• 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 Enter Square Root procedure
• About the On Error statement
  
  
• 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 Watch window
• Using the Locals Window
• Bug Reduction Tips
  
  
• Chapter 14:
• VBA Programming Examples
• Working with Ranges
• Copying a range
• Copying a variable-sized range
• Selecting to the end of a row or column
• Selecting a row or column
• Moving a range
• Looping through a range efficiently
• 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
• Modifying the chart type
• Looping through the Chart Objects 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
  
  
• Chapter 15:
• Simple Dialog Boxes
• Why Create User Forms
• The Msg Box Function
• Displaying a simple message box
• Getting a response from a message box
• Customizing message boxes
• The Input Box Function
• Input Box syntax
• An Input Box example
• The Get Open Filename Method
• The syntax
• A Get Open Filename example
• Selecting multiple files
• The Get Save As Filename Method
• Getting a Folder Name
• Displaying Excel’s Built-in Dialog Boxes
  
  
• Chapter 16:
• User Form Basics
• Knowing When to Use a User Form
• Creating User Forms: An Overview
• Working with User Forms
• Inserting a new User Form
• Adding controls to a User Form
• Changing properties for a User Form control
• Viewing the User Form Code window
• Displaying a User Form
• Using information from a User Form
• A User Form Example
• Creating the User Form
• Adding the Command Buttons
• Adding the Option Buttons
• Adding event-handler procedures
• Creating a macro to display the dialog box
• Making the macro available
• Testing the macro
  
  
• Chapter 17:
• Using User Form Controls
• Getting Started with Dialog Box Controls
• Adding controls
• Introducing control properties
• Dialog Box Controls: The Details
• Check Box control
• Combo Box control
• Command Button control
  
  
• Chapter 18:
• User Form Techniques and Tricks
• Using Dialog Boxes
• A User Form 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
• More User Form Examples
• A List Box example
• Selecting a range
• Using multiple sets of Option Buttons
• Using a Spin Button and a Text Box
• Using a User Form as a progress indicator
• Creating a tabbed dialog box
• Displaying a chart in a dialog box
• A Dialog Box Checklist
  
  
• Chapter 19:
• Accessing Your Macros
• Through the User Interface
• Command Bars and Excel 2007
• Excel 2007 Ribbon Customization
• Working with Command Bars
• Commanding the Command Bars collection
• Listing all shortcut menus
  
  
• Chapter 20:
• Creating Worksheet Functions
• and Living to Tell about It
• Why Create Custom Functions?
• Understanding VBA Function Basics
• Writing Functions
• Working with Function Arguments
• Function Examples
• A function with no argument
• A function with one argument
• A function with two arguments
• A function with a range argument
• A function with an optional argument
• A function with an indefinite number of arguments
• Functions That Return an Array
• Returning an array of month names
• Returning a sorted list
• Using the Insert Function Dialog Box
• Displaying the function’s description
• Argument descriptions
  
  
• Chapter 21:
• Creating Excel Add-Ins
• Okay . . . So What’s an Add-In?
• Why Create Add-Ins
• Working with Add-Ins
• Add-in Basics
• An Add-in Example
• Setting up the workbook
• Testing the workbook
• Adding descriptive information
• Protecting the VBA code
• Creating the add-in
• Opening the add-in
• Distributing the add-in
• Modifying the add-in
  
  
• Chapter 22:
• Ten VBA Questions (And Answers)
• The Top Ten Questions about VBA
  
  
• Chapter 23:
• (Almost) Ten Excel Resources
• The VBA Help System
• Microsoft Product Support
• Internet Newsgroups
• Internet Web Sites
• Excel Blogs
• Google
• Local User Groups

Mastering In Advanced SQL (MIS 3)