{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["Funkce VBA Excel","Odstran diakritiku","Cislo slovem","EN číslo slovem","CZ EN","Barvy","Hodnoty"],"not_rendered":{"charts":0,"images":2,"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":"Funkce VBA Excel","index":0,"n_rows":16,"n_cols":2,"rows_kept":16,"cut":false,"hidden":false,"merged":[[0,0,0,2],[1,0,1,2]]}
["VBA funkce ukázky  UDF"]
["http://office.lasakovi.com"]
[]
[null,"poznámka kódy stažené z innternetu a upravené (vylepšené)"]
[]
[]
[null,"Odstraň diakritiku"]
[]
[null,"číslo slovem"]
[null,"číslo slovem v angličtině"]
[]
[null,"CZ / EN"]
[]
[null,"Barvy"]
[]
[null,"Načti data"]

{"sheet":"Odstran diakritiku","index":1,"n_rows":30,"n_cols":5,"rows_kept":30,"cut":false,"hidden":false,"merged":[[0,0,0,10]]}
["Odstraň diakritiku"]
[]
[]
[null,null,"ěš",{"$f":"OdstarnDiakr(C4)","v":"es"},"=OdstarnDiakr(C4)"]
[]
[null,"Function OdstarnDiakr(source As Variant) As String"]
[]
[null,"Const cz As String = \"áÁčČďĎéÉěĚëËíÍňŇóÓöÖřŘšŠťŤúÚůŮüÜýÝžŽ\""]
[null,"Const en As String = \"aAcCdDeEeEeEiInNoOoOrRsStTuUuUuUyYzZ\""]
[]
[null,"Dim TmpS As String"]
[null,"Dim OutS As String"]
[]
[null,"Dim I As Integer"]
[]
[null,"OutS = \"\""]
[null,"   "]
[null,"   If IsNull(source) Or source = “” Then"]
[null,null,"OdstarnDiakr = \"\""]
[null,"   Else"]
[]
[null,"   For I = 1 To Len(source)"]
[null,"        TmpS = Mid(source, I, 1)"]
[null,"   If InStr(1, cz, TmpS, vbBinaryCompare) > 0 Then TmpS = Mid(en, InStr(1, cz, TmpS, vbBinaryCompare), 1)"]
[null,"        OutS = OutS & TmpS"]
[null,"   Next I"]
[null,null,"OdstarnDiakr = OutS"]
[null,"   End If"]
[]
[null,"End Function"]

{"sheet":"Cislo slovem","index":2,"n_rows":79,"n_cols":2,"rows_kept":79,"cut":false,"hidden":false,"merged":[[0,0,0,7]]}
["Číslo slovem"]
[]
[null,{"$f":"Slovy(1257)","v":"jedentisícdvěstapadesátsedm"}]
[null,"=Slovy(1257)"]
[]
[null,"Function Slovy(Cis As Double) As String"]
[null,"Dim StrCis As String"]
[null,"Dim LenCis As Byte, Rad As Integer, Ofs As Byte"]
[null,"Dim Pol As Byte, pom As String, pom1 As String, pom2 As String"]
[null,"Dim Jedn As Variant, Des1 As Variant, Des As Variant, Sta As Variant"]
[null,"Dim JednTM As Variant, Tis As Variant, Mil As Variant"]
[null,"'"]
[null,"If IsEmpty(Cis) Then End"]
[null,"'"]
[null,"Jedn = Array(\"\", \"jedna\", \"dvě\", \"tři\", \"čtyři\", _"]
[null,"\"pět\", \"šest\", \"sedm\", \"osm\", \"devět\")"]
[null,"Des1 = Array(\"deset\", \"jedenáct\", \"dvanáct\", \"třináct\", \"čtrnáct\", _"]
[null,"\"patnáct\", \"šestnáct\", \"sedmnáct\", \"osmnáct\", \"devatenáct\")"]
[null,"Des = Array(\"\", \"\", \"dvacet\", \"třicet\", \"čtyřicet\", \"padesát\", _"]
[null,"\"šedesát\", \"sedmdesát\", \"osmdesát\", \"devadesát\")"]
[null,"Sta = Array(\"\", \"jednosto\", \"dvěsta\", \"třista\", \"čtyřista\", _"]
[null,"\"pětset\", \"šestset\", \"sedmset\", \"osmset\", \"devětset\")"]
[null,"Tis = Array(\"tisíc\", \"tisíc\", \"tisíce\", \"tisíce\", \"tisíce\", _"]
[null,"\"tisíc\", \"tisíc\", \"tisíc\", \"tisíc\", \"tisíc\")"]
[null,"JednTM = Array(\"\", \"jeden\", \"dva\", \"tři\", \"čtyři\", _"]
[null,"\"pět\", \"šest\", \"sedm\", \"osm\", \"devět\")"]
[null,"Mil = Array(\"milionů\", \"milion\", \"miliony\", \"miliony\", \"miliony\", _"]
[null,"\"milionů\", \"milionů\", \"milionů\", \"milionů\", \"milionů\")"]
[null,"'"]
[null,"'"]
[null,"StrCis = CStr(Format(Cis, \"0.00\"))"]
[null,"Pol = InStr(StrCis, \",\") - 1 ' poloha radu jednotek v cisle"]
[null,"If Pol > 9 Then Slovy = \">999 999 999\": Exit Function"]
[null,"Rad = 0 ' rad cislice v cisle"]
[null,"Slovy = \"\""]
[null,"Do"]
[null,"pom = Mid(StrCis, Pol, 1)"]
[null,"If Pol > 1 Then"]
[null,"pom1 = Mid(StrCis, Pol - 1, 1)"]
[null,"Else"]
[null,"pom1 = \"0\""]
[null,"End If"]
[null,"'"]
[null,"Select Case Rad"]
[null,"Case 0"]
[null,"pom2 = IIf(pom1 <> 1, Jedn(pom), Des1(pom)): Ofs = IIf(pom1 <> 1, 1, 2)"]
[null,"Case 1"]
[null,"pom2 = Des(pom): Ofs = 1"]
[null,"Case 2"]
[null,"pom2 = Sta(pom): Ofs = 1"]
[null,"Case 3"]
[null,"pom2 = IIf(pom1 <> 1, JednTM(pom), Des1(pom)): Ofs = IIf(pom1 <> 1, 1, 2)"]
[null,"If Pol > 3 Then ' kdyz zustavaji jeste >3 cislice"]
[null,"If Mid(StrCis, Pol - 2, 3) <> \"000\" Then"]
[null,"pom2 = pom2 & IIf(pom1 <> 1, Tis(pom), \" tisíc \") ' a jsou i tisice -> vlozeni slova tisic"]
[null,"Else"]
[null,"Ofs = 3 ' preskoci na rad 6 - miliony"]
[null,"End If"]
[null,"Else ' kdyz zustava jeste <3 cislice -> vlozeni slova tisic"]
[null,"pom2 = pom2 & IIf(pom1 <> 1, Tis(pom), \" tisíc \")"]
[null,"End If"]
[null,"Case 4"]
[null,"pom2 = Des(pom): Ofs = 1"]
[null,"Case 5"]
[null,"pom2 = Sta(pom): Ofs = 1"]
[null,"Case 6"]
[null,"pom2 = IIf(pom1 <> 1, JednTM(pom) & Mil(pom), Des1(pom) & \" milionu` \"): Ofs = IIf(pom1 <> 1, 1, 2)"]
[null,"Case 7"]
[null,"pom2 = Des(pom): Ofs = 1"]
[null,"Case 8"]
[null,"pom2 = Sta(pom): Ofs = 1"]
[null,"End Select"]
[null,"'"]
[null,"Slovy = pom2 & Slovy"]
[null,"Pol = Pol - Ofs: Rad = Rad + Ofs"]
[null,"'"]
[null,"Loop While Pol > 0"]
[null,"Slovy = Trim(Slovy) ' & \" \" & Right(StrCis, 2) ' pridani destinne casti"]
[null,"End Function"]

{"sheet":"EN číslo slovem","index":3,"n_rows":311,"n_cols":2,"rows_kept":311,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[null,{"$f":"SpellNumber(32.5)","v":"Thirty Two Dollars and Fifty  Cents"}]
[null,"=SpellNumber(32,5)"]
[]
[]
[]
[null,"Option Explicit"]
[]
[]
[]
[null,"      '****************"]
[]
[null,"      ' Main Function *"]
[]
[null,"      '****************"]
[]
[]
[]
[null,"      Function SpellNumber(ByVal MyNumber)"]
[]
[null,"          Dim Dollars, Cents, Temp"]
[]
[null,"          Dim DecimalPlace, Count"]
[]
[]
[]
[null,"          ReDim Place(9) As String"]
[]
[null,"          Place(2) = \" Thousand \""]
[]
[null,"          Place(3) = \" Million \""]
[]
[null,"          Place(4) = \" Billion \""]
[]
[null,"          Place(5) = \" Trillion \""]
[]
[]
[]
[null,"          ' String representation of amount."]
[]
[null,"          MyNumber = Trim(Str(MyNumber))"]
[]
[]
[]
[null,"          ' Position of decimal place 0 if none."]
[]
[null,"          DecimalPlace = InStr(MyNumber, \".\")"]
[]
[null,"          ' Convert cents and set MyNumber to dollar amount."]
[]
[null,"          If DecimalPlace > 0 Then"]
[]
[null,"              Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _"]
[]
[null,"                  \"00\", 2))"]
[]
[null,"              MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))"]
[]
[null,"          End If"]
[]
[]
[]
[null,"          Count = 1"]
[]
[null,"          Do While MyNumber <> \"\""]
[]
[null,"              Temp = GetHundreds(Right(MyNumber, 3))"]
[]
[null,"              If Temp <> \"\" Then Dollars = Temp & Place(Count) & Dollars"]
[]
[null,"              If Len(MyNumber) > 3 Then"]
[]
[null,"                  MyNumber = Left(MyNumber, Len(MyNumber) - 3)"]
[]
[null,"              Else"]
[]
[null,"                  MyNumber = \"\""]
[]
[null,"              End If"]
[]
[null,"              Count = Count + 1"]
[]
[null,"          Loop"]
[]
[]
[]
[null,"          Select Case Dollars"]
[]
[null,"              Case \"\""]
[]
[null,"                  Dollars = \"No Dollars\""]
[]
[null,"              Case \"One\""]
[]
[null,"                  Dollars = \"One Dollar\""]
[]
[null,"              Case Else"]
[]
[null,"                  Dollars = Dollars & \" Dollars\""]
[]
[null,"          End Select"]
[]
[]
[]
[null,"          Select Case Cents"]
[]
[null,"              Case \"\""]
[]
[null,"                  Cents = \" and No Cents\""]
[]
[null,"              Case \"One\""]
[]
[null,"                  Cents = \" and One Cent\""]
[]
[null,"              Case Else"]
[]
[null,"                  Cents = \" and \" & Cents & \" Cents\""]
[]
[null,"          End Select"]
[]
[]
[]
[null,"          SpellNumber = Dollars & Cents"]
[]
[null,"      End Function"]
[]
[]
[]
[]
[]
[]
[]
[null,"      '*******************************************"]
[]
[null,"      ' Converts a number from 100-999 into text *"]
[]
[null,"      '*******************************************"]
[]
[]
[]
[null,"      Function GetHundreds(ByVal MyNumber)"]
[]
[null,"          Dim Result As String"]
[]
[]
[]
[null,"          If Val(MyNumber) = 0 Then Exit Function"]
[]
[null,"          MyNumber = Right(\"000\" & MyNumber, 3)"]
[]
[]
[]
[null,"          ' Convert the hundreds place."]
[]
[null,"          If Mid(MyNumber, 1, 1) <> \"0\" Then"]
[]
[null,"              Result = GetDigit(Mid(MyNumber, 1, 1)) & \" Hundred \""]
[]
[null,"          End If"]
[]
[]
[]
[null,"          ' Convert the tens and ones place."]
[]
[null,"          If Mid(MyNumber, 2, 1) <> \"0\" Then"]
[]
[null,"              Result = Result & GetTens(Mid(MyNumber, 2))"]
[]
[null,"          Else"]
[]
[null,"              Result = Result & GetDigit(Mid(MyNumber, 3))"]
[]
[null,"          End If"]
[]
[]
[]
[null,"          GetHundreds = Result"]
[]
[null,"      End Function"]
[]
[]
[]
[]
[]
[]
[]
[null,"      '*********************************************"]
[]
[null,"      ' Converts a number from 10 to 99 into text. *"]
[]
[null,"      '*********************************************"]
[]
[]
[]
[null,"     Function GetTens(TensText)"]
[]
[null,"          Dim Result As String"]
[]
[]
[]
[null,"          Result = \"\"           ' Null out the temporary function value."]
[]
[null,"          If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19..."]
[]
[null,"              Select Case Val(TensText)"]
[]
[null,"                  Case 10: Result = \"Ten\""]
[]
[null,"                  Case 11: Result = \"Eleven\""]
[]
[null,"                  Case 12: Result = \"Twelve\""]
[]
[null,"                  Case 13: Result = \"Thirteen\""]
[]
[null,"                  Case 14: Result = \"Fourteen\""]
[]
[null,"                  Case 15: Result = \"Fifteen\""]
[]
[null,"                  Case 16: Result = \"Sixteen\""]
[]
[null,"                  Case 17: Result = \"Seventeen\""]
[]
[null,"                  Case 18: Result = \"Eighteen\""]
[]
[null,"                  Case 19: Result = \"Nineteen\""]
[]
[null,"                  Case Else"]
[]
[null,"              End Select"]
[]
[null,"          Else                                 ' If value between 20-99..."]
[]
[null,"              Select Case Val(Left(TensText, 1))"]
[]
[null,"                  Case 2: Result = \"Twenty \""]
[]
[null,"                  Case 3: Result = \"Thirty \""]
[]
[null,"                  Case 4: Result = \"Forty \""]
[]
[null,"                  Case 5: Result = \"Fifty \""]
[]
[null,"                  Case 6: Result = \"Sixty \""]
[]
[null,"                  Case 7: Result = \"Seventy \""]
[]
[null,"                  Case 8: Result = \"Eighty \""]
[]
[null,"                  Case 9: Result = \"Ninety \""]
[]
[null,"                  Case Else"]
[]
[null,"              End Select"]
[]
[null,"              Result = Result & GetDigit _"]
[]
[null,"                  (Right(TensText, 1))  ' Retrieve ones place."]
[]
[null,"          End If"]
[]
[null,"          GetTens = Result"]
[]
[null,"      End Function"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"      '*******************************************"]
[]
[null,"      ' Converts a number from 1 to 9 into text. *"]
[]
[null,"      '*******************************************"]
[]
[]
[]
[null,"      Function GetDigit(Digit)"]
[]
[null,"          Select Case Val(Digit)"]
[]
[null,"              Case 1: GetDigit = \"One\""]
[]
[null,"              Case 2: GetDigit = \"Two\""]
[]
[null,"              Case 3: GetDigit = \"Three\""]
[]
[null,"              Case 4: GetDigit = \"Four\""]
[]
[null,"              Case 5: GetDigit = \"Five\""]
[]
[null,"              Case 6: GetDigit = \"Six\""]
[]
[null,"              Case 7: GetDigit = \"Seven\""]
[]
[null,"              Case 8: GetDigit = \"Eight\""]
[]
[null,"              Case 9: GetDigit = \"Nine\""]
[]
[null,"              Case Else: GetDigit = \"\""]
[]
[null,"          End Select"]
[]
[null,"      End Function"]

{"sheet":"CZ EN","index":4,"n_rows":21,"n_cols":3,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,null,{"$f":"IF(1,1,1)","v":1}]
[null,null,"=IF(1,1,1)"]
[]
[]
[]
[null,null,"Překlad českých názvů funkcí na anglické"]
[null,null,"hodnota = Cells(3, 3).Formula"]
[null,null,"Cells(4, 3).Value = \"'\" & hodnota"]
[]
[]
[]
[]
[null,null,"=IF(1,1,1)"]
[null,null,{"$f":"IF(1,1,1)","v":1}]
[]
[]
[null,null,"Překlad českých názvů funkcí na anglické"]
[null,null,"hodnota = Cells(15, 3).Formula"]
[null,null,"Cells(16, 3).Value = hodnota"]

{"sheet":"Barvy","index":5,"n_rows":45,"n_cols":7,"rows_kept":45,"cut":false,"hidden":false,"merged":[[3,1,3,9],[28,1,28,9]]}
[]
[]
[]
[null,"SUMA dle barvy"]
[null,"Function SumColor(rColor As Range, rSumRange As Range)"]
[]
[null,"''''''''''''''''''''''''''''''''''''''"]
[null,"'''''''''''''''''''''''''''''''''''''''"]
[null,null,"Dim rCell As Range"]
[null,null,"Dim iCol As Integer"]
[null,null,"Dim vResult"]
[]
[null,null,"iCol = rColor.Interior.ColorIndex"]
[null,null,null,"For Each rCell In rSumRange"]
[null,null,null,null,"If rCell.Interior.ColorIndex = iCol Then"]
[null,null,null,null,null,"vResult = WorksheetFunction.Sum(rCell) + vResult"]
[null,null,null,null,"End If"]
[null,null,null,"Next rCell"]
[null,null,"SumColor = vResult"]
[]
[null,"End Function"]
[]
[]
[null,10,null,{"$f":"SumColor(B24,B24:B27)","v":70},null,null,{"$f":"CountColor(B24,B24:B27)","v":3}]
[null,20,null,"=SumColor(B24;B24:B27)",null,null,"=CountColor(B24;B24:B27)"]
[null,30]
[null,40]
[]
[null,"Počet dle barvy"]
[null,"Function CountColor(rColor As Range, rSumRange As Range)"]
[null,"''''''''''''''''''''''''''''''''''''''"]
[]
[null,"'''''''''''''''''''''''''''''''''''''''"]
[null,"Dim rCell As Range"]
[null,"Dim iCol As Integer"]
[null,"Dim vResult"]
[]
[null,"iCol = rColor.Interior.ColorIndex"]
[null,"   For Each rCell In rSumRange"]
[null,"    If rCell.Interior.ColorIndex = iCol Then"]
[null,"         vResult = vResult + 1"]
[null,"    End If"]
[null,"   Next rCell"]
[null,"CountColor = vResult"]
[null,"End Function"]

{"sheet":"Hodnoty","index":6,"n_rows":52,"n_cols":6,"rows_kept":52,"cut":false,"hidden":false,"merged":[[0,0,0,6]]}
["Hrátky s hodnotami"]
[]
[]
[]
[]
[null,"Z hodnot získat jen čísla"]
[]
[null,"Function JenCislaZiskat(mystr As Variant)",null,"zadání","výsledek"]
[null,"Dim myOutput As String, i As Integer",null,"a12",{"$f":"JenCislaZiskat(D9)","v":12},"=JenCislaZiskat(D3)"]
[null,"    For i = 1 To Len(mystr)",null,12,{"$f":"JenCislaZiskat(D10)","v":12}]
[null,"        If IsNumeric(Mid(mystr, i, 1)) Then _",null,"cc12ff1",121]
[null,"        myOutput = myOutput & Mid(mystr, i, 1)"]
[null,"    Next"]
[null,"JenCislaZiskat = myOutput * 1"]
[null,"End Function"]
[]
[]
[]
[null,"Získat email"]
[null,null,null,"zadání","výsledek"]
[null,"Function ExtractEmailFromText(s As String) As String",null,"aa@dd.cz",{"$f":"ExtractEmailFromText(D21)","v":"aa@dd.cz"},"=ExtractEmailFromText(E21)"]
[null,"    Dim AtTheRateSignSymbol As Long",null,"aa bb@test.com hhh",{"$f":"ExtractEmailFromText(D22)","v":"bb@test.com"}]
[null,"    Dim i As Long"]
[null,"    Dim TempStr As String"]
[null,"    Const CharList As String = \"[A-Za-z0-9._-]\""]
[null,"    "]
[null,"    AtTheRateSignSymbol = InStr(s, \"@\")"]
[null,"    If AtTheRateSignSymbol = 0 Then"]
[null,"        ExtractEmailFromText = \"\""]
[null,"    Else"]
[null,"        TempStr = \"\""]
[null,"        For i = AtTheRateSignSymbol - 1 To 1 Step -1"]
[null,"            If Mid(s, i, 1) Like CharList Then"]
[null,"                TempStr = Mid(s, i, 1) & TempStr"]
[null,"            Else"]
[null,"                Exit For"]
[null,"            End If"]
[null,"        Next i"]
[null,"        If TempStr = \"\" Then Exit Function"]
[null,"        TempStr = TempStr & \"@\""]
[null,"        For i = AtTheRateSignSymbol + 1 To Len(s)"]
[null,"            If Mid(s, i, 1) Like CharList Then"]
[null,"                TempStr = TempStr & Mid(s, i, 1)"]
[null,"            Else"]
[null,"                Exit For"]
[null,"            End If"]
[null,"        Next i"]
[null,"    End If"]
[null,"    If Right(TempStr, 1) = \".\" Then TempStr = _"]
[null,"       Left(TempStr, Len(TempStr) - 1)"]
[null,"    ExtractEmailFromText = TempStr"]
[null,"End Function"]