Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET, Second Edition / Edition 2

Other Format (Print)
Buy New
Buy New from BN.com
$39.42
Used and New from Other Sellers
Used and New from Other Sellers
from $17.99
Usually ships in 1-2 business days
(Save 72%)
Other sellers (Other Format)
  • All (13) from $17.99   
  • New (8) from $36.01   
  • Used (5) from $17.99   

Overview

“As Excel applications become more complex and the Windows development platform more powerful, Excel developers need books like this to help them evolve their solutions to the next level of sophistication. Professional Excel Development is a book for developers who want to build powerful, state-of-the-art Excel applications using the latest Microsoft technologies.”

–Gabhan Berry, Program Manager, Excel Programmability, Microsoft

“The first edition of Professional Excel Development is my most-consulted and most-recommended book on Office development. The second edition expands both the depth and range. It shines because it takes every issue one step further than you expect. The book relies on the authors’ current, real-world experience to cover not only how a feature works, but also the practical implications of using it in professional work.”

–Shauna Kelly, Director, Thendara Green

“This book illustrates techniques that will result in well-designed, robust, and maintainable Excel-based applications. The authors’ advice comes from decades of solid experience of designing and building applications. The practicality of the methods is well illustrated by the example timesheet application that is developed step-by-step through the book. Every serious Excel developer should read this and learn from it. I did.”

Bill Manville, Application Developer, Bill Manville Associates

The Start-to-Finish Guide to Building State-of-the-Art Solutions with Excel 2007

In this book, four world-class Microsoft® Excel developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors—three of whom have been honored by Microsoft as Excel Most Valuable Professionals (MVPs)—show how to consistently make the right design decisions and make the most of Excel’s most powerful new features. Using their techniques,you can reduce development costs, time to market, and hassle—and build more effective, successful solutions.

Fully updated for Excel 2007, this book starts where other books on Excel programming leave off. Through a hands-on case study project, you’ll discover best practices for planning, architecting, and building Excel applications that are robust, secure, easy to maintain, and highly usable. If you’re a working developer, no other book on Excel programming offers you this much depth, insight, or value.

• Design worksheets that will be more useful and reliable

• Leverage built-in and application-specific add-ins

• Construct applications that behave like independent Windows programs

• Make the most of the new Ribbon user interface

• Create cross-version applications that work with legacy versions of Excel

• Utilize XML within Excel applications

• Understand and use Windows API calls

• Master VBA error handling, debugging, and performance optimization

• Develop applications based on data stored in Access, SQL Server, and other databases

• Build powerful visualization solutions with Excel charting engine

• Learn how to work with VB.NET and leverage its IDE

• Automate Microsoft Excel with VB.NET

• Create managed COM add-ins for Microsoft Excel with VB.NET

• Develop Excel solutions with Visual Studio Tools for Office (VSTO)

• Integrate Excel with Web Services

• Deploy applications more securely and efficiently

Read More Show Less

Product Details

  • ISBN-13: 9780321508799
  • Publisher: Addison-Wesley
  • Publication date: 5/22/2009
  • Series: Addison-Wesley Microsoft Technology Series
  • Edition description: Second
  • Edition number: 2
  • Pages: 1176
  • Sales rank: 597,651
  • Product dimensions: 7.00 (w) x 9.00 (h) x 2.40 (d)

Meet the Author

Rob Bovey, President of Application Professionals, has developed several Excel add-ins shipped by Microsoft. He coauthored the Microsoft Excel 97 Developers Kit and Excel 2007 VBA Programmer’s Reference.

Dennis Wallentin has developed Excel solutions since the 1980s through his firm, XL-Dennis, based in Östersund, Sweden.

Stephen Bullen, coauthor of The Excel 2007 VBA Programmer’s Reference, owns Office Automation, Ltd., based in Essex, Ireland.

John Green owns Execuplan Consulting, a Sydney, Australia-based consultancy specializing in Excel and Access development.

Bovey, Bullen, and Green hold Microsoft’s prestigious Most Valuable Professional (MVP) honor.

Read More Show Less

Read an Excerpt

INTRODUCTIONIntroductionAbout This Book

Microsoft Excel is much more than just a spreadsheet. With the introduction of the Visual Basic Editor in Excel 97, followed by the significantly improved stability of Excel 2000, Excel became a respected development platform in its own right. Excel applications are now found alongside those based on C++, Java, and the .NET development platform, as part of the core suite of mission-critical corporate applications.

Unfortunately, Excel is still too often thought of as a hobbyist platform, that people only develop Excel applications in their spare time to automate minor tasks. A brief look at many Excel VBA books seems to confirm this opinion. These books focus on the basics of automating Excel tasks using VBA. This book is the first of its kind in providing a detailed explanation of how to use Excel as the platform for developing professional quality applications.

While most other major development platforms seem to have a de facto standard text that explains the commonly agreed best practices for architecting, designing, and developing applications using that platform, until now Excel has not. This book attempts to fill that gap. The authors are professional Excel developers who create Excel-based applications for clients ranging from individuals to the largest multinational corporations. This book explains the approaches we use when designing, developing, distributing, and supporting the applications we write for our clients.

Who Should Read This Book

This is not a beginner-level book. If you do not already have a clear understanding of the core Excel object model and a basic understanding of Excel VBA development this is not the place to start. We assume that readers of this book have already read and (mostly) understood our Excel 2002 or 2007 VBA Programmer’s Reference, John Walkenbach’s Excel Power Programming, or similar titles. This book begins where other Excel VBA books end.

Owners of the first edition of Professional Excel Development have a different decision to make. Should you purchase the second edition? We have made numerous corrections and improvements throughout this edition as well as expanding it with over 300 pages of new material that you simply will not find anywhere else.

In the interest of full disclosure, however, we want to be very clear that the bulk of the new material is aimed at Excel developers who are working with Excel 2007 and Visual Studio 2008. If you own the first edition of this book and your primary focus is developing VBA applications in Excel 2003 and earlier, you will see incremental rather than revolutionary improvements in this edition. We don’t want to discourage you from upgrading to the second edition and would welcome it if you choose to do so. But most of all we want you to be satisfied with our work, so we state the pros and cons of upgrading honestly to help you make an informed decision.

Excel Developer Categories

