Excel by Example This page intentionally left blank Excel by Example A Microsoft® Excel Cookbook for Electronics Engineers By Aubrey Kagan AMSTERDAM • BOSTON • HEIDELBERG • LONDON NEW YORK • OXFORD • PARIS • SAN DIEGO SAN FRANCISCO • SINGAPORE • SYDNEY • TOKYO Newnes is an imprint of Elsevier Newnes is an imprint of Elsevier 200 Wheeler Road, Burlington, MA 01803, USA Linacre House, Jordan Hill, Oxford OX2 8DP, UK Copyright © 2004, Elsevier Inc. All rights reserved. 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, or otherwise, without the prior written permission of the publisher. Permissions may be sought directly from Elsevier’s Science & Technology Rights De- partment in Oxford, UK: phone: (+44) 1865 843830, fax: (+44) 1865 853333, e-mail: permissions@elsevier.
You may also complete your request on-line via the Elsevier homepage (http://elsevier.com), by selecting “Customer Support” and then “Obtaining Per- missions.” Recognizing the importance of preserving what has been written, Elsevier prints its books on acid-free paper whenever possible. Library of Congress Cataloging-in-Publication Data (Application submitted.) British Library Cataloguing-in-Publication Data A catalogue record for this book is available from the British Library. ISBN: 0-7506-7756-2 For information on all Newnes publications visit our website at www.com 04 05 06 07 08 09 10 9 8 7 6 5 4 3 2 1 Printed in the United States of America. In memory of Jonathan Moshe Kagan This page intentionally left blank Contents Acknowledgments.xiv What’s on the CD-ROM?.
xviii EXAMPLE 1: Voltage-to-Current Converter .2 Data Entry into a Worksheet .9 Relative and Absolute References .15 Bells and Whistles .17 Conditional IF and Absolute Value .22 EXAMPLE 2: Baud Rate Selection .35 EXAMPLE 3: Mean Time Between Failures (MTBF) .42 vii Excel by Example Bill of Material .44 Calculating the Quality Factor .49 Calculate Electrical Stress Factor.54 EXAMPLE 4: Counting Machine Cycles .58 Importing the File .58 Extracting Op-code .62 Opening a Second Workbook .63 Cross Workbook Reference .67 Easing the Pain of Nested IFs .67 EXAMPLE 5: Character Generator .69 Creating the Basic Workbook .75 Double-Click Macro .76 Macro Activation by the Command Button .78 Save to Data File .85 EXAMPLE 6: 8052 Microcomputer Register Setup .86 Counter/Timer 0 Sheet .93 Timer Counter Control Register TCON .97 Macros to Hide and Unhide .106 Add Image Control .108 Timer/Counter 1 Sheet .111 Timer/Counter 2 Sheet .112 Serial Port Sheet .113 Interrupt Control Sheet .116 EXAMPLE 7: Finding the Optimal Resistor Combination: LP 2951 .118 Custom Autofill.123 viii Contents INDEX Function .124 Block Conditional Formatting .125 EXAMPLE 8: Resistor Color Code Decoder Using Speech Input .127 Implementing Speech Recognition .129 Viewing and Hiding the Language Bar .137 Evaluate the Color Code.137 Text to Speech .142 EXAMPLE 9: RTD to 4–20 mA Converter: XTR105 .143 Acquiring RTD Tables .144 Lookup RTD Value .150 Adding a Help Description to a Function .152 Creating the Model in Excel .152 Standard Resistor Values .155 Creation of Add-In .158 Installing the NearestValues Add-In .158 Back to the Project At Hand .159 Prompting for User Input .162 Running Macros when the Workbook is Started .163 Running from the Desktop .166 EXAMPLE 10: Voltage Regulator: LM317 .167 Installing the NearestValues Add-In .170 Worst Case Analysis.173 Half-Wave Rectification .179 True RMS and Integration .181 Standard Capacitance Value .189 EXAMPLE 11: TL431 Adjustable Voltage Reference .190 Installing the NearestValues Add-In .190 ix Excel by Example Initial Model .191 Standard Resistor Values .207 Add User Form .207 Add Image Control .210 Modifying Form Location .212 Monostable Pulse Width Entry .220 Using Standard Capacitor Values .224 EXAMPLE 13: Purchase Order Generator .229 Create a Purchase Order .239 EXAMPLE 14: Interface to a Digital MultimeterUsing a Serial Port .240 DMM Interface Protocol .243 Initializing the Serial Port .248 Conversion of DMM Display to Data .258 Analog Meter Chart.260 Zone Identification .267 Data Plot—Chart Recorder .271 Food For Thought .279 EXAMPLE 15: Vernier Caliper Interface .282 x Contents Timing Diagram.284 PC Parallel Port .292 Thoughts on Improvement .294 EXAMPLE 16: Function Generator Interface .302 Workbook Open and Close.302 Adding VBA Controls: Granularity .305 Adding VBA Controls: Frequency .309 Waveform Sampling Frequency .313 Generating Frequency Tables.322 Setting the Amplitude .327 Average Voltage, RMS Voltage .330 APPENDIX A: VBA and Excel. 333 APPENDIX B: Parallel and Serial I/O. 354 About the Author. 358 List of In Parenthesis Sidebars Copying With and Without Format .6 Autofill of Nonnumeric Sequences.8 Adding Columns/Rows .9 Deleting Columns/Rows/Cells .28 Number Base Conversion .42 xi Excel by Example Comma Delimited Files .51 Recalculation and Auditing Formulas .65 Forms Controls in a Different Version of Excel .77 Cells Notation Versus String Manipulation .87 Excel Warning Detection.101 Communicating Custom Lists Between Different Computers .129 Installing Speech Recognition .197 Use of Constraints .198 More on Combo Boxes .221 Calling an Excel Function from VBA .252 Custom Toolbar Limitation .275 COUNTA/COUNT/DCOUNT/DCOUNTA/COUNTBLANK .297 Controls in Excel .305 Combo Box Control.319 VBA and Bit Manipulation .323 xii Acknowlegments The idea of this book was introduced by Carol Lewis, and her guidance and expertise have piloted it through to publication.
Conversion of my manuscript to the product you have in your hands was done by Kelly Johnson. My thanks goes to them both and Tiffany Gasbarrini, and to those whose work at Elsevier has remained unseen to me, for what I hope you will agree is an outstanding effort. I would also like to thank the management and my co-workers at Emphatec Inc. (previously Weidmuller Canada Ltd.), especially Ernesto Gradin and Don Robinson for their support, advice and encouragement for my original articles and subsequently this book.
Thanks are also due to: Alberto Ricci Bitti for permission to use his idea, which forms the basis of Example 6, Fred Bulback for permission to include IO.DLL on the CD-ROM, Circuit Cellar and EDN for providing the format to allow me to develop my ideas and hone my writing skills. To my children, parents and sister, all of whom encouraged me to tackle this project and whose continued interest continued to motivate, thank you. In her usual self-deprecating manner, my wife, Nicky, has asked that she not be mentioned, and that acknowledgment is not needed for her support, both spiritual and logistical. Far be it from me to contradict her, but nevertheless, Thank You.
xiii Introduction When faced with a new software tool, most of us learn what we need to address our immediate problem, and then armed with 10% of the tools that are available we attempt to solve all future problems. In my discussions with colleagues, I have found that the spreadsheet is the quintessence of this effect. Almost everybody has Microsoft® Excel on their computer, yet few use it for anything but the most mundane tasks, rather like a sophisticated, but unwieldy calculator. In fact, I recently saw a newspaper article that heralded the demise of the calculator as a result of the spreadsheet, PDAs and other electronic tools.
Most of the literature on the subject of spreadsheets in general, and Microsoft Excel in particular, deal with generic cases of home economics or financial projects. Very few have direct analogies to the work done in electronics. Yet, the spreadsheet is ideally suited to allow the electronics engineer (indeed any engineer) to “work smarter, not harder.” Over the years I have worked with Supercalc, Multimate, Lotus 1-2-3, Framework, Symphony, Quattro and Quattro Pro. In the end, they all are very similar.
Most of what is covered in this book can be implemented in any one of the current competitors to Excel, without too many changes. The genesis of the book was a little circuitous. My supervisor at work suggested that we should run seminars on different subjects sharing each individual’s expertise. I thought some reference notes on Excel might be helpful.
This led to a series of three articles that were published in Circuit Cellar Online starting in January 2002. Several readers contacted me and suggested additional subject matter that would be interesting. Then, out of the blue, I was approached by Elsevier to write a book based on these articles. Since the format of a book allows for more scope, I have expanded on the original ideas, added a few, and I have also tried to incorporate much of the feedback that I received.
If you only buy one book on Excel, then of course, I hope it is mine. However, it is not my intention that this book be the only book on the subject that you will ever need. I have only tried to explain general subjects that I use in the examples, since I have found them useful. I leave the detailed explanations to the more general books that are available, since I am sure xiv Introduction they are better at it than I.
Since I am writing this book for electronics engineers, I presume a degree of familiarity with a computer, including programming, and I jump into macros fairly early. I have tried to make most of the macros into a “black box” so that if you don’t really want to know what goes on inside, but still need the function, you can. In addition, I have tried to make the examples “stand alone,” which means that some of the basic techniques like invoking the Visual Basic® Editor (VBE) are described quite frequently. The examples have been developed for this book under Excel 2002.
No doubt by the time the book is published there will be at least one new revision. Some of the original development work was created under Excel 97, so most of this should work on any version from that time. Where I am aware that a feature has been added since ‘97 (such as speech input) I hope to point them out. Please forgive me if I am less than accurate with this information.
Like most of us, after a period of use I have become settled within my knowledge of the subject. I am guilty of not extending my knowledge using more of the features of Excel. Feel free to contact me and let me know what you find useful and what you think is missing. Better yet, why don’t you submit the idea to EDN or Electronic Design and see your name in print (plus make a little money on the side).
That’s how I started; perhaps you too can write a book. An English engineer once told me that my writing style reminded him of Somerset Maugham, a British novelist from the 1930s. This is no small feat considering that I was writing specifications for a robotic arm on the International Space Station at the time. Whilst I am sure my editor will correct all my anglicized spellings, the style will likely remain.
I hope you don’t find it too distracting. It has been my experience that in any technical presentation, when the application has some glamour about it the audience is far more interested, irrespective of how mundane the technology might be. In that light, I hope that you find the ideas included in this book original, provocative and useful. Depending on work commitments, I cannot promise a speedy or detailed response, but feel free to contact me at antediluvian@sympatico.ca with comments and suggestions.
Rules of Engagement Conventions: I have adopted a fairly traditional approach to documenting data entry into Excel. Unless otherwise indicated, a click on the mouse is a click on the left mouse button. Notwithstanding that it is possible to change the allocation of the mouse keys, I am referring to the default configuration. Where a click of the (left) mouse button executes the desired action it is printed in bold text, for instance: Save.
Where there is a sequence of menus that require several mouse clicks the actions are in bold and are combined by a vertical bar, for example File | Save as. Sometimes, a series of selections will result in the presentation of file tabs.