Excel by Example

Excel by Example
A Microsoft Excel Cookbook for Electronics Engineers
By Aubrey Kagan
Acknowledgments .xiii
Introduction .xiv
What’s on the CD-ROM? xviii
EXAMPLE 1: Voltage-to-Current Converter 1
Model Description 1
Starting Excel .2
Data Entry into a Worksheet .3
Autofill .5
Bulk Formatting .7
Formulas .8
Copying Formulas 9
Relative and Absolute References .10
Naming Cells 12
Hiding Cells .15
Borders 15
Bells and Whistles 17
Conditional IF and Absolute Value .17
Chart 17
Error Bars 20
Adding a Trendline 21
Macro: Timer 22
EXAMPLE 2: Baud Rate Selection .26
Model Description 26
Setup Workbook 27
Hexadecimal .29
Lookup Tables 30
Conditional Formatting .33
Macro .35
EXAMPLE 3: Mean Time Between Failures (MTBF) 41
Model Description 41
Factors 42
ContentsBill of Material .44
Calculating the Quality Factor 49
Calculate Electrical Stress Factor .50
Calculation of ?G 52
Scenario 54
EXAMPLE 4: Counting Machine Cycles .58
Model Description 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
Model Description 69
Creating the Basic Workbook 70
LEN Function .71
Forms Controls .71
Text Orientation 75
Comments 75
Double-Click Macro 76
Macro Activation by the Command Button .78
Save to Data File 81
Usage 85
EXAMPLE 6: 8052 Microcomputer Register Setup 86
Model Description 86
Spreadsheet Concept .86
Counter/Timer 0 Sheet 93
Timer Counter Control Register TCON .97
Counting Types 97
Macros to Hide and Unhide 102
Adding Forms .106
Add Image Control 108
Timer/Counter 1 Sheet 111
Timer/Counter 2 Sheet 112
Serial Port Sheet 113
Interrupt Control Sheet .113
Summary Sheet 115
Initialize Values 116
Conclusion .116
EXAMPLE 7: Finding the Optimal Resistor Combination: LP 2951 118
Model Description 118
Custom Autofill 118
Data Tables .120
Min Function .123
MATCH Function .123
viii
Excel by ExampleINDEX Function 124
Block Conditional Formatting .125
EXAMPLE 8: Resistor Color Code Decoder Using Speech Input 127
Model Description 127
Implementing Speech Recognition .129
Viewing and Hiding the Language Bar 129
Worksheet Setup 130
Macros 130
Custom Toolbar 134
Adding Speech .137
Evaluate the Color Code 137
Text to Speech .141
Conclusion .142
EXAMPLE 9: RTD to 4–20 mA Converter: XTR105 143
Model Description 143
Acquiring RTD Tables .144
Lookup RTD Value 148
Creating a Function .149
Accessing a Function 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 .161
Printout 162
Running Macros when the Workbook is Started 163
Running from the Desktop 166
EXAMPLE 10: Voltage Regulator: LM317 .167
Model Description 167
Installing the NearestValues Add-In .168
Initial Model 168
Goal Seek .170
Worst Case Analysis .172
Thermal Analysis .173
Half-Wave Rectification 179
True RMS and Integration .179
More Preparation .181
Standard Capacitance Value 184
Chart 186
Conclusion .189
EXAMPLE 11: TL431 Adjustable Voltage Reference 190
Model Description 190
Installing the NearestValues Add-In .190
ix
ContentsInitial Model 191
Solver .191
Standard Resistor Values 199
Adding a Macro .199
Limitations .204
EXAMPLE 12: 555 Timer 206
Model Description 206
Monostable Operation .206
Setup .207
Add User Form .207
Add Image Control 208
Second Image .210
Modifying Form Location 212
Monostable Pulse Width Entry 214
Command Button 216
Solver .219
Standard Values 220
SolverSolve 220
Using Standard Capacitor Values 221
Tidying Up 223
Astable Operation 223
Worksheet Setup 224
EXAMPLE 13: Purchase Order Generator .229
Model Description 229
Create a Purchase Order 229
Print Macro 231
User Form .233
Initial Procedure .235
Event Actions 236
Auto Startup 238
Running PurchaseOrder .239
EXAMPLE 14: Interface to a Digital MultimeterUsing a Serial Port .240
Model Description 240
DMM Interface Protocol 241
MSComm32 .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 281
Model Description 281
Pinout .282
Hardware Interface .282
x
Excel by ExampleTiming Diagram 283
Installing IO.DLL .284
PC Parallel Port 284
First Steps .286
Actual Interface .287
Acquiring Data .287
Adding Sound 292
Thoughts on Improvement 293
Statistics .294
EXAMPLE 16: Function Generator Interface .301
Model Description 301
Serial Interface .302
Workbook Open and Close 302
Adding VBA Controls: Granularity 305
Adding VBA Controls: Frequency 309
Waveform Sampling Frequency .311
Bump Frequency .313
Generating Frequency Tables .315
Add a Chart .319
Download Waveform .322
Setting the Amplitude .326
Skew .327
Average Voltage, RMS Voltage 330
APPENDIX A: VBA and Excel .333
APPENDIX B: Parallel and Serial I/O 349
References 354
About the Author .357
Index 358
List of In Parenthesis Sidebars
Copying With and Without Format .6
Autofill of Nonnumeric Sequences .7
Multiple Selections .8
Adding Columns/Rows .9
Deleting Columns/Rows/Cells 10
Cell Names 13
Worksheet Navigation 14
Zoom 20
Merge Cells .28
Number Base Conversion .29
Split Screen .30
Lookup .31
Conditional Formatting 34
Multiple Worksheets .42
xi
ContentsComma Delimited Files 45
Comments .46
INDEX .49
Table Functions .51
Recalculation and Auditing Formulas 65
Forms Controls in a Different Version of Excel 72
Cell Protection 77
Cells Notation Versus String Manipulation .78
Forms Control .87
Excel Warning Detection 99
ROUND 101
Communicating Custom Lists Between Different Computers .119
Data Tables 119
Transposing Data .121
MATCH 123
INDEX .124
Speech Recognition 129
Installing Speech Recognition 129
Exporting a Toolbar .136
CONVERT 154
InputBox 162
MessageBox .163
CHOOSE .177
Solver Options 197
Use of Constraints .198
More on Combo Boxes 213
Control Toolbox 218
SolverSolve Function 221
Calling an Excel Function from VBA 221
Additional Controls .245
MSComm Properties .246
Timer .250
DoEvents .251
OnComm Event 252
Custom Toolbar Limitation 257
OFFSET .275
COUNTA/COUNT/DCOUNT/DCOUNTA/COUNTBLANK .275
SERIES Function 276
FREQUENCY .296
NORMDIST .297
Controls in Excel 305
Combo Box Control 307
String Functions 309
Fourier Analysis .316
Fill .319
VBA and Bit Manipulation 323
xii
Excel by Example
كلمة سر فك الضغط : books-world.net
The Unzip Password : books-world.net