VBA programming in business economics:
Gespeichert in:
| 1. Verfasser: | |
|---|---|
| Format: | Buch |
| Sprache: | English |
| Veröffentlicht: |
Copenhagen
Djøf Publ.
2010
|
| Ausgabe: | 1. ed. |
| Schlagworte: | |
| Online-Zugang: | Inhaltsverzeichnis |
| Beschreibung: | Includes index. |
| Beschreibung: | 332 S. graph. Darst. 23 cm |
| ISBN: | 9788757422672 |
Internformat
MARC
| LEADER | 00000nam a2200000zc 4500 | ||
|---|---|---|---|
| 001 | BV042166139 | ||
| 003 | DE-604 | ||
| 005 | 20141119 | ||
| 007 | t | ||
| 008 | 141104s2010 dk d||| |||| 00||| eng d | ||
| 010 | |a 2010484361 | ||
| 020 | |a 9788757422672 |c pbk. |9 978-87-574-2267-2 | ||
| 035 | |a (OCoLC)896823981 | ||
| 035 | |a (DE-599)BVBBV042166139 | ||
| 040 | |a DE-604 |b ger |e aacr | ||
| 041 | 0 | |a eng | |
| 044 | |a dk |c DK | ||
| 049 | |a DE-1028 | ||
| 050 | 0 | |a HF5548.4.M523 | |
| 100 | 1 | |a Wøhlk, Sanne |e Verfasser |0 (DE-588)1046418734 |4 aut | |
| 245 | 1 | 0 | |a VBA programming in business economics |c Sanne Wøhlk |
| 250 | |a 1. ed. | ||
| 264 | 1 | |a Copenhagen |b Djøf Publ. |c 2010 | |
| 300 | |a 332 S. |b graph. Darst. |c 23 cm | ||
| 336 | |b txt |2 rdacontent | ||
| 337 | |b n |2 rdamedia | ||
| 338 | |b nc |2 rdacarrier | ||
| 500 | |a Includes index. | ||
| 630 | 0 | 4 | |a Microsoft Excel (Computer file) |
| 630 | 0 | 4 | |a Microsoft Visual Basic for applications |
| 650 | 4 | |a Wirtschaft | |
| 650 | 4 | |a Business |x Computer programs | |
| 650 | 4 | |a Managerial economics |x Computer programs | |
| 650 | 4 | |a Electronic spreadsheets | |
| 856 | 4 | 2 | |m HBZ Datenaustausch |q application/pdf |u http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=027605666&sequence=000002&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA |3 Inhaltsverzeichnis |
| 999 | |a oai:aleph.bib-bvb.de:BVB01-027605666 | ||
Datensatz im Suchindex
| _version_ | 1804152666558824448 |
|---|---|
| adam_text | Titel: VBA programming in business economics
Autor: Wøhlk, Sanne
Jahr: 2010
Table of Contents
Introduction......................................................................................................................................................................................11
Chapter 1VBA and Your First Program ..............................................................................................13
1.1 What is VBA ......................................................................................................................................................................................13
1.2 The Developer Tab....................................................................................................................................................................14
1.3 The VBA Editor................................................................................................................................................................................15
1.4 Insert a Module..............................................................................................................................................................................17
1.5 Write Your First Macro........................................................................................................................................................18
1.6 Execute your First Macro................................................................................................................................................18
1.7 Save a Workbook that Contains Macros..................................................................................................21
1.8 Exercises....................................................................................................................................................................................................21
Chapter 2 The Macro Recorder..........................................................................................................................23
2.1 How to Record a Macro ....................................................................................................................................................23
2.2 Viewing the Recorded Code........................................................................................................................................27
2.3 Using Relative References..............................................................................................................................................29
2.4 Limitations of the Macro Recorder..................................................................................................................31
2.5 Exercises....................................................................................................................................................................................................32
Chapter 3 Try it Out..............................................................................................................................................................34
3.1 Getting Started................................................................................................................................................................................34
3.2 Variables ..................................................................................................................................................................................................36
3.3 Accessing the Worksheet................................................................................................................................................36
3.4 Input and Output ........................................................................................................................................................................37
3.4.1 Message Box..............................................................................................................................................................38
3.4.2 Input Box ........................................................................................................................................................................38
3.5 Using Excel and VBA Functions ..............................................................................................................................39
3.6 Making Decisions........................................................................................................................................................................41
3.7 Looping........................................................................................................................................................................................................43
3.8 Good Programming Practice......................................................................................................................................44
3.8.1 Structure Your Code ......................................................................................................................................44
3.8.2 Naming Convention........................................................................................................................................45
3.8.3 Commenting/Documenting................................................................................................................45
3.8.4 Indention ........................................................................................................................................................................45
3.9 Putting it all Together ..........................................................................................................................................................46
3.9.1 Example: Delayed Products................................................................................................................46
3.10 Exercises....................................................................................................................................................................................................48
Chapter 4 Variables..............................................................................................................................................................50
4.1 Types of Variables......................................................................................................................................................................50
4.2 Declaring Variables ..................................................................................................................................................................51
4.3 Scope of Variables......................................................................................................................................................................53
4.3.1 Local Variables........................................................................................................................................................53
4.3.2 Module and Global Variables ..........................................................................................................53
4.3.3 Who is in Charge? ....................................................................................................................................................54
4.4 Arrays.......................................................................................................................................................55
4.4.1 Dynamic Arrays......................................................................................................................................................57
4.4.2 Multi Dimensional Arrays ......................................................................................................................58
4.5 Exercises....................................................................................................................................................................................................59
Chapter 5 Subs and Functions..............................................................................................................................62
5.1 Custom Procedures..................................................................................................................................................................62
5.1.1 Subs ........................................................................................................................................................................................62
5.1.2 Functions ........................................................................................................................................................................64
5.1.3 Using Arguments in Procedures ..................................................................................................66
5.1.4 Public vs Private Procedures..............................................................................................................69
5.2 VBA Functions..................................................................................................................................................................................20
5.2.1 Number and Math Functions............................................................................................................71
5.2.2 Date and Time Functions........................................................................................................................72
5.2.3 Financial Functions ..........................................................................................................................................75
5.2.4 Other Functions....................................................................................................................................................77
5.3 Worksheet Functions............................................................................................................................................................79
5.3.1 Using Worksheet Functions in Your Code ....................................................................80
5.3.2 Examples of Worksheet Functions ..........................................................................................82
5.4 Exercises....................................................................................................................................................................................................85
Chapter 6 Decisions and Looping....................................................................................................................88
6.1 Arithmetic and Comparison........................................................................................................................................88
6.1.1 Arithmetic......................................................................................................................................................................88
6.1.2 Comparison..................................................................................................................................................................89
6.2 Decisions..................................................................................................................................................................................................90
6.2.1 If-Then-Else..................................................................................................................................................................91
6.2.2 Select-Case ..................................................................................................................................................................93
6.3 Looping........................................................................................................................................................................................................95
6.3.1 The For-Next Loop ............................................................................................................................................95
6.3.2 The While Loop......................................................................................................................................................101
6.3.3 The Do Loops ............................................................................................................................................................107
6.3.4 Be Aware of Endless Looping............................................................................................................Ill
6.4 Nested Loops ....................................................................................................................................................................................112
6.4.1 Example: Calculate Distances..........................................................................................................114
6.4.2 Example: Gantt Chart ..................................................................................................................................117
6.4.3 Example: Sequencing of Jobs............................................................................................................120
6.5 Jumping................................................................................................... 122
6.5.1 GoTo ......................................................................................................................................................................................122
6.5.2 Exit............................................................................................................................................................................................124
6.6 Exercises....................................................................................................................................................................................................125
Chapter 7 Workbooks, Worksheets, and Ranges................................. 128
7.1 The Object Model ......................................................................................................................................................................128
7.1.1 Referring to an Object ................................................................................................................................129
7.1.2 Properties and Methods..........................................................................................................................130
7.1.3 Collections ....................................................................................................................................................................130
7.1.4 Object Variables....................................................................................................................................................131
7.1.5 The Object Browser........................................................................................................................................131
7.2 Ranges..........................................................................................................................................................................................................132
7.2.1 Size of the Region ..............................................................................................................................................134
7.2.2 Sorting ................................................................................................................................................................................135
7.2.3 Other Range Properties and Methods................................................................................137
7.3 Worksheets..........................................................................................................................................................................................137
7.4 Workbooks............................................................................................................................................................................................138
7. 5 The Application Object........................................................................................................................................................139
7. 6 For-Each Loops................................................................................................................................................................................141
7. 7 With Statements..........................................................................................................................................................................143
7.8 Exercises....................................................................................................................................................................................................144
Chapter 8 Security..................................................................................................................................................................146
8.1 Set Macro Security....................................................................................................................................................................146
8. 2 Trusted Sources ............................................................................................................................................................................148
8.2.1 Trusted Locations ..............................................................................................................................................148
8.2.2 Trusted Digital Certificates ..................................................................................................................150
8. 3 Create a Digital Certificate............................................................................................................................................151
8.3.1 Sign Your Code with Your Digital Certificate ............................................................152
8.4 Protecting Your Work............................................................................................................................................................153
8.4.1 Prevent Others from View or Changing Your Code........................................154
8.4. 2 Prevent Others from Executing Your Program ......................................................154
Chapter 9 Debugging..........................................................................................................................................................157
9.1 Avoid the Need for Debugging................................................................................................................................157
9.2 VBA Debugging Tools............................................................................................................................................................158
9.2.1 Step Into or Single Stepping ..............................................................................................................159
9.2. 2 Step Over........................................................................................................................................................................159
9.2. 3 Step Out............................................................................................................................................................................159
9.2.4 Break Point ..................................................................................................................................................................160
9.2. 5 Break Mode ................................................................................................................................................................160
9.2.6 Locals....................................................................................................................................................................................161
9.2.7 Watch ..................................................................................................................................................................................161
9.2. 8 The Immediate Window..........................................................................................................................161
9. 3 Run-Time Errors............................................................................................................................................................................162
9.4 Exercises....................................................................................................................................................................................................164
Chapter 10 Design of Programs ........................................................................................................................165
10.1 The Process of Creating a Program..................................................................................................................165
10. 2 Designing a Program..............................................................................................................................................................168
10.2.1 Example: Simple Production Planning................................................................................168
10.2. 2 Example: Reservation Problem......................................................................................................170
10. 3 Use of Flow Charts in Designs..................................................................................................................................173
Chapter 11 Making it Easier to Use Your Code ........................................................................175
11.1 The Personal Workbook....................................................................................................................................................175
11. 2 Executing a Macro......................................................................................................................................................................176
11.2.1 Use Alternative Buttons to Execute a Macro ..........................................................177
11.2. 2 Use a Shortcut Key to Execute a Macro............................................................................177
11. 3 Quick Access Toolbar ............................................................................................................................................................178
11. 4 Toolbar in the Ribbon ..........................................................................................................................................................179
11. 5 Create and Use Your Own Add-In ......................................................................................................................181
11.5.1 Preparing the Workbook........................................................................................................................181
11.5. 2 Creating the Add-In ........................................................................................................................................183
11.5. 3 Installing an Add-In..........................................................................................................................................184
11. 6 Exercises....................................................................................................................................................................................................186
Chapter 12 Files..........................................................................................................................................................................188
12.1 The File System ..............................................................................................................................................................................188
12. 2 Working with Folders............................................................................................................................................................189
12.2.1 Create a New Folder......................................................................................................................................189
12.2. 2 Delete a Folder ......................................................................................................................................................I90
12.2. 3 Display Sub Folders..........................................................................................................................................1
12. 3 Working with Files......................................................................................................................................................................191
12.3.1 Get Information about Files................................................................................................................191
12.3. 2 Check if a File Exists........................................................................................................................................I93
12.3. 3 Check if a Workbook is Open............................................................................................................194
12.3. 4 Count Workbooks in Folder................................................................................................................195
12. 4 Working with Text Files......................................................................................................................................................195
12. 5 Exercises....................................................................................................................................................................................................198
Chapter 13 Strings..................................................................................................................................................................201
13.1 Working with Strings..............................................................................................................................................................201
13. 2 String Functions ............................................................................................................................................................................203
13. 3 Exercises....................................................................................................................................................................................................205
Chapter 14 Charts....................................................................................................................................................................207
14.1 Chart Sheets and Embedded Charts ..............................................................................................................207
14. 2 Basic Chart Operations........................................................................................................................................................210
14.2.1 Check if a Chart Exists..................................................................................................................................210
14.2. 2 Delete Charts............................................................................................................................................................211
14.2. 3 Display Names of Charts..........................................................................................................................211
14. 3 Charts in Action..............................................................................................................................................................................212
14.3.1 Example: Sales........................................................................................................................................................212
14.3. 2 Example: Advertisement ........................................................................................................................217
14.3. 3 Example: Members..........................................................................................................................................221
14.3. 4 Example: Sales - Revisited....................................................................................................................224
14.3. 5 Example: The Traveling Salesman Problem................................................................225
14.3. 6 Example: The Vehicle Routing Problem............................................................................228
14. 4 Exercises....................................................................................................................................................................................................231
Chapter 15 Built-in Dialog Boxes....................................................................................................................233
15.1 Message Boxes................................................................................................................................................................................233
15. 2 Input Boxes ..........................................................................................................................................................................................236
15. 3 The Application Input Box..............................................................................................................................................238
15. 4 Other Built-in Dialog Boxes..........................................................................................................................................240
Chapter 16 User Forms....................................................................................................................................................241
16.1 Controls......................................................................................................................................................................................................241
16. 2 Event Programming ................................................................................................................................................................245
16. 3 Designing the First User Form..................................................................................................................................246
16.3.1 Insert the User Form......................................................................................................................................248
16.3. 2 Changing the Appearance of the User Form ............................................................249
16.3. 3 Show the User Form ......................................................................................................................................250
16.3. 4 Adding Controls to the User Form............................................................................................250
16.3. 5 Properties ......................................................................................................................................................................252
16.3. 6 Tab Order........................................................................................................................................................................253
16.3. 7 Short Cut Keys..........................................................................................................................................................254
16.3. 8 Automating the User Form..................................................................................................................254
16.4 User Forms in Action..............................................................................................................................................................256
16.4.1 Example: Selecting Flights ....................................................................................................................256
16.4. 2 Example: Select Background Picture ....................................................................................260
16.4. 3 Example: People Information - Revisited ......................................................................263
16.4.4 Example: Running Experiment........................................................................................................265
16. 5 Exercises....................................................................................................................................................................................................270
Chapter 17 Solver....................................................................................................................................................................274
17.1 Introduction to Solver..........................................................................................................................................................274
17.1.1 Preparing the Worksheet ......................................................................................................................274
17.1. 2 Using the Solver....................................................................................................................................................276
17.2 Preparing the File for Combining Solver and VBA......................................................................281
17. 3 Using Solver from the VBA Code..........................................................................................................................283
17.3.1 A Small Example ..................................................................................................................................................283
17.3. 2 A Walk Through the Functions ......................................................................................................285
17. 4 Solver in Action ..............................................................................................................................................................................292
17.4.1 Example: Adding Additional Constraints ........................................................................293
17.4. 2 Example: Transportation Problem............................................................................................294
17.4. 3 Example: Solving Problems of Flexible Size................................................................296
17. 5 Exercises....................................................................................................................................................................................................299
Chapter 18 Simulation......................................................................................................................................................303
18.1 Generation of Random Numbers........................................................................................................................303
18. 2 Use Simulation to Compare Strategies......................................................................................................305
18.2.1 Example: Production Planning........................................................................................................306
18.2. 2 Example: Inventory Control................................................................................................................310
18. 3 Introduction to Crystal Ball ..........................................................................................................................................313
18.3.1 Example: Investment....................................................................................................................................315
18.3. 2 Example: Sales ........................................................................................................................................................320
18. 4 Using Crystal Ball from the VBA Code..........................................................................................................323
18.4.1 Example: Sales - Revisited....................................................................................................................324
18.4.2 Example: Sales - Extended Version ........................................................................................326
18. 5 Exercises....................................................................................................................................................................................................329
Index ............................................................................................................................................................................................................333
|
| any_adam_object | 1 |
| author | Wøhlk, Sanne |
| author_GND | (DE-588)1046418734 |
| author_facet | Wøhlk, Sanne |
| author_role | aut |
| author_sort | Wøhlk, Sanne |
| author_variant | s w sw |
| building | Verbundindex |
| bvnumber | BV042166139 |
| callnumber-first | H - Social Science |
| callnumber-label | HF5548 |
| callnumber-raw | HF5548.4.M523 |
| callnumber-search | HF5548.4.M523 |
| callnumber-sort | HF 45548.4 M523 |
| callnumber-subject | HF - Commerce |
| ctrlnum | (OCoLC)896823981 (DE-599)BVBBV042166139 |
| edition | 1. ed. |
| format | Book |
| fullrecord | <?xml version="1.0" encoding="UTF-8"?><collection xmlns="http://www.loc.gov/MARC21/slim"><record><leader>01390nam a2200397zc 4500</leader><controlfield tag="001">BV042166139</controlfield><controlfield tag="003">DE-604</controlfield><controlfield tag="005">20141119 </controlfield><controlfield tag="007">t</controlfield><controlfield tag="008">141104s2010 dk d||| |||| 00||| eng d</controlfield><datafield tag="010" ind1=" " ind2=" "><subfield code="a">2010484361</subfield></datafield><datafield tag="020" ind1=" " ind2=" "><subfield code="a">9788757422672</subfield><subfield code="c">pbk.</subfield><subfield code="9">978-87-574-2267-2</subfield></datafield><datafield tag="035" ind1=" " ind2=" "><subfield code="a">(OCoLC)896823981</subfield></datafield><datafield tag="035" ind1=" " ind2=" "><subfield code="a">(DE-599)BVBBV042166139</subfield></datafield><datafield tag="040" ind1=" " ind2=" "><subfield code="a">DE-604</subfield><subfield code="b">ger</subfield><subfield code="e">aacr</subfield></datafield><datafield tag="041" ind1="0" ind2=" "><subfield code="a">eng</subfield></datafield><datafield tag="044" ind1=" " ind2=" "><subfield code="a">dk</subfield><subfield code="c">DK</subfield></datafield><datafield tag="049" ind1=" " ind2=" "><subfield code="a">DE-1028</subfield></datafield><datafield tag="050" ind1=" " ind2="0"><subfield code="a">HF5548.4.M523</subfield></datafield><datafield tag="100" ind1="1" ind2=" "><subfield code="a">Wøhlk, Sanne</subfield><subfield code="e">Verfasser</subfield><subfield code="0">(DE-588)1046418734</subfield><subfield code="4">aut</subfield></datafield><datafield tag="245" ind1="1" ind2="0"><subfield code="a">VBA programming in business economics</subfield><subfield code="c">Sanne Wøhlk</subfield></datafield><datafield tag="250" ind1=" " ind2=" "><subfield code="a">1. ed.</subfield></datafield><datafield tag="264" ind1=" " ind2="1"><subfield code="a">Copenhagen</subfield><subfield code="b">Djøf Publ.</subfield><subfield code="c">2010</subfield></datafield><datafield tag="300" ind1=" " ind2=" "><subfield code="a">332 S.</subfield><subfield code="b">graph. Darst.</subfield><subfield code="c">23 cm</subfield></datafield><datafield tag="336" ind1=" " ind2=" "><subfield code="b">txt</subfield><subfield code="2">rdacontent</subfield></datafield><datafield tag="337" ind1=" " ind2=" "><subfield code="b">n</subfield><subfield code="2">rdamedia</subfield></datafield><datafield tag="338" ind1=" " ind2=" "><subfield code="b">nc</subfield><subfield code="2">rdacarrier</subfield></datafield><datafield tag="500" ind1=" " ind2=" "><subfield code="a">Includes index.</subfield></datafield><datafield tag="630" ind1="0" ind2="4"><subfield code="a">Microsoft Excel (Computer file)</subfield></datafield><datafield tag="630" ind1="0" ind2="4"><subfield code="a">Microsoft Visual Basic for applications</subfield></datafield><datafield tag="650" ind1=" " ind2="4"><subfield code="a">Wirtschaft</subfield></datafield><datafield tag="650" ind1=" " ind2="4"><subfield code="a">Business</subfield><subfield code="x">Computer programs</subfield></datafield><datafield tag="650" ind1=" " ind2="4"><subfield code="a">Managerial economics</subfield><subfield code="x">Computer programs</subfield></datafield><datafield tag="650" ind1=" " ind2="4"><subfield code="a">Electronic spreadsheets</subfield></datafield><datafield tag="856" ind1="4" ind2="2"><subfield code="m">HBZ Datenaustausch</subfield><subfield code="q">application/pdf</subfield><subfield code="u">http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=027605666&sequence=000002&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA</subfield><subfield code="3">Inhaltsverzeichnis</subfield></datafield><datafield tag="999" ind1=" " ind2=" "><subfield code="a">oai:aleph.bib-bvb.de:BVB01-027605666</subfield></datafield></record></collection> |
| id | DE-604.BV042166139 |
| illustrated | Illustrated |
| indexdate | 2024-07-10T01:14:20Z |
| institution | BVB |
| isbn | 9788757422672 |
| language | English |
| lccn | 2010484361 |
| oai_aleph_id | oai:aleph.bib-bvb.de:BVB01-027605666 |
| oclc_num | 896823981 |
| open_access_boolean | |
| owner | DE-1028 |
| owner_facet | DE-1028 |
| physical | 332 S. graph. Darst. 23 cm |
| publishDate | 2010 |
| publishDateSearch | 2010 |
| publishDateSort | 2010 |
| publisher | Djøf Publ. |
| record_format | marc |
| spelling | Wøhlk, Sanne Verfasser (DE-588)1046418734 aut VBA programming in business economics Sanne Wøhlk 1. ed. Copenhagen Djøf Publ. 2010 332 S. graph. Darst. 23 cm txt rdacontent n rdamedia nc rdacarrier Includes index. Microsoft Excel (Computer file) Microsoft Visual Basic for applications Wirtschaft Business Computer programs Managerial economics Computer programs Electronic spreadsheets HBZ Datenaustausch application/pdf http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=027605666&sequence=000002&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA Inhaltsverzeichnis |
| spellingShingle | Wøhlk, Sanne VBA programming in business economics Microsoft Excel (Computer file) Microsoft Visual Basic for applications Wirtschaft Business Computer programs Managerial economics Computer programs Electronic spreadsheets |
| title | VBA programming in business economics |
| title_auth | VBA programming in business economics |
| title_exact_search | VBA programming in business economics |
| title_full | VBA programming in business economics Sanne Wøhlk |
| title_fullStr | VBA programming in business economics Sanne Wøhlk |
| title_full_unstemmed | VBA programming in business economics Sanne Wøhlk |
| title_short | VBA programming in business economics |
| title_sort | vba programming in business economics |
| topic | Microsoft Excel (Computer file) Microsoft Visual Basic for applications Wirtschaft Business Computer programs Managerial economics Computer programs Electronic spreadsheets |
| topic_facet | Microsoft Excel (Computer file) Microsoft Visual Basic for applications Wirtschaft Business Computer programs Managerial economics Computer programs Electronic spreadsheets |
| url | http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=027605666&sequence=000002&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA |
| work_keys_str_mv | AT wøhlksanne vbaprogramminginbusinesseconomics |