Muutke küpsiste eelistusi

E-raamat: Excel VBA Programming for Dummies 2nd Revised edition [Wiley Online]

  • Formaat: 408 pages, Illustrations, Contains 1 Digital online
  • Ilmumisaeg: 21-May-2010
  • Kirjastus: John Wiley & Sons Ltd
  • ISBN-10: 1118257650
  • ISBN-13: 9781118257654
Teised raamatud teemal:
  • Wiley Online
  • Hind: 31,71 €*
  • * hind, mis tagab piiramatu üheaegsete kasutajate arvuga ligipääsu piiramatuks ajaks
Excel VBA Programming for Dummies 2nd Revised editionSuurem pilt
  • Formaat: 408 pages, Illustrations, Contains 1 Digital online
  • Ilmumisaeg: 21-May-2010
  • Kirjastus: John Wiley & Sons Ltd
  • ISBN-10: 1118257650
  • ISBN-13: 9781118257654
Teised raamatud teemal:
Wiley Online e-raamatudRohkem infot Wiley Online kohta
Raamatu kodulehekülg: https://onlinelibrary.wiley.com/doi/book/10.1002/9781118257654
Presents information on using Microsoft's Visual Basic for Applications (VBA) programming language to customize the spreadsheet program Excel.

Are you ready to make Excel jump through hoops? Do your spreadsheets work exactly the way you want? With this book, you can say "yes"! Inside, you'll find step-by-step instructions on how to significantly enhance the power of Excel using the VBA language. You'll soon be building your own Excel applications so you can get the most out of your data.

Take your Excel skills to the next level with VBA programming

Now that you've mastered Excel basics, it's time to move to the next level—creating your own, customized Excel 2010 solutions using Visual Basic for Applications (VBA). The new edition of this non-threatening guide is your key to getting there. Using step-by-step instruction and the accessible, friendly For Dummies style, this practical book shows you how to use VBA, write macros, customize your Excel apps to look and work the way you want, avoid errors, and more.

The book's Web site provides all the programming examples from the book.

  • Introduces you to a new range of Excel 2010 options
  • Shows you how to use Visual Basic for Applications (VBA) to program your own custom Excel applications or to automate functions
  • Explains how to create VBA macros, program, handle errors, debug, and more
  • Covers Visual Basic Editor tools and functions; working with range objects; controlling program flow; and the easiest ways to create custom dialog boxes, toolbars, and menus
  • Includes a companion Web site with all the sample programs from the book

