Excel Vba Formulaarray

Acknowledgments xxi

Introduction xxiii

Chapter 1: Primer in Excel VBA_1

Using the Macro Recorder 2

Recording Macros 2

Running Macros 6

The Visual Basic Editor 8

Other Ways to Run Macros 11

User-Defined Functions 17

Creating a UDF 18

What UDFs Cannot Do 21

The Excel Object Model 21

Objects 22

Getting Help 27

Experimenting in the Immediate Window 29

The VBA Language 30

Basic Input and Output 30

Calling Functions and Sub Procedures 35

Parentheses and Argument Lists 37

Variable Declaration 38

Scope and Lifetime of Variables 40

Variable Type 42

Object Variables 45

Making Decisions 47

Looping 50

Arrays 55

Run-Time Error-Handling 59

Summary 62

Chapter 2: The Application Object_63

Globals 63

The Active Properties 64

Display Alerts 65

Screen Updating 66

Evaluate 66

InputBox 68

StatusBar 70

SendKeys 70

OnTime 71

OnKey 72

Worksheet Functions 73

Caller 74

Summary 75

Chapter 3: Workbooks and Worksheets_77

The Workbooks Collection 77

Getting a Filename from a Path 78

Files in the Same Directory 81

Overwriting an Existing Workbook 81

Saving Changes 82

The Sheets Collection 83

Worksheets 83

Copy and Move 85

Grouping Worksheets 87

The Window Object 89

Synchronizing Worksheets 90

Summary 91

Chapter 4: Using Ranges_93

Activate and Select 93

Range Property 95

Shortcut Range References 96

Ranges on Inactive Worksheets 96

Range Property of a Range Object 97

Cells Property 97

Cells Used in Range 98

Ranges of Inactive Worksheets 99

More on the Cells Property of the Range Object 99

Single-Parameter Range Reference 101

Offset Property 102

Resize Property 103

SpecialCells Method 105

Last Cell 105

Deleting Numbers 107

CurrentRegion Property 108

End Property 110

Referring to Ranges with End 110

Summing a Range 111

Columns and Rows Properties 112

Areas 113

Union and Intersect Methods 115

Empty Cells 115

Transferring Values between Arrays and Ranges 118

Deleting Rows 121

Summary 123

Chapter 5: Using Names_125

Naming Ranges 127

Using the Name Property of the Range Object 128

Special Names 128

Storing Values in Names 129

Storing Arrays 130

Hiding Names 131

Working with Named Ranges 132

Searching for a Name 133

Searching for the Name of a Range 135

Determining which Names Overlap a Range 136

Summary 139

Chapter 6: Data Lists_141

Structuring the Data 141

Sorting a Range 142

Older Excel Versions 144

Creating a Table 144

Sorting a Table 145

AutoFilter 146

AutoFilter Object 147

Filter Object 148

Date Custom Filter 148

Adding Combo Boxes 149

Copying the Visible Rows 153

Finding the Visible Rows 154

Advanced Filter 156

Data Form 158

Summary 159

Chapter 7: PivotTables_161

Creating a PivotTable Report 162

PivotCaches 165

PivotTables Collection 165

PivotFields 166

CalculatedFields 170

Pivotltems 171

Grouping 171

Visible Property 175

Calculatedltems 176

PivotCharts 177

External Data Sources 178

Summary 180

Chapter 8: Charts_181

Chart Sheets 182

The Recorded Macro 184

Adding a Chart Sheet Using VBA Code 184

Embedded Charts 185

Using the Macro Recorder 186

Adding an Embedded Chart Using VBA Code 186

Editing Data Series 187

Defining Chart Series with Arrays 190

Converting a Chart to Use Arrays 193

Determining the Ranges Used in a Chart 194

Chart Labels 195

Summary 196

Chapter 9: Event Procedures_199

Worksheet Events 199

Enable Events 200

Worksheet Calculate 201

Chart Events 202

Before Double Click 202

Workbook Events 205

Save Changes 206

Headers and Footers 207

Summary 208

Chapter 10: Adding Controls_209

Form and ActiveX Controls 209

ActiveX Controls 210

Scrollbar Control 211

Spin Button Control 211

CheckBox Control 212

Option Button Controls 212

Forms Controls 214

Dynamic ActiveX Controls 216

Controls on Charts 220

Summary 221

Chapter 11: Text Files and File Dialog_223

Opening Text Files

223

Writing to Text Files

224

Reading Text Files

226

Writing to Text Files Using Print

227

Reading Data Strings

229

Flexible Separators and Delimiters

