{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["Úvod","Zrychlování","Definování","Pro testy","List1","List2","Poznámky"],"not_rendered":{"charts":0,"images":6,"macros":1,"external_links":0,"defined_names":0,"comments":0,"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":"Úvod","index":0,"n_rows":25,"n_cols":8,"rows_kept":25,"cut":false,"hidden":false,"merged":[[24,1,24,11],[1,2,1,9],[10,2,11,6],[13,3,14,6],[14,7,14,9]]}
[]
[null,null,"Jak na Excel "]
[]
[]
[null,null,null,"Obsah cvičení"]
[null,null,null,null,"Optimalizace / zrychlení kódu "]
[null,null,null,null,"Pomoc na definování"]
[]
[]
[]
[null,null,"Pavel Lasák"]
[]
[]
[null,null,null,"Lektor, expert na Microsoft Excel, držitel prestižního ocenění Microsoftu MVP v České republice"]
[null,null,null,null,null,null,null,5002722]
[]
[]
[]
[]
[null,null,null,"Další informace ke cvičení:"]
[]
[]
[]
[]
[null,"Copyright, Pavel Lasák 2017 rev 2019, 2021"]

{"sheet":"Zrychlování","index":1,"n_rows":204,"n_cols":6,"rows_kept":204,"cut":false,"hidden":false,"merged":[[1,0,1,3],[0,0,0,3]]}
["Ošetření hlášek - pro testy"]
["http://office.lasakovi.com"]
[]
[null,"13_VBA_Ostatni","Složka"]
[null,"13g- VBA ostatni - optimalizace kódu.xlsm","Sešit"]
[null,"Zrychlování","List"]
[]
[]
[]
[null,"Tečky …"]
[]
[null,"čím více teček tím více … tím pomalejší"]
[null,"Tip: nevyužívat celý objektorvý model, když víte na kterém listu jste"]
[]
[null,"Range(\"A1\")","Rychlejší"]
[]
[null,"Worksheets(\"Sheet1\").Range(\"A1\").Value","Pomalejší"]
[]
[]
[null,"Dim NewRange As Range"]
[null,"Set NewRange = Workbooks(\"Book2.xlsx\").Worksheets(\"Sheet1\").Range(\"I5\")"]
[]
[null,"viz SETování"]
[]
[]
[null,"Nahrané makro"]
[null,null,null,null,"a"]
[null,"Přepsat automaticky nahraná makra"]
[null,"Mnoho balastu"]
[]
[null,"' Nahrané makro buňka E27 vložit \"a\" a žlutá buňka",null,null,null,"' Co nahraje záznamník maker"]
[null,"    Range(\"E27\").Select",null,null,null,"    With Selection.Validation"]
[null,"    With Selection.Interior",null,null,null,"        .Delete"]
[null,"        .Pattern = xlSolid",null,null,null,"        .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _"]
[null,"        .PatternColorIndex = xlAutomatic",null,null,null,"        Operator:=xlBetween, Formula1:=\"0\", Formula2:=\"10\""]
[null,"        .Color = 65535",null,null,null,"        .IgnoreBlank = True"]
[null,"        .TintAndShade = 0",null,null,null,"        .InCellDropdown = True"]
[null,"        .PatternTintAndShade = 0",null,null,null,"        .InputTitle = \"\""]
[null,"    End With",null,null,null,"        .ErrorTitle = \"\""]
[null,"    ActiveCell.FormulaR1C1 = \"a\"",null,null,null,"        .InputMessage = \"\""]
[null,"    Range(\"E27\").Select",null,null,null,"        .ErrorMessage = \"\""]
[null,null,null,null,null,"        .ShowInput = True"]
[null,null,null,null,null,"        .ShowError = True"]
[null,null,null,null,null,"    End With"]
[null,"Dim NewRange As Range"]
[null,"Set NewRange = Range(\"E27\")"]
[null,"NewRange.Value = \"a\""]
[null,"NewRange.Interior.Color = RGB(255, 255, 0)"]
[null,null,null,null,null,"Selection.Validation.Add xlValidateWholeNumber, , xlBetween, 1, 10"]
[null,null,null,null,null,"' Overeni dat ručně zadáno"]
[]
[]
[]
[null,"Odkaz na Listy"]
[]
[null,"Worksheets(\"Sheet2\").Range(\"E27\").Value","pomalejší "]
[]
[]
[]
[null,"Kodový název listu"]
[null,"List1.[A1].Value","rychlejší"]
[]
[]
[null,"Worksheets(2).Range(\"E27\").Value","rychlejší"]
[]
[]
[]
[null,"Definování proměnných"]
[]
[null,"Option Explicit"]
[null,"Dim radek As Integer"]
[null,"Viz samosatně"]
[]
[]
[]
[null,"For Each"]
[]
[null,"Dim WS as Worksheet","Rychlejší"]
[null,"For Each WS In Worksheets"]
[null,"    MsgBox WS.Name"]
[null,"Next WS"]
[]
[]
[null,"Dim i as Integer","pomalejší "]
[null,"For i = 1 To Worksheets.Count"]
[null,"    MsgBox Worksheets(i).Name"]
[null,"Next i"]
[]
[]
[null,"Nevyužívat SELECT"]
[]
[null,"Range(\"A1\").Font.Bold = True","rychlejší"]
[]
[]
[null,"Range(\"A1\").Select","pomalejší "]
[null,"Selection.Font.Bold = True"]
[]
[]
[]
[null,"SETování"]
[]
[null,"Dim MyCell As Range"]
[null,"Set MyCell = Workbooks(\"Book2.xlsx\").Worksheets(\"Sheet3\").Range(\"C3\")"]
[null,"MyCell.Value = 123"]
[]
[]
[]
[]
[null,"With"]
[]
[null,"   Worksheets(\"Sheet1\").Range(\"A1\").Font.Bold = True","pomalejší"]
[null,"   Worksheets(\"Sheet1\").Range(\"A1\") .Value = 123"]
[]
[]
[null,"With Worksheets(\"Sheet1\").Range(\"A1\")","rychlejší a přehlednější"]
[null,"    .Font.Bold = True"]
[null,"    .Value = 123"]
[null,"End With"]
[]
[]
[null,"Dim MyCell As Range"]
[null,"Set MyCell = Workbooks(\"Book2.xlsx\").Worksheets(\"Sheet3\").Range(\"C3\")","nejrychlejší a přehlednější"]
[null,"With MyCell"]
[null,"    .Font.Bold = True"]
[null,"    .Value = 123"]
[null,"End With"]
[]
[]
[]
[]
[null,"Funkce v Excel vždy rychlejší !!!!"]
[]
[]
[null,"MySum = Application.WorksheetFunction.Sum(Range(\"A1:A100\"))   ","Rychlejší"]
[]
[]
[null,"For Each C In Range(\"A1:A100\")","nikdy nebude rychlá jako Sum"]
[null,"    MySum = MySum + C.Value"]
[null,"Next C"]
[]
[]
[]
[]
[]
[null,"Vypínání - dat v paměti pro zkopírování"]
[]
[null,"Application.CutCopyMode = False"]
[]
[]
[]
[null,"Vypínání překlreslování atd"]
[]
[null,"Application.ScreenUpdating = False"]
[]
[]
[null,"Nezobrazovat systemové hlášky"]
[]
[null,"Application.EnableEvents = False"]
[]
[null,"Private Sub Worksheet_Change(ByVal Target As Range)"]
[null,"    "]
[null,"    On Error GoTo ErrorHandler"]
[null,"    "]
[null,"    If Target.Address = \"$A$1\" Then"]
[null,"        Application.EnableEvents = False"]
[null,"        Target.Value = 1"]
[null,"    End If"]
[null,"ErrorExit:"]
[null,"    "]
[null,"    Application.EnableEvents = True"]
[null,"    "]
[null,"    Exit Sub"]
[]
[null,"ErrorHandler:"]
[]
[null,"    Debug.Print Err.Number & vbNewLine & Err.Description"]
[null,"    Resume ErrorExit"]
[null,"End Sub"]
[]
[]
[null,"Application.DisplayAlerts = False"]
[]
[null,"    Application.DisplayAlerts = False"]
[null,"    ActiveWorkbook.Close"]
[null,"    Application.DisplayAlerts = True"]
[]
[]
[]
[]
[null,"Přepočty "]
[]
[null,"Application.Calculation = xlCalculationManual"]
[]
[]
[null,"Application.Volatile"]
[]
[]
[]
[null,"Přeskakování chyb"]
[]
[]
[null,"On Error Resume Next"]
[null,"    Kill \"d:\\smaz\\muj.pdf\""]
[null,"On Error GoTo 0"]

{"sheet":"Definování","index":2,"n_rows":62,"n_cols":3,"rows_kept":62,"cut":false,"hidden":false,"merged":[[1,0,1,4]]}
[null,"Definovaní základních nastavení"]
["http://office.lasakovi.com"]
[]
[null,"13_VBA_Ostatni","Složka"]
[null,"13g- VBA ostatni - optimalizace kódu.xlsm","Sešit"]
[null,"Definování","List"]
[]
[]
[null,"Option"]
[]
[null,"Option Explicit"]
[null,"' Každá proměná musí být definována"]
[]
[]
[null,"Deklarace proměných viz samostatný sešit"]
[]
[null,"Option Compare Binary"]
[]
[null,"Option Compare Text"]
[]
[]
[]
[null,"Option Base 1"]
[]
[null,"Option Base 0"]
[]
[]
[]
[]
[null,"Application"]
[]
[null,"Application.EnableEvents = False"]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Application.DisplayAlerts = False"]
[]
[null,"    Application.DisplayAlerts = False"]
[null,"    ActiveWorkbook.Close"]
[null,"    Application.DisplayAlerts = True"]
[]
[]
[]
[]
[null,"Application.ScreenUpdating = False"]
[]
[]
[]
[]
[null,"Application.CutCopyMode = False"]
[]
[]
[null,"Application.Calculation = xlCalculationManual"]
[]
[]
[]
[null,"Application.Volatile"]

{"sheet":"Pro testy","index":3,"n_rows":43,"n_cols":7,"rows_kept":43,"cut":false,"hidden":false,"merged":[[1,0,1,5],[7,0,7,5]]}
[null,"Ošetření hlášek - pro testy"]
["http://office.lasakovi.com"]
[]
[null,"13_VBA_Ostatni","Složka"]
[null,"13g- VBA ostatni - optimalizace kódu.xlsm","Sešit"]
[null,"Definování","List"]
[]
[]
[]
[]
[null,"pokud dojde k chybě přeskoč",null,null,null," "]
[]
[null,"On Error Resume Next"]
[null,"    Kill \"d:\\smaz\\muj.pdf\""]
[null,"On Error GoTo 0"]
[]
[]
[null,"Nepřekesluj obrazovku a nezobrazuj systemové hlášky"]
[null,"With Application \n.ScreenUpdating = False\n.EnableEvents = False \nEnd With ",null,null,null,null,"With Application \n.ScreenUpdating = False\n.EnableEvents = False \nEnd With "]
[null,"... kódy",null,null,null,null,"    Kill \"d:\\smaz\\muj.pdf\""]
[null,"With Application \n.ScreenUpdating = True\n.EnableEvents = true\nEnd With ",null,null,null,null,"With Application \n.ScreenUpdating = True\n.EnableEvents = true\nEnd With "]
[]
[null,"Stav"]
[]
[null,"    MsgBox (Application.EnableEvents)"]
[]
[null,"Zakázání zobrazovaní změn na obazovce  >> jiný list"]
[null,"Application.ScreenUpdating = False"]
[null,"   "]
[null,"For i = 1 To 1000"]
[null,"    Worksheets(\"1\").Range(\"A1\").Offset(i, 0).Value = i"]
[null,"    Worksheets(\"1\").Range(\"A1\").Offset(i, 0).Activate"]
[null,"Next i"]
[]
[null,"Application.ScreenUpdating = True"]
[]
[]
[null,"Změny v module"]
[]
[null,"Application.EnableEvents = True"]
[null,"    MsgBox (\"Změna 1\")"]
[null,"Application.EnableEvents = False"]
[null,"    MsgBox (\"Změna 2\")"]

{"sheet":"List1","index":4,"n_rows":7,"n_cols":3,"rows_kept":7,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[null,null,"smazat"]

{"sheet":"List2","index":5,"n_rows":7,"n_cols":3,"rows_kept":7,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[null,null,"smazat"]

{"sheet":"Poznámky","index":6,"n_rows":2,"n_cols":1,"rows_kept":2,"cut":false,"hidden":false,"merged":[[0,0,0,3],[1,0,1,3]]}
["Prostor pro poznámky"]
["http://office.lasakovi.com"]