{"text_format":"2","workbook":"xls","n_sheets":14,"sheets":["Surprise1","Surprise1_VHD","Introduct","Course1","Course2","Course3","Course4","Course5","Course6","Course7","Course8","Course9","Course10","Course11"],"not_rendered":{"charts":2,"images":48,"macros":1,"external_links":1,"defined_names":23,"comments":5,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"run_cost":"one","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","formulas":"cached_only"}

{"sheet":"Surprise1","index":0,"n_rows":27,"n_cols":10,"rows_kept":27,"cut":false,"hidden":true,"merged":[[8,2,8,6],[0,0,0,2],[0,3,0,6],[0,9,0,10],[2,0,2,5],[7,2,7,6],[17,2,17,6],[18,2,18,6],[19,2,19,6],[20,2,20,6],[9,2,9,6],[10,2,10,6],[11,2,11,6],[13,2,13,6],[14,2,14,6],[16,2,16,6]],"flags":["formulas_cached_only"]}
["MODULE 1",null,null,"MULTIPLICATION (1-10)",{"$nulls":4},"Difficulty","1900-01-01"]
[]
["Build a multiplication table (1-10)"]
[]
[null,null,"This exercice could be skipped if you are not a beginner with excel VBA"]
[]
[]
[null,"Objectifs","1. Basic usage of the VB editor"]
[null,null,"2. Record a macro"]
[null,null,"3. Understanding of a simple code"]
[null,null,"4. Writing a simple code"]
[null,null,"5. Basic notions: variables, modules"]
[]
[null,"Pre-requisites","1. Excel know-how"]
[null,null,"2. Notions of programing"]
[]
[null,"Main resources","Toogle VB editor",{"$nulls":4},"Alt+F11"]
[null,null,"Execute a code in step by step mode",{"$nulls":4},"F8"]
[null,null,"Execute a code up to the cursor",{"$nulls":4},"Ctrl + F8 "]
[null,null,"Run a procedure (subroutine)",{"$nulls":4},"F5"]
[null,null,"VBA Help",{"$nulls":4},"F1"]
[]
[null,"Documentation","(...)/Tools/Documentation/formationVBA.pdf (french)"]
[null,null,"Microsoft - VBA for Excel developers"]
[null,null,"cpearson Excel development"]
[]
[null,"Others resources"]

{"sheet":"Surprise1_VHD","index":1,"n_rows":27,"n_cols":10,"rows_kept":27,"cut":false,"hidden":true,"merged":[[8,2,8,6],[0,0,0,2],[0,3,0,6],[0,9,0,10],[2,0,2,5],[7,2,7,6],[17,2,17,6],[18,2,18,6],[19,2,19,6],[20,2,20,6],[9,2,9,6],[10,2,10,6],[11,2,11,6],[13,2,13,6],[14,2,14,6],[16,2,16,6]],"flags":["formulas_cached_only"]}
["MODULE 1",null,null,"MULTIPLICATION (1-10)",{"$nulls":4},"Difficulty","1900-01-01"]
[]
["Build a multiplication table (1-10)"]
[]
[null,null,"This exercice could be skipped if you are not a beginner with excel VBA"]
[]
[]
[null,"Objectifs","1. Basic usage of the VB editor"]
[null,null,"2. Record a macro"]
[null,null,"3. Understanding of a simple code"]
[null,null,"4. Writing a simple code"]
[null,null,"5. Basic notions: variables, modules"]
[]
[null,"Pre-requisites","1. Excel know-how"]
[null,null,"2. Notions of programing"]
[]
[null,"Main resources","Toogle VB editor",{"$nulls":4},"Alt+F11"]
[null,null,"Execute a code in step by step mode",{"$nulls":4},"F8"]
[null,null,"Execute a code up to the cursor",{"$nulls":4},"Ctrl + F8 "]
[null,null,"Run a procedure (subroutine)",{"$nulls":4},"F5"]
[null,null,"VBA Help",{"$nulls":4},"F1"]
[]
[null,"Documentation","(...)/Tools/Documentation/formationVBA.pdf (french)"]
[null,null,"Microsoft - VBA for Excel developers"]
[null,null,"cpearson Excel development"]
[]
[null,"Others resources"]

{"sheet":"Introduct","index":2,"n_rows":80,"n_cols":10,"rows_kept":80,"cut":false,"hidden":false,"merged":[[10,6,10,10],[24,0,24,4],[25,0,25,4],[37,0,37,3],[12,0,12,3],[21,0,21,3],[23,0,23,2],[30,0,30,3],[15,2,15,3],[14,2,14,5],[16,2,16,5],[17,2,17,5],[18,2,18,5],[19,2,19,5],[7,6,7,10],[8,6,8,10],[9,6,9,10],[1,0,1,1],[1,3,1,4],[1,6,1,7],[2,0,3,1],[2,3,2,4],[4,0,4,1],[4,2,4,4],[6,0,6,3],[6,6,6,9],[7,1,7,5],[8,1,8,5],[9,1,9,5]],"flags":["formulas_cached_only"]}
[{"$nulls":5},"File last save",41425,0.4138888888888889]
[null,null,"Category","EXCEL VBA Training",null,"By","Vincent BREMOND"]
[null,null,"Reference","Course 1",0]
[]
["Sheet subject",null,"INTRODUCTION"]
[]
["1. OBJECTIVES",{"$nulls":5},"2. PRE-REQUISITES"]
[null,"Excel Visual Basic for Application training – beginner to advanced",{"$nulls":4},"1. Excel know-how, medium"]
[null,"People working frequently with Excel and having some needs for:",{"$nulls":4},"2. Motivation and tolerancy (about me, yourself, …)"]
[null,"o Needs for optimization and/or automation",{"$nulls":4},"3. Willingness to progress, with personal work between each module"]
[null,"o willing to use VBA programing",{"$nulls":4},"4. Be aware that: there is no ONE solution "]
[]
["3. USEFUL DOCUMENTATION"]
[]
[null,5,"(...)/Tools/Documentation/formationVBA.pdf (french)"]
[null,4.5,"cpearson Excel development"]
[null,4,"http://chandoo.org/wp/"]
[null,4,"Excel-VBA-Tutorial.html"]
[null,2,"excel-vba-solutions-beginners.htm"]
[null,1,"Microsoft - VBA for Excel developers"]
[]
["4. ENVIRONMENT SETTINGS"]
[]
["Enabling the macros"]
["The macros have to be enabled under Excel (Developer -> Macro security). "]
["If the ‘Developer’ ribbon isn’t visible, \nenable it (excel options => customize ribbon)"]
[]
[]
[]
[]
["5. COURSES CONTENTS"]
[1,"MODULE 1","VBA Environment - introduction",null,1,6,"MODULE 6","Manipulating huge data's",null,5]
[2,"MODULE 2","Workbooks and worksheets",null,2,7,"MODULE 7","Calling the macros - Events",null,3]
[3,"MODULE 3","Subroutines and functions",null,3,8,"MODULE 8","Code optimization",null,5]
[4,"MODULE 4","Range and cells manipulation",null,5,9,"MODULE 9","Emailing functions",null,2]
[5,"MODULE 5","Workbooks and files",null,4,10,"MODULE 10","Interfacing with powerpaint",null,4]
[{"$nulls":5},11,"MODULE 11","Forms Basic (from PP)",null,2]
["6. THINK ABOUT IT…."]
[]
[null,"Try to minimize as much as possible the code part, and to maximize the usage of excel features …."]
[null,null,"=> it will drastically decrease the risk of errors!"]
[]
[null,"=> Scope of the macros could be:"]
[null,null,"1. Automate repetitive tasks"]
[null,null,"2. Automate complex tasks"]
[null,null,"3. Manipulate a large amound of data's (complex reports, filtering ….)"]
[null,null,"4. Protecting data's and integrity from users errors"]
[null,null,"5. When working on 'events' is necessary"]
[null,null,"6. Improving the GUI / access to commands"]
[null,null,"(….)"]
[]
[null,"So do not 're-invent the wheel: rule out if you really need to have a macro at all."]
[]
[null,"Don't forget that your best tool for programing is your …. Brain"]
[null,null,"=> it will drastically decrease the risk of errors!"]
[]
[null,"Always use the 'Option Explicit'"]
[]
[null,"Don't access sheets by their number nor their default name (I.E Sheet1): they could change"]
[]
[null,"Don't access ranges/cells by their address: a line could be inserted or deleted"]
[]
[null,"Comment your code"]
[]
[null,"Use explicit variables names"]
[]
[null,"Minimize the 'On error resume next' statement, don't use the 'On Error goto 0' statement"]
[]
[null,"Don't start to write code for 20 minutes, use \"Debug.Print\" instead of MsgBox function"]
[]
[null,"20/80 rule: achieving 80% of the result requires 20% of the developing effort (what we'll do together maybe …)"]
[null,"and …. 80% of the remaining effort is for: errors management, exceptions, unforcasted cases, adanced debug….."]
[]
[null,"There are a lot of traps …. Like writing the last modification date on a sheet when it has been modified "]
[]
[null,"Think one step forward : the code I'm writing could it be reused by others procedures?"]
[]
[null,"We are just opening some doors here, the next step is up to you"]
[]
[null,"Have fun and … some humility maybe"]

{"sheet":"Course1","index":3,"n_rows":159,"n_cols":10,"rows_kept":159,"cut":false,"hidden":false,"merged":[[147,3,147,4],[149,1,149,4],[93,3,93,4],[65,3,65,4],[117,3,117,4],[119,1,119,4],[127,3,127,4],[0,9,0,10],[10,2,10,6],[0,0,0,2],[0,3,0,6],[2,0,2,5],[7,2,7,6],[8,2,8,6],[9,2,9,6],[11,2,11,6],[57,1,57,4],[86,1,86,4],[36,1,36,5],[32,1,32,4],[76,3,76,4],[12,2,12,6],[55,5,55,7]],"flags":["formulas_cached_only"]}
["MODULE 1",null,null,"VBA Environment - introduction",{"$nulls":4},"Difficulty",1]
[]
["Build a multiplication table (1-10)"]
[]
[null,null,"This exercice could be skipped if you are not a beginner with excel VBA"]
[]
[]
[null,"Main resources","Toogle VB editor",{"$nulls":4},"Alt+F11"]
[null,null,"Execute a code in step by step mode",{"$nulls":4},"F8"]
[null,null,"Execute a code up to the cursor",{"$nulls":4},"Ctrl + F8 "]
[null,null,"Run a procedure (subroutine)",{"$nulls":4},"F5"]
[null,null,"VBA Help",{"$nulls":4},"F1"]
[null,null,"View Macros",{"$nulls":4},"ALT + F8"]
[]
[null,"Others resources"]
{"$rows_empty":17}
[1.1,"Exercice: using 'Record macro'"]
[]
[null,"Description"]
[null,"Start to record a macro called 'Recordmacro11'"]
[null,"Go to Worksheet 1 and clear all the cells"]
[null,"Starting from cells \"A1\":"]
[null,"Create a 10x10 tables using the 'fill mode' of excel, like"]
[]
[]
[]
[]
[null,"Create a row/column multiplaction table using excel formula"]
[null,"Apply \"Accent\" different styles on column 1 and row 1 of the table"]
[null,"Resize the columns of the table to 6"]
[null,"Result should be like:"]
[null,"Stop the record"]
[]
[null,"Go to VB editor"]
[null,"View the code, execute it in step by step mode"]
[null,"Change the module name created to 'Exercice1'"]
[null,"Run the procedure recorded"]
[]
[null,"How to "]
[null,"- Toggle the absolute/relative address in excel",null,null,null,"F4 Key  (formula bar)"]
[]
[1.2,"Exercice: write a simple procedure"]
[]
[null,"Description"]
[null,"Welcome the user by a Message"]
[null,"Same expected results as 1.1 exercice with the following modifications:"]
[null,"- code to be written"]
[]
[null,"How to "]
[null,"- Use of the 'For … to' statement like",null,"For to / next"]
{"$rows_empty":10}
[null,"- For the welcome message",null,"Msgbox function"]
{"$rows_empty":9}
[1.3,"Exercise: dim entered by the user"]
[]
[null,"Description"]
[null,"Same as 1.2 with the following modifcations:"]
[null,"The table dimensions are entered by the user, as an integer between 1 and 100"]
[]
[null,"How to "]
[null,"- For the table dimension:",null,"Inputbox function"]
{"$rows_empty":23}
[null,"- Concatenation",null,"String1 & String2"]
[]
[1.4,"Common error: where the code is running"]
[]
[null,"Description"]
[null,"Open the file (…)Samples\\Report_example.xlsx"]
[null,"Run the code 1.3 and see what happens"]
[null,"Correct the code to execute it from the workbook where the code is located"]
[]
[null,"How to "]
[null,"- Refer to the workbook where the code is",null,"ThisWorkbook"]
{"$rows_empty":19}
[null,"- Refer to another subroutine",null,"Call statement"]
[]
[1.5,"Common error: understanding Excel (!)"]
[]
[null,"Description"]
[null,"Take the result of the exercice 1.4"]
[null,"After user's confirmation, "]
[null,"Delete of the line 2, 4, 6, 8 …2n of the multiplication table"]
[null,"Repeat the deletion until …. "]
[]
[null,"Think about"]
[null,"What happens when you're deleting a line?"]

{"sheet":"Course2","index":4,"n_rows":97,"n_cols":10,"rows_kept":97,"cut":false,"hidden":false,"merged":[[0,9,0,10],[2,0,2,5],[6,2,6,6],[7,2,7,6],[54,1,54,4],[8,2,8,6],[9,2,9,6],[11,2,11,6],[0,0,0,2],[0,3,0,6],[14,1,14,4],[17,1,17,5],[12,2,12,6],[22,3,22,4],[24,3,24,4],[30,1,30,4],[37,3,37,4],[96,3,96,4],[62,3,62,4]],"flags":["formulas_cached_only"]}
["MODULE 2",null,null,"Workbooks and worksheets",{"$nulls":4},"Difficulty",2]
[]
["Get basic knowlodge about workbooks and worksheets"]
[]
[null,null,"This exercice could be skipped if you are familiar with workbooks and worksheets manipulation"]
[]
[null,"Objectifs","1. Notion of collection of workbooks and worksheets"]
[null,null,"2. Basic properties and methods"]
[null,null,"3. Manipulating some elements"]
[null,null,"4. Errors management"]
[]
[null,"Pre-requisites","1. Excel know-how"]
[null,null,"2. Module 1"]
[]
[2.1,"Exercice: count the number of sheets"]
[]
[null,"Description"]
[null,"For this workbook, count the worksheets and display:"]
[null,"- The name of this workbook"]
[null,"- The numnber of worksheets"]
[]
[null,"How to "]
[null,"- Get the name of a workbook",null,"Workbook.Name"]
[]
[null,"- Get worksheets information",null,"Worksheets collection"]
[]
[]
[]
[]
[]
[2.2,"Exercice: display basic worksheets information"]
[]
[null,"Description"]
[null,"For each worksheets in this workbook, display the following information's:"]
[null,"- Name, index, cells used (count), visibility"]
[]
[null,"How to "]
[null,"- Access to worksheets properties",null,"Worksheets object"]
{"$rows_empty":16}
[2.3,"Exercice: manipulation of worksheet"]
[]
[null,"Description"]
[null,"Set all the worksheets visibility to 'visible' in this workbook"]
[null,"Add a worksheet 'test' before the worksheet 'Surprise1_VHD'"]
[null,"Run the subroutine several times"]
[]
[null,"How to "]
[null,"- Manage Errors",null,"Err object - On Error"]
{"$rows_empty":33}
[null,"- Add a sheet",null,"Sheet add"]

{"sheet":"Course3","index":5,"n_rows":243,"n_cols":10,"rows_kept":243,"cut":false,"hidden":false,"merged":[[242,2,242,4],[0,0,0,2],[0,3,0,6],[0,9,0,10],[2,0,2,5],[5,2,5,6],[205,3,205,4],[7,2,7,6],[10,2,10,6],[151,1,151,4],[189,1,189,4],[196,1,196,4],[6,2,6,6],[50,2,50,6],[124,2,124,6]],"flags":["formulas_cached_only"]}
["MODULE 3",null,null,"Subroutines and functions",{"$nulls":4},"Difficulty",3]
[]
["Understanding procedures and functions, arguments and returned"]
[]
[]
[null,"Objectifs","1. Data type"]
[null,null,"2. Subroutines and functions, ByRef and ByVal arguments"]
[null,null,"3. Returned values"]
[null,null,"4. Introduction to range"]
[]
[null,"Pre-requisites","1. Notion of data type and declaration"]
{"$rows_empty":39}
[null,null,"2. Sub and Functions- procedure"]
{"$rows_empty":73}
[null,null,"3. Functions- returned value - arguments"]
{"$rows_empty":26}
[3.1,"Exercice: forcast about a sub and a function"]
[]
[null,"Description"]
[null,"Have a look on the code below"]
{"$rows_empty":26}
[null,"- What are the errors?"]
[null,"- Explain the result after the 2nd run as:"]
[]
[]
[]
[]
[null,"- Correct the errors"]
[]
[3.2,"Exercice: Euro converter"]
[]
[null,"Description"]
[null,"Create a function converting FF to Euro (and returning the result!)"]
[null,"Create a function converting FF to Euro or Euro to FF"]
[]
[]
[3.3,"Exercice: application to range"]
[]
[null,"Description"]
[null,"For the activesheet:"]
[null,"- get the range having numbers and set the background of each cell  to 'green'"]
[null,"- get the range having formulas and set the background of each cell  to 'red'"]
[null,"Run your code on some different sheets, using the Module 1 routines"]
[]
[null,"How to "]
[null,"- Access to special range",null,"UsedRange, Special cells",null,null,"This point will be developped later"]
{"$rows_empty":36}
[null,"- Set the background color","Range.Interior.Color = vbGreen"]

{"sheet":"Course4","index":6,"n_rows":115,"n_cols":10,"rows_kept":115,"cut":false,"hidden":false,"merged":[[99,3,99,4],[87,3,87,4],[114,3,114,4],[6,2,6,6],[9,2,9,6],[42,2,42,6],[48,1,48,5],[52,2,52,6],[65,1,65,4],[5,2,5,6],[0,0,0,2],[0,3,0,6],[0,9,0,10],[2,0,2,5],[4,2,4,6]],"flags":["formulas_cached_only"]}
["MODULE 4",null,null,"Range and cells manipulation",{"$nulls":4},"Difficulty",5]
[]
["To know hot to manipulate ranges and cells"]
[]
[null,"Objectifs","1. Understanding addresses"]
[null,null,"2. To be able to refer to cells and ranges"]
[null,null,"3. Understanding Absolute and relatives references"]
[null,null,"4. Saving ranges as named range"]
[]
[null,"Pre-requisites","1. Referring to cells and ranges"]
{"$rows_empty":32}
[null,null,"2. Module 1"]
[]
[]
[]
[]
[null,"Description"]
[null,"For this workbook, count the worksheets and display:"]
[null,"- The name of this workbook"]
[null,"- The numnber of worksheets"]
[]
[null,null,"2. Sub procedure"]
{"$rows_empty":12}
[4.1,"Manipulating range and cells"]
[]
[null,"Description"]
[null,"Write a procedure which:"]
[null,"- Run the exercice 1.3 Macro (with a table dimension of 20)"]
[null,"- Copy the result to Sheet2, range B5"]
[null,"- Display (debug mode) the result range address (all, one cell, one column), like"]
{"$rows_empty":10}
[null,"- For the non-header rows and columns, add a SUM of all the columns formula to the next column of the table "]
[null,"- Save the final table as a named range"]
[]
[null,"How to "]
[]
[null,"- Copy a range",null,"Range.Copy"]
{"$rows_empty":11}
[null,"- Access to address",null,"Range.Address"]
{"$rows_empty":14}
[null,"- Save a named range",null,"Names.add"]

{"sheet":"Course5","index":7,"n_rows":248,"n_cols":8,"rows_kept":248,"cut":false,"hidden":false,"merged":[[2,7,2,8],[2,0,2,5],[5,2,5,6],[6,2,6,6],[158,1,158,4],[12,1,12,3],[15,2,15,5],[7,2,7,6],[84,1,84,4],[36,2,36,5],[37,2,37,5],[38,2,38,5],[39,2,39,5],[27,2,27,5],[28,2,28,5],[32,2,32,5],[43,2,43,5],[0,0,0,2],[0,3,0,6],[26,2,26,5],[16,2,16,5],[17,2,17,5],[18,2,18,5],[19,2,19,5],[20,2,20,5],[21,2,21,5],[22,2,22,5],[23,2,23,5],[24,2,24,5],[25,2,25,5],[10,2,10,3],[66,2,66,3],[57,2,57,5],[58,2,58,5],[50,2,50,5],[51,2,51,5],[52,2,52,5],[53,2,53,5],[64,2,64,5],[62,2,62,5],[61,2,61,5],[44,2,44,5],[45,2,45,5],[46,2,46,5],[47,2,47,5],[48,2,48,5],[213,1,213,4],[186,2,186,4],[56,2,56,5],[31,2,31,5],[14,2,14,5],[71,1,71,2],[59,2,59,5],[60,2,60,5],[63,2,63,5],[33,2,33,5],[34,2,34,5],[35,2,35,5],[49,2,49,5],[40,2,40,5],[41,2,41,5],[42,2,42,5],[241,3,241,5],[242,3,242,5],[243,3,243,5],[244,3,244,5],[245,3,245,5],[246,3,246,5],[247,3,247,5],[240,3,240,5],[187,2,187,4],[188,2,188,4],[189,2,189,4],[190,2,190,4],[191,2,191,4],[192,2,192,4],[193,2,193,4],[194,2,194,4],[195,2,195,4],[196,2,196,4],[197,2,197,4],[198,2,198,4],[199,2,199,4]],"flags":["formulas_cached_only"]}
["MODULE 5",null,null,"Workbooks and files"]
[]
["Knowlodge about workbooks and files",{"$nulls":5},"Difficulty",4]
[]
[]
[null,"Objectifs","1. Workbooks collection"]
[null,null,"2. Workbook object"]
[null,null,"3. Working with array"]
[null,null,"4. Text and string manipulation"]
[]
[null,"Pre-requisites","1. Workbook object"]
[]
[null,"Workbook Object Members (extract)"]
[null,"Methods"]
[null,"Name","Description"]
[null,"AcceptAllChanges","Accepts all changes in the specified shared workbook."]
[null,"Activate","Activates the first window associated with the workbook."]
[null,"Close","Closes the object."]
[null,"ExclusiveAccess","Assigns the current user exclusive access to the workbook that's open as a shared list."]
[null,"HighlightChangesOptions","Controls how changes are shown in a shared workbook."]
[null,"Protect","Protects a workbook so that it cannot be modified."]
[null,"ProtectSharing","Saves the workbook and protects it for sharing."]
[null,"Save","Saves changes to the specified workbook."]
[null,"SaveAs","Saves changes to the workbook in a different file."]
[null,"SaveCopyAs","Saves a copy of the workbook to a file but doesn't modify the open workbook in memory."]
[null,"SendForReview","Sends a workbook in an e-mail message for review to the specified recipients."]
[null,"SendMail","Sends the workbook by using the installed mail system."]
[null,"Unprotect","Removes protection from a sheet or workbook. This method has no effect if the sheet or workbook isn't protected."]
[null,"UnprotectSharing","Turns off protection for sharing and saves the workbook."]
[]
[null,"Properties"]
[null,"Name","Description"]
[null,"BuiltinDocumentProperties","Returns a DocumentProperties collection that represents all the built-in document properties for the workbook. Read-only."]
[null,"FileFormat","Returns the file format and/or type of the workbook. Read-only XlFileFormat."]
[null,"FullName","Returns the name of the object, including its path on disk, as a string. Read-only String."]
[null,"FullNameURLEncoded","Returns a String indicating the name of the object, including its path on disk, as a string. Read-only."]
[null,"HasPassword","True if the workbook has a protection password. Read-only Boolean."]
[null,"HasVBProject","Returns a Boolean that represents whether a workbook has an attached Microsoft Visual Basic for Applications project. Read-only Boolean."]
[null,"HighlightChangesOnScreen","True if changes to the shared workbook are highlighted on-screen. Read/write Boolean."]
[null,"MultiUserEditing","True if the workbook is open as a shared list. Read-only Boolean."]
[null,"Name","Returns a String value that represents the name of the object."]
[null,"Names","Returns a Names collection that represents all the names in the specified workbook (including all worksheet-specific names). Read-only Names object."]
[null,"Password","Returns or sets the password that must be supplied to open the specified workbook. Read/write String."]
[null,"Path","Returns a String that represents the complete path to the workbook/file that this workbook object respresents."]
[null,"ReadOnly","Returns True if the object has been opened as read-only. Read-only Boolean."]
[null,"ReadOnlyRecommended","True if the workbook was saved as read-only recommended. Read-only Boolean."]
[null,"Saved","True if no changes have been made to the specified workbook since it was last saved. Read/write Boolean."]
[null,"SharedWorkspace","Returns a SharedWorkspace object that represents the Document Workspace in which a specified document is located. Read-only."]
[null,"Sheets","Returns a Sheets collection that represents all the sheets in the specified workbook. Read-only Sheets object."]
[null,"UserStatus","Returns a 1-based, two-dimensional array that provides information about each user who has the workbook open as a shared list. Read-only Variant."]
[null,"Worksheets","Returns a Sheets collection that represents all the worksheets in the specified workbook. Read-only Sheets object."]
[null,"WritePassword","Returns or sets a String for the write password of a workbook. Read/write."]
[null,"WriteReserved","True if the workbook is write-reserved. Read-only Boolean."]
[null,"WriteReservedBy","Returns the name of the user who currently has write permission for the workbook. Read-only String."]
[]
[null,"Events"]
[null,"Name","Description"]
[null,"Activate","Occurs when a workbook, worksheet, chart sheet, or embedded chart is activated."]
[null,"AfterSave","Occurs after the workbook is saved."]
[null,"BeforeClose","Occurs before the workbook closes. If the workbook has been changed, this event occurs before the user is asked to save changes."]
[null,"BeforeSave","Occurs before the workbook is saved."]
[null,"Open","Occurs when the workbook is opened."]
[null,"SheetActivate","Occurs when any sheet is activated."]
[null,"SheetDeactivate","Occurs when any sheet is deactivated."]
[null,"SheetSelectionChange","Occurs when the selection changes on any worksheet (doesn't occur if the selection is on a chart sheet)."]
[]
[null,null,"2. Document propeties (extract)"]
[null,"Syntax"]
[null,"workbook.BuiltinDocumentProperties"]
[null,"This property returns the entire collection of built-in document properties. "]
[null,"You can refer to document properties either by index value or by name. The following list shows the available built-in document property names:"]
[null,"BuiltinDocumentProperties (extract)"]
[null,"Name","Item"]
[null,"Title",1]
[null,"Subject",2]
[null,"Author",3]
[null,"Last Author",7]
[null,"Revision Number",8]
[null,"Creation Date",11]
[null,"Last Save Time",12]
[]
[null,"Because the Item method is the default method for the DocumentProperties collection, the following statements are identical:"]
[null,"BuiltinDocumentProperties.Item(1)",null,"BuiltinDocumentProperties(1)",null,"BuiltinDocumentProperties(\"Title\")"]
[]
[5.1,"Exercice: get open workbooks information"]
[]
[null,"Description"]
[null,"For all the workbooks open (your session), return a table with the following properties / elements:"]
[null,"- All the properties highlighted in 'blue' (pre-requisite 1)"]
[null,"- All the BuiltinDocumentProperties  highlighted in 'blue' (pre-requisite 2)"]
[null,"Display the information to the user (Message)"]
[null,"Print the result on a sheet in this workbbok"]
[null,"Run the code after opening several excel files"]
[]
[null,"How to "]
[null,"- Access to workbooks collection",null,"Workbooks.Count"]
[null,null,null,"Workbooks(item)"]
[null,null,null,"Workbooks(Name)"]
[]
[null,"- Redimensioning an array",null,"ReDim Statement"]
{"$rows_empty":19}
[null,"- Get the boundaries for an Array",null,"Lbound - Ubound function"]
{"$rows_empty":17}
[null,"- Test if the variable is an array",null,"IsArray(varname)"]
[]
[null,"- Perform several statements on an object",null,"With statement"]
{"$rows_empty":18}
[5.2,"Exercice: check if a workbook is open"]
[]
[null,"Description"]
[null,"Write a function testing if a workbook is already open, testing by its name (radical.extension) or its radical"]
[null,"The function should return following elements:"]
[null,"- A true/false flag (True if the file is open)"]
[null,"- A warning message if the workbook is open with another extension"]
[null,"- The path where the workbook is located"]
[null,"Test the function  by a procedure reading the inputs from the following range (saved as lst_wbk range name)"]
[null,"having errors (to be managed ….)"]
[]
[null,"File name"]
[null,"Excel_VBA_Training1.xlsm"]
[null,"Excel_VBA_Training1.xls"]
[null,"Report_example.xlsx"]
[null,true]
[null,"Book1"]
[null,"Excel_VBA_Training1 "]
[null,"excel_vba_training1.xlsm.xlsx"]
[null,"Report example.xlsx"]
[]
[]
[null,"Think how you could reuse the work done for the exercise 5.1"]
[]
[null,"How to "]
[]
[null,"- Manipulate strings"]
[null,null,null,"Text functions (extract)"]
[null,"Action","Function"]
[null,"Compare two strings.","StrComp"]
[null,"Convert strings.","StrConv"]
[null,"Reverse a string.","InStrRev, StrReverse"]
[null,"Convert to lowercase / uppercase.","Format, LCase, UCase"]
[null,"Create a string of repeating characters.","Space, StrDup, String"]
[null,"Find the length of a string.","Len"]
[null,"Format a string.","Format,FormatDateTime, FormatNumber, …."]
[null,"Manipulate strings.","InStr, Left, LTrim, Mid, Right, RTrim, Trim"]
[null,"Set string comparison rules.","Option Compare"]
[null,"Work with ASCII and ANSI values.","Asc, AscW, Chr, ChrW"]
[null,"Replace a specified substring.","Replace"]
[null,"Return a filter-based string array.","Filter"]
[null,"Return a specified number of substrings.","Split, Join"]
{"$rows_empty":13}
[5.3,"Exercice: check if a workbook specified by its full name is open"]
[]
[null,"Description"]
[null,"Write a function testing if a file specified by its full name is open, open it if not, in readonly mode"]
[null,"Test the function  by a procedure reading the inputs from the following range (saved as lst_wbk_full range name)"]
[null,"Adapt the inputs below to have a file from a collab, another from a local disk"]
[]
[null,"File full name"]
[null,"http://collab.intranet.stericsson.com/area3/DSE_test_product_engineering/InnovationCollaboration/Tools/Training/Excel_VBA_Training1_regist.xlsx"]
[null,"C:\\Documents and Settings\\bremondv\\my documents\\Excel\\Training VBA\\Samples\\Report_example.xlsx"]
[null,"C:\\Documents and Settings\\bremondv\\my documents\\Excel\\Training VBA\\Courses\\Excel_VBA_Training1.xlsm"]
[]
[null,"How to "]
[]
[null,"- Find a worksheet where a range is defined",null,"Range.Worksheet.Name"]
[]
[null,"- Open a workbook",null,"Workbooks.Open Method"]
{"$rows_empty":8}
[null,"Workbooks.Open Method"]
[null,"Parameters (extract)"]
[null,"Name","Data Type","Description"]
[null,"FileName","Variant\nString","The file name of the workbook to be opened."]
[null,"UpdateLinks","Variant","Specifies the way external references (links) are updated."]
[null,"ReadOnly","Variant","True to open the workbook in read-only mode."]
[null,"Password","Variant","password required to open a protected workbook."]
[null,"WriteResPassword","Variant","password required to write to a write-reserved workbook. "]
[null,"IgnoreReadOnlyRecommended","Variant","True to have Microsoft Excel not display the read-only recommended message (if the workbook was saved with the Read-Only Recommended option)."]
[null,"AddToMru","Variant","True to add this workbook to the list of recently used files. The default value is False."]

{"sheet":"Course6","index":8,"n_rows":153,"n_cols":9,"rows_kept":153,"cut":false,"hidden":false,"merged":[[147,1,147,4],[0,8,0,9],[2,0,2,5],[5,2,5,6],[6,2,6,6],[95,1,95,4],[7,2,7,6],[9,2,9,3],[0,0,0,2],[0,3,0,6],[31,3,31,4],[34,3,34,4],[141,3,141,4],[45,3,45,4],[59,3,59,4]],"flags":["formulas_cached_only"]}
["MODULE 6",null,null,"Manipulating huge data's",null,null,null,"Difficulty",5]
[]
["Introducing data's management and methods"]
[]
[]
[null,"Objectifs","1. Working with advanced range method"]
[null,null,"2. Retrieving the data's and copy"]
[null,null,"3. Usage of the specialcells and current region"]
[]
[null,"Pre-requisites","Data's range object"]
[]
[null,"Playing with the range object is a very powerfull way of making some complex data's selection, filtering, partial copy, …."]
[]
[null,"It could be a nightmare if you're for exemple using:"]
[null,"For each cl in worksheets(1).Cells"]
[]
[null,"Excel VBA has powerful objects and methods to really increase the efficiency when working with ranges:"]
[null,"1. 'currentRegion' method allow you to select only visible cells in a range"]
[null,"2. 'SpecialCells'  method allow you to select only visible cells by category in a range"]
{"$rows_empty":12}
[null,"- Work with special cells in a range",null,"Range.specialCells Method",null,"See course 3"]
[]
[]
[null,null,null,"Excel view"]
{"$rows_empty":10}
[null,"- Remove duplicate",null,"Range.RemoveDuplicates"]
{"$rows_empty":13}
[null,"- Autofilter a range",null,"Range.AutoFilter "]
{"$rows_empty":35}
[6.1,"Exercice: get the data's from an external file"]
[]
[null,"Description"]
[null,"Open the (…)\\Training VBA\\Samples\\Report_example.xlsx file provided, (possibly using the code from Course5)"]
[null,"Write a procedure:"]
[null,"- finding the range of the data's on the first worksheet (excluding blank lines, tittles, labels, ….)"]
[null,"- copying it in this workbook, in a sheet 'Tmp_data' (create it if not existing)"]
[null,"- closing the source file, without saving it even if modified"]
[null,"Note any strange behavior !"]
[]
[null,"How to "]
[null,"Find the data's in a worksheet"]
[null,"(assuming you don't know their position)"]
{"$rows_empty":33}
[null,"- Close a workbook, without alert (changed)",null,"Workbooks.Close Method"]
[]
[]
[]
[]
[]
[6.2,"Exercice: filtering the data's"]
[]
[null,"Description"]
[null,"On the same external file (see 6.1), propose a solution to get:"]
[null,"- all the records mathing the criteria 'CP Maturity = 30',"]
[null,"- for one planner"]

{"sheet":"Course7","index":9,"n_rows":147,"n_cols":8,"rows_kept":147,"cut":false,"hidden":false,"merged":[[0,0,0,2],[0,3,0,6],[115,3,115,6],[127,3,127,6],[133,3,133,6],[100,2,100,3],[105,1,105,4],[109,2,109,3],[46,1,46,5],[2,0,2,5],[5,2,5,6],[6,2,6,6],[96,1,96,4],[7,2,7,6],[11,2,11,3],[136,1,136,4],[139,1,139,3]],"flags":["formulas_cached_only"]}
["MODULE 7",null,null,"Calling the macros - Events"]
[]
["How to run a macro",{"$nulls":5},"Difficulty",3]
[]
[]
[null,"Objectifs","1. Calling a macro"]
[null,null,"2. Graphical interface"]
[null,null,"3. Custom menu"]
[null,null,"4. Notion of events"]
[null,"Pre-requisites"]
[]
[null,"1. Calling a macro","ALT + F8"]
{"$rows_empty":13}
[null,"2. Through a 'BUTTON'"]
{"$rows_empty":18}
[null,"2. Through the Quick Access Toolbar"]
[]
[null,"   Be careful: customize the THIS FILE ONLY!"]
{"$rows_empty":34}
[null,"3. Through a custom menu"]
[]
[null,"TAB_CSTMENU","Level","Caption","Position/Macro","Divider","FaceID"]
[null,null,1,"&TRAINING",10]
[null,null,2,"COURSE1"]
[null,null,3,"Exerc1","Sol_exerc11"]
[null,null,3,"Exerc2","Sol_exerc12"]
[null,null,2,"COURSE2"]
[null,null,3,"Exerc1","Sol_exerc21",true]
[null,null,3,"Exerc2","Sol_exerc22"]
[null,null,2,"ABOUT INFO","about_info",true]
[null,null,2,"CUSTOM MENUS"]
[null,null,3,"Build menu","Create_cst_Add_menu"]
[null,null,3,"Delete menu","Delete_cst_Add_menu"]
[]
[7.1,"Exercice: run a macro using different methods"]
[]
[null,"Description"]
[null,"Play with the different methods described"]
[null,"Run the macro ","Create_cst_Add_menu"]
[null,"Check the 'Add-Ins' menu bar"]
[null,"Add one of your macro in the \"TAB_CSTMENU\" range"]
[null,"Recompile the menu"]
[]
[7.2,"Exercice: working with events"]
[]
[null,"Description"]
[null,"Based on the events (see Course 5), create subroutines:"]
[null," - which Automatically runs the macro","Create_cst_Add_menu"]
[null,"when the workbook is open"]
[null," - which Automatically delete this custom menu when the workbook is closed"]
[]
[null,"How to "]
[]
[null,"- Detect if a workbbok get open / close",null,"Workbook_Open Workbook_BeforeClose Event"]
{"$rows_empty":11}
[null,"- Detect a change on a worksheet",null,"Workbook_SheetSelectionChange event"]
[]
[]
[]
[]
[]
[null,"- Enable / disable the events",null,"Application.EnableEvents = True - False\nActiveWorkbook.Save\nApplication.EnableEvents = True / False"]
[]
[]
[7.3,"Exercice: working with events"]
[]
[]
[null,"SAVE YOUR WORK BEFORE - DANGER"]
[]
[null,"Description"]
[null,"Based on the events (see Course 5), create a subroutine:"]
[null," - adding the user name, date and time on this sheet when it:"]
[null,"   => has been modified"]
[null,"   => is deactivated"]
[null," Report the information in this sheet, range I3, J3"]

{"sheet":"Course8","index":10,"n_rows":125,"n_cols":9,"rows_kept":125,"cut":false,"hidden":false,"merged":[[43,2,43,9],[41,2,41,9],[36,2,36,9],[35,2,35,9],[37,2,37,9],[38,2,38,9],[42,2,42,9],[32,1,32,9],[12,1,12,4],[7,2,7,6],[9,2,9,3],[31,3,31,4],[6,2,6,6],[0,0,0,2],[0,3,0,6],[0,8,0,9],[2,0,2,5],[5,2,5,6],[124,3,124,5],[44,2,44,9],[45,2,45,9],[46,2,46,9],[47,2,47,9],[70,1,70,4],[103,1,103,4],[48,2,48,9],[49,2,49,9],[50,2,50,9],[116,3,116,5],[51,2,51,9]],"flags":["formulas_cached_only"]}
["MODULE 8",null,null,"Code optimization",null,null,null,"Difficulty",5]
[]
["Some elements of code optimization when manipulating huge data's"]
[]
[]
[null,"Objectifs","1. Application of all the previous courses"]
[null,null,"2. How to speed the code execution"]
[null,null,"3. Manipulating time, data's validation"]
[]
[null,"Pre-requisites","Previous courses"]
[]
[]
[8.1,"Exercice: selecting the data's to import"]
[]
[null,"Description"]
[null,"Similar to exercice 6.1 with the following modifications:"]
[null,"- Get the header titles and report them in the 'Tmp_data2' worksheet (create it if not existing)"]
[null,"- Add a criteria about exporting the data's (True, false)"]
[null,"- Save the range of this header as a named range Head_FILENAME (radical)"]
[null,"=> This will allow us to extract selected records"]
[]
[null,"The result should looks like"]
{"$rows_empty":7}
[null,"How to "]
[]
[null,"- Add data's validation criteria",null,"Validation object"]
[null,"Validation Object Members (extract)"]
[null,"Represents data validation for a worksheet range."]
[null,"Methods"]
[null,"Name","Description"]
[null,"Add","Adds data validation to the specified range."]
[null,"Delete","Deletes the object."]
[null,"Modify","Modifies data validation for a range."]
[]
[null,"Properties"]
[null,"Name","Description"]
[null,"ErrorMessage","Returns or sets the data validation error message. Read/write String."]
[null,"ErrorTitle","Returns or sets the title of the data-validation error dialog box. Read/write String."]
[null,"IgnoreBlank","True if blank values are permitted by the range data validation. Read/write Boolean."]
[null,"InCellDropdown","True if data validation displays a drop-down list that contains acceptable values. Read/write Boolean."]
[null,"InputMessage","Returns or sets the data validation input message. Read/write String."]
[null,"InputTitle","Returns or sets the title of the data-validation input dialog box. Read/write String."]
[null,"ShowError","True if the data validation error message will be displayed whenever the user enters invalid data. Read/write Boolean."]
[null,"ShowInput","True if the data validation input message will be displayed whenever the user selects a cell in the data validation range. Read/write Boolean."]
[null,"Type","Returns a Long value, containing a XlDVType constant, that represents the data type validation for a range."]
[null,"Value","Returns a Boolean value that indicates if all the validation criteria are met (that is, if the range contains valid data)."]
{"$rows_empty":18}
[8.2,"Exercice: Extracting the selected fields"]
[]
[null,"Description"]
[null,"Then, extract all the data's from the Report_example file, except for the one set to 'FALSE' "]
[null,"- copy them in the same 'Tmp_data2' Worksheet, below the header"]
[null,"- Save the range of the data's as a named range DATA_FILENAME (radical)"]
[]
[null,"The data's to be extracted are:",null,"BU code"]
[null,null,null,"Pline code"]
[null,null,null,"CP"]
[null,null,null,"CP Mat"]
[null,null,null,"CP Mat upd"]
[null,null,null,"Planner"]
[null,null,null,"TF PE"]
[null,null,null,"EWS PE"]
[null,null,null,"Pre-delete"]
[]
[null,"The result should looks like"]
{"$rows_empty":15}
[8.3,"Exercice: Filtering the data's"]
[]
[null,"Description"]
[null,"From exercise 8.2, but with all the records selected (Header set to TRUE), "]
[null,"Get the unique records of product line code, CP, Planner, TF-PE and EWS PE having:"]
[null,"- a \"Pre-delete' Flag set to TRUE"]
[null,"- for CP maturity >= 20"]
[null,"Put the result on a sheet \"Tmp_data3\""]
[null,"Get the processing time for this exercice with Application.ScreenUpdating set to True then False"]
[null,"Note any strange behavior like unexpected result"]
[]
[null,"How to "]
[]
[null,"- Set the refresh of the screen on / off",null,"Application.ScreenUpdating"]
{"$rows_empty":7}
[null,"- Get the unique records from a range",null,"Range.RemoveDuplicates Method"]

{"sheet":"Course9","index":11,"n_rows":45,"n_cols":8,"rows_kept":45,"cut":false,"hidden":false,"merged":[[7,2,7,5],[9,2,9,3],[12,1,12,4],[37,2,37,3],[39,1,39,4],[6,2,6,5],[0,0,0,2],[0,3,0,5],[0,7,0,8],[2,0,2,5],[5,2,5,5]],"flags":["formulas_cached_only"]}
["MODULE 9",null,null,"Emailing functions",null,null,"Difficulty","1900-01-02"]
[]
["How to email a workbook"]
[]
[]
[null,"Objectifs","1. Managing the distribution list"]
[null,null,"2. Starting outlook application thru VBA"]
[null,null,"3. Managing the attachment"]
[]
[null,"Pre-requisites","Previous courses"]
[]
[]
["1900-01-09T02:24:00","Inputs under excel: email type, list and attach file option"]
[]
[null,"Description"]
[null,"Let's create 2 lists (Email type, to and cclist) and a 'attach file' option, defined as named ranges (T_EMAILTYP, T_EMAILST, ….)"]
[]
[null,null,"VALUES","INDEX 1"]
[null,"T_EMAILTYP",1,"@stericsson.com"]
[null,null,2,"@st.com"]
[null,null,3,"@user to be defined"]
[]
[]
[null,null,"Model","Names","Email address","TO/CC","Full address"]
[null,"T_EMAILLST",1,"Jean-Paul GUERITTEE","jean-paul.guerittee@stericsson.com","TO","Jean-Paul GUERITTEE <jean-paul.guerittee@stericsson.com>"]
[null,null,1,"Herve GIRODON","herve.girodon@stericsson.com","TO","Herve GIRODON <herve.girodon@stericsson.com>"]
[null,null,1,"Philippe PASCAL","philippe.pascal@stericsson.com","TO","Philippe PASCAL <philippe.pascal@stericsson.com>"]
[null,null,1,"Carine MOUNET","carine.mounet@stericsson.com","TO","Carine MOUNET <carine.mounet@stericsson.com>"]
[null,null,1,"Fabrice LENGRAND","fabrice.lengrand@stericsson.com","TO","Fabrice LENGRAND <fabrice.lengrand@stericsson.com>"]
[null,null,1,"Philippe PLANELLE","philippe.planelle@stericsson.com","TO","Philippe PLANELLE <philippe.planelle@stericsson.com>"]
[null,null,1,"Jean-marc ESCHASSERIAUX","jean-marc.eschasseriaux@stericsson.com","TO","Jean-marc ESCHASSERIAUX <jean-marc.eschasseriaux@stericsson.com>"]
[null,null,1,"Vincent BREMOND","vincent.bremond@stericsson.com","CC","Vincent BREMOND <vincent.bremond@stericsson.com>"]
[null,null,1,"Henry GUYOT","henry.guyot@stericsson.com","CC","Henry GUYOT <henry.guyot@stericsson.com>"]
[null,null,1,"Herve GIRODON","herve.girodon@stericsson.com","CC","Herve GIRODON <herve.girodon@stericsson.com>"]
[null,null,1,"Vincent BREMOND","vincent.bremond@stericsson.com","TO","Vincent BREMOND <vincent.bremond@stericsson.com>"]
[{"$nulls":4},"Undef",null,""]
[]
[null,"T_ATTACHF","Attach file to email",null,true]
[]
["1900-01-09T04:48:00","Get the TOLIST and CCLIST for further usage"]
[]
[null,"Description"]
[null,"We'll build the TOLIST and CCLIST based on the reading of the Range(\"T_EMAILLST\")"]
[null,"There are many ways of doing it (concatenation, array), but we'll use a collection in this example"]
[null,"A dupplicate name detection (between Tolist and Cclist) has been added"]

{"sheet":"Course10","index":12,"n_rows":70,"n_cols":8,"rows_kept":70,"cut":false,"hidden":false,"merged":[[0,7,0,8],[2,0,2,5],[5,2,5,5],[6,2,6,5],[57,1,57,4],[7,2,7,5],[9,2,9,3],[12,1,12,4],[65,1,65,4],[62,2,62,3],[63,2,63,3],[0,0,0,2],[0,3,0,5],[59,2,59,3],[60,2,60,3],[61,2,61,3]],"flags":["formulas_cached_only"]}
["MODULE 10",null,null,"Interfacing with powerpaint",null,null,"Difficulty",4]
[]
["How to send excel data's to Powerpaint"]
[]
[]
[null,"Objectifs","1. Managing the powerpaint application via VBA"]
[null,null,"2. Existing / New presentation"]
[null,null,"3. Managing the elements to send"]
[]
[null,"Pre-requisites","Previous courses"]
[]
[]
["1900-01-09T02:24:00","Test case - example: let's create some data's to be sent"]
[]
[null,"Description","Here are some excel elements we would like to send to powerpaint"]
[]
[null,"PPT_Rng1","BU code","Pline code","CP","CP Mat","Lst6M bill qty","FG WIP Qty"]
[null,"(data's range)","N8","AB3100","AF-AB3100-DI-SAG","1900-01-15",96000,""]
[null,null,"N8","AB3100","AF-AB3100-DI-SAM","1900-01-15",18000,""]
[null,null,"N8","AB3100","AF-AB3100-DI-SHP","1900-01-15",627000,""]
[null,null,"N8","TC2811","AB8500-MB14O04","1900-01-30",800,""]
[null,null,"N8","TC2811","AB8500-MB1TO04","1900-01-30",8750,""]
[null,null,"N8","TC2811","AB8500-MB1TSA4","1900-01-30",5724250,58835]
[null,null,"N8","TC3611","PM2101AST","1900-01-29",1006,""]
[null,null,"N8","TC3611","PM2301AST","1900-01-29",80332,""]
[null,null,"N8","TC6011","AB8505-CB1TH/3","1900-01-15",12500,""]
[null,null,"N8","TC6011","AB8505-DB1TH43","1900-01-21",149800,31888]
[null,null,"N8","TC6011","AB8505-DB1TH53","1900-01-29",5282500,223358]
[null,null,"N8","TC6011","AB8505-DB1TH53","1900-01-29",5912200,43073]
[]
[null,"Some graphs too"]
{"$rows_empty":15}
[null,"And a pivot table","Row Labels","CP Mat","Sum of Lst6M bill qty","Sum of FG WIP Qty"]
[null,null,"AB3100",15,741000,0]
[null,null,"TC2811",30,5733800,58835]
[null,null,"TC3611",29,81338,0]
[null,null,"TC6011",15,12500,0]
[null,null,null,21,149800,31888]
[null,null,null,29,11194700,266431]
[null,null,"Grand Total",null,17913138,357154]
[]
[]
[]
["1900-01-09T04:48:00","Let's create the environment: what to export and how"]
[{"$nulls":5},"Name contening"]
[null,"PPT_EXPORT","Include data's range",null,true,"PPT_Rng"]
[null,null,"Include pivot tables",null,true]
[null,null,"Include graphs",null,true]
[null,null,"Insert as hyperlink",null,false]
[null,null,"Copy as picture",null,true]
[]
["1900-01-09T07:12:00","Set the powerpoint object libray"]
[]
[null,"Description"]
[null,"To allow VBA to dialog and drive powerppoint, the reference to the Object libray must be active"]
[null,"If not, your have to activate it thru Tools menu => References"]

{"sheet":"Course11","index":13,"n_rows":128,"n_cols":10,"rows_kept":128,"cut":false,"hidden":false,"merged":[[9,2,9,6],[0,0,0,2],[0,3,0,6],[0,9,0,10],[2,0,2,5],[6,2,6,8],[119,1,119,5],[10,2,10,6],[11,2,11,6],[12,2,12,6],[13,2,13,6],[14,2,14,6],[16,1,16,4],[19,1,19,3],[20,1,20,5],[34,1,34,4],[49,1,49,5],[88,1,88,6]],"flags":["formulas_cached_only"]}
["MODULE 11",null,null,"Forms Basic (from PP)",{"$nulls":4},"Difficulty",2]
[]
["Build a euro/francs converter using a simple form"]
[]
[null,null,"This exercice could be skipped if you are not a beginner with excel/VBA forms"]
[]
[null,null,"The goal of this exercise is to create a simple form which :\n1) takes data from an input field\n2) write data inside a dialog box\n3) exit from the tool\n4) get basic understanding of events linked to these boxes\n5) get basic understanding how to create the form\n"]
[]
[]
[null,"Main resources","Create a simple form like :"]
[null,null,"input a parameter from a dialog box"]
[null,null,"Write a result the other box"]
[null,null,"Exit the tool"]
[]
[]
[]
["1900-01-01T02:24:00","Step 1 : creating the form"]
[]
[null,"Description"]
[null,"Insert a new UserForm with the Insert button"]
[null,"Note the Empty Userform with its properties and the Userform menu created"]
[]
[]
[]
[]
[]
[]
[null,"Change the Name of the user form"]
[]
[null,"Change the content of the caption"]
[]
[]
[]
[]
["1900-01-01T04:48:00","Step 2 : adding buttons, Textbox etc"]
[]
[null,"Description"]
[]
[]
[null,"Add labels and then modify their caption & name"]
[]
[null,"Add TextBox and then modify their caption & name"]
[]
[null,"Add CommandButtons and then modify their caption & name"]
[]
[]
[]
[]
[]
["1900-01-01T07:12:00","Step 3 : accessing to the VBA code behind & backwards"]
[]
[null,"Double clicking on the Form (graphical side) directs you to the code"]
{"$rows_empty":22}
[null,"double clicking the Form (VBA Project side) directs you to the graphical side"]
{"$rows_empty":13}
["1900-01-01T09:36:00","Step 4 : Creating the code behind the Commandbutton or the Textbox"]
[]
[]
[null,"Double Clicking on a Commandbutton or a TextBox creates automatically the VBA empty routine named as : \"ButtonName\"_\"event\""]
{"$rows_empty":19}
[null,"Events can be changed : use top right menu for this"]
{"$rows_empty":7}
["1900-01-01T12:00:00","Step 5 : opening and closing automatically the Form"]
[]
[null,"Other commands allow to open automotically the form "]
[null,"That one must be located inside ThisWorkbook"]
[]
[]
[]
[]
[null,"or to close it "]