Excel developers can be divided into five general categories based on their experience and knowledge of Excel and VBA. This book has something to offer each of them, but with a focus on the more advanced topics. Putting yourself into one of these categories might help you decide whether this is the right book for you.

Basic Excel users probably don’t think of themselves as developers at all. Excel is no more than a tool to help them get on with their job. They start off using Excel worksheets as a handy place to store lists or perform simple repetitive calculations. As they discover more Excel features their workbooks may begin to include more complex worksheet functions, pivot tables, and charts. There is little in this book for basic Excel users, although Chapter 4, “Worksheet Design,” details the best practices to use when designing and laying out a worksheet for data entry; Chapter 20, “Data Manipulation Techniques,” explains how to structure a worksheet and which functions and features to use to manipulate their lists; and Chapter 21, “Advanced Charting Techniques,” explains how to get the most from Excel’s chart engine. The techniques suggested in these chapters should help the basic Excel user avoid some of the pitfalls often encountered as their experience and the complexity of their worksheets increase.

Excel power users have a broad understanding of Excel’s functionality and they know which tool or function is best used in a given situation. Power users create complex workbooks for their own use and are often called on to help develop workbooks for their colleagues, or to identify why their colleagues’ workbooks don’t work as intended. Power users occasionally use snippets of VBA, either found on the Internet or created with the macro recorder, but struggle to adapt the code to their needs. As a result, their code tends to be messy, slow, and hard to maintain. While this book is not a VBA tutorial, power users have much to gain from following the best practices we suggest for both worksheets and code modules. Most of the chapters in the book are relevant to power users who have an interest in improving their Excel and VBA development skills.

VBA developers make extensive use of VBA code in their workbooks—often too much. They are typically either power users who started to learn VBA too early or Visual Basic developers who switched to Excel VBA development. While they may be proficient with VBA they believe every problem must have a VBA solution. They tend to lack the experience required to know when a problem is best solved using Excel, when a problem is best solved using VBA, and when the best solution is a combination of the two. Their solutions are often cumbersome, slow, and make poor use of the Excel object model. This book has much to offer VBA developers to improve their use of Excel itself, including best practices for designing worksheets and how to use Excel’s features for data entry, analysis, and presentation. The book also seeks to improve their Excel VBA development skills by introducing advanced coding techniques, detailing VBA best practices, and explaining how to improve VBA code performance.

Excel developers realize that the most efficient and maintainable applications are those that make the most of Excel’s built-in functionality, augmented by VBA where appropriate. They are confident in developing Excel-based applications for their colleagues or as part of an in-house development team. While their knowledge of Excel is put to good use in their applications, their design techniques tend to be limited, and they are reluctant to use other languages and applications to augment their Excel solutions. They have probably read John Walkenbach’s Excel 2003 or 2007 Power Programming and/or our own Excel 2002 or 2007 VBA Programmer’s Reference. Now they need a book to take them to the highest level of Excel application development—that of the professional developer. This is the book to do that.

Professional Excel developers design and develop for their clients or employer Excel-based applications and utilities that are robust, fast, easy to use, maintainable, and secure. While Excel forms the core of their solutions, they use other applications and languages where appropriate, including third-party ActiveX controls, Office automation, Windows API calls, external databases, various standalone programming languages, and

Excel as an Application Development Platform

If we look at Excel as a development platform rather than just a spreadsheet, we find that it provides five fundamental components we can use in our applications:

  • The worksheets, charts, and other objects used to create a user interface and presentation layer for data entry and reporting
  • The worksheets used as simple data stores for lists, tables, and other information required by our application
  • VBA code and UserForms for creating business logic and advanced user interfaces
  • Worksheet formulas used as a declarative programming language for high-performance numerical processing
  • The Excel object model, allowing programmatic control of (nearly) all of Excel’s functionality, both from within Excel and from outside it
The Worksheet as a Presentation Layer for Data Entry and Reporting

Most people think about Excel in terms of typing numbers into cells, having some calculations update, and seeing a result displayed in a different cell or on a chart. Without necessarily thinking in such terms, they are using the worksheet as a user interface for their data entry and reporting and are generally comfortable with these tasks. The in-cell editing, validation, and formatting features built in to Excel provide a rich and compelling data entry experience, while the charting, cell formatting, and drawing tools provide a presentation-quality reporting mechanism.

It is hard to imagine the code that would be required if we tried to reproduce this experience using the tools available in most other development environments, yet Excel provides these features right out of the box for use in our Excel-based applications. The biggest problem we face is how to add structure to the free-form worksheet grid to present a simple and easy-to-use interface, while leveraging the rich functionality of Excel. Chapter 4 introduces some techniques and best practices for developing worksheet-based data entry forms, while Chapter 21 covers charting capabilities.

The Worksheet as a Simple Data Store

What is a worksheet when it’s never intended to be shown to the end user? At its simplest, it’s no more than a large grid of cells in which we can store just about anything we want, including numbers, text, lists, tables, and pictures. Most applications use some amount of static data or graphical resources. Storing that information in a worksheet makes it both easy to access using VBA and simple to maintain. Lists and tables in worksheets can directly feed Excel’s data validation feature (as shown in Chapter 4), greatly simplify the creation and maintenance of command bars (Chapter 8, “Advanced Command Bar Handling”), and allow us to construct dynamic UserForms (Chapter 13, “UserForm Design and Best Practices”).

VBA Code and UserForms

We expect most readers of this book have at least some familiarity with VBA. If not, we suggest you read one of the resources mentioned at the beginning of this chapter before continuing much further. Many people see the “A” in VBA as meaning the language is somehow less than Visual Basic itself. In fact, both VB6 and Office use exactly the same DLL to provide the keywords, syntax, and statements we program with.

Most beginner and intermediate VBA developers use VBA as a purely procedural language, with nearly all their code residing in standard modules. VBA also allows us to create applications using an object oriented programming (OOP) approach, in which class modules are used to create our own objects. Chapter 7, “Using Class Modules to Create Objects,” and Chapter 14, “Interfaces,” explain how to use VBA in this manner, while basic OOP concepts (such as encapsulation) are used throughout the book.

Most of this book is dedicated to explaining advanced VBA techniques and a professional approach to application design and development that can put VBA in Excel on par with, and sometimes in front of, VB6 or VB.Net for application development. In Chapters 23 through 26 we show that Excel developers can achieve the best of both worlds by combining Excel with VB6 or VB.Net in a seamless application.

