Microsoft Excel programming pocket primer

260 Pages·2015·10.141 MB·English

by  Korol, Julitta

MICROSOFT®
EXCEL® PROGRAMMING
Pocket Primer
LICENSE, DISCLAIMER OF LIABILITY, AND LIMITED WARRANTY
By purchasing or using this book and disc (the “Work”), you agree that this license
grants permission to use the contents contained herein, including the disc, but does
not give you the right of ownership to any of the textual content in the book / disc
or ownership to any of the information or products contained in it. This license does
not permit uploading of the Work onto the Internet or on a network (of any kind)
without the written consent of the Publisher. Duplication or dissemination of any
text, code, simulations, images, etc. contained herein is limited to and subject to
licensing terms for the respective products, and permission must be obtained from
the Publisher or the owner of the content, etc., in order to reproduce or network any
portion of the textual material (in any media) that is contained in the Work.
MERCURY LEARNING AND INFORMATION (“MLI” or “the Publisher”) and anyone
involved in the creation, writing, or production of the companion disc, accompanying
algorithms, code, or computer programs (“the software”), and any accompanying
Web site or software of the Work, cannot and do not warrant the performance
or results that might be obtained by using the contents of the Work. The author,
developers, and the Publisher have used their best efforts to insure the accuracy
and functionality of the textual material and/or programs contained in this package;
we, however, make no warranty of any kind, express or implied, regarding the
performance of these contents or programs. The Work is sold “as is” without
warranty (except for defective materials used in manufacturing the book or due to
faulty workmanship).
The author, developers, and the publisher of any accompanying content, and anyone
involved in the composition, production, and manufacturing of this work will not be
liable for damages of any kind arising out of the use of (or the inability to use) the
algorithms, source code, computer programs, or textual material contained in this
publication. This includes, but is not limited to, loss of revenue or profit, or other
incidental, physical, or consequential damages arising out of the use of this Work.
The sole remedy in the event of a claim of any kind is expressly limited to replacement
of the book and/or disc, and only at the discretion of the Publisher. The use of
“implied warranty” and certain “exclusions” vary from state to state, and might not
apply to the purchaser of this product.
MICROSOFT®
EXCEL® PROGRAMMING
Pocket Primer
Julitta Korol
M L I
ERCURY EARNING AND NFORMATION
Dulles, Virginia
Boston, Massachusetts
New Delhi
Copyright ©2015 by MERCURY LEARNING AND INFORMATION LLC. All rights reserved.
This publication, portions of it, or any accompanying software may not be reproduced in any
way, stored in a retrieval system of any type, or transmitted by any means, media, electronic
display or mechanical display, including, but not limited to, photocopy, recording, Internet
postings, or scanning, without prior permission in writing from the publisher.
Publisher: David Pallai
MERCURY LEARNING AND INFORMATION
22841 Quicksilver Drive
Dulles, VA 20166
[email protected]
www.merclearning.com
800-232-0223
J.Korol. Microsoft Excel Programming Pocket Primer.
ISBN: 978-1-942270-01-0
The publisher recognizes and respects all marks used by companies, manufacturers, and
developers as a means to distinguish their products. All brand names and product names
mentioned in this book are trademarks or service marks of their respective companies. Any
omission or misuse (of any kind) of service marks or trademarks, etc. is not an attempt to
infringe on the property of others.
Library of Congress Control Number: 2015935261
1516321 Printed in the United States of America
This book is printed on acid-free paper.
Our titles are available for adoption, license, or bulk purchase by institutions, corporations, etc.
For additional information, please contact the Customer Service Dept. at 800-232-0223 (toll free).
All of our titles are available in digital format at authorcloudware.com and other digital
vendors. Companion files (figures and code listings) for this title are available by contacting
[email protected]. The sole obligation of MERCURY LEARNING AND INFORMATION to the
purchaser is to replace the disc, based on defective materials or faulty workmanship, but not
based on the operation or functionality of the product.
For Jola
Start at the beginning and see where it
takes you…But make sure you have a plan.