Create your own, custom Excel programs with this information-packed guide!

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Introduction 1(10)
Is This the Right Book?
1(1)
So You Want to Be a Programmer
2(1)
Why Bother?
3(1)
What I Assume about You
3(1)
Obligatory Typographical Conventions Section
4(1)
Check Your Security Settings
5(1)
How This Book Is Organized
6(2)
Part I Introducing VBA
6(1)
Part II How VBA Works with Excel
6(1)
Part III Programming Concepts
7(1)
Part IV Communicating with Your Users
7(1)
Part V Putting It All Together
7(1)
Part VI The Part of Tens
7(1)
But Wait, There's More!
7(1)
Icons Used in This Book
8(1)
Getting the Sample Files
8(1)
Now What?
9(2)
Part I Introducing VBA
11(24)
Chapter 1 What Is VBA?
13(10)
Okay, So What Is VBA?
13(1)
What Can You Do with VBA?
14(2)
Inserting a bunch of text
15(1)
Automating a task you perform frequently
15(1)
Automating repetitive operations
15(1)
Creating a custom command
15(1)
Creating a custom button
16(1)
Developing new worksheet functions
16(1)
Creating complete, macro-driven applications
16(1)
Creating custom add-ins for Excel
16(1)
Advantages and Disadvantages of VBA
16(2)
VBA advantages
17(1)
VBA disadvantages
17(1)
VBA in a Nutshell
18(2)
An Excursion into Versions
20(3)
Chapter 2 Jumping Right In
23(12)
First Things First
23(2)
Excel 2010 Users
24(1)
Excel 2007 Users
24(1)
What You'll Be Doing
25(1)
Taking the First Steps
25(1)
Recording the Macro
26(1)
Testing the Macro
27(1)
Examining the Macro
27(3)
Modifying the Macro
30(1)
Saving Workbooks That Contain Macros
30(1)
Understanding Macro Security
31(2)
More about the NameAndTime Macro
33(2)
Part II How VBA Works with Excel
35(58)
Chapter 3 Working in the Visual Basic Editor
37(32)
What Is the Visual Basic Editor?
37(3)
Activating the VBE
37(1)
Understanding VBE components
38(2)
Working with the Project Window
40(2)
Adding a new VBA module
41(1)
Removing a VBA module
41(1)
Exporting and importing objects
42(1)
Working with a Code Window
42(7)
Minimizing and maximizing windows
42(2)
Creating a module
44(1)
Getting VBA code into a module
44(1)
Entering code directly
45(2)
Using the macro recorder
47(2)
Copying VBA code
49(1)
Customizing the VBA Environment
49(6)
Using the Editor tab
50(2)
Using the Editor Format tab
52(1)
Using the General tab
53(1)
Using the Docking tab
54(1)
Chapter 4 Introducing the Excel Object Model
55(3)
Excel Is an Object?
56(1)
Climbing the Object Hierarchy
56(2)
Wrapping Your Mind around Collections
58(1)
Referring to Objects
58(2)
Navigating through the hierarchy
59(1)
Simplifying object references
60(1)
Diving into Object Properties and Methods
60(4)
Object properties
62(1)
Object methods
63(1)
Object events
64(1)
Finding Out More
64(5)
Using VBA's Help system
65(1)
Using the Object Browser
66(1)
Automatically listing properties and methods
66(3)
Chapter 5 VBA Sub and Function Procedures
69(12)
Subs versus Functions
69(2)
Looking at Sub procedures
70(1)
Looking at Function procedures
70(1)
Naming Subs and Functions
71(1)
Executing Sub procedures
71(7)
Executing the Sub procedure directly
73(1)
Executing the procedure from the Macro dialog box
74(1)
Executing a macro by using a shortcut key
75(1)
Executing the procedure from a button or shape
76(2)
Executing the procedure from another procedure
78(1)
Executing Function procedures
78(3)
Calling the function from a Sub procedure
79(1)
Calling a function from a worksheet formula
79(2)
Chapter 6 Using the Excel Macro Recorder
81(12)
Is it Live or is It VBA?
81(1)
Recording Basics
82(2)
Preparing to Record
84(1)
Relative or Absolute?
84(3)
Recording in absolute mode
84(1)
Recording in relative mode
85(2)
What Gets Recorded?
87(1)
Recording Options
88(2)
Macro name
88(1)
Shortcut key
89(1)
Store Macro in
89(1)
Description
89(1)
Is This Thing Efficient?
90(3)
Part III Programming Concepts
93(140)
Chapter 7 Essential VBA Language Elements
95(20)
Using Comments in Your VBA Code
95(2)
Using Variables, Constants, and Data Types
97(12)
Understanding variables
97(1)
What are VBA's data types?
98(1)
Declaring and scoping variables
99(6)
Working with constants
105(1)
Pre-made constants
106(1)
Working with strings
107(1)
Working with dates
108(1)
Using Assignment Statements
109(3)
Assignment statement examples
109(1)
About that equal sign
109(1)
Smooth operators
110(2)
Working with Arrays
112(2)
Declaring arrays
112(1)
Multidimensional arrays
113(1)
Dynamic arrays
113(1)
Using Labels
114(1)
Chapter 8 Working with Range Objects
115(14)
A Quick Review
115(2)
Other Ways to Refer to a Range
117(2)
The Cells property
117(1)
The Offset property
118(1)
Referring to entire columns and rows
119(1)
Some Useful Range Object Properties
119(7)
The Value property
120(1)
The Text property
121(1)
The Count property
121(1)
The Column and Row properties
121(1)
The Address property
122(1)
The HasFormula property
122(1)
The Font property
123(1)
The Interior property
123(1)
The Formula property
124(2)
The NumberFormat property
126(1)
Some Useful Range Object Methods
126(3)
The Select method
126(1)
The Copy and Paste methods
127(1)
The Clear method
128(1)
The Delete method
128(1)
Chapter 9 Using VBA and Worksheet Functions
129(12)
What is a Function?
129(1)
Using Built-In VBA Functions
130(5)
VBA function examples
130(2)
VBA functions that do more than return a value
132(1)
Discovering VBA functions
133(2)
Using Worksheet Functions in VBA
135(4)
Worksheet function examples
136(2)
Entering worksheet functions
138(1)
More about Using Worksheet Functions
139(1)
Using Custom Functions
139(2)
Chapter 10 Controlling Program Flow and Making Decisions
141(20)
Going with the Flow, Dude
141(1)
The Go to Statement
142(9)
Decisions, decisions
143(1)
The If-Then structure
143(5)
The Select Case structure
148(3)
Knocking Your Code for a Loop
151(7)
For-Next loops
152(4)
Do-While loop
156(1)
Do-Until loop
157(1)
Looping through a Collection
158(3)
Chapter 11 Automatic Procedures and Events
161(22)
Preparing for the Big Event
161(3)
Are events useful?
163(1)
Programming event-handler procedures
164(1)
Where Does the VBA Code Go?
164(1)
Writing an Event-Handler Procedure
165(2)
Introductory Examples
167(3)
The Open event for a workbook
167(2)
The BeforeClose event for a workbook
169(1)
The BeforeSave event for a workbook
169(1)
Examples of Activation Events
170(4)
Activate and deactivate events in a sheet
170(1)
Activate and deactivate events in a workbook
171(2)
Workbook activation events
173(1)
Other Worksheet-Related Events
174(3)
The BeforeDoubleClick event
174(1)
The BeforeRightClick event
174(1)
The Change event
175(2)
Events Not Associated with Objects
177(6)
The OnTime event
178(2)
Keypress events
180(3)
Chapter 12 Error-Handling Techniques
183(14)
Types of Errors
183(1)
An Erroneous Example
184(4)
The macro's not quite perfect
185(1)
The macro is still not perfect
186(1)
Is the macro perfect yet?
186(1)
Giving up on perfection
187(1)
Handling Errors Another Way
188(2)
Revisiting the EnterSquareRoot procedure
188(1)
About the On Error statement
189(1)
Handling Errors: The Details
190(4)
Resuming after an error
190(2)
Error handling in a nutshell
192(1)
Knowing when to ignore errors
192(1)
Identifying specific errors
193(1)
An Intentional Error
194(3)
Chapter 13 Bug Extermination Techniques
197(14)
Species of Bugs
197(1)
Identifying Bugs
198(1)
Debugging Techniques
199(3)
Examining your code
199(1)
Using the MsgBox function
200(1)
Inserting Debug Print statements
201(1)
Using the VBA debugger
202(1)
About the Debugger
202(6)
Setting breakpoints in your code
202(4)
Using the Watch window
206(1)
Using the Locals Window
207(1)
Bug Reduction Tips
208(3)
Chapter 14 VBA Programming Examples
211(22)
Working with Ranges
211(9)
Copying a range
212(1)
Copying a variable-sized range
213(1)
Selecting to the end of a row or column
214(1)
Selecting a row or column
215(1)
Moving a range
215(1)
Looping through a range efficiently
216(1)
Looping through a range efficiently (Part II)
217(1)
Prompting for a cell value
218(1)
Determining the selection type
219(1)
Identifying a multiple selection
219(1)
Changing Excel Settings
220(2)
Changing Boolean settings
221(1)
Changing non-Boolean settings
221(1)
Working with Charts
222(5)
Modifying the chart type
224(1)
Looping through the ChartObjects collection
224(1)
Modifying chart properties
225(1)
Applying chart formatting
225(2)
VBA Speed Tips
227(6)
Turning off screen updating
227(1)
Turning off automatic calculation
228(1)
Eliminating those pesky alert messages
228(1)
Simplifying object references
229(1)
Declaring variable types
230(1)
Using the With-End With structure
231(2)
Part IV Communicating with Your Users
233(98)
Chapter 15 Simple Dialog Boxes
235(18)
UserForm Alternatives
235(1)
The MsgBox Function
236(5)
Getting a response from a message box
237(1)
Customizing message boxes
238(3)
The InputBox Function
241(3)
InputBox syntax
241(1)
An InputBox example
242(2)
The GetOpenFilename Method
244(4)
The syntax for the GetOpenFilename method
244(1)
A GetOpenFilename example
245(2)
Selecting multiple files
247(1)
The GetSaveAsFilename Method
248(1)
Getting a Folder Name
249(1)
Displaying Excel's Built-in Dialog Boxes
250(3)
Chapter 16 UseForm Basics
253(16)
Knowing When to Use a UserForm
253(1)
Creating UserForms: An Overview
254(1)
Working with UserForms
255(5)
Inserting a new UserForm
255(1)
Adding controls to a UserForm
256(1)
Changing properties for a UserForm control
257(1)
Viewing the UserForm Code window
258(1)
Displaying a UserForm
259(1)
Using information from a UserForm
259(1)
A UserForm Example
260(9)
Creating the UserForm
260(1)
Adding the CommandButtons
261(1)
Adding the OptionButtons
262(1)
Adding event-handler procedures
263(2)
Creating a macro to display the dialog box
265(1)
Making the macro available
266(1)
Testing the macro
267(2)
Chapter 17 Using UserForm Controls
269(20)
Getting Started with Dialog Box Controls
269(4)
Adding controls
269(1)
Introducing control properties
270(3)
Dialog Box Controls: The Details
273(11)
CheckBox Control
274(1)
ComboBox Control
274(1)
CommandButton control
275(1)
Frame control
276(1)
Image control
276(1)
Label control
277(1)
ListBox control
278(1)
MultiPage control
279(1)
OptionButton control
279(1)
RefEdit control
280(1)
ScrollBar control
281(1)
SpinButton control
282(1)
TabStrip control
282(1)
TextBox control
282(1)
ToggleButton control
283(1)
Working with Dialog Box Controls
284(3)
Moving and resizing controls
284(1)
Aligning and spacing controls
284(1)
Accommodating keyboard users
285(2)
Testing a UserForm
287(1)
Dialog Box Aesthetics
287(2)
Chapter 18 UserForm Techniques and Tricks
289(24)
Using Dialog Boxes
289(1)
A UserForm Example
289(7)
Creating the dialog box
290(2)
Writing code to display the dialog box
292(1)
Making the macro available
292(1)
Trying out your dialog box
293(1)
Adding event-handler procedures
294(1)
Validating the data
295(1)
Now the dialog box works
296(1)
More UserForm Examples
296(15)
A ListBox example
296(5)
Selecting a range
301(1)
Using multiple sets of OptionButtons
302(1)
Using a SpinButton and a TextBox
303(2)
Using a UserForm as a progress indicator
305(3)
Creating a tabbed dialog box
308(2)
Displaying a chart in a UserForm
310(1)
A Dialog Box Checklist
311(2)
Chapter 19 Accessing Your Macros through the User Interface
313(18)
What Happened to CommandBars?
313(1)
Ribbon Customization
314(7)
Customizing the Ribbon manually
314(2)
Customizing the Ribbon with XML
316(5)
Customizing Shortcut Menus
321(5)
Commanding the CommandBars collection
321(1)
Listing all shortcut menus
321(1)
Referring to CommandBars
322(1)
Referring to controls in a CommandBar
323(1)
Properties of CommandBar controls
324(2)
VBA Shortcut Menu Examples
326(3)
Adding a new item to the Cell shortcut menu
326(2)
Disabling a shortcut menu
328(1)
Creating a Custom Toolbar
329(2)
Part V Putting It All Together
331(30)
Chapter 20 Creating Worksheet Functions --- and Living to Tell about It
333(16)
Why Create Custom Functions?
333(1)
Understanding VBA Function Basics
334(1)
Writing Functions
335(1)
Working with Function Arguments
335(1)
Function Examples
336(7)
A function with no argument
336(1)
A function with one argument
336(2)
A function with two arguments
338(1)
A function with a range argument
339(1)
A function with an optional argument
340(2)
A function with an indefinite number of arguments
342(1)
Functions That Return an Array
343(2)
Returning an array of month names
343(1)
Returning a sorted list
344(1)
Using the Insert Function Dialog Box
345(4)
Displaying the function's description
346(1)
Argument descriptions
347(2)
Chapter 21 Creating Excel Add-Ins
349(12)
Okay...So What's an Add-In?
349(1)
Why Create Add-Ins?
350(1)
Working with Add-Ins
351(1)
Add-In Basics
352(1)
An Add-In Example
353(8)
Setting up the workbook
353(2)
Testing the workbook
355(1)
Adding descriptive information
356(1)
Protecting the VBA code
357(1)
Creating the add-in
357(1)
Opening the add-in
357(1)
Distributing the add-in
358(1)
Modifying the add-in
359(2)
Part VI The Part of Tens
361(10)
Chapter 22 Ten VBA Questions (And Answers)
363(8)
Chapter 23 (Almost) Ten Excel Resources
367(1)
The VBA Help System
367(1)
Microsoft Product Support
367(1)
Internet Newsgroups
368(1)
Internet Web Sites
369(1)
Excel Blogs
369(1)
Google
369(1)
Bing
369(1)
Local User Groups
370(1)
My Other Books
370(1)
Bonus
Chapters See companion Web site (www.dummies.com/go/excelubaprogramming/d2e)
Bonus
Chapter 1 Working with Colors Companion web site
Bonus
Chapter 2 Ten VBA Do's and Don'ts companion Web site
Index 371
John Walkenbach is principal of J-Walk and Associates, Inc., a leading authority on spreadsheet software and creator of the award-winning Power Utility Pak. Walkenbach has written more than 50 books and 300 articles for publications including PC World , InfoWorld , and Windows .
Püsilink: https://www.kriso.ee/db/9781118257654_pe.html
Märksõnad: