Excel office script

Microsoft 365® Excel Office ScriptsMicrosoft 365® Excel Office Scripts by Michael AlexanderMicrosoft 365® Excel Office Scripts For Dummies® Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com Copyright © 2026 by John Wiley & Sons, Inc. All rights reserved, including rights for text and data mining and training of artificial technologies or similar technologies. Media and software compilation copyright © 2026 by John Wiley & Sons, Inc. All rights reserved, including rights for text and data mining and training of artificial technologies or similar technologies. No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without the prior written permission of the Publisher or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 750-4470, or on the web at www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions. The manufacturer’s authorized representative according to the EU General Product Safety Regulation is Wiley-VCH GmbH, Boschstr. 12, 69469 Weinheim, Germany, e-mail: [email protected]. Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. Microsoft 365 and Excel are trademarks or registered trademarks of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book. Microsoft 365® Excel Office Scripts For Dummies® is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved, by Microsoft Corporation. LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ. For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002. For technical support, please visit https://hub.wiley.com/community/support/dummies. Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media that is not included in the version you purchased, you may download this material at http://booksupport. wiley.com. For more information about Wiley products, visit www.wiley.com. Library of Congress Control Number: 2026942666 ISBN 978-1-394-41269-3 (pbk); ISBN 978-1-394-41271-6 (ebk); ISBN 978-1-394-41270-9 (ebk)Contents at a Glance Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Part 1: The Fundamentals of Excel Office Scripts . . . . . . . . . . . . 5 CHAPTER 1: Introducing Office Scripts for Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 CHAPTER 2: Exploring the Office Scripts Environment . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Part 2: Basic Programming Concepts in Office Scripts . . . . . 33 CHAPTER 3: Understanding Scripting Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 CHAPTER 4: Handling Errors with Style . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 CHAPTER 5: Controlling Program Flow and Conditions . . . . . . . . . . . . . . . . . . . . . . . . . 81 Part 3: Scripting Common Excel Objects . . . . . . . . . . . . . . . . . . . . 103 CHAPTER 6: Starting with Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 CHAPTER 7: Working with Ranges and Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131 CHAPTER 8: Pivoting with Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163 CHAPTER 9: Scripting Visual Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185 Part 4: Going Beyond Excel with Power Automate . . . . . . . 221 CHAPTER 10: Introducing Power Automate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223 CHAPTER 11: Integrating Excel with Email . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247 CHAPTER 12: Integrating Excel with SharePoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267 Part 5: The Part of Tens . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283 CHAPTER 13: Ten Best Practices When Writing Scripts . . . . . . . . . . . . . . . . . . . . . . . . 285 CHAPTER 14: Ten Tips for Improving Script Performance . . . . . . . . . . . . . . . . . . . . . . 299 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309Table of Contents vii Table of Contents INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 About This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Foolish Assumptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Icons Used in This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Beyond the Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Where to Go from Here . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 PART 1: THE FUNDAMENTALS OF EXCEL OFFICE SCRIPTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 CHAPTER 1: Introducing Office Scripts for Excel . . . . . . . . . . . . . . . . . . . 7 Seeing How VBA and Office Scripts Differ . . . . . . . . . . . . . . . . . . . . . . . . 8 Offline versus online . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Types of procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Code storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Script triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Understanding the Requirements and Limitations of Office Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Subscription requirement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Technical and organizational limitations . . . . . . . . . . . . . . . . . . . . . 12 Data limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Meager documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Benefits of Office Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 CHAPTER 2: Exploring the Office Scripts Environment . . . . . . . . . . . 15 Diving into the Automate Tab . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Writing Your First Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Sharing Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Emailing the .osts file . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Moving or copying the .osts file to a shared location............21 Attaching scripts to your workbook and adding a button . . . . . . . 22 Detaching scripts from your workbook . . . . . . . . . . . . . . . . . . . . . . 23 Creating Office Scripts with Action Recorder . . . . . . . . . . . . . . . . . . . . 23 Recording your actions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Renaming your script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Running your script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Editing a script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26viii Microsoft 365 Excel Office Scripts For Dummies Deleting scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 Limitations of Action Recorder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Using the Insert Action Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Comparing Absolute Mode and Relative Mode Recording . . . . . . . . . 30 PART 2: BASIC PROGRAMMING CONCEPTS IN OFFICE SCRIPTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 CHAPTER 3: Understanding Scripting Fundamentals . . . . . . . . . . . . 35 Understanding the Object Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Understanding Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 Enhancing Code with Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Using let versus const . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Understanding data types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Adding Clarity with Comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Adding Flexibility with Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 Defining a function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 Understanding function return values . . . . . . . . . . . . . . . . . . . . . . . 57 Using function parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 CHAPTER 4: Handling Errors with Style . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 Exploring Office Scripts Debugging Tools . . . . . . . . . . . . . . . . . . . . . . . 64 Using the console.log method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Understanding try . . . catch statements . . . . . . . . . . . . . . . . . . . . . 67 Examining Error-Handling Patterns . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 The try . . . catch . . . finally pattern . . . . . . . . . . . . . . . . . . . . . . . . . . 72 The validation-first pattern . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 The guard clause pattern . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 The fallback pattern . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 Contending with Common Error Types . . . . . . . . . . . . . . . . . . . . . . . . . 75 Syntax errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 Reference errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 Object not found errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 Invalid range and out-of-bounds errors . . . . . . . . . . . . . . . . . . . . . . 78 Invalid arguments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 CHAPTER 5: Controlling Program Flow and Conditions . . . . . . . . . . 81 Getting Logical with Conditions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 Using the basic if statement.................................82 A word about equality operators . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 Planning for false conditions with if . . . else statements.........84 Extending conditional checks with if . . . else if statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 Simplifying if statements with switch statements . . . . . . . . . . . . . 87