CONTENTS
Dedication .............................................................................................v
Acknowledgments ................................................................................xv
Introduction ......................................................................................xvii
1. Getting Started with Excel VBA ...........................................1
Introduction........................................................................................................1
Macros and VBA ................................................................................................2
Excel Macro-Enabled File Formats .........................................................2
Macro Security Settings ............................................................................3
Enabling the Developer Tab in Excel ...............................................................5
Hands-On 1.1. Setting Up Excel for Macro Development ...............................................5
Using the Built-In Macro Recorder ..................................................................7
Planning a Macro ......................................................................................7
Hands-On 1.2. Getting Things Ready for Macro Recording .................................8
Recording a Macro ....................................................................................8
Hands-On 1.3. Recording a Macro that Applies Formatting to a Worksheet .......8
Using Relative or Absolute References in Macros ........................10
Hands-On 1.4. Recording a Macro that Removes Formatting
from a Worksheet .....................................................................................11
Editing a Macro.......................................................................................12
Hands-On 1.5. Examining the Macro Code .........................................................12
Macro Comments ..........................................................................15
Hands-On 1.6. Adding Comments to the Macro Code ..............................16
Cleaning Up the Macro Code ........................................................17
Hands-On 1.7. Cleaning Up the Macro Code ............................................17
Running a Macro .....................................................................................18
Hands-On 1.8. Running a Macro Using the Macro Dialog Box ..........................19
Testing and Debugging a Macro ............................................................19
viii • Microsoft Excel Programming Pocket Primer
Hands-On 1.9. Running a Macro from the VBE Screen ......................................19
Saving and Renaming a Macro ...............................................................20
Hands-On 1.10. Saving Macros and Running Macros from Another
Workbook...........................................................................................................20
Printing Macro Code ..............................................................................21
Hands-On 1.11. Printing Macro Code .................................................................21
Improving Your Recorded Macros ..................................................................21
Hands-On 1.12. Recording Additional Features for the Existing Macro
in a New Macro ...........................................................................................................22
Hands-On 1.13. Adding Additional Code to the Existing Macro ..................................23
Hands-On 1.14. Adding Visual Basic Statements to the Recorded Macro Code ..........24
Various Methods of Running Macros ..............................................................24
Running the Macro Using a Keyboard Shortcut ...........................25
Hands-On 1.15. Assigning a Macro to a Keyboard Shortcut .....................25
Running the Macro from the Quick Access Toolbar ....................26
Hands-On 1.16. Running a Macro from the Quick Access Toolbar ...........26
Running the Macro from a Worksheet Button .............................28
Hands-On 1.17. Running a Macro from a Button Placed on a Worksheet ....28
Summary ...........................................................................................................30
2. Getting to Know Visual Basic Editor (VBE) .......................31
Understanding the Project Explorer Window ................................................31
Understanding the Properties Window ...........................................................33
Understanding the Code Window ...................................................................33
Setting the VBE Options .................................................................................34
Syntax and Programming Assistance ...............................................................35
List Properties/Methods .........................................................................36
List Constants ..........................................................................................36
Parameter Info ........................................................................................37
Quick Info................................................................................................38
Complete Word .......................................................................................38
Indent/Outdent .......................................................................................39
Hands-On 2.1. Indenting/Outdenting Visual Basic Code ...................................39
Comment Block/Uncomment Block ......................................................39
Using the Object Browser ................................................................................40
Hands-On 2.2. Writing a VBA Procedure to Move a Text Box on the Worksheet ........42
Hands-On 2.3. Writing a VBA Procedure to Move a Circle on the Worksheet ............44
Locating Procedures with the Object Browser ......................................45
Hands-On 2.4. Using Object Browser to Locate VBA Procedures ......................45
Using the VBA Object Library ........................................................................46
Hands-On 2.5. Writing a VBA Procedure to Create a Folder in Windows ..................46
Using the Immediate Window .........................................................................47
Hands-On 2.6. Entering and Executing VBA Statements in
the Immediate Window ...............................................................................................48
Obtaining Information in the Immediate Window................................50
Hands-On 2.7. Obtaining Information in the Immediate Window .....................50
Contents • ix
Working with Worksheet Cells and Ranges ....................................................51
Using the Range Property ......................................................................52
Hands-On 2.8. Practice Using the Range Property to Select Worksheet Cells ...52
Using the Cells Property .........................................................................52
Hands-On 2.9. Practice Using the Cells Property to
Select Worksheet Cells (Part I) .........................................................................52
Hands-On 2.10. Practice Using the Cells Property to Select Worksheet Cells
(Part II) ..............................................................................................................53
Using the Offset Property .......................................................................53
Hands-On 2.11. Selecting Cells Using the Offset Property .................................54
Using the Resize Property ......................................................................54
Hands-On 2.12. Writing a VBA Statement to Resize a Selection of Cells ...........54
Using the End Property ..........................................................................56
Hands-On 2.13. Selecting Cells Using the End Property ....................................56
Moving, Copying, and Deleting Cells ....................................................56
Hands-On 2.14. Moving, Copying, and Deleting Cells .......................................56
Working with Rows and Columns ...................................................................57
Hands-On 2.15. Selecting Entire Rows and Columns ...................................................57
Obtaining Information about the Worksheet .........................................58
Hands-On 2.16. Counting Rows and Columns ....................................................58
Entering Data and Formatting Cells ...............................................................58
Hands-On 2.17. Using VBA Statements to Enter Data in a Worksheet ........................58
Returning Information Entered in a Worksheet ...................................58
Finding Out about Cell Formatting .......................................................59
Working with Workbooks and Worksheets .....................................................60
Hands-On 2.18. Working with Workbooks ....................................................................60
Hands-On 2.19. Working with Worksheets ....................................................................61
Working with Windows ....................................................................................61
Hands-On 2.20. Working with Windows .......................................................................62
Working with the Excel Application ................................................................62
Hands-On 2.21. Working with the Excel Application ....................................................63
Summary ...........................................................................................................63
3. Excel VBA Fundamentals ....................................................65
Excel Objects, Properties, and Methods .........................................................65
Microsoft Excel Object Model ........................................................................67
Writing Simple and Complex VBA Statements ..............................................68
Breaking Up Long VBA Statements ......................................................71
Saving Results of VBA Statements ..................................................................72
Introducing Data Types ...................................................................................72
Using Variables .................................................................................................75
How to Create Variables .........................................................................76
How to Declare Variables .......................................................................76
Specifying the Data Type of a Variable ..................................................79
Assigning Values to Variables ..................................................................81
Hands-On 3.1. Writing a VBA Procedure with Variables ...................................81