The Worksheet as a Declarative Programming Language

Take the following code:

dSales = 1000dPrice = 10.99dRevenue = dSales
• dPrice

That could easily be a few lines of VBA. We give the variable dSales a value of 1000, the variable dPrice a value of 10.99, and then calculate the revenue as sales times price. If we change the names of the variables and adjust the spacing, the same code could also be written as

D1 =1000D2 =10.99D3 =D1*D2

This looks much more like worksheet cell addresses and formulas than lines of VBA code, showing that worksheet formulas are in fact a programming language of their own if we choose to think of it in those terms. The IF() worksheet function is directly equivalent to the If...Then...Else VBA statement, while the judicious use of circular references and iteration can be equivalent to either the For...Next or Do...Loop structures.

Instead of stating a set of operations that are executed line-by-line, we “program” in this language by making a set of declarations (by typing formulas and values into worksheet cells), in any order we want:

“D3 is the product of D1 and D2”

“D1 has the value 1000”

“D2 has the value 10.99”

To “run” this program, Excel first examines all the declarations and builds a precedence tree to identify which cells depend on the results of which other cells and thereby determine the most efficient order in which the cells must be calculated. The same precedence tree is also used to identify the minimum set of calculations that must be performed whenever the value in a cell is changed. The result is a calculation engine that is vastly more efficient than an equivalent VBA program, and one that should be used whenever complex numerical computations are required in your application.

Microsoft Excel is unique among application development platforms in providing both a procedural (VBA) and a declarative (worksheet functions) programming language. The most efficient Excel application is one that makes appropriate use of both these languages.

It is assumed the reader of this book has a basic understanding of worksheet functions, so Chapter 20 focuses on using advanced worksheet functions (including best-practice suggestions for handling circular references) and Excel’s other data analysis features.

The Excel Object Model

While the other four components of the Excel platform are invaluable in the development of applications, it is probably the rich Excel object model that provides the most compelling reason to base our applications in Excel. Almost everything that can be accomplished through the Excel user interface can also be accomplished programmatically using the objects in the Excel object model. (Accessing the list of number formats and applying a digital signature to a workbook are perhaps the most notable exceptions.)

The vast feature set exposed by these objects makes many complex applications fairly simple to develop. Unlike most other development platforms, there is no need to figure out how to program these features from scratch. Excel provides them ready-made, so all we need to do is determine how to plug them together most effectively. This book does not attempt to explore and document every obscure niche of the Excel object model. Instead, we demonstrate the best way to use the objects we most commonly use in our own application development.

Structure

Over the course of this book we cover both the concepts and details of each topic and apply those concepts to a time sheet reporting and analysis application that we will build in stages as we move along. The chapters are therefore arranged approximately in the order in which we would design and develop an Excel application:

  • Chapter 2 discusses the different styles of application we might choose to create.
  • Chapter 3 identifies some general best practices for working with Excel and VBA. These are followed throughout the book.
  • Chapter 4 explains how to design and structure a worksheet for data entry and analysis.
  • Chapters 5 and 6 introduce two specific types of application—the add-in and the dictator application, which form the basis of our time sheet reporting and analysis application.
  • Chapter 7 introduces the use of class modules in our Excel applications.
  • Chapters 8 to 11 discuss topics relevant to building command bar and Ribbon user interfaces as well as designing applications that must run in all current Excel versions using a single code base.
  • Chapters 12 to 17 discuss advanced techniques for a range of VBA topics.
  • Chapters 18 and 19 cover database development for Excel developers.
  • Chapters 20 and 21 explain how to efficiently use Excel’s features to analyze data and present results.
  • Chapters 22 to 27 look outside Excel, by explaining how to automate other applications and extend Excel with Visual Basic 6, VB.NET, and C.
  • Chapter 28 focuses on how Excel applications can make use of Web Services.
  • Chapter 29 completes the development by explaining how to provide help for, secure, and deploy an Excel application.
Examples

As mentioned previously, throughout the book, we illustrate the concepts and techniques we introduce by building a time sheet data entry, consolidation, analysis, and reporting application. This consists of a data entry template to be completed by each employee, with the data sent to a central location for consolidation, analysis, and reporting. At the end of most chapters we show an updated working example of the application that incorporates ideas presented in those chapters, so the application grows steadily more complex as the book progresses.

In Chapter 4, we start with a simple data entry workbook and assume that each employee would e-mail the completed file to a manager who would analyze the results manually—a typical situation for a company with just a few employees.

By the end of the book, the data entry workbook will use

Along the way we rewrite some parts of the application in a number of different ways to show how easy it can be to include other languages and delivery mechanisms in our Excel-based applications. Most chapters also include specific concept examples to illustrate key points that are important to understand but would be too artificial if forced into the architecture of our time sheet application.

Supported Versions of Excel

When we develop an Excel application for a client, that client’s upgrade policy usually determines the version of Excel we must use. Few clients agree to upgrade just so we can develop using the latest version of Excel unless there is a compelling business requirement that can only be satisfied by using features the latest version introduces. At the time of this writing, an extremely unscientific poll (based on postings to the Microsoft support newsgroups) seems to indicate the following approximate usage distribution for each current version of Excel:

Excel 2000

10%

Excel 2002

15%

Excel 2003

50%

Excel 2007

25%

There are still a small number of users on Excel 97 and earlier versions, but for various reasons we no longer consider these versions of Excel to be viable development platforms. We therefore decided to use Excel 2000 as our lowest supported version. Many features we discuss, especially when we cover

Typefaces

The following text styles are used in this book:

Menu items and dialog text are shown as Tools > Options > Calculation > Manual, where the “>” indicates navigation to a submenu or dialog tab.

Sub SomeCode() ‘Code listings are shown like thisEnd Sub

Code within the text of a paragraph is shown in a fixed-width font like Application.Calculation = xlManual.

Paths on the CD are shown as \Concepts\Ch14 - Interfaces.

New terms introduced or defined appear like this.

Important points or emphasized words appear like this.

On the CD

