{"text_format":"1","workbook":"xlsx","n_sheets":18,"sheets":["Introduction","Index","Addressing","Charts","Classes","Data","Error handling","Events","Files","Forms","Menus","Miscellaneous","Office integration","Strings","WorksheetFunctions","Versions","Labels","zz"],"not_rendered":{"charts":4,"images":7,"macros":1,"external_links":0,"defined_names":170,"comments":5,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Introduction","index":0,"n_rows":28,"n_cols":13,"rows_kept":28,"cut":false,"hidden":false,"merged":[[18,2,18,5],[8,2,17,5],[22,2,24,12],[1,1,1,10],[8,8,8,12],[2,1,2,10],[3,1,3,10],[4,12,4,13]]}
[]
[]
[null,"Visual Basic for Finance"]
[null,"Reference Guide"]
[null,null,null,null,null,null,null,null,null,null,null,null,"www.tykoh.com"]
[]
[]
[]
[null,null,"This spreadsheet is used as a reference in a course - \"Visual Basic for Finance\" - conducted internationally by Tykoh Training. To see more about the course click on \"related pages\" links to the right.  \n\nThe spreadsheet illustrates various Visual Basic solutions to common needs and problems.  The spreadsheet is interactive - You can try out the techniques illustrated. \n\nThis spreadsheet may be freely downloaded.  It may be redistributed provided it is not altered in any way.",null,null,null,null,null,"Related Pages"]
[]
[null,null,null,null,null,null,null,null,"Information about the Visual Basic for Finance course",null,"Visual Basic for Finance"]
[]
[]
[]
[null,null,null,null,null,null,null,null,"Contact us",null,"info@tykoh.com"]
[]
[]
[]
[null,null,"Copyright © 2009 Tykoh Group - All rights reserved"]
[]
[]
[]
[null,null,"Disclaimer\nBy downloading and/or using this program, the user agrees that Tykoh Group disclaims all warranties of noninfringement of third party rights, quality, performance, merchantability, or fitness for a particular purpose. The user assumes the entire risk as to the quality and performance of the software. The user assumes the entire risk as to consequences of any actions user may take or not take as a result of anything in this software.  In no event will Tykoh Group be liable for any indirect, special, or consequential damages."]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"v1.2"]

{"sheet":"Index","index":1,"n_rows":22,"n_cols":13,"rows_kept":22,"cut":false,"hidden":false,"merged":[[22,3,22,12],[18,2,18,12],[8,2,8,12],[20,2,20,12],[21,2,21,12],[16,2,16,12],[9,2,9,12],[12,2,12,12],[14,2,14,12],[11,2,11,12],[10,2,10,12],[19,2,19,12],[15,2,15,12],[17,2,17,12],[13,2,13,10],[1,2,1,10],[7,2,7,12],[4,12,4,13],[2,2,5,10]]}
[]
[null,null,{"$f":"label_overview & \"About this Reference Guide\"","v":"Overview -  About this Reference Guide"}]
[null,null,"This Reference Guide shows working examples of Visual Basic.  You can try out each example - generally by pressing a button.  The Visual Basic code for each example is listed in the spreadsheet next to the button.  The examples are \"building blocks\" that are often useful in developing complete business applications.  A more extensive version of this Guide is used as a reference in a two day workshop conducted worldwide by Tykoh Group:  \"Visual Basic for Financial Applications\"."]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"www.tykoh.com"]
[]
[]
[null,null,"Main Index"]
[null,null,"Back to introduction"]
[null,null,"Addressing cells and ranges"]
[null,null,"Charts"]
[null,null,"Classes"]
[null,null,"Data"]
[null,null,"Error handling"]
[null,null,"Events"]
[null,null,"Files"]
[null,null,"Forms"]
[null,null,"Menus"]
[null,null,"Miscellaneous"]
[null,null,"Office integration"]
[null,null,"Strings and text"]
[null,null,"Worksheet functions"]

