V předchozím článku jsem se zabýval zaokrouhlováním. Dnes se k vybraným funkcím VBA vrátím,doplním je o přidružené konverzní funkce a pod mikroskopem prozkoumám megafunkci Format.
Není potřeba probírat teorii, vrhneme se rovnou na jednotlivé funkce v kódu VBA.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 | Sub TestZaokrouhlovaniKonverze() 'Fix 'vrací pro kladná čísla celočíselnou část hodnoty 'vrací pro záporná čísla nejbližší záporné celé číslo 'VĚTŠÍ nebo rovné hodnotě vstupu 'tj. zaokrouhluje na celá čísla směrem k nule Debug.Print "Fix(3.5) = " & Fix(3.5) Debug.Print "Fix(-3.5) = " & Fix(-3.5) 'Int 'vrací pro kladná čísla celočíselnou část hodnoty 'vrací pro záporná čísla nejbližší záporné celé číslo 'MENŠÍ nebo rovné hodnotě vstupu 'tj. zaokrouhluje na celá čísla směrem doleva Debug.Print "Int(3.5) = " & Int(3.5) Debug.Print "Int(-3.5) = " & Int(-3.5) 'Val 'extrahuje číslo z řetězce 'automaticky ořezává mezery 'končí vyhodnocování s prvním nečíselným znakem kromě mezery 'pracuje s řetezcovou podobou amerického formátu čísla Debug.Print "Val(""-6.5"") = " & Val("-6.5") Debug.Print "Val(""-6,5"") = " & Val("-6,5") Debug.Print "Val(""&O166"") = " & Val("&O166") Debug.Print "Val(""&H166"") = " & Val("&H166") 'Str 'převádí číslo na řetězec 'zaokrouhlí výraz delší jak 17 znaků '(včetně znaménka a desetinné tečky) 'pro kladné výrazy zobrazí na začátku mezeru, 'pro záporné znaménko mínus 'pracuje s americkým formátem čísla strA = Str(3.14) strB = Str(-3.14) 'CStr 'převádí číslo na řetězec 'pro kladné výrazy nezobrazí na začátku mezeru jako Str 'americký formát čísla konvertuje na řetězec české podoby čísla strC = CStr(3.14) strD = CStr(-3.14) 'CInt zaokrouhluje 0.5 na nejbližší sudé číslo 'podobně CLng Debug.Print "CInt(3.5) = " & CInt(3.5) Debug.Print "CInt(-3.5) = " & CInt(-3.5) Debug.Print "CInt(6.5) = " & CInt(6.5) Debug.Print "CInt(-6.5) = " & CInt(-6.5) 'CBool bPravHodnotaA = CBool(0) 'False bPravHodnotaB = CBool(25.1) 'True bPravHodnotaC = CBool(-1) 'True bPravHodnotaD = CBool(-3.14) 'True 'CDate dtDatumA = CDate("25.12.2009") 'dtDatumA = #25.12.2009# dtDatumB = CDate("25.12") 'dtDatumB = #25.12.2011#, tj. 25. prosince aktuálního roku 'stejně pro "12.25", "25/12" a další dtDatumC = CDate("40260") 'dtDatumC = #23.3.2010# 'bráno jako datum ve formátu Long dtCas = CDate("12:25") 'dtCas = #12:25:00#, tj. čas dtDatumCas = CDate("25.12.2009 13:46") 'dtDatumCas = #25.12.2009 13:46# End Sub |
Zcela jistě jste se již setkali s funkcí Format. Znáte ji ale dobře?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | Sub TestFunkceFormatCastA() 'přirozené číslo v lokalizované verzi 'řetězec "1234,567" strCisloFormat = Format(1234.567, "General Number") 'měna (dle systému), oddělovač tisíců 'řetězec "1 234,57 Kč" strCisloFormat = Format(1234.567, "Currency") 'alternativně strCisloFormat = Format(1234.567, "#,##0.00 Kč") strCisloFormat = Format(1234.567, "#,##0.00 \K\č") strCisloFormat = Format(1234.567, "#,##0.00"" Kč""") strCisloFormat = Format(1234.567, "$#,##0.00") 'jednotky a jiný text 'řetězec "m = 1234,6 kg" strCisloFormat = Format(1234.567, """m = ""0.0"" kg""") 'dvě desetinná místa 'řetězec "1234,57" strCisloFormat = Format(1234.567, "Fixed") 'alternativně 'strCisloFormat = Format(1234.567, "0.00") 'dvě desetinná místa, oddělovač tisíců 'řetězec "1 234,57" strCisloFormat = Format(1234.567, "Standard") 'alternativně strCisloFormat = Format(1234.567, "#,##0.00") 'zaokrouhlení na tisíce 'řetězec "123457" strCisloFormat = Format(123456789, "0,") 'zaokrouhlení na miliony 'řetězec "123" strCisloFormat = Format(123456789, "0,,") 'procenta (násobeno 100), dvě desetinná místa 'řetězec "56,70%" strCisloFormat = Format(0.567, "Percent") 'alternativně strCisloFormat = Format(0.567, "0.00%") 'doplnění na daný počet cifer 'řetězec "001234" strCisloFormat = Format(1234, "000000") 'sekce oddělené středníkem 'počet sekcí: '2 ... kladné hodnoty; záporné hodnoty '3 ... kladné hodnoty; záporné hodnoty; nulové hodnoty '4 ... kladné hodnoty; záporné hodnoty; nulové hodnoty; Null 'zde vrácen řetězec "kladné", "záporné" nebo "nula" strCisloFormat = Format(-1234.567, """kladné"";""záporné"";""nula""") strCisloFormat = Format(0, """kladné"";""záporné"";""nula""") strCisloFormat = Format(1234.567, """kladné"";""záporné"";""nula""") 'exponenciální tvar čísla 'řetězec "1,23E+03" strCisloFormat = Format(1234.567, "Scientific") '0 ... "Ne" ("No"), jinak "Ano" ("Yes") 'řetězec "Ano" strCisloFormat = Format(1234.567, "Yes/No") '0 ... "Nepravda" ("False"), jinak "Pravda" ("True") 'řetězec "Pravda" strCisloFormat = Format(1234.567, "True/False") '0 ... "Vypnuto" ("Off"), jinak "Zapnuto" ("On") 'řetězec "Ano" strCisloFormat = Format(1234.567, "On/Off") End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 | Sub TestFunkceFormatCastB() 'datum 'např. řetězec "29.6.2010" strDatumFormat = Format(Date, "General Date") 'datum dle místních nastavení 'např. řetězec "29. červen 2010" strDatumFormat = Format(Date, "Long Date") 'datum dle místních nastavení 'např. řetězec "29.VI 2010" strDatumFormat = Format(Date, "Medium Date") 'datum dle místních nastavení 'např. řetězec "29.6.2010" strDatumFormat = Format(Date, "Short Date") '"univerzální" formát datumu pro předání do databáze SQL Server strDatumFormat = Format(Date, "'yyyymmdd'") 'číslo týdne (nerespektuje evropskou normu ISO) strDatumFormat = Format(Date, "ww") 'čas dle místních nastavení 'např. řetězec "12:04:50" strCasFormat = Format(Time, "Long Time") 'alternativně strCasFormat = Format(Time, "hh:mm:ss") 'čas dle místních nastavení '12hodinový formát bez sekund se značkou dop./odp (AM/PM) 'např. řetězec "12:05 odp." strCasFormat = Format(Time, "Medium Time") 'alternativně strCasFormat = Format(Time, "hh:mm AMPM") 'čas dle místních nastavení '24hodinový formát bez sekund 'např. řetězec "12:05" strCasFormat = Format(Time, "Short Time") 'alternativně strCasFormat = Format(Time, "hh:mm") 'vlastní formát 'h ... hodiny 'm ... minuty 's ... sekundy 'např. řetězec "120450" strCasFormat = Format(Time, "hhmmss") 'vlastní formát (datum i čas pohromadě) 'd, dd ... pořadové číslo dne v měsíci (jedna, dvě číslice) 'ddd, dddd ... den týdne (zkratka, plný název) 'w ... pořadové číslo dne v týdnu 'ww ... pořadové číslo týdne v roce 'm, mm ... pořadové číslo měsíce roku 'mmm, mmmm ... měsíc roku (zkratka/římsky, plný název) 'q ... pořadové číslo čtvrtletí roku 'y ... pořadové číslo dne v roce 'yy, yyyy ... rok (poslední dvojčíslí, včetně století) 'h, hh ... hodiny (jedna, dvě číslice) 'n, nn ... minuty (jedna, dvě číslice) 's, ss ... sekundy (jedna, dvě číslice) 'AM/PM, AMPM, A/P ... značka časového údaje (dopoledne/odpoledne) 'např. řetězec "29. červen 2010, 12:04:50" strCasFormat = Format(Now, "dd. mmmm yyyy\, hh:nn:ss") 'vlastní formát 'použitelný jako jedinečná časová značka v názvu souboru 'např. řetězec "290610120450" strCasFormat = Format(Now, "ddmmyyhhnnss") 'konverze na velká písmena 'řetězec "ABC" strTextFormat = Format("abc", ">") 'konverze na malá písmena 'řetězec "xyz" strTextFormat = Format("XYZ", "< ") 'doplnění o vlastní text 'řetězec "Smlouva č. 343403/56C" strTextFormat = Format("343403/56C", """Smlouva č. ""@") 'vložení znaku uvnitř řetězce (zde "l" na druhé pozici) 'řetězec "plátek" strTextFormat = Format("pátek", "@l") 'tip: ošetření Null jako návratové hodnoty (databáze apod.) 'hodnota Null převedena na prázdný řetězec strNullJakoPrazdnyRetezec = Format(Null) 'další podobné funkce 'zaokrouhlení na čtyři desetinná místa, vedoucí nula 'podobně FormatCurrency strCislo = FormatNumber(-0.12345, 4, vbTrue, vbFalse, vbTrue) End Sub |
Je vidět, že funkce Format je velmi robustní, což ji někdy ubírá na rychlosti. Pozor na jednu věc. Nezaměňujte funkci Format s vlastností NumberFormat a současně si uvědomte rozdíly v lokalizované definici formátu buňky na listu a ve VBA.
1 2 3 4 5 6 7 8 9 10 11 | Sub NumberFormatPriklad() 'formát buňky definovaný na listu: d.m.rrrr 'a ve VBA: Selection.NumberFormat = "m/d/yyyy" 'formát buňky definovaný na listu: # ##0,00 Kč 'a ve VBA: Selection.NumberFormat = "#,##0.00 $" End Sub |