Most of the code listings shown in the book are also included in example workbooks on the accompanying CD. For clarity, the code shown in the printed examples may use shorter line lengths, reduced indent settings, fewer code comments, and less error handling than the corresponding code in the workbooks. The CD has three main directories, containing the following files:

  • \Tools contains a number of tools and utilities developed by the authors that we have found to be invaluable during our application development. The MustHaveTools.htm file contains details about each of these tools and links to other third-party utilities.
  • \Concepts has separate subdirectories for each chapter, each one containing example files to support the text of the chapter. For best results, we suggest you have these workbooks open while reading the corresponding chapter.
  • \Application has separate subdirectories for the chapters where we have updated our time sheet example application. These chapters end with a Practical Example section that explains the changes made to implement concepts introduced in that chapter.
Help and Support

By far the best place to go for help with any of your Excel development questions, whether related to this book or not, are the Microsoft support newsgroup archives maintained by Google at http://groups.google.com. A quick search of the archives is almost certain to find a question similar to yours, already answered by one of the many professional developers who volunteer their time helping out in the newsgroups, including all the authors of this book. On the rare occasions that the archives fail to answer your question, you’re welcome to ask it directly in the newsgroups by connecting a newsreader (such as Outlook Express) to msnews."microsoft."com and selecting an appropriate newsgroup, such as

http://microsoft.public.excel.misc for general Excel questions

http://microsoft.public.excel.programming for VBA-related questions

http://microsoft.public.excel.worksheet.functions for help with worksheet functions

For assistance with Excel and VB.NET integration issues we recommend the MSDN VSTO Web forum located here:

http://social.msdn.microsoft.com/Forums/en-US/vsto/threads/

A number of Web sites provide a great deal of information and free downloadable examples and utilities targeted towards the Excel developer, including

http://www.appspro.com

http://www.excelkb.com

http://www.oaltd.co.uk

http://peltiertech.com

http://www.cpearson.com

http://msdn.microsoft.com/office

The Professional Excel Development Web Site

As an experiment for the second edition of Professional Excel Development, we are introducing a new Web site to accompany the book at http://www.ProExcelDev.net.

As of this writing the site does not yet exist, so it is difficult to say exactly what you will find there. However, at a minimum you will find the latest corrections, bug fixes, and clarifications related to this book. Our hope is to eventually expand the site to provide more in-depth coverage of popular topics than we were able to fit into our publishing deadline as well as blogs and possibly even interactive technical forums.

Feedback

We have tried very hard to present the information in this book in a clear and concise manner, explaining both the concepts and details needed to get things working as well as providing working examples of everything we cover. We have tried to provide sufficient information to enable you to apply these techniques in your own applications without getting bogged down in line-by-line explanations of entire code listings.

We’d like to think we’ve been successful in our attempt, but we encourage you to let us know what you think. Constructive criticism is always welcomed, as are suggestions for topics you think we may have overlooked. Please send feedback to the following authors:

Rob Bovey: robbovey@appspro.com

Dennis Wallentin: dennis@excelkb.com

© Copyright Pearson Education. All rights reserved.

Read More Show Less

Table of Contents

Acknowledgments . . . xiv

About the Authors . . . xv

Chapter 1 Introduction

About This Book . . . 1

Who Should Read This Book . . . 2

Excel Developer Categories . . . 2

Excel as an Application Development Platform . . . 4

Structure . . . 7

Examples . . . 8

Supported Versions of Excel . . . 9

Typefaces . . . 10

On the CD . . . 10

Help and Support . . . 11

The Professional Excel Development Web Site . . . 12

Feedback . . . 12

Chapter 2 Application Architectures

Concepts . . . 13

Chapter 3 Excel and VBA Development Best Practices

Naming Conventions . . . 27

Best Practices for Application Structure and Organization . . . 40

General Application Development Best Practices . . . 45

Chapter 4 Worksheet Design

Principles of Good Worksheet UI Design . . . 69

Program Rows and Columns: The Fundamental UI Design Technique . . . 70

Defined Names . . . 71

Styles . . . 78

User Interface Drawing Techniques . . . 83

Data Validation . . . 88

Conditional Formatting . . . 92

Using Controls on Worksheets . . . 98

Practical Example . . . 100

Chapter 5 Function, General, and Application-Specific Add-ins

The Four Stages of an Application . . . 107

Function Library Add-ins . . . 110

General Add-ins . . . 117

Application-Specific Add-ins . . . 118

Practical Example . . . 125

Chapter 6 Dictator Applications

Structure of a Dictator Application . . . 141

Practical Example . . . 157

Chapter 7 Using Class Modules to Create Objects

Creating Objects . . . 166

Creating a Collection . . . 170

Trapping Events . . . 177

Raising Events . . . 180

Practical Example . . . 188

Chapter 8 Advanced Command Bar Handling

Command Bar Design . . . 198

Table-Driven Command Bars . . . 199

Putting It All Together . . . 219

Loading Custom Icons from Files . . . 228

Hooking Command Bar Control Events . . . 232

Practical Example . . . 241

Chapter 9 Introduction to XML

XML . . . 249

Chapter 10 The Office 2007 Ribbon User Interface

The RibbonX Paradigm . . . 273

An Introduction to the Office 2007 Open XML File Format . . . 274

Ribbon Design and Coding Best Practices . . . 278

Table-Driven Ribbon UI Customization . . . 289

Advanced Problem Solving . . . 291

Further Reading . . . 300

Related Portals . . . 300

Chapter 11 Creating Cross-Version Applications

Command Bar and Ribbon User Interfaces in a Single Application . . . 304

Other Excel 2007 Development Issues . . . 319

Windows Vista Security and Folder Structure . . . 326

Chapter 12 Understanding and Using Windows API Calls Overview . . . 331

Working with the Screen . . . 337

Working with Windows . . . 340

Working with the Keyboard . . . 349

Working with the File System and Network . . . 355

Practical Examples . . . 369

Chapter 13 UserForm Design and Best Practices

Principles . . . 375

Control Fundamentals . . . 384

Visual Effects . . . 392

UserForm Positioning and Sizing . . . 400

Wizards . . . 407

Dynamic UserForms . . . 411

Modeless UserForms . . . 419

Control Specifics . . . 425

Practical Example . . . 432

Chapter 14 Interfaces

What Is an Interface? . . . 433

Code Reuse . . . 435

Defining a Custom Interface . . . 437

Implementing a Custom Interface . . . 438

