Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

ASAP Utilities user guide how to get the best out of this Excel add-in ASAP Uti

ASAP Utilities user guide how to get the best out of this Excel add-in ASAP Utilities user guide how to get the best out of this Excel add-in author Bastien Mensink pages 190 latest revision April 9, 2009 ASAP Utilities version 4.5.0 © 1999-2009 A Must in Every Office BV / Bastien Mensink Eekwal 31 8011 LB Zwolle The Netherlands www.amustineveryoffice.com www.asap-utilities.com Information in this document is subject to change without notice. Except as permitted by such license, no part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, recording, or otherwise, without the prior written permission of the author. While every precaution has been taken in the preparation of this user guide, the publisher and author assume no responsibility for errors or omissions, or for damages resulting from the use of the information contained in herein. Microsoft, Microsoft Office, and Excel are registered trademarks of Microsoft Corporation. FREQUENTLY UPDATED ASAP Utilities User Guide 1 Contents 1 CONTENTS__________________________________________________________ 5 2 INTRODUCTION _____________________________________________________ 9 2.1 Intended audience ................................................................................................... 9 2.2 The Excel screen in English..................................................................................... 10 3 GETTING STARTED__________________________________________________ 11 3.1 How to get a copy of ASAP Utilities ....................................................................... 11 3.2 System requirements.............................................................................................. 11 3.2.1 International versions of Excel.................................................................... 11 3.2.2 Office Update ............................................................................................ 12 3.2.3 Windows update........................................................................................ 12 3.3 Installation............................................................................................................. 12 3.3.1 Automatic installation................................................................................ 12 3.3.2 Manual installation Excel 2000, 2002/XP, 2003.......................................... 18 3.3.3 Manual installation Excel 2007 .................................................................. 20 3.3.4 Upgrade to a new version.......................................................................... 24 3.3.5 DLL registration, administrative privileges.................................................. 26 3.3.6 Network installation................................................................................... 26 3.4 Macro security ....................................................................................................... 29 3.4.1 Message: Macros are disabled ................................................................... 30 3.4.2 Trusted sources.......................................................................................... 32 3.5 How to operate ASAP Utilities................................................................................ 33 3.5.1 How to use ASAP Utilities in your own macros .......................................... 34 3.6 Undo...................................................................................................................... 34 3.6.1 Not all tools in ASAP Utilities have an undo............................................... 34 3.6.2 No undo in macros by design of Excel ....................................................... 34 3.7 Uninstall................................................................................................................. 34 3.7.1 Automatic.................................................................................................. 34 3.7.2 Automatic uninstall #2.............................................................................. 35 3.7.3 Manual ...................................................................................................... 36 3.7.4 Temporarily uninstalling ASAP Utilities....................................................... 38 3.8 User data and registry settings............................................................................... 40 3.8.1 Registry...................................................................................................... 40 3.8.2 Favorites menu .......................................................................................... 40 3.8.3 Add ASAP Utilities to the Excel menu for all users...................................... 41 Contents 5 ASAP Utilities User Guide FREQUENTLY UPDATED 3.8.4 Registering the DLL.....................................................................................41 3.8.5 Registration/license settings........................................................................41 4 PURCHASE ASAP UTILITIES __________________________________________ 42 4.1 When to buy a license.............................................................................................42 4.2 Key benefits of registration.....................................................................................42 4.3 How to order ..........................................................................................................42 4.3.1 Instant delivery ...........................................................................................42 4.3.2 Secure ordering ..........................................................................................43 4.4 How to turn ASAP Utilities into a registered version ...............................................43 5 LIST OF TOOLS _____________________________________________________ 44 5.1 The menu bar .........................................................................................................44 5.2 Short descriptions...................................................................................................45 5.2.1 Vision control... ..........................................................................................45 5.2.2 Favorites & Shortcuts..................................................................................46 5.2.3 Select..........................................................................................................48 5.2.4 Sheets.........................................................................................................55 5.2.5 Range .........................................................................................................63 5.2.6 Columns & Rows.........................................................................................68 5.2.7 Numbers.....................................................................................................74 5.2.8 Text.............................................................................................................76 5.2.9 Formula ......................................................................................................81 5.2.10 Fill.............................................................................................................85 5.2.11 Format......................................................................................................88 5.2.12 Objects & Comments ................................................................................93 5.2.13 Web..........................................................................................................95 5.2.14 System......................................................................................................97 5.2.15 Information ............................................................................................101 5.2.16 Import ....................................................................................................105 5.2.17 Export.....................................................................................................107 5.2.18 Start .......................................................................................................113 5.2.19 ASAP Utilities Options.............................................................................114 5.2.20 Find and run a utility... ...........................................................................120 5.2.21 Info.........................................................................................................121 6 EXTRA WORKSHEET FUNCTIONS____________________________________ 122 6.1 Worksheet functions.............................................................................................122 6.1.1 =ASAPFullFileName()................................................................................122 Chapter 1 6 FREQUENTLY UPDATED ASAP Utilities User Guide 6.1.2 =ASAPFileName() .................................................................................... 122 6.1.3 =ASAPFilePath()....................................................................................... 122 6.1.4 =ASAPSheetName()................................................................................. 122 6.1.5 =ASAPCountShades(range) ..................................................................... 122 6.1.6 = ASAPCellColorIndex(range) .................................................................. 122 6.1.7 =ASAPFontColorIndex(range) .................................................................. 123 6.1.8 =ASAPSumByFontColor(range, color number)......................................... 123 6.1.9 =ASAPSumByCellColor(range, color number) .......................................... 123 6.1.10 =ASAPCountByCellColor(Lookin_Range, Color_Index_Nr)...................... 124 6.1.11 =ASAPCountByFontColor(Lookin_Range, Color_Index_Nr)..................... 124 6.1.12 =ASAPGetComment(range)................................................................... 125 6.1.13 =ASAPGetHyperlink(range, optional sNA) ............................................. 125 6.1.14 =ASAPSpellNumber()............................................................................. 125 6.1.15 =ASAPExtractNumbers(ref).................................................................... 126 6.1.16 =ASAPStripNumbers(ref) ....................................................................... 127 6.1.17 =ASAPGetFontName(range) .................................................................. 127 6.1.18 =ASAPGetFontSize(range) ..................................................................... 127 6.1.19 =ASAPGetNumberformat(range)........................................................... 128 6.1.20 =ASAPIsFormula(range)......................................................................... 128 6.1.21 =ASAPCountChar(SearchText, SearchValue) .......................................... 128 6.1.22 =ASAPExtractFilename(FullFilename, optional separator) ...................... 128 6.1.23 =ASAPExtractFolderName(FullFilename, optional separator) ................. 129 6.1.24 =ASAPGetFormula(FormulaCell) ............................................................ 129 6.1.25 =ASAPGetFormulaInt(FormulaCell)........................................................ 129 6.1.26 =ASAPMergeCells(inputrange, delimiter, skipemptycells)...................... 130 6.1.27 =ASAPRandomPassword(length, use_symbols) ..................................... 130 6.1.28 =ASAPIsBold(range) .............................................................................. 131 6.2 Examples.............................................................................................................. 131 6.3 Remarks on distributing....................................................................................... 131 6.4 Remarks on calculation ........................................................................................ 132 7 HOW TO?_________________________________________________________ 133 7.1 Introduction......................................................................................................... 133 7.2 How to... examples .............................................................................................. 133 8 TROUBLESHOOTING FAQ___________________________________________ 163 8.1 Bug report guidelines........................................................................................... 163 8.1.1 The basics: what you did, what you wanted, and what happened........... 163 Contents 7 ASAP Utilities User Guide FREQUENTLY UPDATED 8.1.2 Excel and windows version .......................................................................163 8.1.3 Always search our website and documentation first.................................164 8.1.4 Be brief, but don't leave any important details out...................................164 8.1.5 Use meaningful, specific subject headers..................................................164 8.1.6 Use English or Dutch ................................................................................165 8.1.7 Don't report bugs about old versions........................................................165 9 HISTORY OF ASAP UTILITIES________________________________________ 166 9.1 The primary goals of ASAP Utilities .......................................................................167 10 CONTACT & SUPPORT _____________________________________________ 168 10.1 Get the latest information on ASAP Utilities .........................................................168 10.2 Contact us ............................................................................................................168 10.3 About the author..................................................................................................168 11 APPENDIX SCREENSHOTS __________________________________________ 170 11.1 Screenshots from the ASAP Utilities submenus.....................................................170 11.1.1 Excel 2000, 2002/XP and 2003 ...............................................................170 11.1.2 Excel 2007 ..............................................................................................180 12 INDEX____________________________________________________________ 189 Chapter 1 8 FREQUENTLY UPDATED ASAP Utilities User Guide 2 Introduction Welcome to the ASAP Utilities User Guide. This User Guide will show you what you can do with ASAP Utilities. It will tell you how to install the add-in and it gives a description of all the utilities available. ASAP Utilities is a powerful Excel add-in that fills the gaps in Excel, and automates frequently used tasks. Since 1999 it has grown to become probably one of the world’s most popular add-ins for MS Excel. In my opinion a good program should be easy to use without the need of any documentation. However during the last years the need for documentation has increased. ASAP Utilities has more tools then ever and this user guide will help you to get the best out of it. Besides documenting the utilities in ASAP Utilities I have also included a “How to” chapter. In that chapter (chapter 7, “How to?” on page 133) I have listed common Excel questions and how to use ASAP Utilities to answer them. The most recent version of this User Guide is available on our website: http://www.asap-utilities.com This document currently covers everything available in version 4.2.5 which is at this moment officially the new version. Some utilities might not be available in version 3.10L which is available for people that have either Excel 97 or are not able to install version 4. 2.1 Intended audience The principal audience for this user guide is people that use Excel on a regular basis. The focus is on introducing you to the tools available in ASAP Utilities. We assume that you are already familiar with Excel and its structure (workbooks, worksheets, ranges etc.). If you do not use the international version of Excel, I recommend you to read paragraph 2.2, “The Excel screen in English” on page 10. Introduction 9 ASAP Utilities User Guide FREQUENTLY UPDATED 2.2 The Excel screen in English This documentation is in English since most people that use Excel speak this language. Despite the fact that most people speak English, translating certain things in Excel might become difficult or look odd. That is why we show you the layout of Excel and how certain things are called in English. The different sheet types in English: menu bar command bars or toolbars formula bar grid lines selection or range of cells page break vertical scrollbar horizontal scrollbar (work)sheet tabs column headings row headings active cell name box status bar title bar Chapter 2 10 FREQUENTLY UPDATED ASAP Utilities User Guide 3 Getting started 3.1 How to get a copy of ASAP Utilities You can download a copy of ASAP Utilities from our website: http://www.asap-utilities.com If you don’t have access to the internet but you do have email, you can send an email to the following address and you will get a reply email with ASAP Utilities attached: download@asap-utilities.com If you have neither an internet connection nor an email address you have to ask a colleague or friend. We only provide support through the Internet. Alternatively, you can also order ASAP Utilities on a cd-rom. 3.2 System requirements ASAP Utilities will run on all computers that meet the requirements of Excel 2000, 2002/XP 2003 or 2007 and have MS Windows as operating system (for example Windows 2000, XP, Vista, 64 bit systems, Terminal Server etc.). For people that still use Excel 97 there is a separate version available (at this moment version 3.10L). ASAP Utilities needs approximately 15 MB of disk space. This is for the program files and the documentation together. The recommended minimum screen resolution is 1024 x 768 pixels although ASAP Utilities will also work on smaller screens. “If you can run MS Windows Excel version 2000 or newer, you can use ASAP Utilities.” When the installation is finished every user on the computer is able to use ASAP Utilities but during the installation you must run the setup as administrator or must be logged in as an administrator or as a member of the Power Users group1. More information running the setup if you are not a member of the administrator- or power users group is on our website: http://www.asap-utilities.com/faq-questions-answers- detail.php?m=109 3.2.1 International versions of Excel ASAP Utilities uploads/s3/ asap-utilities-user-guide.pdf