Excel 2019 Power Programming with VBA

Name: Excel 2019 Power Programming with VBA
Brand: Wiley-Scrivener
Price: 36.99 EUR
Availability: OnlineOnly

Michael Alexander Dick Kusleika(Author)

Wiley-Scrivener (Publisher)

1st Edition

Published on 24. April 2019

784 pages

E-Book

PDF with Adobe-DRM

System requirements

978-1-119-51494-7 (ISBN)

€36.99incl. 7% vat

System requirements

for PDF with Adobe-DRM

E-Book Single Licence

Available for download

Description

More details

Other editions

Content

Cover
Title Page
Copyright
About the Authors
About the Technical Editors
Credits
Acknowledgments
Contents at a Glance
Contents
Introduction
Topics Covered
What You Need to Know
What You Need to Have
Conventions Used in This Book
Excel commands
Visual Basic Editor commands
Keyboard conventions
Typographical conventions
Mouse conventions
What the Icons Mean
How This Book Is Organized
Part I: Introduction to Excel VBA
Part II: Advanced VBA Techniques
Part III: Working with UserForms
Part IV: Developing Excel Applications
Part V: Appendix
How to Use This Book
What's on the Website
Part I Introduction to Excel VBA
Chapter 1 Essentials of Spreadsheet Application Development
What Is a Spreadsheet Application?
Steps for Application Development
Determining User Needs
Planning an Application That Meets User Needs
Determining the Most Appropriate User Interface
Customizing the Ribbon
Customizing shortcut menus
Creating shortcut keys
Creating custom dialog boxes
Using ActiveX controls on a worksheet
Executing the development effort
Concerning Yourself with the End User
Testing the application
Making the application bulletproof
Making the application aesthetically appealing and intuitive
Creating a user Help system
Documenting the development effort
Distributing the application to the user
Updating the application when necessary
Other Development Issues
The user's installed version of Excel
Language issues
System speed
Video modes
Chapter 2 Introducing Visual Basic for Applications
Getting a Head Start with the Macro Recorder
Creating your first macro
Examining your macro
Testing your macro
Editing your macro
Comparing absolute and relative macro recording
Recording macros with absolute references
Recording macros with relative references
Other macro recording concepts
Macro security in Excel
Trusted locations
Storing macros in your Personal Macro Workbook
Assigning a macro to a button and other form controls
Placing a macro on the Quick Access toolbar
Working with the Visual Basic Editor
Understanding VBE components
Menu bar
Toolbar
Project window
Code window
Immediate window
Working with the Project window
Adding a new VBA module
Removing a VBA module
Working with a Code window
Minimizing and maximizing windows
Getting VBA code into a module
Customizing the VBA environment
The Editor tab
The Editor Format tab
The General tab
The Docking tab
VBA Fundamentals
Understanding objects
Understanding collections
Understanding properties
Specifying properties for the active object
Understanding methods
Deep Dive: Working with Range Objects
Finding the properties of the Range object
The Range property
The Cells property
The Offset property
Essential Concepts to Remember
Don't Panic-You Are Not Alone
Read the rest of the book
Let Excel help write your macro
Use the Help system
Location matters when asking for help
You need to be connected to the Internet
Use the Object Browser
Pilfer code from the Internet
Leverage user forums
Visit expert blogs
Mine YouTube for video training
Learn from the Microsoft Office Dev Center
Dissect the other Excel files in your organization
Ask your local Excel genius
Chapter 3 VBA Programming Fundamentals
VBA Language Elements: An Overview
Comments
Variables, Data Types, and Constants
Defining data types
Declaring variables
Determining a data type
Forcing yourself to declare all variables
Scoping variables
Local variables
Module-wide variables
Public variables
Static variables
Working with constants
Declaring constants
Using predefined constants
Working with strings
Working with dates
Assignment Statements
Arrays
Declaring arrays
Declaring multidimensional arrays
Declaring dynamic arrays
Object Variables
User-Defined Data Types
Built-in Functions
Manipulating Objects and Collections
With-End With constructs
For Each-Next constructs
Controlling Code Execution
GoTo statements
If-Then constructs
Select Case constructs
Looping blocks of instructions
For-Next loops
Do While loops
Do Until loops
Chapter 4 Working with VBA Sub Procedures
About Procedures
Declaring a Sub procedure
Scoping a procedure
Public procedures
Private procedures
Executing Sub Procedures
Executing a procedure with the Run Sub/UserForm command
Executing a procedure from the Macro dialog box
Executing a procedure with a Ctrl+shortcut key combination
Executing a procedure from the Ribbon
Executing a procedure from a customized shortcut menu
Executing a procedure from another procedure
Calling a procedure in a different module
Calling a procedure in a different workbook
Executing a procedure by clicking an object
Executing a procedure when an event occurs
Executing a procedure from the Immediate window
Passing Arguments to Procedures
Error-Handling Techniques
Trapping errors
Error-handling examples
A Realistic Example That Uses Sub Procedures
The goal
Project requirements
What you know
The approach
Some preliminary recording
Initial setup
Code writing
Writing the Sort procedure
More testing
Fixing the problems
Utility availability
Evaluating the project
Chapter 5 Creating Function Procedures
Sub Procedures vs. Function Procedures
Why Create Custom Functions?
An Introductory Function Example
Using the function in a worksheet
Using the function in a VBA procedure
Analyzing the custom function
Function Procedures
A function's scope
Executing function procedures
From a procedure
In a worksheet formula
In a conditional formatting formula
From the VBE Immediate Window
Function Arguments
Function Examples
Functions with no argument
A function with one argument
A function with two arguments
A function with an array argument
A function with optional arguments
A function that returns a VBA array
A function that returns an error value
A function with an indefinite number of arguments
Emulating Excel's SUM Function
Extended Date Functions
Debugging Functions
Dealing with the Insert Function Dialog Box
Using the MacroOptions method
Specifying a function category
Adding a function description manually
Using Add-Ins to Store Custom Functions
Using the Windows API
Windows API examples
Determining the Windows directory
Detecting the Shift key
Learning more about API functions
Chapter 6 Understanding Excel's Events
What You Should Know About Events
Understanding event sequences
Where to put event-handler procedures
Disabling events
Entering event-handler code
Event-handler procedures that use arguments
Getting Acquainted with Workbook-Level Events
The Open event
The Activate event
The SheetActivate event
The NewSheet event
The BeforeSave event
The Deactivate event
The BeforePrint event
Updating a header or footer
Hiding columns before printing
The BeforeClose event
Examining Worksheet Events
The Change event
Monitoring a specific range for changes
Monitoring a range to make formulas bold
Monitoring a range to validate data entry
The SelectionChange event
The BeforeDoubleClick event
The BeforeRightClick event
Monitoring with Application Events
Enabling Application-level events
Determining when a workbook is opened
Monitoring Application-Level events
Accessing events not associated with an object
The OnTime event
The OnKey event
An OnKey event example
Key Codes
Disabling shortcut menus
Chapter 7 VBA Programming Examples and Techniques
Learning by Example
Working with Ranges
Copying a range
Moving a range
Copying a variably sized range
Selecting or otherwise identifying various types of ranges
Resizing a range
Prompting for a cell value
Entering a value in the next empty cell
Pausing a macro to get a user-selected range
Counting selected cells
Determining the type of selected range
Looping through a selected range efficiently
Deleting all empty rows
Duplicating rows a variable number of times
Determining whether a range is contained in another range
Determining a cell's data type
Reading and writing ranges
A better way to write to a range
Transferring one-dimensional arrays
Transferring a range to a variant array
Selecting cells by value
Copying a noncontiguous range
Working with Workbooks and Sheets
Saving all workbooks
Saving and closing all workbooks
Hiding all but the selection
Creating a hyperlink table of contents
Synchronizing worksheets
VBA Techniques
Toggling a Boolean property
Displaying the date and time
Displaying friendly time
Getting a list of fonts
Sorting an array
Processing a series of files
Some Useful Functions for Use in Your Code
The FileExists function
The FileNameOnly function
The PathExists function
The RangeNameExists function
The SheetExists function
The WorkbookIsOpen function
Retrieving a value from a closed workbook
Some Useful Worksheet Functions
Returning cell formatting information
A talking worksheet
Displaying the date when a file was saved or printed
Understanding object parents
Counting cells between two values
Determining the last nonempty cell in a column or row
Does a string match a pattern?
Extracting the nth element from a string
Spelling out a number
A multifunctional function
The SHEETOFFSET function
Returning the maximum value across all worksheets
Returning an array of nonduplicated random integers
Randomizing a range
Sorting a range
Windows API Calls
Understanding API declarations
32-bit vs. 64-bit declarations
Determining file associations
Determining default printer information
Determining video display information
Reading from and writing to the Registry
Reading from the Registry
Writing to the Registry
Part II Advanced VBA Techniques
Chapter 8: Working with Pivot Tables
An Introductory Pivot Table Example
Creating a pivot table
Examining the recorded code for the pivot table
Cleaning up the recorded pivot table code
Creating a More Complex Pivot Table
The code that created the pivot table
How the more complex pivot table works
Creating Multiple Pivot Tables
Creating a Reverse Pivot Table
Chapter 9: Working with Charts
Getting the Inside Scoop on Charts
Chart locations
The macro recorder and charts
The Chart object model
Creating an Embedded Chart
Creating a Chart on a Chart Sheet
Modifying Charts
Using VBA to Activate a Chart
Moving a Chart
Using VBA to Deactivate a Chart
Determining Whether a Chart Is Activated
Deleting from the ChartObjects or Charts Collection
Looping Through All Charts
Sizing and Aligning ChartObjects
Creating Lots of Charts
Exporting a Chart
Exporting all graphics
Changing the Data Used in a Chart
Changing chart data based on the active cell
Using VBA to determine the ranges used in a chart
Using VBA to Display Custom Data Labels on a Chart
Displaying a Chart in a UserForm
Understanding Chart Events
An example of using Chart events
Enabling events for an embedded chart
Create a class module
Declare a public Chart object
Connect the declared object with your chart
Write event-handler procedures for the chart class
Example: Using Chart events with an embedded chart
Discovering VBA Charting Tricks
Printing embedded charts on a full page
Creating unlinked charts
Displaying text with the MouseOver event
Scrolling a chart
Working with Sparkline Charts
Chapter 10: Interacting with Other Applications
Understanding Microsoft Office Automation
Understanding the concept of binding
Early binding
Late binding
A simple automation example
Automating Access from Excel
Running an Access query from Excel
Running an Access macro from Excel
Automating Word from Excel
Sending Excel data to a Word document
Simulating Mail Merge with a Word document
Automating PowerPoint from Excel
Sending Excel data to a PowerPoint presentation
Sending all Excel charts to a PowerPoint presentation
Convert a workbook into a PowerPoint presentation
Automating Outlook from Excel
Mailing the active workbook as an attachment
Mailing a specific range as an attachment
Mailing a Single Sheet as an Attachment
Mailing All E-mail Addresses in Your Contact List
Starting Other Applications from Excel
Using the VBA Shell function
Using the Windows ShellExecute API function
Using AppActivate
Running Control Panel Dialog Boxes
Chapter 11: Working with External Data and Files
Working with External Data Connections
Power Query Basics
Understanding query steps
Refreshing Power Query data
Managing existing queries
Using VBA to create dynamic connections
Iterating through all connections in a workbook
Using ADO and VBA to Pull External Data
The connection string
Declaring a Recordset
Referencing the ADO object library
Putting it all together in code
Using ADO with the active workbook
Querying data from an Excel workbook
Appending records to an existing Excel table
Working with Text Files
Opening a text file
Reading a text file
Writing a text file
Getting a file number
Determining or setting the file position
Statements for reading and writing
Text File Manipulation Examples
Importing data in a text file
Exporting a range to a text file
Importing a text file to a range
Logging Excel usage
Filtering a text file
Performing Common File Operations
Using VBA file-related statements
A VBA function to determine whether a file exists
A VBA function to determine whether a path exists
A VBA procedure to display a list of files in a directory
A recursive VBA procedure to display a list of files in nested directories
Using the FileSystemObject object
Using FileSystemObject to determine whether a file exists
Using FileSystemObject to determine whether a path exists
Using FileSystemObject to list information about all available disk drives
Zipping and Unzipping Files
Zipping files
Unzipping a file
Part III: Working with UserForms
Chapter 12: Leveraging Custom Dialog Boxes
Alternatives to UserForms
Using an Input Box
Using the VBA InputBox function
Using the Application.InputBox method
Using the VBA MsgBox Function
Using the Excel GetOpenFilename Method
Using the Excel GetSaveAsFilename Method
Prompting for a Folder
Displaying Excel's Built-in Dialog Boxes
Displaying a Data Form
Making the data form accessible
Displaying a data form by using VBA
Chapter 13: Introducing UserForms
How Excel Handles Custom Dialog Boxes
Inserting a New UserForm
Adding Controls to a UserForm
Toolbox Controls
CheckBox
ComboBox
CommandButton
Frame
Image
Label
ListBox
MultiPage
OptionButton
RefEdit
ScrollBar
SpinButton
TabStrip
TextBox
ToggleButton
Adjusting UserForm Controls
Adjusting a Control's Properties
Using the Properties window
Common properties
Accommodating keyboard users
Changing the tab order of controls
Setting hot keys
Displaying a UserForm
Adjusting the display position
Displaying a modeless UserForm
Displaying a UserForm based on a variable
Loading a UserForm
About event-handler procedures
Closing a UserForm
Creating a UserForm: An Example
Creating the UserForm
Writing code to display the dialog box
Testing the dialog box
Adding event-handler procedures
The finished dialog box
Learning about events
SpinButton events
Mouse-initiated events
Keyboard-initiated events
What about code-initiated events?
Pairing a SpinButton with a TextBox
Referencing UserForm Controls
Customizing the Toolbox
Adding new pages to the Toolbox
Customizing or combining controls
Adding other ActiveX controls
Creating UserForm Templates
A UserForm Checklist
Chapter 14: Looking at UserForm Examples
Creating a UserForm "Menu"
Using CommandButtons in a UserForm
Using a list box in a UserForm
Selecting Ranges from a UserForm
Creating a Splash Screen
Disabling a UserForm's Close Button
Changing a UserForm's Size
Zooming and Scrolling a Sheet from a UserForm
Exploring ListBox Techniques
Adding items to a ListBox control
Adding items to a list box at design time
Adding items to a list box at run-time
Adding only unique items to a list box
Determining the selected item in a list box
Determining multiple selections in a list box
Multiple lists in a single list box
List box item transfer
Moving items in a list box
Working with multicolumn ListBox controls
Using a list box to select worksheet rows
Using a list box to activate a sheet
Using a text box to filter a list box
Using the MultiPage Control in a UserForm
Using an External Control
Animating a Label
Chapter 15 Implementing Advanced UserForm Techniques
A Modeless Dialog Box
Displaying a Progress Indicator
Creating a stand-alone progress indicator
Building the stand-alone progress indicator UserForm
Creating the code that increments the progress bar
Calling the stand-alone progress indicator from your code
Benefits of a stand-alone progress indicator
Showing a progress indicator that's integrated into a UserForm
Modifying your UserForm for a progress indicator with a MultiPage control
Inserting the UpdateProgress procedure for a progress indicator with a MultiPage control
Modifying your procedure for a progress indicator with a MultiPage control
How a progress indicator with a MultiPage control works
Showing a progress indicator without using a MultiPage control
Creating a nongraphical progress indicator
Creating the UserForm to display the steps
Modifying the calling procedure to use the progress indicator
Creating Wizards
Setting up the MultiPage control for the wizard
Adding the buttons to the wizard's UserForm
Programming the wizard's buttons
Programming dependencies in a wizard
Performing the task with the wizard
Emulating the MsgBox Function
MsgBox emulation: MyMsgBox code
How the MyMsgBox function works
Using the MyMsgBox function
A UserForm with Movable Controls
A UserForm with No Title Bar
Simulating a Toolbar with a UserForm
Emulating a Task Pane with a UserForm
A Resizable UserForm
Handling Multiple UserForm Controls with One Event Handler
Selecting a Color in a UserForm
Displaying a Chart in a UserForm
Saving a chart as a GIF file
Changing the Image control's Picture property
Making a UserForm Semitransparent
A Puzzle on a UserForm
Video Poker on a UserForm
Part IV Part IVDeveloping Excel Applications
Chapter 16 Creating and Using Add-Ins
What Is an Add-In?
Comparing an add-in with a standard workbook
Why create add-ins?
Understanding Excel's Add-in Manager
Creating an Add-In
An Add-In Example
Adding descriptive information for the example add-in
Creating an add-in
Installing an add-in
Testing the add-in
Distributing an add-in
Modifying an add-in
Comparing XLAM and XLSM Files
XLAM file VBA collection membership
Visibility of XLSM and XLAM files
Worksheets and chart sheets in XLSM and XLAM files
Accessing VBA procedures in an add-in
Manipulating Add-Ins with VBA
Adding an item to the AddIns collection
Removing an item from the AddIns collection
AddIn object properties
The Name property of an AddIn object
The Path property of an AddIn object
The FullName property of an AddIn object
The Title property of an AddIn object
The Comments property of an AddIn object
The Installed property of an AddIn object
Accessing an add-in as a workbook
AddIn object events
Optimizing the Performance of Add-Ins
Special Problems with Add-Ins
Ensuring that an add-in is installed
Referencing other files from an add-in
Chapter 17 Working with the Ribbon
Ribbon Basics
Customizing the Ribbon
Adding a button to the Ribbon
Adding a button to the Quick Access toolbar
Understanding the limitations of Ribbon customization
Creating a Custom Ribbon
Adding a button to an existing tab
The RibbonX code
Callback procedures
The CUSTOM UI part
Adding a check box to an existing tab
The RibbonX code
The VBA code
Ribbon controls demo
Creating a new tab
Creating a Ribbon group
Creating controls
A dynamicMenu control example
More on Ribbon customization
Using VBA with the Ribbon
Accessing a Ribbon control
Working with the Ribbon
Activating a tab
Creating an Old-Style Toolbar
Limitations of old-style toolbars
Code to create a toolbar
Chapter 18 Working with Shortcut Menus
CommandBar Overview
CommandBar types
Listing shortcut menus
Referring to CommandBars
Referring to Controls in a CommandBar
Properties of CommandBar Controls
Displaying All Shortcut Menu Items
Using VBA to Customize Shortcut Menus
Shortcut menu and the single-document interface
Resetting a Shortcut Menu
Disabling a shortcut menu
Disabling shortcut menu items
Adding a new item to the Cell shortcut menu
Adding a submenu to a shortcut menu
Limiting a shortcut menu to a single workbook
Shortcut Menus and Events
Adding and deleting menus automatically
Disabling or hiding shortcut menu items
Creating a context-sensitive shortcut menu
Chapter 19 Providing Help for Your Applications
Help for Your Excel Applications
Help Systems That Use Excel Components
Using cell comments for help
Using a text box for help
Using a worksheet to display help text
Displaying help in a UserForm
Using Label controls to display help text
Using a scrolling label to display help text
Using a ComboBox control to select a help topic
Displaying Help in a Web Browser
Using HTML files
Using an MHTML file
Using the HTML Help System
Using the Help method to display HTML Help
Associating a help file with your application
Associating a help topic with a VBA function
Chapter 20 Leveraging Class Modules
What Is a Class Module?
Built-in class modules
Custom class modules
Classes and objects
Objects, properties, and methods
Creating a NumLock Class
Inserting a class module
Adding VBA code to the class module
Using the CNumLock class
Coding Properties, Methods, and Events
Programming properties of objects
Programming methods for objects
Class module events
Exposing a QueryTable Event
Creating a Class to Hold Classes
Creating the CSalesRep and CSalesReps classes
Creating the CInvoice and CInvoices classes
Filling the parent classes with objects
Calculating the commissions
Chapter 21 Understanding Compatibility Issues
What Is Compatibility?
Types of Compatibility Problems
Avoid Using New Features
But Will It Work on a Mac?
Dealing with 64-Bit Excel
Creating an International Application
Multilanguage Applications
VBA Language Considerations
Using Local Properties
Identifying System Settings
Date and Time Settings
Part V Appendix: VBA Statements and Functions Reference
Appendix: VBA Statements and Functions Reference
VBA Statements
Functions
Index
EULA

System requirements

Save as PDF Copy link into clipboard

Schweitzer Fachinformationen

Excel 2019 Power Programming with VBA

Description

More details

Other editions

Additional editions

Content

System requirements