Using a Custom Interface . . . 440

Polymorphic Classes . . . 443

Improving Robustness . . . 448

Simplifying Development . . . 448

A Plug-in Architecture . . . 460

Practical Example . . . 462

Chapter 15 VBA Error Handling

Error Handling Concepts . . . 465

The Single Exit Point Principle . . . 475

Simple Error Handling . . . 475

Complex Project Error Handler Organization . . . 476

The Central Error Handler . . . 481

Error Handling in Classes and UserForms . . . 488

Putting It All Together . . . 490

Practical Example . . . 496

Chapter 16 VBA Debugging

Basic VBA Debugging Techniques . . . 507

The Immediate Window (Ctrl+G) . . . 517

The Call Stack (Ctrl+L) . . . 521

The Watch Window . . . 522

The Locals Window . . . 532

The Object Browser (F2) . . . 533

Creating and Running a Test Harness . . . 537

Using Assertions . . . 540

Debugging Shortcut Keys That Every Developer Should Know . . . 542

Chapter 17 Optimizing VBA Performance

Measuring Performance . . . 545

The PerfMon Utility . . . 546

Creative Thinking . . . 551

Macro-Optimization . . . 556

Micro-Optimization . . . 567

Chapter 18 Introduction to Database Development

An Introduction to Databases . . . 577

An Introduction to SQL . . . 594

Data Access with ADO . . . 598

Further Reading . . . 613

Chapter 19 Programming with Access and SQL Server

A Note on the Northwind Sample Database . . . 615

Designing the Data Access Tier . . . 616

Working with Microsoft Access Databases . . . 620

Working with Microsoft SQL Server Databases . . . 630

Upsizing from Access to SQL Server . . . 642

Further Reading . . . 647

Practical Example . . . 648

Chapter 20 Data Manipulation Techniques

Excel’s Data Structures . . . 661

Data Processing Features . . . 667

Advanced Functions . . . 678

Chapter 21 Advanced Charting Techniques

Fundamental Techniques . . . 687

VBA Techniques . . . 702

Chapter 22 Controlling Other Office Applications

Fundamentals . . . 709

The Primary Office Application Object Models . . . 725

Further Reading . . . 739

Practical Example . . . 740

Chapter 23 Excel and Visual Basic 6

A Hello World ActiveX DLL . . . 742

Why Use VB6 ActiveX DLLs in Excel VBA Projects . . . 758

In-Process Versus Out-of-Process . . . 774

Automating Excel from a VB6 EXE . . . 775

COM Add-ins . . . 783

A “Hello World” COM Add-in . . . 783

The Add-in Designer . . . 788

Installation Considerations . . . 790

The AddinInstance Events . . . 792

Command Bar Handling . . . 795

Why Use a COM Add-in? . . . 798

Automation Add-ins . . . 799

Practical Examples . . . 802

Chapter 24 Excel and VB.NET

.NET Framework Fundamentals . . . 818

Visual Basic.NET . . . 819

Debugging . . . 845

Useful Development Tools . . . 853

Automating Excel . . . 855

Resources in .NET Solutions . . . 863

Retrieving Data with ADO.NET . . . 864

Further Reading . . . 870

Additional Development Tools . . . 871

Q&A Forums . . . 871

Practical Example–PETRAS Report Tool .NET . . . 872

Chapter 25 Writing Managed COM Add-ins with VB.NET

Choosing a Development Toolset . . . 890

Creating a Managed COM Add-in . . . 891

Building the User Interface . . . 908

Creating Managed Automation Add-ins . . . 928

Manually Register and Unregister COM Add-ins . . . 940

Using Classes in VB.NET . . . 940

Using Classic ADO to Export Data to Excel . . . 948

Shimming COM Add-ins . . . 952

Related Blogs . . . 962

Additional Development Tools . . . 962

Practical Example–PETRAS Report Tool.NET . . . 963

Chapter 26 Developing Excel Solutions with Visual Studio Tools for Office System (VSTO)

What Is VSTO? . . . 976

When Should You Use VSTO? . . . 983

Working with VSTO Add-Ins . . . 985

Working with VSTO Templates and Workbook Solutions . . . 1006

Deployment and Security . . . 1016

Further Reading . . . 1026

Related Portal and Blogs . . . 1026

Additional Development Tools . . . 1026

Chapter 27 XLLs and the C API

Why Create an XLL-Based Worksheet Function . . . 1029

Creating an XLL Project in Visual Studio . . . 1030

The Structure of an XLL . . . 1034

The XLOPER and OPER Data Types . . . 1044

The Excel4 Function . . . 1050

Commonly Used C API Functions . . . 1052

XLOPERs and Memory Management . . . 1053

Registering and Unregistering Custom Worksheet Functions . . . 1054

Sample Application Function . . . 1057

Debugging the Worksheet Functions . . .1060

Miscellaneous Topics . . .1061

Additional Resources . . . 1062

Chapter 28 Excel and Web Services

Web Services . . . 1065

Practical Example . . . 1072

Chapter 29 Providing Help, Securing, Packaging, and Distributing

Providing Help . . . 1085

Securing . . . 1094

Packaging . ..1099

Distributing . . . 1104

Index . . . 1107

Read More Show Less

Preface

Introduction

About This Book

Microsoft Excel is much more than just a spreadsheet. With the introduction of the Visual Basic Editor in Excel 97, followed by the significantly improved stability of Excel 2000, Excel became a respected development platform in its own right. Excel applications are now found alongside those based on C++, Java, and the .NET development platform, as part of the core suite of mission-critical corporate applications.

Unfortunately, Excel is still too often thought of as a hobbyist platform, that people only develop Excel applications in their spare time to automate minor tasks. A brief look at many Excel VBA books seems to confirm this opinion. These books focus on the basics of automating Excel tasks using VBA. This book is the first of its kind in providing a detailed explanation of how to use Excel as the platform for developing professional quality applications.

While most other major development platforms seem to have a de facto standard text that explains the commonly agreed best practices for architecting, designing, and developing applications using that platform, until now Excel has not. This book attempts to fill that gap. The authors are professional Excel developers who create Excel-based applications for clients ranging from individuals to the largest multinational corporations. This book explains the approaches we use when designing, developing, distributing, and supporting the applications we write for our clients.

Who Should Read This Book