230

FileDialog

233

FileDialogFilters

235

FileDialogSelectedItems

235

Dialog Types

235

Execute Method

235

MultiSelect

236

Summary

238

Chapter 12: Working with XML and the Open XML File Formats_239

The Basics of Using XML Data in Excel 240

XML Fundamentals 240

Consuming XML Data Directly 246

Creating and Managing Your Own XML Maps 249

Using VBA to Program XML Processes 253

Programming XML Maps 253

Leveraging DOM and XPath to Manipulate XML Files 258

Using VBA to Program Open XML Files 265

Programming Open XML Files with VBA 266

Programmatically Zipping an Excel Container 267

Summary 272

Chapter 13: UserForms_273

Displaying a UserForm 273

Creating a UserForm 275

Directly Accessing Controls in UserForms 277

Stopping the Close Button 281

Maintaining a Data List 282

Modeless UserForms 288

Progress Indicator 288

Variable UserForm Name 291

Summary 291

Chapter 14: RibbonX_293

Overview 293

Prerequisites 294

Adding the Customizations 294

XML Structure 295

RibbonX and VBA 298

Control Types 299

Basic Controls 299

Container Controls 300

Control Attributes 301

Control Callbacks 303

Managing Control Images 305

Other RibbonX Elements, Attributes, and Callbacks 307

Sharing Controls among Multiple Workbooks 308

Updating Controls at Run Time 309

Hooking Built-In Controls 311

RibbonX in Dictator Applications 312

Customizing the Office Menu 312

Customizing the QAT 313

Controlling Tabs, Tab Sets, and Groups 313

Dynamic Controls 314

dropDown, comboBox, and gallery 315

dynamicMenu 315

CommandBar Extensions for the Ribbon 316

RibbonX Limitations 317

Summary 318

Chapter 15: Command Bars_319

Toolbars, Menu Bars, and Popups 320

Excel's Built-in Command Bars 322

Controls at All Levels 325

FaceIds 328

Creating New Menus 330

The OnAction Macros 332

Passing Parameter Values 333

Deleting a Menu 334

Creating a Toolbar 335

Popup Menus 338

Showing Popup Command Bars 342

Table-Driven Command Bar Creation 344

Summary 354

Chapter 16: Class Modules_355

Creating Your Own Objects 356

Property Procedures 357

Creating Collections 359

Class Module Collection 360

Encapsulation 363

Trapping Application Events 363

Embedded Chart Events 365

A Collection of UserForm Controls 368

Referencing Classes Across Projects 370

Summary 371

Chapter 17: Add-ins_373

Hiding the Code 374

Creating an Add-in 374

Closing Add-ins 375

Code Changes 376

Saving Changes 377

Interface Changes 377

Installing an Add-in 379

Addinlnstall Event 381

Removing an Add-in from the Add-ins List 381

Summary 382

Chapter 18: Automation Add-Ins and COM Add-Ins_383

Automation Add-Ins 383

A Simple Add-In — Sequence 384

Registering Automation Add-Ins with Excel 385

Using Automation Add-Ins 386

Introducing the IDTExtensibility2 Interface 388

COM Add-Ins 394

The IDTExtensibility2 Interface (Continued) 395

Registering a COM Add-In with Excel 395

The COM Add-In Designer 396

Summary 409

Chapter 19: Interacting with Other Office Applications_411

Establishing the Connection 411

Late Binding 412

Early Binding 414

Opening a Document in Word 416

Accessing an Active Word Document 417

Creating a New Word Document 418

Access and ADO 419

Access, Excel, and, Outlook 420

Better than Mail Merge 423

Readable Document Variables 428

Summary 430

Chapter 20: Data Access with ADO_431

An Introduction to Structured Query Language (SQL) 431

The SELECT Statement 432

The INSERT Statement 434

The UPDATE Statement 434

The DELETE Statement 435

An Overview of ADO 436

The Connection Object 437

The Recordset Object 441

The Command Object 445

Using ADO in Microsoft Excel Applications 447

Using ADO with Microsoft Access 448

Using ADO with Microsoft SQL Server 454

Using ADO with Non-Standard Data Sources 463

Summary 468

Chapter 21: Managing External Data_469

The External Data User Interface 469

Get External Data 470

Manage Connections 471

The QueryTable and ListObject 472

A QueryTable from a Relational Database 472

A Query Table Associated with a ListObject 475

QueryTables and Parameter Queries 476

QueryTables from Web Queries 479

A QueryTable from a Text File 482

Creating and Using Connection Files 484

