Klienditugi: 7440010 (E-R 10-18)

Abi | Registreeri | Logi sisse

E-raamat: Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)

3.33/5 (6 hinnangut Goodreads-ist)

Bill Jelen, Tracy Syrstad

Formaat: 640 pages
Sari: Business Skills
Ilmumisaeg: 18-Mar-2022
Kirjastus: Addison Wesley
Keel: eng
ISBN-13: 9780137521609

Teised raamatud teemal:

Formaat - EPUB+DRM
Hind: 35,09 €*
* hind on lõplik, st. muud allahindlused enam ei rakendu
Lisa ostukorvi
Lisa soovinimekirja
See e-raamat on mõeldud ainult isiklikuks kasutamiseks. E-raamatuid ei saa tagastada.

Formaat: 640 pages
Sari: Business Skills
Ilmumisaeg: 18-Mar-2022
Kirjastus: Addison Wesley
Keel: eng
ISBN-13: 9780137521609

Teised raamatud teemal:

DRM piirangud

Kopeerimine (copy/paste):

ei ole lubatud
Printimine:

ei ole lubatud
Kasutamine:

Digitaalõiguste kaitse (DRM)
Kirjastus on väljastanud selle e-raamatu krüpteeritud kujul, mis tähendab, et selle lugemiseks peate installeerima spetsiaalse tarkvara. Samuti peate looma endale Adobe ID Rohkem infot siin. E-raamatut saab lugeda 1 kasutaja ning alla laadida kuni 6'de seadmesse (kõik autoriseeritud sama Adobe ID-ga).

Vajalik tarkvara
Mobiilsetes seadmetes (telefon või tahvelarvuti) lugemiseks peate installeerima selle tasuta rakenduse: PocketBook Reader (iOS / Android)

