Excel VBA programming for dummies

著者

• Alexander, Michael
• Walkenbach, John

書誌事項

Excel VBA programming for dummies

by Michael Alexander, John Walkenbach

（--For dummies）

John Wiley & Sons, c2019

5th ed

• pbk.

タイトル別名

Excel VBA programming for dummies : learning made easy

注記

Includes index

Previous edition: 2015

内容説明・目次

内容説明

Take your Excel programming skills to the next level To take Excel to the next level, you need to understand and implement the power of Visual Basic for Applications (VBA). Excel VBA Programming For Dummies introduces you to a wide array of new Excel options, beginning with the most important tools and operations for the Visual Basic Editor. Inside, you'll find an overview of the essential elements and concepts for programming with Excel. In no time, you'll discover techniques for handling errors and exterminating bugs, working with range objects and controlling program flow, and much more. With friendly advice on the easiest ways to develop custom dialog boxes, toolbars, and menus, readers will be creating Excel applications custom fit to their unique needs! Fully updated for the new Excel 2019 Step-by-step instructions for creating VBA macros to maximize productivity Guidance on customizing your applications so they work the way you want All sample programs, VBA code, and worksheets are available at dummies.com Beginning VBA programmers rejoice! This easy-to-follow book makes it easier than ever to excel at Excel VBA!

目次