This is not a beginner-level book. If you do not already have a clear understanding of the core Excel object model and a basic understanding of Excel VBA development this is not the place to start. We assume that readers of this book have already read and (mostly) understood our Excel 2002 or 2007 VBA Programmer’s Reference, John Walkenbach’s Excel Power Programming, or similar titles. This book begins where other Excel VBA books end.

Owners of the first edition of Professional Excel Development have a different decision to make. Should you purchase the second edition? We have made numerous corrections and improvements throughout this edition as well as expanding it with over 300 pages of new material that you simply will not find anywhere else.

In the interest of full disclosure, however, we want to be very clear that the bulk of the new material is aimed at Excel developers who are working with Excel 2007 and Visual Studio 2008. If you own the first edition of this book and your primary focus is developing VBA applications in Excel 2003 and earlier, you will see incremental rather than revolutionary improvements in this edition. We don’t want to discourage you from upgrading to the second edition and would welcome it if you choose to do so. But most of all we want you to be satisfied with our work, so we state the pros and cons of upgrading honestly to help you make an informed decision.

Excel Developer Categories

Excel developers can be divided into five general categories based on their experience and knowledge of Excel and VBA. This book has something to offer each of them, but with a focus on the more advanced topics. Putting yourself into one of these categories might help you decide whether this is the right book for you.

Basic Excel users probably don’t think of themselves as developers at all. Excel is no more than a tool to help them get on with their job. They start off using Excel worksheets as a handy place to store lists or perform simple repetitive calculations. As they discover more Excel features their workbooks may begin to include more complex worksheet functions, pivot tables, and charts. There is little in this book for basic Excel users, although Chapter 4, “Worksheet Design,” details the best practices to use when designing and laying out a worksheet for data entry; Chapter 20, “Data Manipulation Techniques,” explains how to structure a worksheet and which functions and features to use to manipulate their lists; and Chapter 21, “Advanced Charting Techniques,” explains how to get the most from Excel’s chart engine. The techniques suggested in these chapters should help the basic Excel user avoid some of the pitfalls often encountered as their experience and the complexity of their worksheets increase.

Excel power users have a broad understanding of Excel’s functionality and they know which tool or function is best used in a given situation. Power users create complex workbooks for their own use and are often called on to help develop workbooks for their colleagues, or to identify why their colleagues’ workbooks don’t work as intended. Power users occasionally use snippets of VBA, either found on the Internet or created with the macro recorder, but struggle to adapt the code to their needs. As a result, their code tends to be messy, slow, and hard to maintain. While this book is not a VBA tutorial, power users have much to gain from following the best practices we suggest for both worksheets and code modules. Most of the chapters in the book are relevant to power users who have an interest in improving their Excel and VBA development skills.

VBA developers make extensive use of VBA code in their workbooks—often too much. They are typically either power users who started to learn VBA too early or Visual Basic developers who switched to Excel VBA development. While they may be proficient with VBA they believe every problem must have a VBA solution. They tend to lack the experience required to know when a problem is best solved using Excel, when a problem is best solved using VBA, and when the best solution is a combination of the two. Their solutions are often cumbersome, slow, and make poor use of the Excel object model. This book has much to offer VBA developers to improve their use of Excel itself, including best practices for designing worksheets and how to use Excel’s features for data entry, analysis, and presentation. The book also seeks to improve their Excel VBA development skills by introducing advanced coding techniques, detailing VBA best practices, and explaining how to improve VBA code performance.

Excel developers realize that the most efficient and maintainable applications are those that make the most of Excel’s built-in functionality, augmented by VBA where appropriate. They are confident in developing Excel-based applications for their colleagues or as part of an in-house development team. While their knowledge of Excel is put to good use in their applications, their design techniques tend to be limited, and they are reluctant to use other languages and applications to augment their Excel solutions. They have probably read John Walkenbach’s Excel 2003 or 2007 Power Programming and/or our own Excel 2002 or 2007 VBA Programmer’s Reference. Now they need a book to take them to the highest level of Excel application development—that of the professional developer. This is the book to do that.

Professional Excel developers design and develop for their clients or employer Excel-based applications and utilities that are robust, fast, easy to use, maintainable, and secure. While Excel forms the core of their solutions, they use other applications and languages where appropriate, including third-party ActiveX controls, Office automation, Windows API calls, external databases, various standalone programming languages, and XML. This book teaches all of those skills. If you are already a professional Excel developer, you will know that learning never stops and will appreciate the knowledge and best practices presented in this book by four of your peers.

Excel as an Application Development Platform

If we look at Excel as a development platform rather than just a spreadsheet, we find that it provides five fundamental components we can use in our applications:

  • The worksheets, charts, and other objects used to create a user interface and presentation layer for data entry and reporting
  • The worksheets used as simple data stores for lists, tables, and other information required by our application
  • VBA code and UserForms for creating business logic and advanced user interfaces
  • Worksheet formulas used as a declarative programming language for high-performance numerical processing
  • The Excel object model, allowing programmatic control of (nearly) all of Excel’s functionality, both from within Excel and from outside it

The Worksheet as a Presentation Layer for Data Entry and Reporting

Most people think about Excel in terms of typing numbers into cells, having some calculations update, and seeing a result displayed in a different cell or on a chart. Without necessarily thinking in such terms, they are using the worksheet as a user interface for their data entry and reporting and are generally comfortable with these tasks. The in-cell editing, validation, and formatting features built in to Excel provide a rich and compelling data entry experience, while the charting, cell formatting, and drawing tools provide a presentation-quality reporting mechanism.

It is hard to imagine the code that would be required if we tried to reproduce this experience using the tools available in most other development environments, yet Excel provides these features right out of the box for use in our Excel-based applications. The biggest problem we face is how to add structure to the free-form worksheet grid to present a simple and easy-to-use interface, while leveraging the rich functionality of Excel. Chapter 4 introduces some techniques and best practices for developing worksheet-based data entry forms, while Chapter 21 covers charting capabilities.

The Worksheet as a Simple Data Store