The WorkbookConnection Object and the Connections Collection 487

External Data Security Settings 489

Summary 490

Chapter 22: The Trust Center and Document Security_491

The Trust Center 491

Trusted Publishers 492

Trusted Locations 492

Add-ins 494

ActiveX Settings 495

Macro Settings 497

Message Bar 498

External Content 499

Privacy Options 501

Automating Document Inspection 503

The RemoveDocumentInformation Method 503

The DocumentInspectors Collection 505

Summary 506

Chapter 23: Browsing OLAP Data Sources with Excel_507

Analyzing OLAP Data via Pivot Tables 508

Connecting to an OLAP Data Source 508

Browsing the OLAP Data Source 510

Understanding the MDX behind OLAP-based Pivot Tables 512

The Basics of MDX 513

Browsing OLAP Data Sources without Pivot Tables 517

Using ADO to Return Flattened Recordsets 517

Using ADO MD to Get Cube Schema Information 518

Creating an Inventory of Dimensions, Hierarchies, and Levels 519

Creating Offline Cubes 521

Creating an Offline Cube Manually 521

Using the CreateCubeFile Method 521

Creating an Offline Cube Using ADO MD and VBA 522

Summary 523

Chapter 24: Excel and the Internet_525

What Can the Internet Do for You? 526

Using the Internet for Storing Workbooks 526

Using the Internet as a Data Source 527

Opening Web Pages as Workbooks 528

Using Web Queries 528

Parsing Web Pages for Specific Information 530

Using the Internet to Publish Results 531

Setting Up a Web Server 532

Saving Worksheets as Web Pages 532

Creating Interactive Web Pages 533

Using the Internet as a Communication Channel 533

Communicating with a Web Server 534

Summary 536

Chapter 25: International Issues_537

Changing Windows Regional Settings and the Office 2007 UI Language 537

Responding to Regional Settings and the Windows Language 538

Identifying the User's Regional Settings and Windows Language 538

VBA Conversion Functions from an International Perspective 539

Interacting with Excel 545

Sending Data to Excel 545

Reading Data from Excel 548

The Rules for Working with Excel 548

Interacting with Users 549

Paper Sizes 549

Displaying Data 549

Interpreting Data 550

The xxxLocal Properties 550

The Rules for Working with Your Users 551

Excel 2007's International Options 552

Features That Don't Play by the Rules 554

The OpenText Function 555

The SaveAs Function 556

The ShowDataForm Sub Procedure 556

Pasting Text 557 PivotTable Calculated Fields and Items, and Conditional Format and

Data Validation Formulas 557

Web Queries 558

=TEXT() Worksheet Function 558

The Range.Value, Range.Formula, and Range.FormulaArray Properties 559

The Range.AutoFilter Method 559

The Range.AdvancedFilter Method 559 The Application.Evaluate, Application.ConvertFormula, and

Application.ExecuteExcel4Macro Functions 560

Responding to Office 2007 Language Settings 560

Where Does the Text Come From? 560

Identifying the Office UI Language Settings 562

Creating a Multilingual Application 562

Working in a Multilingual Environment 564

The Rules for Developing a Multilingual Application 565

Some Helpful Functions 565

The bWinToNum Function 566

The bWinToDate Function 566

The sFormatDate Function 567

The ReplaceHolders Function 568

Summary 568

Chapter 26: Programming the VBE_571

Identifying VBE Objects in Code 572

The VBE Object 572

The VBProject Object 572

The VBComponent Object 573

The CodeModule Object 574

The CodePane Object 574

The Designer Object 574

Starting Up 575

Adding Menu Items to the VBE 576

Working with Workbooks 580

Working with Code 589

Working with UserForms 594

Working with References 598

COM Add-ins 599

Summary 600

Chapter 27: Programming with the Windows API_601

Anatomy of an API Call 602

Interpreting C-Style Declarations 603

Constants, Structures, Handles, and Classes 606

What If Something Goes Wrong? 609

Wrapping API Calls in Class Modules 611

Some Example Classes 616

A High-Resolution Timer Class 616

Class Module CHighResTimer 616

Freeze a UserForm 618

A System Info Class 619

Modifying UserForm Styles 622

Window Styles 623

The CFormChanger Class 624

Resizable UserForms 625

Absolute Changes 626

Relative Changes 627

The CFormResizer Class 628

Summary 634

Appendix A: Excel 2007 Object Model_635

Appendix B: VBE Object Model_971

Appendix C: Office 2007 Object Model_995

Index 1079

0 0

Post a comment