{"sheet":"Addressing","index":2,"n_rows":247,"n_cols":13,"rows_kept":247,"cut":false,"hidden":false,"merged":[[2,2,5,10],[5,12,5,13],[131,2,131,13],[151,2,151,13],[84,2,84,13],[86,2,86,13],[68,2,68,13],[218,2,218,13],[231,2,231,13],[233,2,233,13],[246,2,246,13],[168,2,168,13],[185,2,185,13],[170,2,170,13],[103,2,103,13],[118,2,118,13],[116,2,116,13],[204,2,204,13],[216,2,216,13],[187,2,187,13],[206,2,206,13],[212,2,215,10],[1,2,1,10],[4,12,4,13],[8,2,8,12],[9,2,9,12],[149,2,149,13],[133,2,133,13],[25,2,25,13],[41,2,41,13],[55,2,55,13],[23,2,23,13],[10,2,10,12],[11,2,11,12],[100,2,100,13],[70,2,70,13],[39,2,39,13],[53,2,53,13],[20,2,20,12],[21,2,21,12],[12,2,12,12],[13,2,13,12],[14,2,14,12],[15,2,15,12],[16,2,16,12],[17,2,17,12],[18,2,18,12],[19,2,19,12]]}
[]
[null,null,{"$f":"label_overview & Index!C10","v":"Overview -  Addressing cells and ranges"}]
[null,null,"Almost any Visual Basic working with Excel will need to reference particular cells and ranges in worksheets. Typical needs might be to address a named range, to find the last used cell in a block of cells or to select a subset of rows or columns.  The examples below show techniques that can be used to do these types of addressing.  With Visual Basic it is almost always better to refer to cells and areas by names (e.g. \"tax_table\") rather than by absolute values (e.g. A1:B15)."]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"www.tykoh.com"]
[]
[]
[null,null,"Index"]
[null,null,"Addressing a whole range"]
[null,null,"Addressing a cell in a range"]
[null,null,{"$f":"addr_row_in_range","v":"Addressing a row in a range"}]
[null,null,{"$f":"addr_block_rows","v":"Addressing a block of rows in a range"}]
[null,null,{"$f":"addr_block_cells","v":"Addressing a block of cells in a range"}]
[null,null,{"$f":"addr_cells_using_offset","v":"Addressing a cell by using the offset property"}]
[null,null,{"$f":"addr_range_offset","v":"Addressing a range by using the offset function"}]
[null,null,{"$f":"addr_last_cell","v":"Addressing the last cell in a block of filled cells"}]
[null,null,{"$f":"addr_last_row","v":"Addressing the last row in a block of filled cells"}]
[null,null,{"$f":"addr_right_cell","v":"Addressing the rightmost cell in a block of filled cells"}]
[null,null,{"$f":"addr_right_col","v":"Addressing the rightmost column in a block of filled cells"}]
[null,null,{"$f":"addr_last_general","v":"Addressing the last cell in a block of filled cells (general method)"}]
[null,null,{"$f":"addr_intersect","v":"Addressing a range by using the Intersect function"}]
[null,null,{"$f":"addr_union","v":"Addressing a range by using the Union function"}]
[]
[null,null,{"$f":"label_back_to_main_index","v":"Back to main index"}]
[]
[null,null,"Addressing a whole range          <--- Click on a topic to scroll it to the top of the page"]
[null,null,null,null,null,null,null,null,"Sub AddressWholeRange()"]
[null,null,"range_1",null,null,null,null,null,"    Dim r As Range"]
[null,null,null,null,null,null,null,null,"    Set r = Range(\"range_1\")"]
[null,null,null,null,null,null,null,null,"    r.Interior.ColorIndex = 3"]
[null,null,null,null,null,null,null,null,"End Sub"]
[]
[null,null,null,null,null,null,null,null,"Sub UndoAddressWholeRange()"]
[null,null,null,null,null,null,null,null,"    Dim r As Range"]
[null,null,null,null,null,null,null,null,"    Set r = Range(\"range_1\")"]
[null,null,"range_1 is a named range that spans the area defined",null,null,null,null,null,"    r.Interior.ColorIndex = 40"]
[null,null,"above.  The \"AddressWholeRange\" button to the right",null,null,null,null,null,"End Sub"]
[null,null,"shows how you can address (refer to) the whole range."]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Addressing a cell in a range       <--- Click on a topic to scroll it to the top of the page"]
[null,null,null,null,null,null,null,null,"Sub AddressCellInRange()"]
[null,null,"range_2",null,null,null,null,null,"    Dim r As Range"]
[null,null,null,null,null,null,null,null,"    Set r = Range(\"range_2\")"]
[null,null,null,null,null,null,null,null,"    r.Cells(2, 3).Interior.ColorIndex = 3"]
[null,null,null,null,null,null,null,null,"End Sub"]
[]
[null,null,"range_2 spans the area defined above.  This example ",null,null,null,null,null,"Sub UndoAddressCellInRange()"]
[null,null,"shows how you can refer to a particular, single,",null,null,null,null,null,"    Dim r As Range"]
[null,null,"cell within the range.",null,null,null,null,null,"    Set r = Range(\"range_2\")"]
[null,null,null,null,null,null,null,null,"    r.Cells(2, 3).Interior.ColorIndex =40"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Addressing a row in a range"]
[null,null,null,null,null,null,null,null,"Sub AddressRowInRange()"]
[null,null,"range_3",null,null,null,null,null,"    Dim r As Range"]
[null,null,null,null,null,null,null,null,"    Set r = Range(\"range_3\")"]
[null,null,null,null,null,null,null,null,"    r.Rows(5).Interior.ColorIndex = 3"]
[null,null,null,null,null,null,null,null,"End Sub"]
[]
[null,null,null,null,null,null,null,null,"Sub UndoAddressRowInRange()"]
[null,null,null,null,null,null,null,null,"    Dim r As Range"]
[null,null,"range_3 spans the area defined above.  This example",null,null,null,null,null,"    Set r = Range(\"range_3\")"]
[null,null,"shows how you can refer to a single row within the",null,null,null,null,null,"    r.Rows(5).Interior.ColorIndex = 40"]
[null,null,"range.",null,null,null,null,null,"End Sub"]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Addressing a block of rows in a range"]
[null,null,null,null,null,null,null,null,"Sub AddressBlockRowsInRange()"]
[null,null,"range_5",null,null,null,null,null,"    Dim r As Range"]
[null,null,null,null,null,null,null,null,"    Set r = Range(\"range_5\")"]
[null,null,null,null,null,null,null,null,"    Set r = Range(r.Rows(2), r.Rows(4))"]
[null,null,null,null,null,null,null,null,"    r.Interior.ColorIndex = 3"]
[null,null,null,null,null,null,null,null,"End Sub"]
[]
[null,null,null,null,null,null,null,null,"Sub UndoAddressBlockRowsInRange()"]
[null,null,null,null,null,null,null,null,"    Dim r As Range"]
[null,null,"range_5 spans the area defined above.  This example",null,null,null,null,null,"    Set r = Range(\"range_5\")"]
[null,null,"shows how you can refer to a block of rows within",null,null,null,null,null,"    Set r = Range(r.Rows(2), r.Rows(4))"]
[null,null,"the range.",null,null,null,null,null,"    r.Interior.ColorIndex = 40"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Addressing a block of cells in a range"]
[null,null,null,null,null,null,null,null,"Sub AddressBlockInRange()"]
[null,null,"range_4",null,null,null,null,null,"    Dim r As Range"]
[null,null,null,null,null,null,null,null,"    Set r = Range(\"range_4\")"]
[null,null,null,null,null,null,null,null,"    Set r = Range(r.Cells(2, 3), r.Cells(6, 4))"]
[null,null,null,null,null,null,null,null,"    r.Interior.ColorIndex = 3"]
[null,null,null,null,null,null,null,null,"End Sub"]
[]
[null,null,null,null,null,null,null,null,"Sub UndoAddressBlockInRange()"]
[null,null,null,null,null,null,null,null,"    Dim r As Range"]
[null,null,"range_4 spans the area defined above.  This example",null,null,null,null,null,"    Set r = Range(\"range_4\")"]
[null,null,"shows how you can refer to a block of cells within",null,null,null,null,null,"    Set r = Range(r.Cells(2, 3), r.Cells(6, 4))"]
[null,null,"the range.",null,null,null,null,null,"    r.Interior.ColorIndex = 42"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[]
[null,null,"Addressing a cell by using the offset property"]
[]
[null,null,"Offset_1",null,null,null,null,null,"Sub AddressCellOffset()"]
[null,null,null,null,null,null,null,null,"    Dim r As Range"]
[null,null,"Offset_1 refers to a single cell.  This example shows how",null,null,null,null,null,"    Set r = Range(\"Offset_1\")"]
[null,null,"you can refer to another cell that is offset by a certain",null,null,null,null,null,"    r.Offset(1, 2).Interior.ColorIndex = 3"]
[null,null,"amount from the first cell.",null,null,null,null,null,"End Sub"]
[]
[null,null,null,null,null,null,null,null,"Sub UndoAddressCellOffset()"]
[null,null,null,null,null,null,null,null,"    Dim r As Range"]
[null,null,null,null,null,null,null,null,"    Set r = Range(\"Offset_1\")"]
[null,null,null,null,null,null,null,null,"    r.Offset(1, 2).Interior.ColorIndex = 40"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Addressing a range by using the offset function"]
[]
[null,null,null,null,"offset_2",null,null,null,"Sub AddressRangeOffset()"]
[null,null,null,null,null,null,null,null,"    Dim r As Range"]
[null,null,null,null,null,null,null,null,"    Set r = Range(\"Offset_2\")"]
[null,null,null,null,null,null,null,null,"    r.Offset(-1, -2).Interior.ColorIndex = 3"]
[null,null,null,null,null,null,null,null,"End Sub"]
[]
[null,null,null,null,null,null,null,null,"Sub UndoAddressRangeOffset()"]
[null,null,"In this example we offset an entire range",null,null,null,null,null,"    Dim r As Range"]
[null,null,null,null,null,null,null,null,"    Set r = Range(\"Offset_2\")"]
[null,null,null,null,null,null,null,null,"    r.Offset(-1, -2).Interior.ColorIndex = 40"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Addressing the last cell in a block of filled cells"]
[]
[null,null,"end_1",18,5,11,97,null,"Sub EndBlockCell()"]
[null,null,30,36,53,25,58,null,"    Dim r As Range"]
[null,null,95,40,97,8,16,null,"    Set r = Range(\"end_1\")"]
[null,null,34,67,16,11,94,null,"    Set r = r.End(xlDown)"]
[null,null,98,20,0,75,72,null,"    r.Interior.ColorIndex = 3"]
[null,null,null,null,null,null,null,null,"End Sub"]
[]
[null,null,88,6,93,40,33,null,"Sub UndoEndBlockCell()"]
[null,null,14,45,99,76,49,null,"    Dim r As Range"]
[null,null,null,null,null,null,null,null,"    Set r = Range(\"end_1\")"]
[null,null,"end_1 is a named range at the top left corner of a block",null,null,null,null,null,"    Set r = r.End(xlDown)"]
[null,null,"of cells.  We want to find the last cell in the block in the ",null,null,null,null,null,"    r.Interior.ColorIndex = 2"]
[null,null,"same column as end_1.",null,null,null,null,null,"End Sub"]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Addressing the last row in a block of filled cells"]
[]
[null,null,"end_2",18,5,11,33,null,"Sub EndBlockRow()"]
[null,null,30,36,53,25,58,null,"    Dim r As Range"]
[null,null,95,40,97,8,16,null,"    Set r = Range(\"end_2\")"]
[null,null,34,67,16,11,94,null,"    Set r = r.End(xlDown)"]
[null,null,98,20,0,75,72,null,"    Set r = Range(r, r.End(xlToRight))"]
[null,null,null,null,null,null,null,null,"    r.Interior.ColorIndex = 3"]
[null,null,88,6,93,40,33,null,"End Sub"]
[null,null,14,45,99,76,49]
[null,null,null,null,null,null,null,null,"Sub UndoEndBlockRow()"]
[null,null,"end_2 is a named range at the top left corner of a block",null,null,null,null,null,"    Dim r As Range"]
[null,null,"of cells.  We want to find the last row of cells in the block.",null,null,null,null,null,"    Set r = Range(\"end_2\")"]
[null,null,null,null,null,null,null,null,"    Set r = r.End(xlDown)"]
[null,null,"Business use: Updating a daily report where each day's",null,null,null,null,null,"    Set r = Range(r, r.End(xlToRight))"]
[null,null,"record is placed at the end of a history of earlier records.",null,null,null,null,null,"    r.Interior.ColorIndex = 2"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Addressing the rightmost cell in a block of filled cells"]
[]
[null,null,"end_3",18,5,11,97,null,"Sub EndRightBlockCell()"]
[null,null,30,36,53,25,58,null,"    Dim r As Range"]
[null,null,95,40,97,8,16,null,"    Set r = Range(\"end_3\")"]
[null,null,34,67,16,11,94,null,"    Set r = r.End(xlToRight)"]
[null,null,98,20,0,75,72,null,"    r.Interior.ColorIndex = 3"]
[null,null,null,null,null,null,null,null,"End Sub"]
[]
[null,null,88,6,93,40,33,null,"Sub UndoEndRightBlockCell()"]
[null,null,14,45,99,76,49,null,"    Dim r As Range"]
[null,null,null,null,null,null,null,null,"    Set r = Range(\"end_3\")"]
[null,null,"end_3 is a named range at the top left corner of a block",null,null,null,null,null,"    Set r = r.End(xlToRight)"]
[null,null,"of cells.  We want to find the rightmost cell in the block in ",null,null,null,null,null,"    r.Interior.ColorIndex = 2"]
[null,null,"the same row as end_3.",null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Addressing the rightmost column in a block of filled cells"]
[]
[null,null,"end_4",18,5,11,33,null,"Sub EndRightColumn()"]
[null,null,30,36,53,25,58,null,"    Dim r As Range"]
[null,null,95,40,97,8,16,null,"    Set r = Range(\"end_4\")"]
[null,null,34,67,16,11,94,null,"    Set r = r.End(xlToRight)"]
[null,null,98,20,0,75,72,null,"    Set r = Range(r, r.End(xlDown))"]
[null,null,null,null,null,null,null,null,"    r.Interior.ColorIndex = 3"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,88,6,93,40,33]
[null,null,14,45,99,76,49,null,"Sub UndoEndRightColumn()"]
[null,null,null,null,null,null,null,null,"    Dim r As Range"]
[null,null,null,null,null,null,null,null,"    Set r = Range(\"end_4\")"]
[null,null,null,null,null,null,null,null,"    Set r = r.End(xlToRight)"]
[null,null,null,null,null,null,null,null,"    Set r = Range(r, r.End(xlDown))"]
[null,null,null,null,null,null,null,null,"    r.Interior.ColorIndex = 2"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Addressing the last cell in a block of filled cells (general method)"]
[]
[null,null,"end_5",4,null,"end_6",4]
[]
[null,null,2,3,null,2,3]
[]
[null,null,"The technique shown earlier for finding the last cell doesn't work properly if the named range is already on the last row or column. The end_5 example shows the incorrect bevaviour of the technique we have been using so far. The end_6 example shows the correct behaviour using a more general approach."]
[]
[]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Addressing a range by using the Intersect function"]
[]
[null,null,null,null,"intersect_1",null,null,null,"Sub AddressIntersect()"]
[null,null,null,null,null,null,null,null,"    Dim r As Range"]
[null,null,null,null,null,null,null,null,"    Set r = Application.Intersect(Range(\"intersect_1\"), Range(\"intersect_2\"))"]
[null,null,null,null,null,null,null,null,"    r.Interior.ColorIndex = 5"]
[null,null,"intersect_2",null,null,null,null,null,"End Sub"]
[]
[null,null,null,null,null,null,null,null,"Sub UndoAddressIntersect()"]
[null,null,null,null,null,null,null,null,"    Dim r As Range"]
[null,null,null,null,null,null,null,null,"    Set r = Application.Intersect(Range(\"intersect_1\"), Range(\"intersect_2\"))"]
[null,null,null,null,null,null,null,null,"    r.Interior.ColorIndex = 46"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Addressing a range by using the Union function"]
[]
[null,null,"union_1",null,null,null,null,null,"Sub AddressUnion()"]
[null,null,null,null,null,null,null,null,"    Dim r As Range"]
[null,null,null,null,null,null,null,null,"    Set r = Application.Union(Range(\"union_1\"), Range(\"union_2\"))"]
[null,null,"union_2",null,null,null,null,null,"    r.Interior.ColorIndex = 3"]
[null,null,null,null,null,null,null,null,"End Sub"]
[]
[null,null,null,null,null,null,null,null,"Sub UndoAddressUnion()"]
[null,null,null,null,null,null,null,null,"    Dim r As Range"]
[null,null,null,null,null,null,null,null,"    Set r = Application.Union(Range(\"union_1\"), Range(\"union_2\"))"]
[null,null,null,null,null,null,null,null,"    r.Interior.ColorIndex = 40"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]

{"sheet":"Charts","index":3,"n_rows":70,"n_cols":13,"rows_kept":70,"cut":false,"hidden":false,"merged":[[53,2,53,13],[69,2,69,13],[12,2,12,13],[14,2,14,13],[31,2,31,13],[55,2,55,13],[43,3,43,5],[49,3,51,10],[29,2,29,13],[18,4,19,4],[9,2,9,12],[10,2,10,12],[2,2,5,10],[5,12,5,13],[4,12,4,13],[8,2,8,12]]}
[]
[null,null,{"$f":"label_overview & Index!C11","v":"Overview -  Charts"}]
[null,null,"Charts can be tedious and time-consuming to set up manually.  So they are a good candidate for \"automating\" with Visual Basic. A good way to find out how to write chart macros is to first record a macro in which you create a chart. After that it's a good idea to study the Visual Basic Help pages and try to understand how the various chart components (e.g. Charts, ChartObjects, etc) fit within Visual Basic's object model. Once you understand that, then you can write chart macros. \n"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"www.tykoh.com"]
[]
[]
[null,null,"Index"]
[null,null,"Creating a chart"]
[null,null,{"$f":"charts_changing_attributes","v":"Changing the attributes of a chart"}]
[null,null,{"$f":"charts_adding_series","v":"Adding a series to a chart"}]
[]
[null,null,{"$f":"label_back_to_main_index","v":"Back to main index"}]
[]
[null,null,"Creating a chart          <--- Click on a topic to scroll it to the top of the page"]
[]
[]
[]
[null,null,null,null,"Chart goes here"]
[]
[]
[]
[]
[]
[null,null,null,"Chart Data"]
[null,null,null,1,null,2]
[null,null,null,3,null,4]
[]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Changing the attributes of a chart"]
[]
[]
[null,null,null,null,null,null,null,"Sub SetSeriesColor()"]
[null,null,null,null,null,null,null,"    Dim co As ChartObject, se As Series"]
[null,null,null,null,null,null,null,"    Set co = Worksheets(\"Charts\").ChartObjects(\"Chart 3\")"]
[null,null,null,null,null,null,null,"    Set se = co.Chart.SeriesCollection(2)"]
[null,null,null,null,null,null,null,"    se.Interior.ColorIndex = 55"]
[null,null,null,null,null,null,null,"End Sub"]
[]
[]
[null,null,null,null,null,null,null,"Sub ResetSeriesColor()"]
[null,null,null,"Chart is named \"Chart 3\"",null,null,null,"    Dim co As ChartObject, se As Series"]
[null,null,null,null,null,null,null,"    Set co = Worksheets(\"Charts\").ChartObjects(\"Chart 3\")"]
[null,null,null,"Chart Data",null,null,null,"    Set se = co.Chart.SeriesCollection(2)"]
[null,null,null,1,null,2,null,"    se.Interior.ColorIndex = 35"]
[null,null,null,3,null,4,null,"End Sub"]
[]
[null,null,null,"Business use:  You might have a standard style you want to apply to a set of charts.  For example you might always want six divisions on the horizontal axis.  Or you might have a certain color palette you want to use.  Visual Basic lets you set any chart attribute to the exact way you want it."]
[]
[]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Adding a series to a chart"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,"Chart Data"]
[null,null,null,1,null,2]
[null,null,null,3,null,4]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]

{"sheet":"Classes","index":4,"n_rows":81,"n_cols":13,"rows_kept":81,"cut":false,"hidden":false,"merged":[[12,2,12,13],[8,2,8,12],[9,2,9,12],[10,2,10,12],[2,2,5,10],[4,12,4,13],[54,2,54,12],[80,2,80,12],[56,2,56,13],[14,2,14,13],[69,2,69,13],[67,2,67,12],[23,2,26,6]]}
[]
[null,null,{"$f":"label_overview & Index!C12","v":"Overview -  Classes"}]
[null,null,"Classes let you define new types of Visual Basic objects.  These objects can be quite complex internally but can be made to appear quite simple to the end user.  So Classes let you \"hide\" things that users don't need to know and to reveal only those things they do need to know.\n"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"www.tykoh.com"]
[]
[]
[null,null,"Index"]
[null,null,"Using a queue class"]
[null,null,{"$f":"classes_auto_sort","v":"Using an automatic sort class"}]
[null,null,{"$f":"classes_employee","v":"Using an employee class"}]
[]
[null,null,{"$f":"label_back_to_main_index","v":"Back to main index"}]
[]
[null,null,"Using a queue class          <--- Click on a topic to scroll it to the top of the page"]
[]
[null,null,"Here we use a queue class.  The Push subroutine lets us \"push\""]
[null,null,"an item into the queue.  The Pop function \"pop's\" the oldest",null,null,null,null,null,"Sub Queue()"]
[null,null,"item from the queue.  In other words - this is a \"first in , first out\"",null,null,null,null,null,"    Dim q As New CQueue"]
[null,null,"queue.",null,null,null,null,null,"    q.Push \"apple\""]
[null,null,null,null,null,null,null,null,"    q.Push \"banana\""]
[null,null,"Push","orange","banana","apple","Pop",null,"    q.Push \"orange\""]
[null,null,null,null,null,null,null,null,"    MsgBox \"Oldest in queue was: \" & q.Pop"]
[null,null,"Business use: A class like this might be useful in tracking inventories, managing customers who are serviced on a first-come, first-served basis, and in calculating depreciation of assets.",null,null,null,null,null,"    MsgBox \"Oldest in queue was: \" & q.Pop"]
[null,null,null,null,null,null,null,null,"    q.Push \"peach\""]
[null,null,null,null,null,null,null,null,"    MsgBox \"Oldest in queue was: \" & q.Pop"]
[null,null,null,null,null,null,null,null,"    MsgBox \"Oldest in queue was: \" & q.Pop"]
[null,null,null,null,null,null,null,null,"End Sub"]
[]
[null,null,"The Visual Basic code for the CQueue class is shown to",null,null,null,null,null,"Private pQueue As Collection"]
[null,null,"the right.  The code is put into a class module named"]
[null,null,"CQueue.  The Data tab explains about Collections - which ",null,null,null,null,null,"Sub Push(v)"]
[null,null,"we use to store the queue in.",null,null,null,null,null,"    pQueue.Add v"]
[null,null,null,null,null,null,null,null,"End Sub"]
[]
[null,null,null,null,null,null,null,null,"Function Pop()"]
[null,null,null,null,null,null,null,null,"    Dim item"]
[null,null,null,null,null,null,null,null,"    On Error GoTo err_Pop"]
[null,null,null,null,null,null,null,null,"    item = pQueue.item(1)"]
[null,null,null,null,null,null,null,null,"    pQueue.Remove (1)"]
[null,null,null,null,null,null,null,null,"    Pop = item"]
[null,null,null,null,null,null,null,null,"    Exit Function"]
[null,null,null,null,null,null,null,null,"err_Pop:"]
[null,null,null,null,null,null,null,null,"    Set Pop = Nothing"]
[null,null,null,null,null,null,null,null,"End Function"]
[]
[null,null,null,null,null,null,null,null,"Private Sub Class_Initialize()"]
[null,null,null,null,null,null,null,null,"    Set pQueue = New Collection"]
[null,null,null,null,null,null,null,null,"End Sub"]
[]
[null,null,null,null,null,null,null,null,"Private Sub Class_Terminate()"]
[null,null,null,null,null,null,null,null,"    Set pQueue = Nothing"]
[null,null,null,null,null,null,null,null,"End Sub"]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Using an automatic sort class"]
[]
[null,null,"In this example we are using a new class named CAutoSort.",null,null,null,null,null,"Sub Auto_Sort()"]
[null,null,"This class automatically sorts the items you add to",null,null,null,null,null,"    Dim a As New CAutoSort"]
[null,null,"it:  When the items are retrieved they can be accessed by ",null,null,null,null,null,"    a.Add \"mandarin\""]
[null,null,"their sorted position (1 = lowest).  In the example to the right",null,null,null,null,null,"    a.Add \"apple\""]
[null,null,"\"apple\" was the second item to be added but it is item number 1",null,null,null,null,null,"    a.Add \"banana\""]
[null,null,"when retrieved since \"apple\" comes before \"banana\" and ",null,null,null,null,null,"    MsgBox \"Number items: \" & a.Count"]
[null,null,"\"mandarin\".",null,null,null,null,null,"    MsgBox \"First item: \" & a.Item(1)"]
[null,null,null,null,null,null,null,null,"    MsgBox \"Third item: \" & a.Item(3)"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Using an employee class"]
[]
[null,null,"In this example we use a class designed to work with",null,null,null,null,null,"Sub Employee()"]
[null,null,"employee data.  The Setup method allows us to define in",null,null,null,null,null,"    Dim e As New CEmployee"]
[null,null,"one step what the employee's name, title and division is.",null,null,null,null,null,"    "]
[null,null,null,null,null,null,null,null,"    e.SetUp \"J Smith\", \"Analyst\", \"Sales\""]
[null,null,"If employee data hasn't been set up (as is the case for ",null,null,null,null,null,"    MsgBox \"Name of first employee is \" & e.name"]
[null,null,"the second, \"f\", employee) then the class warns us",null,null,null,null,null,"    MsgBox \"Title of first employee is \" & e.title"]
[null,null,"of that when we attempt to retrieve the employee's data.",null,null,null,null,null,"    Dim f As New CEmployee"]
[null,null,null,null,null,null,null,null,"    MsgBox \"Name of second employee is \" & f.name"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]

{"sheet":"Data","index":5,"n_rows":109,"n_cols":13,"rows_kept":109,"cut":false,"hidden":false,"merged":[[82,2,82,12],[94,2,94,12],[108,2,108,12],[56,2,56,12],[72,2,72,13],[84,2,84,13],[96,2,96,13],[70,2,70,12],[16,2,16,12],[15,2,15,12],[45,2,45,13],[58,2,58,13],[43,2,43,12],[26,2,26,12],[18,2,18,13],[28,2,28,13],[2,2,5,10],[4,12,4,13],[13,2,13,12],[14,2,14,12],[8,2,8,12],[9,2,9,12],[11,2,11,12],[12,2,12,12],[10,2,10,12]]}
[]
[null,null,{"$f":"label_overview & Index!C13","v":"Overview -  Data"}]
[null,null,"In Visual Basic we often need a location to store temporary data.  We might, for example, be sorting a list and need somewhere to hold the list whilst we're rearranging and sorting it.  Visual Basic offers a variety of temporary data storage types.  Examples of these are given below."]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"www.tykoh.com"]
[]
[]
[null,null,"Index"]
[null,null,"Putting items into an array"]
[null,null,{"$f":"data_resize_aray","v":"Resizing an array"}]
[null,null,{"$f":"data_array_bounds","v":"Finding the upper and lower bounds of an array"}]
[null,null,{"$f":"data_add_to_collection","v":"Adding items to a collection"}]
[null,null,{"$f":"data_count_collection","v":"Determining how many items are in a collection"}]
[null,null,{"$f":"data_collection_key","v":"Associating items with keys in a collection"}]
[null,null,{"$f":"data_collection_remove","v":"Removing an item from a collection"}]
[]
[null,null,{"$f":"Labels!A1","v":"Back to main index"}]
[]
[null,null,"Putting items into an array          <--- Click on a topic to scroll it to the top of the page"]
[null,null,"In this example we define an array of size 3.",null,null,null,null,"Sub Array1()"]
[null,null,"We put three items into the array.",null,null,null,null,"    Dim groups(3)"]
[null,null,"Last, we report on what the second item is.",null,null,null,null,"    groups(1) = \"Front Office\""]
[null,null,null,null,null,null,null,"    groups(2) = \"Middle Office\""]
[null,null,"You can put items into whichever position of the array you want.",null,null,null,null,"    groups(3) = \"Treasury\""]
[null,null,"For example, into position 1, then position 3 and then 2.",null,null,null,null,"    MsgBox \"The second group is: \" & groups(2)"]
[null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Resizing an array"]
[null,null,null,null,null,null,null,"Sub Array2()"]
[null,null,"In the example above we defined the array as having a size of 3 items.  ",null,null,null,null,"    Dim groups()"]
[null,null,"We then proceeded to fill the array with three items.  But what if,",null,null,null,null,"    ReDim groups(3)"]
[null,null,"at the time we declare the array, we don't know how many items",null,null,null,null,"    groups(1) = \"Front Office\""]
[null,null,"it will contain.  In that case we can give the array in initial size",null,null,null,null,"    groups(2) = \"Middle Office\""]
[null,null,"and later can redimension it to become bigger.",null,null,null,null,"    groups(3) = \"Treasury\""]
[null,null,null,null,null,null,null,"    ' We want to add more items"]
[null,null,null,null,null,null,null,"    ' Redimension the array and keep its contents"]
[null,null,null,null,null,null,null,"    ReDim Preserve groups(6)"]
[null,null,null,null,null,null,null,"    ' Now we can add more items"]
[null,null,null,null,null,null,null,"    groups(4) = \"Central Exec\""]
[null,null,null,null,null,null,null,"    MsgBox \"The second group is: \" & groups(2)"]
[null,null,null,null,null,null,null,"    MsgBox \"The fourth group is: \" & groups(4)"]
[null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Finding the upper and lower bounds of an array"]
[null,null,null,null,null,null,null,"Sub ArrayBounds()"]
[null,null,"The LBound and UBound functions give the lower and upper bounds",null,null,null,null,"    Dim groups()"]
[null,null,"of an array.  The lower bound is the lowest numbered position in the",null,null,null,null,"    ReDim groups(4)"]
[null,null,"array and the upper bound is the highest numbered position.",null,null,null,null,"    MsgBox \"Lower bound of array is: \" & LBound(groups)"]
[null,null,null,null,null,null,null,"    MsgBox \"Upper bound of array is: \" & UBound(groups)"]
[null,null,null,null,null,null,null,"    ReDim Preserve groups(11)"]
[null,null,null,null,null,null,null,"    MsgBox \"New lower bound of array is: \" & LBound(groups)"]
[null,null,null,null,null,null,null,"    MsgBox \"New upper bound of array is: \" & UBound(groups)"]
[null,null,null,null,null,null,null,"End Sub"]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Adding items to a collection"]
[null,null,"A collection is a useful container to store data.",null,null,null,null,"Sub Collection1()"]
[null,null,"Data can be numbers, text, other collections and",null,null,null,null,"    Dim c As Collection"]
[null,null,"many other types of variables.",null,null,null,null,"    Set c = Nothing"]
[null,null,null,null,null,null,null,"    Set c = New Collection"]
[null,null,"In this example we show how to create a",null,null,null,null,"    c.Add \"apple\""]
[null,null,"collection, how to add items to it",null,null,null,null,"    c.Add 12"]
[null,null,"and how to retrieve the items.",null,null,null,null,"    c.Add \"banana\""]
[null,null,null,null,null,null,null,"    MsgBox \"Item number 2 is \" & c.Item(2)"]
[null,null,"Note that we didn't need to specify the initial size of the",null,null,null,null,"    MsgBox \"Item number 1 is \" & c.Item(1)"]
[null,null,"collection.  Note also that items, when added, get added",null,null,null,null,"    MsgBox \"Item number 3 is \" & c.Item(3)"]
[null,null,"to the end of the collection.",null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Determining how many items are in a collection"]
[null,null,null,null,null,null,null,"Sub Collection2()"]
[null,null,"The Count property of a collection gives the",null,null,null,null,"    Dim c As Collection"]
[null,null,"number of items in that collection.",null,null,null,null,"    Set c = Nothing"]
[null,null,null,null,null,null,null,"    Set c = New Collection"]
[null,null,null,null,null,null,null,"    c.Add \"apple\""]
[null,null,null,null,null,null,null,"    c.Add \"pear\""]
[null,null,null,null,null,null,null,"    c.Add \"banana\""]
[null,null,null,null,null,null,null,"    MsgBox \"Number of items is \" & c.Count"]
[null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Associating items with keys in a collection"]
[null,null,null,null,null,null,null,"Sub Collection3()"]
[null,null,"When you add an item to a collection you can",null,null,null,null,"    Dim c As Collection"]
[null,null,"associate a \"key\" with the item. Then, later,",null,null,null,null,"    Set c = Nothing"]
[null,null,"you can look up any key in the collection and ",null,null,null,null,"    Set c = New Collection"]
[null,null,"retrieve the associated item.",null,null,null,null,"    c.Add \"East coast division\", \"EC-D\""]
[null,null,null,null,null,null,null,"    c.Add \"Northern group (2)\", \"N2-G\""]
[null,null,null,null,null,null,null,"    c.Add \"Western territory (1)\", \"W1-T\""]
[null,null,null,null,null,null,null,"    MsgBox c.Item(\"N2-G\")"]
[null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Removing an item from a collection"]
[null,null,null,null,null,null,null,"Sub collection4()"]
[null,null,"The Remove method lets you remove an ",null,null,null,null,"    Dim c As Collection"]
[null,null,"item from a collection.  When you remove an item you",null,null,null,null,"    Set c = Nothing"]
[null,null,"specify at which position it is.",null,null,null,null,"    Set c = New Collection"]
[null,null,null,null,null,null,null,"    c.Add \"apple\""]
[null,null,null,null,null,null,null,"    c.Add \"pear\""]
[null,null,null,null,null,null,null,"    c.Add \"banana\""]
[null,null,null,null,null,null,null,"    MsgBox \"Item 1 is \" & c.Item(1)"]
[null,null,null,null,null,null,null,"    c.Remove 1"]
[null,null,null,null,null,null,null,"    MsgBox \"Item 1 is \" & c.Item(1)"]
[null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]

{"sheet":"Error handling","index":6,"n_rows":70,"n_cols":13,"rows_kept":70,"cut":false,"hidden":false,"merged":[[26,2,26,13],[35,2,35,13],[46,2,46,13],[58,2,58,13],[69,2,69,13],[33,2,33,13],[44,2,44,13],[56,2,56,13],[2,2,5,10],[4,12,4,13],[24,2,24,13],[14,2,14,13],[8,2,8,12],[9,2,9,12],[10,2,10,12],[11,2,11,12],[12,2,12,12],[16,2,16,13]]}
[]
[null,null,{"$f":"label_overview & Index!C14","v":"Overview -  Error handling"}]
[null,null,"Errors can occur for many reasons in Visual Basic.  For example a statement might ask Visual Basic to open a file but the file might be missing.  Visual Basic has a \"default\" way of dealing with errors which is to stop Visual Basic and give an error message. You can add your own error handling to override this behaviour.  Examples are below."]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"www.tykoh.com"]
[]
[]
[null,null,"Index"]
[null,null,"Default treatment of errors"]
[null,null,{"$f":"error_2","v":"On Error Resume Next #1"}]
[null,null,{"$f":"error_3","v":"On Error Resume Next #2"}]
[null,null,{"$f":"error_4","v":"On Error GoTo #1"}]
[null,null,{"$f":"error_5","v":"On Error GoTo #2"}]
[]
[null,null,{"$f":"label_back_to_main_index","v":"Back to main index"}]
[]
[null,null,"Default treatment of errors          <--- Click on a topic to scroll it to the top of the page"]
[]
[null,null,"In this example we divide one number by another.  The ",null,null,null,null,null,"Sub Error1()"]
[null,null,"second number happens to be zero.  The default Visual",null,null,null,null,null,"    MsgBox \"Deliberate error: \" & 1 / 0"]
[null,null,"Basic action is shown when you press the button to the ",null,null,null,null,null,"    MsgBox \"Next line\""]
[null,null,"right.  Note that the statement following the one that",null,null,null,null,null,"End Sub"]
[null,null,"caused the error isn't carried out."]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"On Error Resume Next #1"]
[]
[null,null,"Here we define an error handler.  If an error occurs we want",null,null,null,null,null,"Sub Error2()"]
[null,null,"Visual Basic to skip to the next statement.",null,null,null,null,null,"    On Error Resume Next"]
[null,null,null,null,null,null,null,null,"    MsgBox \"Deliberate error: \" & 1 / 0"]
[null,null,null,null,null,null,null,null,"    MsgBox \"Line 1\""]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"On Error Resume Next #2"]
[]
[null,null,"The \"Resume Next\" error handler will keep working for",null,null,null,null,null,"Sub Error3()"]
[null,null,"multiple errors ..",null,null,null,null,null,"    On Error Resume Next"]
[null,null,null,null,null,null,null,null,"    MsgBox \"Deliberate error: \" & 1 / 0"]
[null,null,null,null,null,null,null,null,"    MsgBox \"Line 1\""]
[null,null,null,null,null,null,null,null,"    MsgBox \"Deliberate error: \" & 1 / 0"]
[null,null,null,null,null,null,null,null,"    MsgBox \"Line 2\""]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"On Error GoTo #1"]
[]
[null,null,"Another type of error handler is the On Error GoTo one.",null,null,null,null,null,"Sub Error4()"]
[null,null,"If there is an error then Visual Basic jumps to another ",null,null,null,null,null,"    On Error GoTo err_4"]
[null,null,"place and carries on there.",null,null,null,null,null,"    MsgBox \"Deliberate error: \" & 1 / 0"]
[null,null,null,null,null,null,null,null,"    MsgBox \"Line 1\""]
[null,null,null,null,null,null,null,null,"    Exit Sub"]
[null,null,null,null,null,null,null,null,"err_4:"]
[null,null,null,null,null,null,null,null,"    MsgBox \"There was an error: \" & Err.Number"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"On Error GoTo #2"]
[]
[null,null,"Note that once an GoTo error happens the error handling is",null,null,null,null,null,"Sub Error5()"]
[null,null,"turned off.  The error is repeated in our example but the",null,null,null,null,null,"    On Error GoTo err_5"]
[null,null,"second error isn't caught in the same way as the first.",null,null,null,null,null,"    MsgBox \"Deliberate error: \" & 1 / 0"]
[null,null,null,null,null,null,null,null,"    MsgBox \"Line 1\""]
[null,null,"You can turn error handling back on with a Resume or",null,null,null,null,null,"    Exit Sub"]
[null,null,"Resume Next statement.",null,null,null,null,null,"err_5:"]
[null,null,null,null,null,null,null,null,"    MsgBox \"There was an error: \" & Err.Number"]
[null,null,null,null,null,null,null,null,"    MsgBox \"Deliberate error: \" & 1 / 0"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]

{"sheet":"Events","index":7,"n_rows":93,"n_cols":13,"rows_kept":93,"cut":false,"hidden":false,"merged":[[80,2,80,13],[92,2,92,13],[13,2,13,13],[78,2,78,13],[25,2,25,13],[15,2,15,13],[54,2,54,13],[56,2,58,12],[2,2,5,10],[4,12,4,13],[60,2,61,12],[35,2,38,9],[40,2,40,13],[8,2,8,12],[10,2,10,12],[11,2,11,12],[27,2,27,13],[9,2,9,12]]}
[]
[null,null,{"$f":"label_overview & Index!C15","v":"Overview -  Events"}]
[null,null,"Microsoft Office (and, more generally, Windows also) is an \"event-driven\" system.  Most things that happen (e.g. a user clicking a mouse button) cause events.  Events can be \"intercepted\" and dealt with by Visual Basic on a case-by-case basis.  By doing that we can \"extend\" the abilities of the Office products we use.  Some examples of event processing are given below."]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"www.tykoh.com"]
[]
[]
[null,null,"Index"]
[null,null,"Processing a cell selection event"]
[null,null,{"$f":"event_sel_change2","v":"Processing a cell selection event (#2)"}]
[null,null,{"$f":"events_file_save","v":"Processing a file save event"}]
[null,null,{"$f":"events_hyperlink","v":"Processing a hyperlink event"}]
[]
[null,null,{"$f":"label_back_to_main_index","v":"Back to main index"}]
[]
[null,null,"Processing a cell selection event          <--- Click on a topic to scroll it to the top of the page"]
[]
[null,null,"Sometimes it is useful to detect when a cell changes."]
[null,null,"Visual Basic can notify you when this happens and you"]
[null,null,"can deal with cell changes on a case-by-case basis."]
[null,null,"To demonstrate that happening:"]
[]
[null,null,"1.  Select one of these colored cells:"]
[null,null,null,null,null,null,null,"You chose a red color."]
[null,null,"2.  This cell will show what color your selection was:"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Processing a cell selection event (#2)"]
[]
[null,null,"In this example we simulate a tabbed window by detecting "]
[null,null,"which tab the user clicks on.  The example could"]
[null,null,"be modified to produce an \"in-sheet\" menuing system."]
[]
[null,null,null,"Help A","Help B","Help C","Help D","Help E","Help F"]
[]
[null,null,"There is a risk in this type of event processing of going into a loop.  If the event processing causes another selection event then it would go into an endless loop (unless we deal with this possibility with extra code)."]
[]
[]
[]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"This is the text for help A.  When the user clicks on a \"tab\" the appropriate help message is shown in the body of the help window."]
[null,null,"The help system works by catching the worksheet's SelectionChange event.  When the event occurs a custom section of code works out what text to display to the user."]
[null,null,"There is a risk in this type of event processing of going into a loop.  If the event processing causes another selection event then it would go into an endless loop (unless we deal with this possibility with extra code)."]
[null,null,"Fortunately the event processing doesn't cause any selection events on its own so the risk discussed in Help C doesn't eventuate."]
[null,null,"The code used in this help system could also be used with slight modifications to produce an \"in-sheet\" menuing system."]
[null,null,"This is the last help topic - F."]
[]
[]
[]
[]
[]
[]
[null,null,"Processing a file save event"]
[]
[null,null,"In this example we detect the File Save event that occurs when the user saves this workbook.  When the File Save event occurs we first check all of the sheets in the workbook for errors.  If any errors are found we warn the user and ask them whether they really want to save.  This gives them the opportunity to cancel the save, fix the errors and then save the workbook in a clean state.  "]
[]
[]
[]
[null,null,"Business use:  This technique might be useful for certain business-critical workbooks.  During a file save it automatically checks the workbook for errors and alerts the user if any are found."]
[]
[]
[null,null,"To see how the file save event can be managed:"]
[]
[null,null,"1.   Save this workbook.  If the workbook is titled"]
[null,null,"      as [Read-Only] you need to save it as a writeable"]
[null,null,"      worksheet."]
[]
[null,null,"2.  Press the EnableCheckBeforeSave button.  This"]
[null,null,"     will enable Visual Basic to detect File Save events."]
[]
[null,null,"3.  Try saving the workbook again.  This time error"]
[null,null,"     checking will be done."]
[]
[null,null,"4.  Press the DisableCheckBeforeSave button to"]
[null,null,"     disable the checks."]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Processing a hyperlink event"]
[]
[null,null,"You may have noticed that when you hyperlink (and also when you click on headings) that Excel scrolls "]
[null,null,"the new topic to the top of the page.  This isn't standard Excel behaviour.  The hyperlink scrolling"]
[null,null,"is achieved by an event handler that looks like this:"]
[]
[null,null,"Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)"]
[null,null,"    ActiveWindow.ScrollRow = ActiveCell.Row"]
[null,null,"    On Error Resume Next",null,null,null,null,null,"These statements have been added to"]
[null,null,"    ActiveCell.Offset(0, -1).Select",null,null,null,null,null,"achieve the auto-scroll."]
[null,null,"End Sub"]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]

{"sheet":"Files","index":8,"n_rows":84,"n_cols":13,"rows_kept":84,"cut":false,"hidden":false,"merged":[[2,2,5,10],[4,12,4,13],[13,2,13,13],[19,2,25,6],[15,2,15,13],[8,2,8,12],[9,2,9,12],[10,2,10,12],[11,2,11,12],[27,2,27,13],[45,2,45,13],[59,2,59,13],[83,2,83,13],[29,2,29,13],[47,2,47,13],[61,2,61,13]]}
[]
[null,null,{"$f":"label_overview & Index!C16","v":"Overview -  Files"}]
[null,null,"Files let you store data \"permanently\".  To access files in Visual Basic you generally open the file, then read from or write to it and then close the file.  Generally, when reading or writing you refer to the file by a \"handle\".  The handle is defined when you open the file.  Examples of working with files are given below."]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"www.tykoh.com"]
[]
[]
[null,null,"Index"]
[null,null,"Writing to a file"]
[null,null,{"$f":"files_read","v":"Reading a file"}]
[null,null,{"$f":"files_delete","v":"Deleting a file"}]
[null,null,{"$f":"files_list_in_folder","v":"Listing files in a folder"}]
[]
[null,null,{"$f":"label_back_to_main_index","v":"Back to main index"}]
[]
[null,null,"Writing to a file          <--- Click on a topic to scroll it to the top of the page"]
[null,null,null,null,null,null,null,null,"Sub FileWrite()"]
[null,null,null,null,null,null,null,null,"    On Error GoTo err_WF"]
[null,null,null,null,null,null,null,null,"    Open \"c:\\test.txt\" For Output As #1"]
[null,null,"In this example we open an file and write to it. If the file doesn't exist at the time we open it then Visual Basic will create it.  If the file already exists then it will be re-created (and all current content removed). After we have written data to the file (with Print statements) we close the file and exit the subroutine.",null,null,null,null,null,"    Print #1, \"The first line\""]
[null,null,null,null,null,null,null,null,"    Print #1, \"The second line\""]
[null,null,null,null,null,null,null,null,"    Close #1"]
[null,null,null,null,null,null,null,null,"    MsgBox \"Write to c\\test.txt is complete\""]
[null,null,null,null,null,null,null,null,"    Exit Sub"]
[null,null,null,null,null,null,null,null,"err_WF:"]
[null,null,null,null,null,null,null,null,"    MsgBox \"There was an error writing the file\""]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Reading a file"]
[]
[null,null,"To read a file we open it for input.  The EOF",null,null,null,null,null,"Sub FileRead()"]
[null,null,"detects when we have reached the end of file #1.",null,null,null,null,null,"    On Error GoTo err_RF1"]
[null,null,"When finished with the file we close it.",null,null,null,null,null,"    Open \"c:\\test.txt\" For Input As #1"]
[null,null,null,null,null,null,null,null,"    Dim s"]
[null,null,null,null,null,null,null,null,"    While Not EOF(1)"]
[null,null,null,null,null,null,null,null,"        Input #1, s"]
[null,null,null,null,null,null,null,null,"        MsgBox \"Read from c:\\test: \" & s"]
[null,null,null,null,null,null,null,null,"    Wend"]
[null,null,null,null,null,null,null,null,"    Close #1"]
[null,null,null,null,null,null,null,null,"    Exit Sub"]
[null,null,null,null,null,null,null,null,"err_RF1:"]
[null,null,null,null,null,null,null,null,"    MsgBox \"Error in reading c:\\test.txt. Does it exist?\""]
[null,null,null,null,null,null,null,null,"    Exit Sub"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Deleting a file"]
[]
[null,null,"To remove a file you can use the Kill statement.",null,null,null,null,null,"Sub FileDelete()"]
[null,null,"Killed files don't go to the recycle bin so they can't",null,null,null,null,null,"    ' Note - Killed files don't go to the"]
[null,null,"be recovered if you need them again.",null,null,null,null,null,"    ' recycle bin so Kill cannot be undone!!"]
[null,null,null,null,null,null,null,null,"    On Error GoTo err_FD"]
[null,null,null,null,null,null,null,null,"    Kill \"c:\\test.txt\""]
[null,null,null,null,null,null,null,null,"    MsgBox \"c:\\test.txt was deleted\""]
[null,null,null,null,null,null,null,null,"    Exit Sub"]
[null,null,null,null,null,null,null,null,"err_FD:"]
[null,null,null,null,null,null,null,null,"    MsgBox \"Error in deleting c\\test.txt.  Does it exist?\""]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Listing files in a folder"]
[]
[null,null,"In this example we list the"]
[null,null,"first five files we find in a",null,null,"First five files in C:\\",null,null,"Sub ListFilesInFolder()"]
[null,null,"specified folder.",null,null,null,null,null,"    Dim file: file = Dir(\"c:\\\", vbNormal)"]
[null,null,null,null,null,null,null,null,"    Dim r As Range: Set r = Range(\"files_report\")"]
[null,null,"Range name is files_report",null,null,null,null,null,"    Dim k: k = 0"]
[null,null,null,null,null,null,null,null,"    Do While file <> \"\" And k < 5"]
[null,null,null,null,null,null,null,null,"        r = file"]
[null,null,null,null,null,null,null,null,"        Set r = r.Offset(1, 0)"]
[null,null,"Business use:  This could be part of an integrated",null,null,null,null,null,"        file = Dir"]
[null,null,"file management system whereby the user could",null,null,null,null,null,"        k = k + 1"]
[null,null,"do their file management without needing to",null,null,null,null,null,"    Loop"]
[null,null,"alternate between Excel and Explorer.",null,null,null,null,null,"End Sub"]
[]
[]
[null,null,null,null,null,null,null,null,"Sub UndoListFilesInFolder()"]
[null,null,null,null,null,null,null,null,"    On Error GoTo err_Undo"]
[null,null,null,null,null,null,null,null,"    Dim r As Range:   Set r = Range(\"files_report\")"]
[null,null,null,null,null,null,null,null,"    Range(r, r.End(xlDown)).ClearContents"]
[null,null,null,null,null,null,null,null,"err_Undo:"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]

{"sheet":"Forms","index":9,"n_rows":106,"n_cols":13,"rows_kept":106,"cut":false,"hidden":false,"merged":[[2,2,5,10],[4,12,4,13],[7,2,7,13],[9,2,9,12],[8,2,8,12],[80,2,80,13],[105,2,105,13],[12,2,12,12],[10,2,10,12],[11,2,11,12],[78,2,78,13],[53,2,53,13],[14,2,14,13],[55,2,55,13],[37,2,37,13],[16,2,16,13],[28,2,28,13],[26,2,26,13],[39,2,39,13]]}
[]
[null,null,{"$f":"label_overview & Index!C17","v":"Overview -  Forms"}]
[null,null,"Form elements consist of checkboxes, option buttons, text entry fields and so on.  Form elements can be placed on separate forms or they can exist in worksheets.  Form elements let you produce more user-friendly and secure user experiences.  In the following examples we look at how in-worksheet form elements can be accessed and used."]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"www.tykoh.com"]
[]
[]
[null,null,"Index"]
[null,null,"Detect a checkbox event / read a checkbox"]
[null,null,{"$f":"forms_read_option_button","v":"Detect an option button event / read an option button"}]
[null,null,{"$f":"forms_read_combo","v":"Detect a drop-down event / read a drop down "}]
[null,null,{"$f":"forms_drop_down_fill","v":"Filling a DropDown from Visual Basic"}]
[null,null,{"$f":"forms_select_chart","v":"Selecting Chart Series"}]
[]
[null,null,{"$f":"label_back_to_main_index","v":"Back to main index"}]
[]
[null,null,"Detect a checkbox event / read a checkbox          <--- Click on a topic to scroll it to the top of the page"]
[null,null,null,null,null,null,null,null,null,null,"Sub CheckBoxEvent()"]
[null,null,"The checkbox to the right is linked to the",null,null,null,null,null,null,null,"    Dim cb As CheckBox"]
[null,null,"macro CheckBoxEvent.  When the checkbox",null,null,null,null,null,null,null,"    Set cb = Worksheets(\"forms\").CheckBoxes(\"cb_1\")"]
[null,null,"is clicked CheckBoxEvent is run ",null,null,null,null,null,null,null,"    If cb.Value = 1 Then"]
[null,null,"automatically by Visual Basic.  That macro",null,null,null,null,"Checkbox is ",null,null,"        MsgBox \"Check box is on\""]
[null,null,"then reads the state of the checkbox and",null,null,null,null,"named cb_1",null,null,"    Else"]
[null,null,"shows a corresponding message to the user.",null,null,null,null,null,null,null,"        MsgBox \"Check box is off\""]
[null,null,null,null,null,null,null,null,null,null,"    End If"]
[null,null,null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Detect an option button event / read an option button"]
[null,null,null,null,null,null,null,null,null,null,"Sub OptionButtonEvent()"]
[null,null,"The option buttons to the right are linked to ",null,null,null,null,null,null,null,"    Dim op1 As OptionButton, op2 As OptionButton"]
[null,null,"the macro OptionButtonEvent.  When an",null,null,null,null,null,null,null,"    Set op1 = Worksheets(\"forms\").OptionButtons(\"op_1\")"]
[null,null,"option button is clicked the macro is run.",null,null,null,null,null,null,null,"    Set op2 = Worksheets(\"forms\").OptionButtons(\"op_2\")"]
[null,null,"The macro then reads the states of the",null,null,null,null,"Option buttons",null,null,"    If op1.Value = 1 Then MsgBox \"Option 1 was chosen\""]
[null,null,"option buttons and shows a corresponding",null,null,null,null,"are named",null,null,"    If op2.Value = 1 Then MsgBox \"Option 2 was chosen\""]
[null,null,"message to the user.",null,null,null,null,"op_1 and op_2",null,null,"End Sub"]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Detect a drop-down event / read a drop down "]
[]
[null,null,"The combobox (drop-down) to the right is linked to",null,null,null,null,null,null,null,"Sub DropDownEvent()"]
[null,null,"the macro DropDownEvent.  When a choice is ",null,null,null,null,null,null,null,"    Range(\"forms_all\").Interior.ColorIndex = 2"]
[null,null,"made the macro reads the choice and highlights",null,null,null,null,null,null,null,"    Dim dd As DropDown, r As Range"]
[null,null,"the corresponding row below.",null,null,null,null,"DropDown is ",null,null,"    Set dd = Worksheets(\"forms\").DropDowns(\"combo_1\")"]
[null,null,null,null,null,null,null,"named combo_1",null,null,"    Set r = Range(\"forms_north\")"]
[null,null,null,"Range name is forms_north",null,null,null,null,null,null,"    Set r = r.Offset(dd.Value - 1, 0)"]
[null,null,null,null,null,null,null,null,null,null,"    Set r = Range(r, r.End(xlToRight))"]
[null,null,null,"North",12,16,23,55,null,null,"    r.Interior.ColorIndex = 3"]
[null,null,null,"South",27,36,98,12,null,null,"End Sub"]
[null,null,null,"East",36,78,23,85]
[null,null,null,"West",36,45,97,29,null,null,"Range name is forms_all"]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Filling a DropDown from Visual Basic"]
[null,null,null,null,null,null,null,null,null,null,"Sub GetNames()"]
[null,null,"The button on the right is linked to the GetNames ",null,null,null,null,null,null,null,"    Dim nms As Names, nm As name"]
[null,null,"macro. When the button is pressed we list all the",null,null,null,null,null,null,null,"    Set nms = ActiveWorkbook.Names"]
[null,null,"names in the active workbook and put them into",null,null,null,null,null,null,null,"    Dim dd As DropDown, r As Range"]
[null,null,"the DropDown below.",null,null,null,null,null,null,null,"    Set dd = Worksheets(\"forms\").DropDowns(\"combo_2\")"]
[null,null,null,null,null,null,null,null,null,null,"    dd.RemoveAllItems"]
[null,null,null,null,null,null,null,"DropDown is ",null,null,"    For Each nm In nms"]
[null,null,null,null,null,null,null,"named combo_2",null,null,"        dd.AddItem nm.name & \":\" & nm.RefersTo"]
[null,null,null,null,null,null,null,null,null,null,"    Next"]
[null,null,"The DropDown above is linked to the macro",null,null,null,null,null,null,null,"    dd.ListIndex = 1"]
[null,null,"ShowNameAndReference.  When you make",null,null,null,null,null,null,null,"End Sub"]
[null,null,"a selection in the DropDown the macro is"]
[null,null,"run.  The macro reads the current selection",null,null,null,null,null,null,null,"Sub ShowNameAndReference()"]
[null,null,"and fills out the section below detailing",null,null,null,null,null,null,null,"    Dim dd As DropDown"]
[null,null,"information about the name.",null,null,null,null,null,null,null,"    Set dd = Worksheets(\"forms\").DropDowns(\"combo_2\")"]
[null,null,null,null,null,"Range name is forms_name",null,null,null,null,"    Dim item: item = dd.List(dd.ListIndex)"]
[null,null,null,null,null,null,null,null,null,null,"    Dim parts() As String"]
[null,null,"Name:","addr_block_cells",null,null,null,"Range name is",null,null,"    parts = Split(item, \":\")"]
[null,null,"Refers to:","=Addressing!$C$114",null,null,null,"forms_refers_to",null,null,"    Range(\"forms_name\") = parts(0)"]
[null,null,"Contains:",{"$f":"Addressing!$C$87","v":"Addressing a block of cells in a range"},null,null,null,null,null,null,"    Range(\"forms_refers_to\") = \"'\" & parts(1)"]
[null,null,null,null,null,null,null,null,null,null,"    Range(\"forms_contains\") = parts(1)"]
[null,null,null,null,null,"Range name is forms_contains",null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Selecting Chart Series"]
[null,null,"In this example we use checkbox and dropdown form elements to let the user select which columns (Div A, Div B & Div C) they want"]
[null,null,"charted.  The dropdown lets the user select the type of chart used to display the data."]
[]
[null,null,"Business use:  Charts and their presentation can be more dynamic."]
[]
[]
[null,null,null,"Chart type:"]
[]
[null,null,null,"Select columns:"]
[null,null,"Year",null,"Div A","Div B","Div C"]
[null,null,{"$f":"C93-1","v":2000},null,568,120,{"$f":"D92+F92/2","v":60}]
[null,null,{"$f":"C94-1","v":2001},null,568,104,{"$f":"D93+F93/2","v":52}]
[null,null,2002,null,444,99,49.5]
[null,null,2003,null,568,122,61,null,"No Data to chart.  Please tick one or more tickboxes."]
[null,null,2004,null,444,123,61.5,null,null,null,"Clustered Column"]
[null,null,2005,null,444,100,50,null,null,null,"Stacked Column"]
[null,null,2006,null,568,150,75,null,null,null,"100% Stacked Column"]
[null,null,2007,null,444,190,95,null,null,null,"Clustered Column 3D "]
[null,null,{"$f":"C101-1","v":2008},null,568,201,100.5,null,null,null,"Stacked Column 3D"]
[null,null,2009,null,444,177,88.5,null,null,null,"100% Stacked Column 3D"]
[]
[]
[]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]

{"sheet":"Menus","index":10,"n_rows":118,"n_cols":13,"rows_kept":118,"cut":false,"hidden":false,"merged":[[117,2,117,13],[11,2,11,13],[69,2,69,13],[13,2,13,13],[2,2,5,10],[4,12,4,13],[66,2,66,13],[8,2,8,12],[9,2,9,12]]}
[]
[null,null,{"$f":"label_overview & Index!C18","v":"Overview -  Menus"}]
[null,null,"Menus are a convenient way of invoking or running macros.  Different menu items can be linked to different macros.  The linking can be done manually (by going to Tools | Customize in Excel) or the linking can be \"automatic\" and under the control of Visual Basic.  The examples below show how Visual Basic can dynamically alter menu layouts and attach and detach items in a menu and link them to macros.\n"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"www.tykoh.com"]
[]
[]
[null,null,"Index"]
[null,null,"Extending toolbar menu"]
[null,null,{"$f":"menus_extend_right_click","v":"Extending right-click popup menu"}]
[]
[null,null,{"$f":"Labels!A1","v":"Back to main index"}]
[]
[null,null,"Extending toolbar menu          <--- Click on a topic to scroll it to the top of the page"]
[]
[null,null,"The main Excel toolbar menu typically looks something like that shown below."]
[]
[]
[]
[]
[]
[null,null,"You can extend the menu to add links to your own macros.  If you do that the"]
[null,null,"toolbar might then look as shown below."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"To demonstrate that a new menu can be added follow these steps:"]
[]
[null,null,"1  Look at the main menu on your version of Excel.  Remember what the "]
[null,null,null,"right-hand part of it looks like - because that will soon change."]
[]
[null,null,"2  Press this button:"]
[]
[null,null,"3  Look at the main Excel menu again.  The menu should now"]
[null,null,"    include an additional submenu:"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,"If you click on one of the new menu or submenu items a corresponding macro"]
[null,null,null,"will run.  In our simple example the macros just give a message.  In practice, though"]
[null,null,null,"the macros would do more."]
[]
[null,null,"   If you keep this workbook open, open another workbook and then switch between the two "]
[null,null,"   workbooks you will find that the additional menu item appears only when this workbook"]
[null,null,"   is active.  Alternatively you can make menu extensions \"permanent\" and then they will"]
[null,null,"   will appear whichever workbooks you have open."]
[]
[null,null,"4  To remove the additional menu item"]
[null,null,"    press the button to the right"]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[]
[null,null,"Extending right-click popup menu"]
[]
[null,null,null,"In Excel when you click the right mouse button a popu menu appears.  "]
[null,null,null,"The menu will look much like the one shown on the right."]
[]
[null,null,null,"You can extend the popup list to add links to your own macros."]
[null,null,null,"If you do that then the popup list might look as shown below."]
[null,null,null,"A new link has been added at the end of the list."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null," "]
[]
[null,null,null,"To demonstrate that a popup menu can be added to - follow these steps:"]
[]
[null,null,null,"1  Click the right mouse button and look at the popup menu that appears."]
[]
[null,null,null,"2. Press the button to the right."]
[]
[null,null,null,"3. Click the right mouse button again.  This time your popup menu should"]
[null,null,null,"    include an additional item:"]
[]
[null,null,null,"    You can then cllick on the new"]
[null,null,null,"    link and run the macro."]
[]
[null,null,null,"4. To remove the additional menu item "]
[null,null,null,"    press the button to the right."]
[]
[]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]

{"sheet":"Miscellaneous","index":11,"n_rows":248,"n_cols":13,"rows_kept":248,"cut":false,"hidden":false,"merged":[[247,2,247,13],[123,2,123,13],[215,2,215,13],[220,10,226,12],[148,4,148,5],[150,2,150,13],[220,7,220,8],[134,2,134,13],[126,2,131,6],[152,2,152,13],[4,12,4,13],[216,2,218,12],[2,2,5,10],[33,2,33,13],[8,2,8,12],[121,2,121,13],[21,2,21,13],[60,2,60,13],[88,2,88,13],[100,2,100,13],[9,2,9,12],[10,2,10,12],[11,2,11,12],[12,2,12,12],[13,2,13,12],[213,2,213,13],[205,2,205,3],[206,2,206,3],[208,2,208,3],[209,2,209,3],[17,2,17,12],[198,2,198,13],[14,2,14,12],[19,2,19,13],[35,2,35,13],[62,2,62,13],[102,2,102,13],[15,2,15,12],[113,2,116,5],[136,2,136,13],[90,2,90,13],[196,2,196,13],[16,2,16,12]]}
[]
[null,null,{"$f":"label_overview & Index!C19","v":"Overview -  Miscellaneous"}]
[null,null,"A sampling of miscellaneous Visual Basic techniques are listed below.  Techiques include:  Starting another application external to Excel,  giving Excel a \"memory\" of past cell values,  maintaining information in the Windows Registry, and listing cells that meet given criteria."]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"www.tykoh.com"]
[]
[]
[null,null,"Index"]
[null,null,"Setting the text in the status bar"]
[null,null,{"$f":"misc_find_cell_type","v":"Finding the type of a cell"}]
[null,null,{"$f":"misc_cells_meet_test","v":"Listing cells that meet a given criterion"}]
[null,null,{"$f":"misc_start_app","v":"Starting another program"}]
[null,null,{"$f":"misc_counter","v":"Maintaining information external to the workbook"}]
[null,null,{"$f":"misc_find_path","v":"Find the pathname to and name of this workbook"}]
[null,null,{"$f":"misc_get_details","v":"Find the name of the user and machine"}]
[null,null,{"$f":"misc_date_time","v":"Getting the date and time"}]
[null,null,{"$f":"misc_copy_paste","v":"Copying and pasting"}]
[null,null,{"$f":"misc_past_values","v":"Keeping track of past cell values"}]
[]
[null,null,{"$f":"label_back_to_main_index","v":"Back to main index"}]
[]
[null,null,"Setting the text in the status bar          <--- Click on a topic to scroll it to the top of the page"]
[]
[]
[null,null,"The status bar is shown at the bottom of the Excel window.",null,null,null,null,"Sub SetStatusBar()"]
[null,null,"If you have macros that take a while to run then the status",null,null,null,null,"    Application.StatusBar = \"The status bar has been set\""]
[null,null,"bar can be used to give a periodic progress report to the",null,null,null,null,"End Sub"]
[null,null,"user."]
[]
[]
[null,null,null,null,null,null,null,"Sub UndoSetStatusBar()"]
[null,null,null,null,null,null,null,"    Application.StatusBar = \"\""]
[null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Finding the type of a cell"]
[null,null,"In this example we find what types of data are in the table"]
[]
[null,null,"below.  We then colorise the cells by their type.",null,null,null,null,"Sub ShowCellTypes()"]
[null,null,null,null,null,null,null,"    Dim cell"]
[null,null,"Range name is misc_1",null,null,null,null,"    On Error Resume Next"]
[null,null,null,null,null,null,null,"    Dim r: Set r = Range(\"misc_1\")"]
[null,null,3.7,"cat",{"$f":"1/0","v":{"$error":"#DIV/0!"}},null,null,"    r.SpecialCells(xlCellTypeFormulas, xlTextValues).Interior.ColorIndex = 38"]
[null,null,{"$f":"LEN(D43)>2","v":true}," ",67,null,null,"    r.SpecialCells(xlCellTypeFormulas, xlNumbers).Interior.ColorIndex = 35"]
[null,null,{"$f":"C43+E44","v":70.7},38,{"$f":"D43&\" \" & D45","v":"cat 38"},null,null,"    r.SpecialCells(xlCellTypeComments).Interior.ColorIndex = 37"]
[null,null,null,"2009-01-20",null,null,null,"    r.SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 39"]
[null,null,null,null,null,null,null,"    For Each cell In r"]
[null,null,"Following is the color convention we use.",null,null,null,null,"        Select Case True"]
[null,null,null,null,null,null,null,"            Case IsDate(cell): cell.Interior.ColorIndex = 6"]
[null,null,"Legend",null,null,null,null,"            Case Application.IsLogical(cell): cell.Interior.ColorIndex = 8"]
[null,null,"(Color = 38)","Formulas that return text",null,null,null,"            Case Application.IsErr(cell): cell.Interior.ColorIndex = 43"]
[null,null,"(Color = 35)","Formulas that return numbers",null,null,null,"        End Select"]
[null,null,"(Color = 37)","Cells containing comments",null,null,null,"    Next"]
[null,null,"(Color = 39)","Empty cells",null,null,null,"End Sub"]
[null,null,"(Color = 6)","Cells with dates"]
[null,null,"(Color = 8)","Cells with logical expressions"]
[null,null,"(Color = 43)","Cells with errors"]
[null,null,null,null,null,null,null,"Sub UndoShowCellTypes()"]
[null,null,null,null,null,null,null,"    Range(\"misc_1\").Interior.ColorIndex = 2"]
[null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Listing cells that meet a given criterion"]
[]
[null,null,"In the following example we report on which cells in a range"]
[null,null,"meet a particular criterion (the cell's value is above 50).",null,null,null,null,"Sub MakeReport()"]
[null,null,null,null,null,null,null,"    Dim cell, r As Range"]
[null,null,"misc_2",{"$f":"1/0","v":{"$error":"#DIV/0!"}},12,null,null,"    Set r = Range(\"misc_report\")"]
[null,null,78,"cat",49.9,null,null,"    For Each cell In Range(\"misc_2\")"]
[null,null,122,35,{"$f":"D70+C68","v":{"$error":"#VALUE!"}},null,null,"        If IsNumeric(cell) Then"]
[null,null,null,null,null,null,null,"            If cell.Value > 50 Then"]
[null,null,null,null,"Range name: misc_report",null,null,"                r.Cells(1, 1).Value = \"The cell at \" & _"]
[null,null,null,null,null,null,null,"                    cell.Address & \" has a value of \" & cell.Value"]
[null,null,"Report on the cells above that exceed 50:",null,null,null,null,"                Set r = r.Offset(1, 0)"]
[null,null,null,null,null,null,null,"            End If"]
[null,null,null,null,null,null,null,"        End If"]
[null,null,null,null,null,null,null,"    Next"]
[null,null,null,null,null,null,null,"End Sub"]
[]
[]
[]
[null,null,null,null,null,null,null,"Sub ClearReport()"]
[null,null,null,null,null,null,null,"    On Error GoTo err_CR:"]
[null,null,null,null,null,null,null,"    Dim r As Range"]
[null,null,null,null,null,null,null,"    Set r = Range(\"misc_report\")"]
[null,null,null,null,null,null,null,"    Range(r, r.End(xlDown)).ClearContents"]
[null,null,null,null,null,null,null,"err_CR:"]
[null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Starting another program"]
[null,null,"Sometimes you might want to start another program from"]
[null,null,"Visual Basic.  In this example we start the Notepad"]
[null,null," application from Visual Basic.",null,null,null,null,"Sub RunApplication()"]
[null,null,null,null,null,null,null,"    Shell \"notepad.exe\", vbNormalFocus"]
[null,null,"Often, when you start another program, you will want to",null,null,null,null,"End Sub"]
[null,null,"command it to do something.  For more on this topic see"]
[null,null,"the \"Office integration\" examples."]
[]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Maintaining information external to the workbook"]
[]
[null,null,"Sometimes you might want to store and read information",null,null,null,null,"Sub GetCount()"]
[null,null,"but not have that information tied to a particular workbook.",null,null,null,null,"    Dim c"]
[null,null,null,null,null,null,null,"    c = GetSetting(\"MyApplication\", \"A-section-name\", \"counter\")"]
[null,null,"The SaveSetting and GetSetting functions let you save and",null,null,null,null,"    If c = \"\" Then c = 0"]
[null,null,"get information from the Windows Registry.  Information is",null,null,null,null,"    MsgBox \"Count is: \" & c"]
[null,null,"on a per-user basis on the local machine.",null,null,null,null,"End Sub"]
[]
[]
[]
[null,null,"Business use: Examples of where you might use this technique could be in assigning sequential numbers like \"new-customer-id\", \"deal ticket\", \"consignment number\", etc.",null,null,null,null,"Sub IncrementCount()"]
[null,null,null,null,null,null,null,"    Dim c"]
[null,null,null,null,null,null,null,"    c = GetSetting(\"MyApplication\", \"A-section-name\", \"counter\")"]
[null,null,null,null,null,null,null,"    If c = \"\" Then c = 0"]
[null,null,null,null,null,null,null,"    c = c + 1"]
[null,null,null,null,null,null,null,"    SaveSetting \"MyApplication\", \"A-section-name\", \"counter\", c"]
[null,null,null,null,null,null,null,"    MsgBox \"Counter was set to: \" & c"]
[null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Find the pathname to and name of this workbook"]
[]
[]
[null,null,"You might want to make a copy (perhaps a modified version) of a workbook.  If the workbook is called YY.xls, for example, you might want to save a modified version as YY_new.xls in the same folder as the original.  To do that it's useful to know the name of the current workbook and the folder it resides in.\n",null,null,null,null,"Sub GetPathName()"]
[null,null,null,null,null,null,null,"    On Error GoTo err_GPN"]
[null,null,null,null,null,null,null,"    MsgBox \"The path is \" & ThisWorkbook.Path"]
[null,null,null,null,null,null,null,"    MsgBox \"The name is \" & ThisWorkbook.name"]
[null,null,null,null,null,null,null,"    Exit Sub"]
[null,null,null,null,null,null,null,"err_GPN:"]
[null,null,null,null,null,null,null,"    MsgBox \"Make sure you save this workbook first.\""]
[null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Find the name of the user and machine"]
[]
[]
[null,null,"Sometimes it is useful to know the name of the user and"]
[null,null,"the machine they are using.  Visual Basic allows you "]
[null,null,"to make \"system calls\" to Windows to find out "]
[null,null,"information like this."]
[]
[]
[null,null,"User name:"]
[null,null,"Machine name:"]
[null,null,"Date:"]
[null,null,"Time:"]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Getting the date and time"]
[]
[null,null,"The Date function returns the current date:"]
[null,null,null,null,null,null,"Sub GetDate()"]
[null,null,null,null,null,null,"    MsgBox Date"]
[null,null,null,null,null,null,"End Sub"]
[]
[null,null,"Often you will want to format the date so that"]
[null,null,"it is shown in a different format from its default"]
[null,null,"value.  To do that you can use the Format",null,null,null,"Sub GetDateFormatted()"]
[null,null,"function.",null,null,null,"    MsgBox Format(Date, \"dd mmmm yy\")"]
[null,null,null,null,null,null,"End Sub"]
[]
[null,null,"To get the date in 90 days simply add 90"]
[null,null,"to the date."]
[null,null,null,null,null,null,"Sub GetDateIn90Days()"]
[null,null,null,null,null,null,"    MsgBox Format(Date + 90, \"dd mmmm yy\")"]
[null,null,null,null,null,null,"End Sub"]
[]
[null,null,"The second parameter of the Format function specifies how you want the date formatted.  \"mmmm\" means"]
[null,null,"\"show a month in long format\", \"mmm\" means show it in slightly shorter format, \"mm\" means \"shorter still\", etc."]
[null,null,"The following table shows various format statements and the results they generate."]
[]
[null,null,null,null,null,"Visual Basic statement ",null,null,"Generates this"]
[null,null,null,null,null,"Format(Date, \"dddd\")",null,null,{"$f":"InterpretDateTime(misc_update_dt,F177)","v":"Sunday"}]
[null,null,null,null,null,"Format(Date, \"ddd\")",null,null,"Sun"]
[null,null,"3rd day will be shown as 03 ---->",null,null,"Format(Date, \"dd\")",null,null,"02"]
[null,null,"3rd day will be shown as 3 ---->",null,null,"Format(Date, \"d\")",null,null,"2"]
[null,null,null,null,null,"Format(Date, \"mmmm\")",null,null,"September"]
[null,null,null,null,null,"Format(Date, \"mmm\")",null,null,"Sep"]
[null,null,"3rd month will be shown as 03 ---->",null,null,"Format(Date, \"mm\")",null,null,"09"]
[null,null,"3rd month will be shown as 3 ---->",null,null,"Format(Date, \"m\")",null,null,"9"]
[null,null,null,null,null,"Format(Date, \"yyyy\")",null,null,"2018"]
[null,null,null,null,null,"Format(Date, \"yy\")",null,null,"18"]
[null,null,0,null,null,"Format(Date, \"dddd-mm-yyyy\")",null,null,"Sunday-09-2018"]
[null,null,null,null,null,"Format(Date, \"ddd dd mmmm yyyy\")",null,null,"Sun 02 September 2018"]
[null,null,null,null,null,"Format(Date, \"mm/dd/yy\")",null,null,"09/02/18"]
[null,null,null,null,null,"Format(Date, \"Long Date\")",null,null,"Sunday, 2 September 2018"]
[null,null,null,null,null,"Format(Date, \"Short Date\")",null,null,"2/09/2018"]
[null,null,null,null,null,"Format(Time,\"hh\")",null,null,"13"]
[null,null,null,null,null,"Format(Time,\"h\")",null,null,"13"]
[null,null,null,null,null,"Format(Time,\"Short Time\")",null,null,"13:44"]
[null,null,null,null,null,"Format(Time,\"Long Time\")",null,null,"1:44:36 PM"]
[null,null,null,null,null,"Format(Time,\"hh:mm:ss AMPM\")",null,null,"01:44:36 PM"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Copying and pasting"]
[]
[null,null,"The example to the right shows how copying and "]
[null,null,"pasting can be done.",null,null,null,null,"Sub CopyAndPaste()"]
[null,null,null,null,null,null,null,"    Range(\"misc_copy_from\").Copy"]
[null,null,null,null,null,null,null,"    Range(\"misc_copy_to\").PasteSpecial (xlPasteAll)"]
[null,null,null,null,null,null,null,"End Sub"]
[null,null,"Range name is",null,null,"Order qty"]
[null,null,"misc_copy_from",null,null,12.34]
[]
[null,null,"Range name is",null,null,null,null,"Sub UndoCopyAndPaste()"]
[null,null,"misc_copy_to",null,null,null,null,"    Range(\"misc_copy_to \").ClearContents \"\""]
[null,null,null,null,null,null,null,"End Sub"]
[]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Keeping track of past cell values"]
[null,null,"Generally the value in any Excel cell will depend on the current values in other cells.  However, sometimes you might want the value in a cell to depend on an earlier value in another cell.  For example you might want to change certain inputs and see a histogram of a particular output.  The histogram summarises the current and past results by showing how frequently particular values have occurred.\n"]
[]
[]
[]
[null,null,"To see an example of tracking a history in this way:",null,null,null,null,"Histogram",null,null,"How does the histogram work? We define a function called Tyk_Histogram that accepts a history of cell values and presents the histogram data to the user.  The Tyk_Histogram function is linked to a new CHistogram class.  [See the Classes tab for an introduction to classes.]"]
[null,null,"1.  Put numbers into this cell:",null,null,null,null,"Value","Frequency"]
[null,null,null,null,null,null,null,{"$f":"Tyk_Histogram(histogram_reset,histogram_input)","v":0},1]
[null,null,"2.  For each new number the histogram will update.",null,null,null,null,0,0]
[null,null,"     The histogram will \"know\" about all the past values the",null,null,null,null,0,0]
[null,null,"     cell has taken.",null,null,null,null,0,0]
[null,null,null,null,null,null,null,0,0]
[null,null,"3.  Press this button to reset the histogram:",null,null,null,null,0,0]
[null,null,null,null,null,null,null,0,0]
[null,null,null,null,null,null,null,0,0]
[null,null,null,null,null,null,null,0,0]
[null,null,null,null,null,null,null,0,0]
[null,null,null,null,null,null,null,{"$f":"\"Histogram of values. [Number of values: \" & SUM(I223:I232) & \", Mean of values: \" & TEXT(SUMPRODUCT(H223:H232,I223:I232)/SUM(I223:I232),\"0.00\")& \"]\"","v":"Histogram of values. [Number of values: 1, Mean of values: 0.00]"},null,null,0]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]

{"sheet":"Office integration","index":12,"n_rows":79,"n_cols":13,"rows_kept":79,"cut":false,"hidden":false,"merged":[[2,2,5,10],[4,12,4,13],[12,2,12,12],[14,2,14,13],[8,2,8,12],[9,2,9,12],[10,2,10,12],[11,2,11,12],[78,2,78,13],[30,2,30,13],[16,2,16,13],[42,2,42,13],[67,2,67,13],[56,2,56,13],[32,2,32,13],[58,2,58,13],[64,2,64,7],[65,2,65,13],[40,2,40,13]]}
[]
[null,null,{"$f":"label_overview & Index!C20","v":"Overview -  Office integration"}]
[null,null,"Visual Basic is built into most Microsoft Office products.  With Visual Basic you can control one or more Office products from another.  So Visual Basic gives powerful workflow integration abilities.  The following simple examples illustrate controlling one Office application from another.\n"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"www.tykoh.com"]
[]
[]
[null,null,"Index"]
[null,null,"Controlling Microsoft Word"]
[null,null,{"$f":"C33","v":"Controlling Microsoft Word - more complex example"}]
[null,null,{"$f":"integration_ie","v":"Controlling Internet Explorer"}]
[null,null,{"$f":"integration_powerpoint","v":"Controlling PowerPoint"}]
[null,null,{"$f":"integration_outlook","v":"Controlling Outlook"}]
[]
[null,null,{"$f":"label_back_to_main_index","v":"Back to main index"}]
[]
[null,null,"Controlling Microsoft Word          <--- Click on a topic to scroll it to the top of the page"]
[null,null,null,null,null,null,null,null,null,"Sub WordExample()"]
[null,null,"In this example we demonstrate a simple integration between Excel and",null,null,null,null,null,null,"    On Error GoTo err_WE"]
[null,null,"Word.  When the button on the right is pressed Excel will start Word,",null,null,null,null,null,null,"    Dim wd, doc As Object"]
[null,null,"insert a blank document into Word and then place some text on the",null,null,null,null,null,null,"    Set wd = CreateObject(\"Word.Application\")"]
[null,null,"document.",null,null,null,null,null,null,"    wd.visible = True"]
[null,null,null,null,null,null,null,null,null,"    Set doc = wd.Documents.Add"]
[null,null,null,null,null,null,null,null,null,"    With wd.Selection"]
[null,null,null,null,null,null,null,null,null,"        .TypeText text:=\"Text from Excel to Word\""]
[null,null,null,null,null,null,null,null,null,"    End With"]
[null,null,null,null,null,null,null,null,null,"    Exit Sub"]
[null,null,null,null,null,null,null,null,null,"err_WE:"]
[null,null,null,null,null,null,null,null,null,"    MsgBox \"Couldn't start Word - is it installed?\""]
[null,null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Controlling Microsoft Word - more complex example"]
[]
[null,null,"This example builds on the preceding one.  Additionallly we paste"]
[null,null,"an image, set a hyperlink and define a header and footer."]
[]
[]
[]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Controlling Internet Explorer"]
[]
[]
[null,null,"When the button on the right is pressed Internet Explorer is started, the"]
[null,null,"web site www.ft.com (home site of the Financial Times) is navigated to,"]
[null,null,"the current price of the FTSE 100 stock index is extracted from that"]
[null,null,"page and Internet Explorer is quit.  Last, the FTSE 100 price is shown"]
[null,null,"to the user with a message box."]
[]
[null,null,"It can take a while to load a web page so this process may take a few seconds."]
[]
[null,null,"Business use:  This technique could be used to automate extracting information from a Internet or intranet site.  This can be"]
[null,null,"can be extended so that information flows both ways - from the internet to Excel and from Excel to the internet."]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Controlling PowerPoint"]
[]
[null,null,"In this example we demonstrate a simple integration between Excel and"]
[null,null,"PowerPoint.  When the button on the right is pressed Excel will start"]
[null,null,"PowerPoint, insert a blank slide into the presentation and then place "]
[null,null,"some text and graphics onto the slide."]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Controlling Outlook"]
[null,null,null,null,null,null,null,"Subject:",null,"A test mail"]
[null,null,"This example shows Outlook being controlled by Visual ",null,null,null,null,"Recipient:",null,"nobody@nobody.net"]
[null,null,"Basic from an Excel Application.  The example will work",null,null,null,null,"Message:",null,"Hi, This is a test mail from me to you."]
[null,null,"only if you have Outlook installed on this computer."]
[]
[null,null,"The mail will be put as a draft into the Outllook Drafts folder"]
[null,null,"but won't actually be sent."]
[]
[]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]

{"sheet":"Strings","index":13,"n_rows":159,"n_cols":13,"rows_kept":159,"cut":false,"hidden":false,"merged":[[73,2,73,13],[81,2,81,13],[79,2,79,13],[38,2,38,13],[55,2,55,13],[11,2,11,12],[12,2,12,12],[18,2,18,12],[19,2,19,12],[158,2,158,13],[146,2,146,13],[90,2,90,13],[101,2,101,13],[134,2,134,13],[112,2,112,13],[148,2,148,13],[46,2,46,13],[124,2,124,13],[24,2,24,13],[32,2,32,13],[40,2,40,13],[71,2,71,13],[2,2,5,10],[4,12,4,13],[136,2,136,13],[92,2,92,13],[103,2,103,13],[114,2,114,13],[126,2,126,13],[48,2,48,13],[57,2,57,13],[8,2,8,12],[17,2,17,12],[30,2,30,13],[22,2,22,13],[9,2,9,12],[10,2,10,12],[20,2,20,12],[13,2,13,12],[14,2,14,12],[15,2,15,12],[16,2,16,12]]}
[]
[null,null,{"$f":"label_overview & Index!C21","v":"Overview -  Strings and text"}]
[null,null,"String or text manipulation is a common Visual Basic need.  Text often needs to be joined, split, parsed or replaced.  There are a wide range of Visual Basic functions that can be used to do this.  Examples of those functions and their applications are listed below.\n"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"www.tykoh.com"]
[]
[]
[null,null,"Index"]
[null,null,"Getting the left part of a string"]
[null,null,{"$f":"string_right_part","v":"Getting the right part of a string"}]
[null,null,{"$f":"string_middle","v":"Getting the middle part of a string"}]
[null,null,{"$f":"strings_join","v":"Joining strings together"}]
[null,null,{"$f":"strings_split","v":"Splitting strings"}]
[null,null,{"$f":"string_length","v":"Finding the length of a string"}]
[null,null,{"$f":"strings_replace","v":"Replacing one part of a string with another"}]
[null,null,{"$f":"strings_replace_once","v":"Replacing one part of a string with another (but only once)"}]
[null,null,{"$f":"strings_substring","v":"Finding the position of a substring "}]
[null,null,{"$f":"strings_left_to_marker","v":"Extracting the left part of a string up to a marker"}]
[null,null,{"$f":"strings_left_to_marker2","v":"Extracting the left part of a string up to a marker (another way)"}]
[null,null,{"$f":"strings_right_to_marker","v":"Extracting the right part of a string back to a marker"}]
[null,null,{"$f":"strings_right_to_marker2","v":"Extracting the right part of a string back to a marker (another way)"}]
[]
[null,null,{"$f":"label_back_to_main_index","v":"Back to main index"}]
[]
[null,null,"Getting the left part of a string          <--- Click on a topic to scroll it to the top of the page"]
[null,null,null,null,null,null,null,null,"Sub StringLeft()"]
[null,null,"In this example we find the leftmost four ",null,null,null,null,null,"    Dim s"]
[null,null,"characters in a string.",null,null,null,null,null,"    s = \"ABCDEFGHIJKL\""]
[null,null,null,null,null,null,null,null,"    MsgBox \"The left 4 characters are: \" & Left(s, 4)"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Getting the right part of a string"]
[null,null,null,null,null,null,null,null,"Sub StringRight()"]
[null,null,"In this example we find the rightmost five",null,null,null,null,null,"    Dim s"]
[null,null,"characters in a string.",null,null,null,null,null,"    s = \"ABCDEFGHIJKL\""]
[null,null,null,null,null,null,null,null,"    MsgBox \"The right 5 characters are: \" & Right(s, 5)"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Getting the middle part of a string"]
[null,null,null,null,null,null,null,null,"Sub StringMiddle()"]
[null,null,"Here we find the middles characters in a string",null,null,null,null,null,"    Dim s"]
[null,null,"starting at the third character and up to and",null,null,null,null,null,"    s = \"ABCDEFGHIJKL\""]
[null,null,"including the seventh character.",null,null,null,null,null,"    MsgBox \"The 3rd through 7th characters are: \" & Mid(s, 3, 7 - 3 + 1)"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Joining strings together"]
[null,null,null,null,null,null,null,null,"Sub StringJoin()"]
[null,null,"In this example we concatenate - or join - two",null,null,null,null,null,"    Dim s: s = \"The cat \""]
[null,null,"strings.",null,null,null,null,null,"    Dim t: t = \" in the hat.\""]
[null,null,null,null,null,null,null,null,"    Dim j: j = s & t"]
[null,null,null,null,null,null,null,null,"    MsgBox \"The joined strings are \" & j"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Splitting strings"]
[null,null,null,null,null,null,null,null,"Sub StringSplit()"]
[null,null,"The split function is very useful in separating a",null,null,null,null,null,"    Dim s"]
[null,null,"string that contains markers or delimiters.",null,null,null,null,null,"    s = \"apple,grape,,lemon\""]
[null,null,null,null,null,null,null,null,"    Dim t() As String"]
[null,null,"In this example we use a delimiter of \",\" to split",null,null,null,null,null,"    t = Split(s, \",\")"]
[null,null,"a string into parts.",null,null,null,null,null,"    MsgBox \"First is \" & t(0)"]
[null,null,null,null,null,null,null,null,"    MsgBox \"Second is \" & t(1)"]
[null,null,"After that we use another delimiter \",,\" to split",null,null,null,null,null,"    MsgBox \"Third is \" & t(2)"]
[null,null,"the string a different way.",null,null,null,null,null,"    MsgBox \"Fourth is \" & t(3)"]
[null,null,null,null,null,null,null,null,"    t = Split(s, \",,\")"]
[null,null,"The split function splits the string into an array",null,null,null,null,null,"    MsgBox \"New first is \" & t(0)"]
[null,null,"of substrings.",null,null,null,null,null,"    MsgBox \"New second is \" & t(1)"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Finding the length of a string"]
[null,null,null,null,null,null,null,null,"Sub StringLength()"]
[null,null,"In this example we determine how many ",null,null,null,null,null,"    Dim s"]
[null,null,"characters there are in a string.",null,null,null,null,null,"    s = \"ABCDEFGHIJKL\""]
[null,null,null,null,null,null,null,null,"    MsgBox \"The length of the string is: \" & Len(s)"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Replacing one part of a string with another"]
[null,null,null,null,null,null,null,null,"Sub StringReplace()"]
[null,null,"You can use the Replace function to replace",null,null,null,null,null,"    Dim s"]
[null,null,"one part of a string with another.  Several",null,null,null,null,null,"    s = \"ABCDEFGHIJCDKL\""]
[null,null,"replacements are made if the string has several",null,null,null,null,null,"    MsgBox \"Original string is: \" & s"]
[null,null,"occurrences of the substring you want to replace.",null,null,null,null,null,"    Dim t"]
[null,null,null,null,null,null,null,null,"    t = Replace(s, \"CD\", \"ZZZ\")"]
[null,null,null,null,null,null,null,null,"    MsgBox \"New string is: \" & t"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Replacing one part of a string with another (but only once)"]
[null,null,null,null,null,null,null,null,"Sub StringReplaceOnce()"]
[null,null,"You can use the Replace function with four ",null,null,null,null,null,"    Dim s"]
[null,null,"arguments.  The last argument tells how many",null,null,null,null,null,"    s = \"ABCDEFGHIJCDKL\""]
[null,null,"replacements you want made.",null,null,null,null,null,"    MsgBox \"Original string is: \" & s"]
[null,null,null,null,null,null,null,null,"    Dim t"]
[null,null,"In this example we limit the number of ",null,null,null,null,null,"    t = Replace(s, \"CD\", \"ZZZ\", 1, 1)"]
[null,null,"replacements to one.",null,null,null,null,null,"    MsgBox \"New string is: \" & t"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Finding the position of a substring "]
[null,null,null,null,null,null,null,null,"Sub StringPosOfSubstring()"]
[null,null,"The InStr function lets us find the position",null,null,null,null,null,"    Dim s, k"]
[null,null,"of one string inside another.",null,null,null,null,null,"    s = \"ABCDEFGHIJCDKL\""]
[null,null,null,null,null,null,null,null,"    MsgBox \"String is : \" & s"]
[null,null,"In this example we find the position of CDE",null,null,null,null,null,"    k = InStr(1, s, \"CDE\")"]
[null,null,"in a larger string.",null,null,null,null,null,"    MsgBox \"CDE starts at character position: \" & k"]
[null,null,null,null,null,null,null,null,"End Sub"]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Extracting the left part of a string up to a marker"]
[null,null,null,null,null,null,null,null,"Sub StringLeftToMarker()"]
[null,null,"In this example a string has special markers: \"?\"",null,null,null,null,null,"    Dim s, k"]
[null,null,"We want to find the left part of the string up to,",null,null,null,null,null,"    s = \"AB?DEFG?IJCDKL\""]
[null,null,"but not including, the first marker.",null,null,null,null,null,"    MsgBox \"String is : \" & s"]
[null,null,null,null,null,null,null,null,"    k = InStr(1, s, \"?\")"]
[null,null,"This example shows how to do that.",null,null,null,null,null,"    MsgBox \"Marker is at character position: \" & k"]
[null,null,null,null,null,null,null,null,"    MsgBox \"Left part of string is: \" & Left(s, k - 1)"]
[null,null,null,null,null,null,null,null,"    MsgBox \"Rest of string is: \" & Right(s, Len(s) - k)"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Extracting the left part of a string up to a marker (another way)"]
[null,null,null,null,null,null,null,null,"Sub StringLeftToMarker2()"]
[null,null,"Another way of getting the left part of a string",null,null,null,null,null,"    Dim s, k"]
[null,null,"is to split it into a string array and then ",null,null,null,null,null,"    s = \"AB?DEFG?IJCDKL\""]
[null,null,"reference the first string.  This example shows",null,null,null,null,null,"    MsgBox \"String is : \" & s"]
[null,null,"how to do that.",null,null,null,null,null,"    MsgBox \"Left part of string is: \" & Split(s, \"?\")(0)"]
[null,null,null,null,null,null,null,null,"End Sub"]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Extracting the right part of a string back to a marker"]
[null,null,null,null,null,null,null,null,"Sub StringRightToMarker()"]
[null,null,"In this example we have a full pathname to a ",null,null,null,null,null,"    Dim s, k"]
[null,null,"workbook.  We want to extract the name of the",null,null,null,null,null,"    s = \"C:/Documents and Settings/JWhite/My documents/test.xls\""]
[null,null,"workbook.  To do that we need to find the rightmost",null,null,null,null,null,"    MsgBox \"String is : \" & s"]
[null,null,"part of the string - the part to the right of the last \"/\".",null,null,null,null,null,"    k = InStrRev(s, \"/\")"]
[null,null,null,null,null,null,null,null,"    MsgBox \"Marker is at character position: \" & k"]
[null,null,"This example shows how to do that by using the",null,null,null,null,null,"    MsgBox \"Right part of string is: \" & Right(s, Len(s) - k)"]
[null,null,"InStrRev function.",null,null,null,null,null,"End Sub"]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Extracting the right part of a string back to a marker (another way)"]
[]
[null,null,"This example shows another way of solving the",null,null,null,null,null,"Sub StringRightToMarker2()"]
[null,null,"preceding problem - by using the Split function.",null,null,null,null,null,"    Dim s, k"]
[null,null,null,null,null,null,null,null,"    s = \"C:/Documents and Settings/JWhite/My documents/test.xls\""]
[null,null,null,null,null,null,null,null,"    MsgBox \"String is : \" & s"]
[null,null,null,null,null,null,null,null,"    Dim t() As String: t = Split(s, \"/\")"]
[null,null,null,null,null,null,null,null,"    MsgBox \"String has \" & UBound(t) - LBound(t) + 1 & \" parts\""]
[null,null,null,null,null,null,null,null,"    MsgBox \"Right part of string is: \" & t(UBound(t))"]
[null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]

{"sheet":"WorksheetFunctions","index":14,"n_rows":94,"n_cols":13,"rows_kept":94,"cut":false,"hidden":false,"merged":[[14,2,14,12],[32,2,32,13],[40,2,40,13],[48,2,48,13],[2,2,5,10],[4,12,4,13],[26,2,26,13],[34,2,34,13],[24,2,24,13],[18,2,18,13],[16,2,16,13],[8,2,8,12],[9,2,9,12],[10,2,10,12],[11,2,11,12],[12,2,12,12],[13,2,13,12],[42,2,42,13],[50,2,50,13],[61,2,61,13],[77,2,77,13],[93,2,93,13],[63,2,63,13],[79,2,79,13]]}
[]
[null,null,{"$f":"label_overview & Index!C22","v":"Overview -  Worksheet functions"}]
[null,null,"Almost all of the the functions available in Excel are also useable from Visual Basic.  Suppose you want to look up an Excel table, for example.  In  Excel you might want to use the VLOOKUP function for this purpose.  In Visual Basic, also, you can use the Excel VLOOKUP function - but in a special way.  Examples of using Excel functions in Visual Basic are shown below.\n"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"www.tykoh.com"]
[]
[]
[null,null,"Index"]
[null,null,"Summing a range"]
[null,null,{"$f":"wf_largest","v":"Finding the largest in a range"}]
[null,null,{"$f":"wf_2nd_large","v":"Finding the second-largest in a range"}]
[null,null,{"$f":"wf_sd","v":"Finding the standard deviation"}]
[null,null,{"$f":"wf_lookup_t","v":"Looking up a table"}]
[null,null,{"$f":"wf_lf","v":"Looking up a table (fails)"}]
[null,null,{"$f":"wf_eh","v":"Looking up a table (with error handler)"}]
[]
[null,null,{"$f":"label_back_to_main_index","v":"Back to main index"}]
[]
[null,null,"Summing a range          <--- Click on a topic to scroll it to the top of the page"]
[null,null,null,null,null,null,"wf_1",10]
[null,null,"In this example we use the SUM",null,null,null,null,null,4.3,null,"Sub WF_Sum()"]
[null,null,"worksheet function to add all the cells",null,null,null,34.200000000000003,19,null,null,"    MsgBox \"Sum is \" & _"]
[null,null,"in a particular range.",null,null,null,null,null,null,null,"        Application.WorksheetFunction.Sum(Range(\"wf_1\"))"]
[null,null,null,null,null,null,null,null,null,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Finding the largest in a range"]
[null,null,null,null,null,null,"wf_2",1.2]
[null,null,"Here we use the MAX function to find",null,null,null,null,-99,null,null,"Sub WF_Largest()"]
[null,null,"the largest number in a range.",null,null,null,87,null,34.5,null,"    MsgBox \"Largest is \" & _"]
[null,null,null,null,null,null,null,-23,null,null,"        Application.WorksheetFunction.Max(Range(\"wf_2\"))"]
[null,null,null,null,null,null,404,null,995,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Finding the second-largest in a range"]
[null,null,null,null,null,null,"wf_3",1.2]
[null,null,"In this example we use the Excel",null,null,null,null,-99,null,null,"Sub WF_2nd_Largest()"]
[null,null,"LARGE function to get the second-",null,null,null,87,null,34.5,null,"    MsgBox \"2nd largest is \" & _"]
[null,null,"largest number in a range.",null,null,null,null,-23,null,null,"        Application.WorksheetFunction.Large(Range(\"wf_3\"), 2)"]
[null,null,null,null,null,null,404,null,995,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Finding the standard deviation"]
[null,null,null,null,null,null,"wf_4",1.2]
[null,null,"Here get use the STDEV function to",null,null,null,null,-99,null,null,"Sub WF_StdDev()"]
[null,null,"calculate the standard deviation of numbers",null,null,null,87,null,34.5,null,"    MsgBox \"Standard deviation is \" & _"]
[null,null,"in a range.",null,null,null,null,-23,null,null,"        Application.WorksheetFunction.StDev(Range(\"wf_4\"))"]
[null,null,null,null,null,null,404,null,995,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Looking up a table"]
[]
[null,null,null,null,null,null,null,"Mar",null,null,"Range name is wf_item"]
[null,null,"In this example we use the VLOOKUP function"]
[null,null,"to look up the sales for March in a table.",null,null,null,null,"Month","Sales ($M)"]
[null,null,null,null,null,null,null,"Jan",2.2999999999999998,null,"Sub WF_Lookup()"]
[null,null,"Range name is wf_lookup_table",null,null,null,null,"Feb",2.2000000000000002,null,"    MsgBox \"Sales for \" & Range(\"wf_item\") & \" are \" & _"]
[null,null,null,null,null,null,null,"Mar",2.5,null,"        Application.WorksheetFunction.VLookup( _"]
[null,null,null,null,null,null,null,"Apr",3.1,null,"            Range(\"wf_item\"), _"]
[null,null,null,null,null,null,null,"May",3.2,null,"            Range(\"wf_lookup_table\"), 2, False)"]
[null,null,null,null,null,null,null,"June",2.4,null,"End Sub"]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Looking up a table (fails)"]
[null,null,null,null,null,null,null,"July",null,null,"Range name is wf_item2"]
[null,null,"In this example the lookup fails because the item"]
[null,null,"we are looking for (July) isn't in the table.",null,null,null,null,"Month","Sales ($M)"]
[null,null,null,null,null,null,null,"Jan",2.2999999999999998,null,"Sub WF_Lookup_fails()"]
[null,null,"In that case Visual Basic will generate a run-time",null,null,null,null,"Feb",2.2000000000000002,null,"    MsgBox \"Sales for \" & Range(\"wf_item2\") & \" are \" & _"]
[null,null,"error.  Such errors can be disconcerting to users",null,null,null,null,"Mar",2.5,null,"        Application.WorksheetFunction.VLookup( _"]
[null,null,"and are often obscure and generalised.",null,null,null,null,"Apr",3.1,null,"            Range(\"wf_item2\"), _"]
[null,null,null,null,null,null,null,"May",3.2,null,"            Range(\"wf_lookup_table2\"), 2, False)"]
[null,null,null,null,null,null,null,"June",2.4,null,"End Sub"]
[]
[]
[null,null,null,null,null,null,null,null,null,null,"Range name is wf_lookup_table2"]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]
[]
[null,null,"Looking up a table (with error handler)"]
[null,null,null,null,null,null,null,null,null,"Range name is wf_item3"]
[null,null,"This example follows on from the previous one.",null,null,null,null,"July"]
[null,null,null,null,null,null,null,null,null,null,"Sub WF_Lookup_EH()"]
[null,null,"Here we define an error handler, catch the error",null,null,null,null,"Month","Sales ($M)",null,"    On Error GoTo err_EH"]
[null,null,"Visual Basic generates, and provide a more",null,null,null,null,"Jan",2.2999999999999998,null,"    MsgBox \"Sales for \" & Range(\"wf_item3\") & \" are \" & _"]
[null,null,"specific and user-friendly message to the user.",null,null,null,null,"Feb",2.2000000000000002,null,"        Application.WorksheetFunction.VLookup( _"]
[null,null,null,null,null,null,null,"Mar",2.5,null,"            Range(\"wf_item3\"), _"]
[null,null,null,null,null,null,null,"Apr",3.1,null,"            Range(\"wf_lookup_table3\"), 2, False)"]
[null,null,null,null,null,null,null,"May",3.2,null,"    Exit Sub"]
[null,null,null,null,null,null,null,"June",2.4,null,"err_EH:"]
[null,null,null,null,null,null,null,null,null,null,"    MsgBox Range(\"wf_item3\") & \" doesn't exist - can't look it up.\""]
[null,null,null,null,"Range name is wf_lookup_table3",null,null,null,null,null,"End Sub"]
[]
[null,null,{"$f":"label_back_to_index","v":"Back to this sheet's index"}]

{"sheet":"Versions","index":15,"n_rows":5,"n_cols":3,"rows_kept":5,"cut":false,"hidden":true,"merged":[]}
["Date","Version number","Change"]
["7-Apr-2009","1.0","First version"]
["6-May-2009","1.1","Added Selecting Chart Series example to Forms set.  Changed logo icon."]
["2009-05-16",1.2,"Added Processing a cell selection event (#2) to events set."]
["2009-06-12","1.3","Removed comments about getting full version"]

{"sheet":"Labels","index":16,"n_rows":5,"n_cols":1,"rows_kept":5,"cut":false,"hidden":true,"merged":[]}
["Back to main index"]
["Back to this sheet's index"]
["Overview -  "]
["<---- Click on a topic title to scroll it to the top of the page"]
["Enrol on our"]

{"sheet":"zz","index":17,"n_rows":1,"n_cols":1,"rows_kept":1,"cut":false,"hidden":true,"merged":[]}
[true]