What is a worksheet when it’s never intended to be shown to the end user? At its simplest, it’s no more than a large grid of cells in which we can store just about anything we want, including numbers, text, lists, tables, and pictures. Most applications use some amount of static data or graphical resources. Storing that information in a worksheet makes it both easy to access using VBA and simple to maintain. Lists and tables in worksheets can directly feed Excel’s data validation feature (as shown in Chapter 4), greatly simplify the creation and maintenance of command bars (Chapter 8, “Advanced Command Bar Handling”), and allow us to construct dynamic UserForms (Chapter 13, “UserForm Design and Best Practices”).

VBA Code and UserForms

We expect most readers of this book have at least some familiarity with VBA. If not, we suggest you read one of the resources mentioned at the beginning of this chapter before continuing much further. Many people see the “A” in VBA as meaning the language is somehow less than Visual Basic itself. In fact, both VB6 and Office use exactly the same DLL to provide the keywords, syntax, and statements we program with.

Most beginner and intermediate VBA developers use VBA as a purely procedural language, with nearly all their code residing in standard modules. VBA also allows us to create applications using an object oriented programming (OOP) approach, in which class modules are used to create our own objects. Chapter 7, “Using Class Modules to Create Objects,” and Chapter 14, “Interfaces,” explain how to use VBA in this manner, while basic OOP concepts (such as encapsulation) are used throughout the book.

Most of this book is dedicated to explaining advanced VBA techniques and a professional approach to application design and development that can put VBA in Excel on par with, and sometimes in front of, VB6 or VB.Net for application development. In Chapters 23 through 26 we show that Excel developers can achieve the best of both worlds by combining Excel with VB6 or VB.Net in a seamless application.

The Worksheet as a Declarative Programming Language

Take the following code:

dSales = 1000dPrice = 10.99dRevenue = dSales
• dPrice

That could easily be a few lines of VBA. We give the variable dSales a value of 1000, the variable dPrice a value of 10.99, and then calculate the revenue as sales times price. If we change the names of the variables and adjust the spacing, the same code could also be written as

D1 =1000D2 =10.99D3 =D1*D2

This looks much more like worksheet cell addresses and formulas than lines of VBA code, showing that worksheet formulas are in fact a programming language of their own if we choose to think of it in those terms. The IF() worksheet function is directly equivalent to the If...Then...Else VBA statement, while the judicious use of circular references and iteration can be equivalent to either the For...Next or Do...Loop structures.

Instead of stating a set of operations that are executed line-by-line, we “program” in this language by making a set of declarations (by typing formulas and values into worksheet cells), in any order we want:

“D3 is the product of D1 and D2”

“D1 has the value 1000”

“D2 has the value 10.99”

To “run” this program, Excel first examines all the declarations and builds a precedence tree to identify which cells depend on the results of which other cells and thereby determine the most efficient order in which the cells must be calculated. The same precedence tree is also used to identify the minimum set of calculations that must be performed whenever the value in a cell is changed. The result is a calculation engine that is vastly more efficient than an equivalent VBA program, and one that should be used whenever complex numerical computations are required in your application.

Microsoft Excel is unique among application development platforms in providing both a procedural (VBA) and a declarative (worksheet functions) programming language. The most efficient Excel application is one that makes appropriate use of both these languages.

It is assumed the reader of this book has a basic understanding of worksheet functions, so Chapter 20 focuses on using advanced worksheet functions (including best-practice suggestions for handling circular references) and Excel’s other data analysis features.

The Excel Object Model

While the other four components of the Excel platform are invaluable in the development of applications, it is probably the rich Excel object model that provides the most compelling reason to base our applications in Excel. Almost everything that can be accomplished through the Excel user interface can also be accomplished programmatically using the objects in the Excel object model. (Accessing the list of number formats and applying a digital signature to a workbook are perhaps the most notable exceptions.)

The vast feature set exposed by these objects makes many complex applications fairly simple to develop. Unlike most other development platforms, there is no need to figure out how to program these features from scratch. Excel provides them ready-made, so all we need to do is determine how to plug them together most effectively. This book does not attempt to explore and document every obscure niche of the Excel object model. Instead, we demonstrate the best way to use the objects we most commonly use in our own application development.

Structure

Over the course of this book we cover both the concepts and details of each topic and apply those concepts to a time sheet reporting and analysis application that we will build in stages as we move along. The chapters are therefore arranged approximately in the order in which we would design and develop an Excel application:

  • Chapter 2 discusses the different styles of application we might choose to create.
  • Chapter 3 identifies some general best practices for working with Excel and VBA. These are followed throughout the book.
  • Chapter 4 explains how to design and structure a worksheet for data entry and analysis.
  • Chapters 5 and 6 introduce two specific types of application—the add-in and the dictator application, which form the basis of our time sheet reporting and analysis application.
  • Chapter 7 introduces the use of class modules in our Excel applications.
  • Chapters 8 to 11 discuss topics relevant to building command bar and Ribbon user interfaces as well as designing applications that must run in all current Excel versions using a single code base.
  • Chapters 12 to 17 discuss advanced techniques for a range of VBA topics.
  • Chapters 18 and 19 cover database development for Excel developers.
  • Chapters 20 and 21 explain how to efficiently use Excel’s features to analyze data and present results.
  • Chapters 22 to 27 look outside Excel, by explaining how to automate other applications and extend Excel with Visual Basic 6, VB.NET, and C.
  • Chapter 28 focuses on how Excel applications can make use of Web Services.
  • Chapter 29 completes the development by explaining how to provide help for, secure, and deploy an Excel application.

Examples

As mentioned previously, throughout the book, we illustrate the concepts and techniques we introduce by building a time sheet data entry, consolidation, analysis, and reporting application. This consists of a data entry template to be completed by each employee, with the data sent to a central location for consolidation, analysis, and reporting. At the end of most chapters we show an updated working example of the application that incorporates ideas presented in those chapters, so the application grows steadily more complex as the book progresses.

In Chapter 4, we start with a simple data entry workbook and assume that each employee would e-mail the completed file to a manager who would analyze the results manually—a typical situation for a company with just a few employees.

By the end of the book, the data entry workbook will use XML to upload the data to a Web site, where it will be stored in a central database. The reporting application will extract the data from the database, perform various analyses, and present the results as reports in Excel worksheets and charts.

Along the way we rewrite some parts of the application in a number of different ways to show how easy it can be to include other languages and delivery mechanisms in our Excel-based applications. Most chapters also include specific concept examples to illustrate key points that are important to understand but would be too artificial if forced into the architecture of our time sheet application.