Introduction 1 About This Book 1 Obligatory Typographical Conventions Section 2 Check Your Security Settings 3 Foolish Assumptions 4 Icons Used in This Book 5 Sample Files Online 5 Where to Go from Here 6 Part 1: Getting Started with Excel VBA Programming 7 Chapter 1: What Is VBA? 9 Okay, So What Is VBA? 9 What Can You Do with VBA? 10 Inserting a bunch of text 11 Automating a task you perform frequently 11 Automating repetitive operations 11 Creating a custom command 11 Creating a custom button 12 Developing new worksheet functions 12 Creating custom add-ins for Excel 12 Advantages and Disadvantages of VBA 12 VBA advantages 12 VBA disadvantages 13 VBA in a Nutshell 13 Excel Compatibility 16 Chapter 2: Jumping Right In 17 First Things First 17 What You'll Be Doing 18 Taking the First Steps 18 Recording the Macro 19 Testing the Macro 21 Examining the Macro 21 Modifying the Macro 23 Saving Workbooks That Contain Macros 24 Understanding Macro Security 24 Revealing More about the NameAndTime Macro 26 Part 2: How VBA Works with Excel 29 Chapter 3: Working in the Visual Basic Editor 31 What Is the Visual Basic Editor? 31 Activating the VBE 32 Understanding VBE components 32 Working with the Project Window 34 Adding a new VBA module 35 Removing a VBA module 36 Exporting and importing objects 36 Working with a Code Window 37 Minimizing and maximizing windows 37 Creating a module 38 Getting VBA code into a module 39 Entering code directly 39 Using the macro recorder 42 Copying VBA code 45 Customizing the VBA Environment 45 Using the Editor tab 45 Using the Editor Format tab 48 Using the General tab 49 Using the Docking tab 50 Chapter 4: Introducing the Excel Object Model 51 Excel Is an Object? 52 Climbing Down the Object Hierarchy 52 Wrapping Your Mind around Collections 53 Referring to Objects 54 Navigating through the hierarchy 55 Simplifying object references 56 Diving into Object Properties and Methods 56 Object properties 58 Object methods 59 Object events 61 Finding Out More 61 Using VBA's Help system 61 Using the Object Browser 62 Automatically listing properties and methods 63 Chapter 5: VBA Sub and Function Procedures 65 Understanding Subs versus Functions 65 Looking at Sub procedures 66 Looking at Function procedures 66 Naming Subs and Functions 67 Executing Sub procedures 68 Executing the Sub procedure directly 70 Executing the procedure from the Macro dialog box 70 Executing a macro by using a shortcut key 71 Executing the procedure from a button or shape 72 Executing the procedure from another procedure 74 Executing Function procedures 75 Calling the function from a Sub procedure 75 Calling a function from a worksheet formula 76 Chapter 6: Using the Excel Macro Recorder 79 Recording Basics 80 Preparing to Record 81 Relative or Absolute? 82 Recording in absolute mode 82 Recording in relative mode 83 What Gets Recorded? 84 Recording Options 86 Macro name 86 Shortcut key 87 Store Macro In option 87 Description 87 Is This Thing Efficient? 87 Part 3: Programming Concepts 91 Chapter 7: Essential VBA Language Elements 93 Using Comments in Your VBA Code 93 Using Variables, Constants, and Data Types 95 Understanding variables 95 What are VBA's data types? 97 Declaring and scoping variables 98 Working with constants 104 Premade constants 105 Working with strings 106 Working with dates 107 Using Assignment Statements 108 Assignment statement examples 108 About that equal sign 108 Smooth operators 109 Working with Arrays 111 Declaring arrays 111 Multidimensional arrays 112 Dynamic arrays 112 Using Labels 113 Chapter 8: Working with Range Objects 115 A Quick Review 115 Other Ways to Refer to a Range 117 The Cells property 117 The Offset property 118 Some Useful Range Object Properties 119 The Value property 120 The Text property 121 The Count property 121 The Column and Row properties 121 The Address property 122 The HasFormula property 122 The Font property 123 The Interior property 124 The Formula property 125 The NumberFormat property 126 Some Useful Range Object Methods 127 The Select method 127 The Copy and Paste methods 127 The Clear method 128 The Delete method 128 Chapter 9: Using VBA and Worksheet Functions 131 What Is a Function? 131 Using Built-In VBA Functions 132 VBA function examples 132 VBA functions that do more than return a value 135 Discovering VBA functions 135 Using Worksheet Functions in VBA 138 Worksheet function examples 139 Entering worksheet functions 141 More about using worksheet functions 142 Using Custom Functions 142 Chapter 10: Controlling Program Flow and Making Decisions 145 Going with the Flow, Dude 145 The GoTo Statement 146 Decisions, Decisions 148 The If-Then structure 148 The Select Case structure 152 Knocking Your Code for a Loop 155 For-Next loops 156 Do-While loop 161 Do-Until loop 161 Using For Each-Next Loops with Collections 161 Chapter 11: Automatic Procedures and Events 165 Preparing for the Big Event 165 Are events useful? 167 Programming event-handler procedures 168 Where Does the VBA Code Go? 168 Writing an Event-Handler Procedure 169 Introductory Examples 171 The Open event for a workbook 171 The BeforeClose event for a workbook 173 The BeforeSave event for a workbook 174 Examples of Activation Events 175 Activate and deactivate events in a sheet 175 Activate and deactivate events in a workbook 176 Workbook activation events 178 Other Worksheet-Related Events 178 The BeforeDoubleClick event 178 The BeforeRightClick event 179 The Change event 179 Events Not Associated with Objects 181 The OnTime event 182 Keypress events 183 Chapter 12: Error-Handling Techniques 187 Types of Errors 187 An Erroneous Example 188 The macro's not quite perfect 189 The macro is still not perfect 190 Is the macro perfect yet? 191 Giving up on perfection 192 Handling Errors Another Way 192 Revisiting the EnterSquareRoot procedure 192 About the On Error statement 194 Handling Errors: The Details 194 Resuming after an error 194 Error handling in a nutshell 196 Knowing when to ignore errors 197 Identifying specific errors 197 An Intentional Error 199 Chapter 13: Bug Extermination Techniques 201 Species of Bugs 201 Identifying Bugs 202 Debugging Techniques 203 Examining your code 204 Using the MsgBox function 204 Inserting Debug.Print statements 206 Using the VBA debugger 206 About the Debugger 207 Setting breakpoints in your code 207 Using the Watches window 210 Using the Locals window 211 Bug Reduction Tips 212 Chapter 14: VBA Programming Examples 213 Working with Ranges 214 Copying a range 214 Copying a variable-size range 215 Selecting to the end of a row or column 217 Selecting a row or column 218 Moving a range 218 Looping through a range efficiently 219 Looping through a range efficiently (Part II) 220 Prompting for a cell value 221 Determining the selection type 222 Identifying a multiple selection 223 Changing Excel Settings 223 Changing Boolean settings 224 Changing non-Boolean settings 225 Working with Charts 225 AddChart versus AddChart2 226 Modifying the chart type 228 Looping through the ChartObjects collection 228 Modifying chart properties 229 Applying chart formatting 229 VBA Speed Tips 231 Turning off screen updating 231 Turning off automatic calculation 232 Eliminating those pesky alert messages 233 Simplifying object references 233 Declaring variable types 234 Using the With-End With structure 235 Part 4: Communicating with Your Users 237 Chapter 15: Simple Dialog Boxes 239 UserForm Alternatives 239 The MsgBox Function 240 Displaying a simple message box 241 Getting a response from a message box 241 Customizing message boxes 243 The InputBox Function 246 InputBox syntax 246 An InputBox example 247 Another type of InputBox 248 The GetOpenFilename Method 249 The syntax for the GetOpenFilename method 250 A GetOpenFilename example 250 The GetSaveAsFilename Method 252 Getting a Folder Name 253 Displaying Excel's Built-in Dialog Boxes 254 Chapter 16: UserForm Basics 257 Knowing When to Use a UserForm 257 Creating UserForms: An Overview 258 Working with UserForms 259 Inserting a new UserForm 259 Adding controls to a UserForm 260 Changing properties for a UserForm control 261 Viewing the UserForm Code window 262 Displaying a UserForm 263 Using information from a UserForm 263 A UserForm Example 264 Creating the UserForm 264 Adding the CommandButtons 265 Adding the OptionButtons 266 Adding event-handler procedures 268 Creating a macro to display the dialog box 270 Making the macro available 270 Testing the macro 272 Chapter 17: Using UserForm Controls 275 Getting Started with Dialog Box Controls 275 Adding controls 276 Introducing control properties 276 Dialog Box Controls: The Details 279 CheckBox control 279 ComboBox control 280 CommandButton control 280 Frame control 281 Image control 281 Label control 282 ListBox control 283 MultiPage control 284 OptionButton control 285 RefEdit control 286 ScrollBar control 286 SpinButton control 287 TabStrip control 288 TextBox control 288 ToggleButton control 289 Working with Dialog Box Controls 289 Moving and resizing controls 290 Aligning and spacing controls 290 Accommodating keyboard users 291 Testing a UserForm 292 Dialog Box Aesthetics 293 Chapter 18: UserForm Techniques and Tricks 295 Using Dialog Boxes 296 A UserForm Example 296 Creating the dialog box 296 Writing code to display the dialog box 299 Making the macro available 299 Trying out your dialog box 300 Adding event-handler procedures 300 Validating the data 302 Now the dialog box works 303 A ListBox Example 303 Filling a ListBox 304 Determining the selected item 305 Determining multiple selections 306 Selecting a Range 308 Using Multiple Sets of OptionButtons 310 Using a SpinButton and a TextBox 311 Using a UserForm as a Progress Indicator 312 Creating the progress-indicator dialog box 313 The procedures 314 How this example works 316 Creating a Modeless Tabbed Dialog Box 316 Displaying a Chart in a UserForm 318 A Dialog Box Checklist 319 Chapter 19: Accessing Your Macros through the User Interface 321 Customizing the Ribbon 321 Customizing the Ribbon manually 322 Adding a macro to the Ribbon 324 Customizing the Ribbon with XML 325 Customizing Shortcut Menus 329 Adding a new item to the Cell shortcut menu 330 What's different since Excel 2007? 332 Part 5: Putting It All Together 333 Chapter 20: Creating Worksheet Functions - and Living to Tell about It 335 Why Create Custom Functions? 336 Understanding VBA Function Basics 337 Writing Functions 338 Working with Function Arguments 338 A function with no argument 339 A function with one argument 339 A function with two arguments 341 A function with a range argument 342 A function with an optional argument 344 Introducing Wrapper Functions 346 The NumberFormat function 346 The ExtractElement function 347 The SayIt function 348 The IsLike function 348 Working with Functions That Return an Array 348 Returning an array of month names 349 Returning a sorted list 349 Using the Insert Function Dialog Box 351 Displaying the function's description 351 Adding argument descriptions 353 Chapter 21: Creating Excel Add-Ins 355 Okay So What's an Add-In? 355 Why Create Add-Ins? 356 Working with Add-Ins 357 Understanding Add-In Basics 358 Looking at an Add-In Example 359 Setting up the workbook 359 Testing the workbook 362 Adding descriptive information 362 Protecting the VBA code 363 Creating the add-in 363 Opening the add-in 364 Distributing the add-in 364 Modifying the add-in 365 Part 6: The Part of Tens 367 Chapter 22: Ten Handy Visual Basic Editor Tips 369 Applying Block Comments 370 Copying Multiple Lines of Code at Once 371 Jumping between Modules and Procedures 371 Teleporting to Your Functions 371 Staying in the Right Procedure 372 Stepping Through Your Code 372 Stepping to a Specific Line in Your Code 373 Stopping Your Code at a Predefined Point 374 Seeing the Beginning and End of Variable Values 375 Turning Off Auto Syntax Check 375 Chapter 23: Resources for VBA Help 377 Letting Excel Write Code for You 378 Pilfering Code from the Internet 379 Leveraging User Forums 379 Visiting Expert Blogs 380 Mining YouTube for Video Training 381 Attending Live and Online Training Classes 381 Learning from the Microsoft Office Dev Center 382 Dissecting the Other Excel Files in Your Organization 382 Asking Your Local Excel Guru 382 Chapter 24: Ten VBA Do's and Don'ts 383 Do Declare All Variables 383 Don't Confuse Passwords with Security 384 Do Clean Up Your Code 384 Don't Put Everything in One Procedure 385 Do Consider Other Software 385 Don't Assume That Everyone Enables Macros 385 Do Get in the Habit of Experimenting 386 Don't Assume That Your Code Will Work with Other Excel Versions 386 Do Keep Your Users in Mind 386 Don't Forget About Backups 387 Index 389

「Nielsen BookData」 より

詳細情報

• NII書誌ID(NCID): BB27538609
• ISBN:
  • 9781119518174
• LCCN: 2018957226
• 出版国コード: us
• タイトル言語コード: eng
• 本文言語コード: eng
• 出版地: Hoboken, NJ
• ページ数/冊数: xiv, 398 p.
• 分類:
  • DC23 : 005.54
• 件名:
  • LCSH : Microsoft Excel (Computer file)
  • LCSH : Microsoft Visual Basic for applications (Computer file)
  • LCSH : Computer software -- Development
• 親書誌ID:
  • BA22893377