PC või Mac seadmes lugemiseks peate installima Adobe Digital Editionsi (Seeon tasuta rakendus spetsiaalselt e-raamatute lugemiseks. Seda ei tohi segamini ajada Adober Reader'iga, mis tõenäoliselt on juba teie arvutisse installeeritud )

Seda e-raamatut ei saa lugeda Amazon Kindle's.

Renowned Excel experts Bill Jelen (MrExcel) and Tracy Syrstad help you automate Excel tasks to get more done, faster than ever.

Use this guide to automate virtually any routine Excel task: save yourself hours, days, maybe even weeks. Make Excel do things you thought were impossible, discover macro techniques you won’t find anywhere else, and create automated reports that are amazingly powerful. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable; capture data from anywhere, and use it anywhere; and automate the best new features in Microsoft Excel. You’ll find simple, step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and complete, easy-to-adapt solutions.

By reading this book, you will:

Quickly master Excel macro development
Work more efficiently with ranges, cells, and formulas
Generate automated reports and quickly adapt them for new requirements
Learn to automate PivotTables to summarize, analyze, explore, and present data
Leverage more of Excel VBA’s power with classes, collections, and Advanced Filter
Make the most of User-Defined Functions (UDFs), including new LAMBDA functions
Build sophisticated UserForm custom dialogs to collect data from other Excel users
Integrate data from the Internet, Access and SQL Server databases, and other sources
Automatically generate charts, visualizations, sparklines, and Word documents
Extend Excel’s power by controlling other Office programs and Windows itself
Create Office Add-ins to expand worksheet functionality or streamline retrieval of external information
Get started creating brand-new TypeScript applications for Excel Online

About This Book

• For everyone who wants to get more done with Microsoft Excel in less time

• For business and financial professionals, entrepreneurs, students, and others who need to efficiently manage and analyze data

This book shows students how to make Excel do things you thought were impossible, discover macro techniques you won't find anywhere else, and create automated reports that are amazingly powerful. Bill Jelen and Tracy Syrstad help students instantly visualize information, so they can act on it… capture data from anywhere, and use it anywhere… automate Excel's best new features. Students will find simple, step-by-step instructions, real-world case studies, and workbooks packed with bonus examples, macros, and solutions—straight from MrExcel!

Acknowledgments

xxv

About the Authors

xxvii

Introduction

xxix

Chapter 1 Unleashing the power of Excel with VBA

(26)

Barriers to entry

(1)

The macro recorder doesn't work!

(1)

No one person on the Excel team is focused on the macro recorder

(1)

Visual Basic is not like BASIC

(1)

Good news: Climbing the learning curve is easy

(1)

Great news: Excel with VBA is worth the effort

(1)

Knowing your tools: The Developer tab

(1)

Understanding which file types allow macros

(2)

Macro security

(1)

Adding a trusted location

(1)

Using macro settings to enable macros in workbooks outside trusted locations

(1)

Using Disable All Macros With Notification

(1)

Overview of recording, storing, and running a macro

(1)

Filling out the Record Macro dialog box

(1)

Running a macro

(1)

Creating a macro button on the ribbon

(1)

Creating a macro button on the Quick Access Toolbar

(1)

Assigning a macro to a form control, text box, or shape

(1)

Understanding the VB Editor

(1)

VB Editor settings

(1)

The Project Explorer

(1)

The Properties window

(1)

Understanding shortcomings of the macro recorder

(2)

Recording the macro

(1)

Examining code in the Programming window

(2)

Running the macro on another day produces undesired results

(1)

Possible solution: Use relative references when recording

(4)

Never use AutoSum or Quick Analysis while recording a macro

(1)

Four tips for using the macro recorder

(1)

Next steps

(1)

Chapter 2 This sounds like BASIC, so why doesn't it look familiar?

(26)

Understanding the parts of VBA "speech"

(4)

VBA is not really hard

(1)

VBA Help files: Using F1 to find anything

(1)

Using Help topics

(1)

Examining recorded macro code: Using the VB Editor and Help

(1)

Optional parameters

(1)

Defined constants

(3)

Properties can return objects

(1)

Using debugging tools to figure out recorded code

(1)

Stepping through code

(2)

More debugging options: Breakpoints

(1)

Backing up or moving forward in code

(1)

Not stepping through each line of code

(1)

Querying anything while stepping through code

(2)

Using a watch to set a breakpoint

(1)

Using a watch on an object

(1)

Object Browser: The ultimate reference

(1)

Seven tips for cleaning up recorded code

(1)

Tip 1 Don't select anything

(1)

Tip 2 Use Cells(2, 5) because it's more convenient than Range("E2")

(1)

Tip 3 Use more reliable ways to find the last row

(2)

Tip 4 Use variables to avoid hard-coding rows and formulas

(1)

Tip 5 Use R1C1 formulas that make your life easier

(1)

Tip 6 Copy and paste in a single statement

(1)

Tip 7 Use With... End With to perform multiple actions

(2)

Next steps

(1)

Chapter 3 Referring to ranges

(16)

The Range object

(1)

Syntax for specifying a range

(1)

Referencing named ranges

(1)

Shortcut for referencing ranges

(1)

Referencing ranges in other sheets

(1)

Referencing a range relative to another range

(1)

Using the Cells property to select a range

(1)

Using the Offset property to refer to a range

(2)

Using the Resize property to change the size of a range

(1)

Using the Columns and Rows properties to specify a range

(1)

Using the Union method to join multiple ranges

(1)

Using the Intersect method to create a new range from overlapping ranges

(1)

Using the is Empty function to check whether a cell is empty

(1)

Using the Current Region property to select a data range

(3)

Using the Areas collection to return a noncontiguous range

(1)

Referencing tables

(1)

Next steps

(1)

Chapter 4 Looping and flow control

(18)

For...Next loops

(3)

Using variables in the For statement

(1)

Variations on the For. Next loop

(1)

Exiting a loop early after a condition is met

(1)

Nesting one loop inside another loop

(1)

Do loops

(2)

Using the While or Until clause in Do loops

(2)

The VBA loop: For Each

(1)

Object variables

(2)

Flow control: Using If... Then... Else and Select Case

(1)

Basic flow control: If... Then... Else

(2)

Using Select Case... End Select for multiple conditions

(3)

Next steps

(1)

Chapter 5 R1C1-style formulas

(10)

Toggling to R1C1-style references

(1)

Witnessing the miracle of Excel formulas

(1)

Entering a formula once and copying 1,000 times

(1)

The secret: It's not that amazing

(1)

Understanding the R1C1 reference style

(1)

Using R1C1 with relative references

(1)

Using R1C1 with absolute references

(1)

Using R1C1 with mixed references

(1)

Referring to entire columns or rows with R1C1 style

(1)

Replacing many A1 formulas with a single R1C1 formula

(1)

Remembering column numbers associated with column letters

(1)

Next steps

(1)

Chapter 6 Creating and manipulating names in VBA

(14)

Global versus local names

(1)

Adding names

(2)

Deleting names

100

(1)

Adding comments

100

(1)

Types of names

101

(1)

Formulas

101

(1)

Strings

101

(2)

Numbers

103

(1)

Tables

103

(1)

Using arrays in names

104

(1)

Reserved names

104

(2)

Hiding names

106

(1)

Checking for the existence of a name

106

(3)

Next steps

109

(2)

Chapter 7 Event programming

111

(14)

Levels of events

111

(1)

Using events

112

(1)

Event parameters

112

(1)

Enabling events

113

(1)

Workbook events

113

(2)

Workbook-level sheet events

115

(1)

Worksheet events

116

(2)

Chart events

118

(1)

Embedded charts

118

(1)

Embedded chart and chart sheet events

119

(1)

Application-level events

120

(4)

Next steps

124

(1)

Chapter 8 Arrays

125

(8)

Declaring an array

125

(1)

Declaring a multidimensional array

126

(1)

Filling an array

127

(1)

Retrieving data from an array

128

(1)

Using arrays to speed up code

129

(1)

Using dynamic arrays

130

(1)

Passing an array

131

(1)

Next steps

132

(1)

Chapter 9 Creating classes and collections

133

(20)

Inserting a class module

133

(1)

Trapping application and embedded chart events

134

(1)

Application events

134

(2)

Embedded chart events

136

(1)

Creating a custom object

137

(2)

Using a custom object

139

(1)

Using collections

140

(1)

Creating a collection

140

(1)

Creating a collection in a standard module

141

(1)

Creating a collection in a class module

142

(3)

Using dictionaries

145

(3)

Using user-defined types to create custom properties

148

(4)

Next steps

152

(1)

Chapter 10 Userforms: An introduction

153

(22)

Input boxes

153

(1)

Message boxes

154

(1)

Creating a userform

155

(1)

Calling and hiding a userform

156

(1)

Programming userforms

157

(1)

Userform events

157

(1)

Programming controls

158

(1)

Using basic form controls

159

(1)

Using labels, text boxes, and command buttons

159

(3)

Deciding whether to use list boxes or combo boxes in forms

162

(1)

Using the Multi Select property of a list box

163

(2)

Adding option buttons to a userform

165

(2)

Adding graphics to a userform

167

(1)

Using a spin button on a userform

168

(1)

Using the Multi Page control to combine forms

169

(2)

Verifying field entry

171

(1)

Illegal window closing

172

(1)

Getting a file name

173

(1)

Next steps

174

(1)

Chapter 11 Data mining with Advanced Filter

175

(36)

Replacing a loop with AutoFilter

175

(3)

Using AutoFilter techniques

178

(3)

Selecting visible cells only

181

(2)

Advanced Filter---easier in VBA than in Excel

183

(1)

Using the Excel interface to build an advanced filter

184

(1)

Using Advanced Filter to extract a unique list of values

184

(1)

Extracting a unique list of values with the user interface

185

(1)

Extracting a unique list of values with VBA code

186

(4)

Getting unique combinations of two or more fields

190

(1)

Using Advanced Filter with criteria ranges

191

(1)

Joining multiple criteria with a logical OR

192

(1)

Joining two criteria with a logical AND

193

(1)

Other slightly complex criteria ranges

193

(1)

The most complex criteria: Replacing the list of values with a condition created as the result of a formula

193

(1)

Setting up a condition using computed criteria

194

(7)

Using Filter In Place in Advanced Filter

201

(1)

Catching no records when using a filter in place

201

(1)

Showing all records after running a filter in place

202

(1)

The real workhorse: xlFilterCopy with all records rather than unique records only

202

(1)

Copying all columns

202

(1)

Copying a subset of columns and reordering

203

(6)

Excel in practice: Turning off a few drop-down menus in the AutoFilter

209

(1)

Next steps

210

(1)

Chapter 12 Using VBA to create pivot tables

211

(46)

Understanding how pivot tables evolved over various Excel versions

211

(1)

Building a pivot table in Excel VBA

212

(1)

Defining the pivot cache

213

(1)

Creating and configuring the pivot table

213

(1)

Adding fields to the data area

214

(3)

Learning why you cannot move or change part of a pivot report

217

(1)

Determining the size of a finished pivot table to convert the pivot table to values

217

(3)

Using advanced pivot table features

220

(1)

Using multiple value fields

220

(1)

Grouping daily dates to months, quarters, or years

221

(2)

Changing the calculation to show percentages

223

(3)

Eliminating blank cells in the Values area

226

(1)

Controlling the sort order with AutoSort

226

(1)

Replicating the report for every product

226

(3)

Filtering a data set

229

(1)

Manually filtering two or more items in a pivot field

229

(1)

Using the conceptual filters

230

(4)

Using the search filter

234

(3)

Setting up slicers to filter a pivot table

237

(4)

Setting up a timeline to filter an Excel pivot table

241

(2)

Formatting the intersection of values in a pivot table

243

(1)

Using the Data Model in Excel

244

(1)

Adding both tables to the Data Model

244

(1)

Creating a relationship between the two tables

245

(1)

Defining the pivot cache and building the pivot table

245

(1)

Adding model fields to the pivot table

246

(1)

Adding numeric fields to the Values area

246

(1)

Putting it all together

247

(2)

Using other pivot table features

249

(1)

Calculated data fields

249

(1)

Calculated items

250

(1)

Using ShowDetail to filter a record set

250

(1)

Changing the layout from the Design tab

250

(1)

Settings for the report layout

251

(1)

Suppressing subtotals for multiple row fields

252

(1)

Comparing VBA to TypeScript

253

(3)

Next steps

256

(1)

Chapter 13 Excel power

257

(28)

File operations

257

(1)

Listing files in a directory

257

(3)

Importing and deleting a CSV file

260

(1)

Reading a text file into memory and parsing

260

(1)

Combining and separating workbooks

261

(1)

Separating worksheets into workbooks

261

(1)

Combining workbooks

262

(1)

Copying data to separate worksheets without using Filter

263

(1)

Exporting data to an XML file

264

(1)

Placing a chart in a cell note

265

(2)

Tracking user changes

267

(1)

Techniques for VBA pros

268

(1)

Creating an Excel state class module

268

(2)

Drilling-down a pivot table

270

(1)

Filtering an OLAP pivot table by a list of items

271

(2)

Creating a custom sort order

273

(1)

Creating a cell progress indicator

274

(1)

Using a protected password box

275

(2)

Selecting with SpecialCells

277

(1)

Resetting a table's format

278

(1)

Using VBA Extensibility to add code to new workbooks

279

(1)

Converting a fixed-width report to a data set

280

(4)

Next steps

284

(1)

Chapter 14 Sample user-defined functions

285

(28)

Creating user-defined functions

285

(1)

Building a simple custom function

286

(2)

Sharing UDFs

288

(1)

Useful custom Excel functions

288

(1)

Checking whether a workbook is open

288

(1)

Checking whether a sheet in an open workbook exists

289

(1)

Counting the number of workbooks in a directory

290

(1)

Retrieving the user ID

291

(1)

Retrieving date and time of last save

292

(1)

Retrieving permanent date and time

292

(1)

Validating an email address

293

(2)

Summing cells based on interior color

295

(1)

Counting unique values

296

(1)

Finding the first nonzero-length cell in a range

296

(1)

Substituting multiple characters

297

(1)

Retrieving numbers from mixed text

298

(1)

Converting week number into date

299

(1)

Sorting and concatenating

300

(1)

Sorting numeric and alpha characters

301

(2)

Searching for a string within text

303

(1)

Returning the addresses of duplicate maximum values

304

(1)

Returning a hyperlink address

305

(1)

Returning the column letter of a cell address

305

(1)

Using Select...Case on a worksheet

306

(1)

Creating LAMBDA functions

307

(1)

Building a simple LAMBDA function

307

(1)

Sharing LAMBDA functions

308

(1)

Useful LAMBDA functions

309

(2)

Next steps

311

(2)

Chapter 15 Creating charts

313

(26)

Using AddChart2 to create a chart

314

(1)

Understanding chart styles

315

(3)

Formatting a chart

318

(1)

Referring to a specific chart

318

(1)

Specifying a chart title

319

(1)

Applying a chart color

320

(2)

Filtering a chart

322

(1)

Using SetElement to emulate changes from the plus icon

322

(5)

Using the Format tab to micromanage formatting options

327

(1)

Changing an object's fill

328

(3)

Formatting line settings

331

(1)

Creating a combo chart

331

(4)

Creating map charts

335

(1)

Creating waterfall charts

336

(1)

Exporting a chart as a graphic

337

(1)

Considering backward compatibility

337

(1)

Next steps

338

(1)

Chapter 16 Data visualizations and conditional formatting

339

(24)

VBA methods and properties for data visualizations

340

(2)

Adding data bars to a range

342

(4)

Adding color scales to a range

346

(1)

Adding icon sets to a range

347

(1)

Specifying an icon set

348

(2)

Specifying ranges for each icon

350

(1)

Using visualization tricks

350

(1)

Creating an icon set for a subset of a range

351

(1)

Using two colors of data bars in a range

352

(3)

Using other conditional formatting methods

355

(1)

Formatting cells that are above or below average

355

(1)

Formatting cells in the top 10 or bottom 5

355

(1)

Formatting unique or duplicate cells

356

(2)

Formatting cells based on their value

358

(1)

Formatting cells that contain text

358

(1)

Formatting cells that contain dates

359

(1)

Formatting cells that contain blanks or errors

359

(1)

Using a formula to determine which cells to format

359

(2)

Using the new NumberFormat property

361

(1)

Next steps

362

(1)

Chapter 17 Dashboarding with sparklines in Excel

363

(22)

Creating sparklines

363

(3)

Scaling sparklines

366

(3)

Formatting sparklines

369

(1)

Using theme colors

369

(4)

Using RGB colors

373

(1)

Formatting sparkline elements

374

(3)

Formatting win/loss charts

377

(1)

Creating a dashboard

378

(1)

Observations about sparklines

379

(1)

Creating hundreds of individual sparklines in a dashboard

379

(4)

Next steps

383

(2)

Chapter 18 Reading from the web using M and VBA

385

(28)

Get credentials for accessing an API

386

(1)

Build a query in Power Query using the M language to retrieve data from the web for one specific value

387

(3)

Refreshing the credentials after they expire

390

(1)

Building a custom function in Power Query

390

(3)

Using the new function in your code

393

(1)

Duplicating an existing query to make a new query

393

(2)

Querying the list of songs on an album

395

(1)

Generalizing the queries using VBA

396

(1)

Simplifying the SearchArtist query to a single line of code

396

(1)

Simplifying the ArtistAlbums query

396

(1)

Simplifying the AlbumTracks query

397

(1)

Grouping queries to clean up the queries list

397

(1)

Planning the arrangement of query results on your dashboard

398

(4)

Using global variables and loops in M

402

(1)

Storing global variables in a Settings record in Power Query

402

(1)

Simple error handling using try with otherwise

403

(1)

Using If logic in M

403

(1)

Looping using List.Generate

404

(2)

Application. On Time to periodically analyze data

406

(1)

Using Ready mode for scheduled procedures

407

(1)

Specifying a window of time for an update

407

(1)

Canceling a previously scheduled macro

408

(1)

Closing Excel cancels all pending scheduled macros

408

(1)

Scheduling a macro to run X minutes in the future

408

(1)

Scheduling a verbal reminder

409

(1)

Scheduling a macro to run every two minutes

410

(1)

Next steps

411

(2)

Chapter 19 Text file processing

413

(14)

Importing from text files

413

(1)

Importing text files with fewer than 1,048,576 rows

413

(6)

Dealing with text files with more than 1,048,576 rows

419

(5)

Writing text files

424

(1)

Next steps

425

(2)

Chapter 20 Automating Word

427

(20)

Using early binding to reference a Word object

427

(3)

Using late binding to reference a Word object

430

(1)

Using the New keyword to reference the Word application

430

(1)

Using the CreateObject function to create a new instance of an object

431

(1)

Using the GetObject function to reference an existing instance of Word

431

(2)

Using constant values

433

(1)

Using the Watches window to retrieve the real value of a constant

433

(1)

Using the Object Browser to retrieve the real value of a constant

433

(2)

Understanding Word's objects

435

(1)

The Document object

435

(8)

Controlling form fields in Word

443

(2)

Next steps

445

(2)

Chapter 21 Using Access as a back end to enhance multiuser access to data

447

(18)

ADO versus DAO

448

(2)

The tools of ADO

450

(2)

Adding a record to a database

452

(1)

Retrieving records from a database

453

(2)

Updating an existing record

455

(3)

Deleting records via ADO

458

(1)

Summarizing records via ADO

458

(1)

Other utilities via ADO

459

(1)

Checking for the existence of tables

460

(1)

Checking for the existence of a field

461

(1)

Adding a table on the fly

461

(1)

Adding a field on the fly

462

(1)

SQL Server examples

463

(1)

Next steps

464

(1)

Chapter 22 Advanced userform techniques

465

(26)

Using the UserForm toolbar in the design of controls on userforms

465

(1)

More userform controls

466

(1)

CheckBox controls

466

(2)

TabStrip controls

468

(2)

RefEdit controls

470

(1)

ToggleButton controls

471

(1)

Using a scrollbar as a slider to select values

472

(1)

Controls and collections

473

(2)

Modeless userforms

475

(1)

Using hyperlinks in userforms

476

(1)

Adding controls at runtime

477

(2)

Resizing the userform on the fly

479

(1)

Adding a control on the fly

479

(1)

Sizing on the fly

479

(1)

Adding other controls

480

(1)

Adding an image on the fly

480

(1)

Putting it all together

481

(2)

Adding help to a userform

483

(1)

Showing accelerator keys

483

(1)

Adding control tip text

484

(1)

Creating the tab order

484

(1)

Coloring the active control

485

(2)

Creating transparent forms

487

(2)

Next steps

489

(2)

Chapter 23 The Windows Application Programming Interface (API)

491

(10)

Understanding an API declaration

492

(1)

Using an API declaration

493

(1)

Making 32-bit- and 64-bit-compatible API declarations

493

(1)

API function examples

494

(1)

Retrieving the computer name

495

(1)

Checking whether an Excel file is open on a network

495

(1)

Retrieving display-resolution information

496

(1)

Customizing the About dialog box

497

(1)

Disabling the X for closing a userform

498

(1)

Creating a running timer

498

(1)

Playing sounds

499

(1)

Next steps

500

(1)

Chapter 24 Handling errors

501

(16)

What happens when an error occurs?

501

(2)

A misleading Debug error in userform code

503

(2)

Basic error handling with the On Error GoTo syntax

505

(1)

Generic error handlers

506

(1)

Handling errors by choosing to ignore them

506

(2)

Suppressing Excel warnings

508

(1)

Encountering errors on purpose

509

(1)

Training your clients

509

(1)

Errors that won't show up in Debug mode

510

(1)

Errors while developing versus errors months later

510

(1)

Runtime error 9: Subscript out of range

511

(1)

Runtime error 1004: Method range of object global failed

512

(1)

The ills of protecting code

513

(2)

E-raamat: Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)

DRM piirangud

Kopeerimine (copy/paste):

Printimine:

Kasutamine:

Konto & seaded

Otsing

Otsingu andmebaas

Filtreeri tulemusi

Teemad E-raamatute teemad

Vali ostukorv