Supported Versions of Excel

When we develop an Excel application for a client, that client’s upgrade policy usually determines the version of Excel we must use. Few clients agree to upgrade just so we can develop using the latest version of Excel unless there is a compelling business requirement that can only be satisfied by using features the latest version introduces. At the time of this writing, an extremely unscientific poll (based on postings to the Microsoft support newsgroups) seems to indicate the following approximate usage distribution for each current version of Excel:

Excel 2000

10%

Excel 2002

15%

Excel 2003

50%

Excel 2007

25%

There are still a small number of users on Excel 97 and earlier versions, but for various reasons we no longer consider these versions of Excel to be viable development platforms. We therefore decided to use Excel 2000 as our lowest supported version. Many features we discuss, especially when we cover XML and the .NET development platform, are only supported in Excel 2002 or 2003 and higher. Whenever we discuss a feature that is only supported in a later version of Excel we state which version(s) it applies to.

Typefaces

The following text styles are used in this book:

Menu items and dialog text are shown as Tools > Options > Calculation > Manual, where the “>” indicates navigation to a submenu or dialog tab.

Sub SomeCode() ‘Code listings are shown like thisEnd Sub

Code within the text of a paragraph is shown in a fixed-width font like Application.Calculation = xlManual.

Paths on the CD are shown as \Concepts\Ch14 - Interfaces.

New terms introduced or defined appear like this.

Important points or emphasized words appear like this .

On the CD

Most of the code listings shown in the book are also included in example workbooks on the accompanying CD. For clarity, the code shown in the printed examples may use shorter line lengths, reduced indent settings, fewer code comments, and less error handling than the corresponding code in the workbooks. The CD has three main directories, containing the following files:

  • \Tools contains a number of tools and utilities developed by the authors that we have found to be invaluable during our application development. The MustHaveTools.htm file contains details about each of these tools and links to other third-party utilities.
  • \Concepts has separate subdirectories for each chapter, each one containing example files to support the text of the chapter. For best results, we suggest you have these workbooks open while reading the corresponding chapter.
  • \Application has separate subdirectories for the chapters where we have updated our time sheet example application. These chapters end with a Practical Example section that explains the changes made to implement concepts introduced in that chapter.

Help and Support

By far the best place to go for help with any of your Excel development questions, whether related to this book or not, are the Microsoft support newsgroup archives maintained by Google at http://groups.google.com. A quick search of the archives is almost certain to find a question similar to yours, already answered by one of the many professional developers who volunteer their time helping out in the newsgroups, including all the authors of this book. On the rare occasions that the archives fail to answer your question, you’re welcome to ask it directly in the newsgroups by connecting a newsreader (such as Outlook Express) to msnews. microsoft. com and selecting an appropriate newsgroup, such as

http://microsoft.public.excel.misc for general Excel questions

http://microsoft.public.excel.programming for VBA-related questions

http://microsoft.public.excel.worksheet.functions for help with worksheet functions

For assistance with Excel and VB.NET integration issues we recommend the MSDN VSTO Web forum located here:

http://social.msdn.microsoft.com/Forums/en-US/vsto/threads/

A number of Web sites provide a great deal of information and free downloadable examples and utilities targeted towards the Excel developer, including

http://www.appspro.com

http://www.excelkb.com

http://www.oaltd.co.uk

http://peltiertech.com

http://www.cpearson.com

http://msdn.microsoft.com/office

The Professional Excel Development Web Site

As an experiment for the second edition of Professional Excel Development, we are introducing a new Web site to accompany the book at http://www.ProExcelDev.net.

As of this writing the site does not yet exist, so it is difficult to say exactly what you will find there. However, at a minimum you will find the latest corrections, bug fixes, and clarifications related to this book. Our hope is to eventually expand the site to provide more in-depth coverage of popular topics than we were able to fit into our publishing deadline as well as blogs and possibly even interactive technical forums.

Feedback

We have tried very hard to present the information in this book in a clear and concise manner, explaining both the concepts and details needed to get things working as well as providing working examples of everything we cover. We have tried to provide sufficient information to enable you to apply these techniques in your own applications without getting bogged down in line-by-line explanations of entire code listings.

We’d like to think we’ve been successful in our attempt, but we encourage you to let us know what you think. Constructive criticism is always welcomed, as are suggestions for topics you think we may have overlooked. Please send feedback to the following authors:

Rob Bovey: robbovey@appspro.com

Dennis Wallentin: dennis@excelkb.com

© Copyright Pearson Education. All rights reserved.

Read More Show Less

Customer Reviews

Average Rating 2
( 6 )
Rating Distribution

5 Star

(1)

4 Star

(0)

3 Star

(0)

2 Star

(1)

1 Star

(4)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing all of 6 Customer Reviews
  • Posted September 25, 2009

    This book is tripe

    If you were to replace 'Professional' with 'Beginning' in the title, this book probably would have been worth two or three more stars. It would have also been nice if they didn't have 'VBA' and '.NET' in the subtitle. Do not get this book if you are hoping to find any ream meat about using VBA and/or .NET with excel. Most of the material devoded to '.NET' goes over the basics of VB.NET. There is very little about programming the Excel object model, which is sort of the whole point of using a .NET language with excel. The same is true about VBA; general stuff that does not have much to do with Excel. Yes, this might be useful for beginners, but professionals will likely either know the basics of VBA and/or '.NET', or have more complete references for these technologies. A professional isn't going to buy a book about Excel to learn the basics of VBA. The internet, or a basic book on VBA would be much greater choices. A professional will buy a book about Excel to learn about how to use VBA to automate chart creation and manipulation, automate actions in spreadsheets, etc.

    And by the way, if you buy this book, you will also be spending your hard earned money on a chapter about VB6, which largely repeats what is already gone over in VB.NET and VBA; just one more time in an obsolete version of VB.

    3 out of 5 people found this review helpful.

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted June 24, 2011

    No text was provided for this review.

  • Anonymous

    Posted December 18, 2013

    No text was provided for this review.

  • Anonymous

    Posted November 3, 2009

    No text was provided for this review.

  • Anonymous

    Posted October 24, 2010

    No text was provided for this review.

  • Anonymous

    Posted January 11, 2011

    No text was provided for this review.

Sort by: Showing all of 6 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)