Jak používat (velmi rychlá) VBA makra v MS Office

Znám hodně lidí, typicky různí účetní, ekonomové atp., kteří používají makra v aplikacích MS Office, aniž jim rozumí. Prostě jim někdo ukázal funkci Zaznamenat makro a Spustit makro a oni ji používají. A to je skvělé.

Vážně. Dal bych nevím co, aby takovýhle makro rekordér obsahovala každá aplikace a považuji za zásadní nedostatek Google Apps, že ač obsahují mají svou obdobu maker – Apps Script, makro rekordér neobsahují.

Úvod pro laiky

Následuje úvod pro laiky, zkušení přeskočte až na tučný nadpis níže:

Poznámka: panel Developer/Vývojář, ve kterém se s Makry pracuje, musíte v novějších verzích Office extra zapnout – v aplikaci stistknete tlačítko Office (takové to kulaté vlevo nahoře), nebo Soubor (v nejnovějších Office), a tam „někde“ to bude – např. v anglickém Excel 2007 je to v Excel options (tlačítko dole) a zvolit Show Developer tab in the Ribbon, v českém Excel 2016 je to v Přizpůsobit pás karet a vpravo zaškrtnout Vývojář.

Pokračovat ve čtení „Jak používat (velmi rychlá) VBA makra v MS Office“

ARRAYFORMULA – důvod, proč si zamilujete Google Sheets

Mám moc rád MS Excel a jsem přesvědčen, že neexistuje lepší tabulkový kalkulátor. Ale přesto čím dál častěji používám Google Sheets a to ze dvou důvodů – snadné (a bezplatné) sdílení tabulek a funkce ARRAYFORMULA.

Ona to vlastně není až tak úplně funkce, je to jen jakýsi konstrukt, který říká, že daný vzorec se týká nějakého pole buněk, nikoliv jedné. Ano, i MS Excel má pole vzorců, jenže je zde podstatný rozdíl – u MS Excelu musíte předem označit všechny buňky, které se mají vyplnit výsledkem, u Google Sheets napíšete vzorec jen do jedné a on vyplní všechny ostatní.

Skvěle se to totiž hodí i pro řešení problému, který jsem zmínil včera, tedy když potřebujete do nějakého sloupce tabulky dát nějaké hodnoty či výpočet a chcete, aby se to automaticky počítalo u všech řádků a nemuseli řešit problémy, když nějaké řádky přesunete, přidáte, smažete.

Tedy řekněme, že máte

  • v buňkách A1 až A1000 nějaké hodnoty
  • v buňkách B1 až B1000 nějaké další hodnoty

A potřebujete do buněk C1 až C1000 dá třeba násobek hodnot v předchozích dvou sloupcích, tedy pro buňku C1 byste napsali =A1*B1

Takže vy místo toho napíšete do C1 vzorec

=ARRAYFORMULA(A1:A*B1:B)

a Google automaticky doplní všechny hodnoty. A co víc, když zkusíte nějakou hodnotu z kteréhokoliv řádku ve sloupci C smazat nebo změnti, tak ji Sheets okamžitě dopočítá zpět. Další výhodou je pak rychlost, nemáte to totiž 1000 výpočtů, ale jen jeden (byť nad tisíci řádků).

Nahrazení A1 za A1:A jsme Google Sheets řekli, aby prostě počítal od A1 až do konce sloupce A. Proto jste si asi všimli, že Sheets doplní výpočet do všech řádků, nejen do těch tisíce. Při řešení využijeme druhé úžasné funkce Google Sheets, o které si někdy povíme více a to je funkce Filter. Prostě napíšeme

=ARRAYFORMULA(FILTER(A1:A*B1:B;A1:A<>""))

Tím říkáme „Použij výpočet A1*B1 na všechny pole od A1 resp. B1 po poslední pole ve sloupci A resp. B, ale pouze pakliže příslušná buňka ve sloupci A není prázdná“.

Teď si možná říkáte, že to tak užitečné není, že toho byste dosáhli i třeba pomocí Formátovaných tabulek, které má Excel někdy od verze 2007 a které také umí vyplňovat vzorce do všech řádků. Jenže tyhle funkce v Google Sheets fungují i napříč listy. Takže si dáme další příklad:

Řekněme, že vám nějaký online systém vyjíždí CSV tabulku, kde máte seznam objednávek – ID uživatele, ID objednávky a její hodnotu. Vy si tenhle seznam natáhnete do vaší Google tabulky pomocí další skvělé funkce IMPORTDATA.

No a teď chcete mít na dalším listu seznam objednávek, které byly vyšší než 1000 Kč. Zvládnete to tímhle jedním zápisem na další list:

=ARRAYFORMULA(FILTER('List 1'!A1:C;'List 1'!C1:C>1000))

Tedy vezmi mi všechny hodnoty z A1:C, ale pouze za předpokladu, že hodnota ve sloupci C je vyšší než 1000. Úchvatné, ne? V Excelu byste to museli řešit makry či kontingenčními tabulkami, se všemi jejich nevýhodami.

A to není všechno – výstup pak můžete i seřadit podle nějakého sloupce, třeba sestupně podle posledního sloupce, díky funkci SORT

=ARRAYFORMULA(SORT(FILTER('List 1'!A1:C;'List 1'!C1:C>10);3;FALSE))

Jo a možná vás napadlo: kdybyste chtěli udělat součet všech objednávek pro jednotlivé uživatele, tak aby se jejich ID neopakovalo, můžete použít funkci UNIQUE nad sloupcem s ID uživateli a následně využít funkce SUMIF, a opět přes ARRAYFORMULA si ji natáhnout do všech řádků ke všem uživatelům.

Excel – automatické vyplnění hodnot ve všech řádcích

Tenhle tip jsem se dozvěděl teprve nedávno, což vzhledem k tomu, jak hodně Excel používám posledních 20 let a jak často jsem to potřeboval, je dost na pováženou.

Asi to znáte – máte v Excelu tabulku, která má tunu řádků. Vy potřebujete dát do nějakého sloupce hodnotu nebo vzorec a chcete je nakopírovat (či rozšířit) do všech řádků až do konce tabulky.

Nepochybně víte, že můžete vzít tu tečku/křížek vpravo dole myší a táhnout a táhnout, ale to je u hodně dlouhých tabulek fakt pruda.

Nebo to možná řešíte jako donedávna já, kdy jsem šel nejdříve do řádku, kde jsou hodnoty, tam jsem zmáčknul CTRL+šipka_dolů, přešel do sloupce, který jsem chtěl vyplnit, tam napsal cokoliv, pak kliknul na CTRL+šipka_nahoru, abych se dostal na tu horní buňku, kde mám vzorec, pak CTRL+SHIFT+šipka_dolů pro označení rozsahu od této buňky až po poslední buňku, načež jsem zmáčknul F2 pro aktivování vzorce v té horní buňce, a konečně CTRL+ENTER pro vyplnění dané hodnoty ve všech označených buňkách. Zní to strašně složitě, ale takhle jsem to dělal dvacet let a je to otázka méně než jedné vteřiny a nemusím sundat ruce z klávesnice.

Nebo jste si možná jako já udělali makro (doporučuji podívat se na vlastnost Range.CurrentRegion (tedy třeba ActiveCell.CurrentRegion.Rows.Count, což vám spočítá počet řádků v regionu buněk okolo aktuálně vybrané buňky).

No a nebo prostě a jednoduše uděláte dvojklik, na té tečce/křížku v pravém dolním rohu buňky, do které jste napsali vzorec, místo abyste za to táhli. Tímto vyplníte všechny následující buňky, až do konce aktuálního regionu. A pakliže máte vybráno více buněk a kliknete na tu tečku/křížek, tak Excel zkusí klasicky následující hodnoty dopočítat (tj. když budete mít v 1. řádku napsáno 1, ve druhém 2, označíte obě buňky a ve vedlejším sloupci budete mít třeba sto řádků s nějakými hodnotami, tak po dvojkliku na tečku/křížek se vyplní následujících 98 řádků hodnotami 3-100).

P.S.: kdybyste někdo věděl, jak toho dosáhnout nějakou klávesovou zkratkou, tak to uvítám.

Excel+VBA – vložit jako text

Asi to znáte – kopírujete odněkud text do Excelu a on se tam vloží i s formátováním. Jasně, můžete jít na Home / Paste / Paste special / Insert as Unicode, nebo Home / Paste / Paste values (podle toho, jestli vkládáte text z nějakého dokumentu, či jestli vkládáte hodnoty z nějaké excelové tabulky) – ale to je samozřejmě otravné a zdržující.

Takže pro tyto účely jsem si vytvořil toto makro, kterému jsem přiřadil myslím dobře zapamatovatelnou zkratku CTRL+SHIFT+V.

Makro nejdřív zkusí obsah schránky vložit jako Excelové hodnoty, když se nezadaří, tak to zkusí jako Unicode text, a když ani to nejde, tak prostě jen pípne.

Sub Vlozit_jako_text()
'
' Vlozit_jako_text Makro
'
' Klávesová zkratka: Ctrl+Shift+V
'
On Error GoTo unicode
 Selection.PasteSpecial Paste:=xlPasteValues
 Exit Sub
unicode:
 On Error GoTo finish
 'ActiveSheet.PasteSpecial Format:="Text v kódu Unicode"
 ActiveSheet.PasteSpecial Format:="Unicode Text"
 Exit Sub
finish:
 Beep
 
End Sub

Excel+VBA – reset filtru tabulky při jejím zavření

Jestli pracujete ve firmě ve více lidech na jedné tabulce a používáte filtry, tak se vám už určitě stalo, že jste otevřeli tabulku a byly tam vyfiltrované nějaké položky podle potřeb někoho jiného a vy jste to museli nejdřív zrušit.

Já to vyřešil tak, že se u příslušné tabulky při ukládání (případně by šlo při otvírání filtr prostě zrezetuje. Následující makro je třeba přiřadit objektu ThisWorkbook („Pricelist“ je název listu, kde chci při ukládání rezetovat filtr, šlo by samozřejmě smyčkou projít všechny):

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next
 ActiveWorkbook.Sheets("Pricelist").ShowAllData

End Sub

Excel+VBA – Ověření EAN

Potřebujete zjistit, jestli EAN je validní 13místný kód – tedy že má daný řetězec 13 číslic, z toho ta poslední je správný kontrolní checksum? Tahle funkce vám to vyřeší (vrací TRUE/FALSE).

Function checkEAN(ean)

Dim s As String
Dim cs As Integer
Dim i As Integer
Dim digit As Integer

If (TypeName(ean) = "Range") Then
 s = ean.Value
ElseIf (TypeName(ean) = "String" Or TypeName(ean) = "Integer") Then
 s = ean
End If
 
If (Len(s) <> 13) Then
checkEAN = False
Return
End If
 

cs = 0 'checksum
 
For i = 1 To 12
 digit = Mid(s, i, 1) - "0" 'get the next digit from bar code text
 If i Mod 2 = 0 Then
 cs = cs + digit * 3 'multiply each bar code digit by it's weight, 1 or 3
 Else
 cs = cs + digit * 1
 End If
Next i
 
cs = (10 - (cs Mod 10)) Mod 10 'which digit must be added to cs to make it divisible by 10

checkEAN = False
checkEAN = (Mid(s, 13, 1) = cs)


End Function

Excel+VBA – vlastní vzorec pro regulární výrazy

Tohle je asi můj nejpoužívanější Excelový hack – vlastní vzorec pro regulární výrazy. Bez toho se některé věci dělají strašně složitě přes funkce jako NAJÍT, DOSADIT či NAHRADIT, a nebo dokonce vůbec nejdou a člověk se musí uchýlit k jiným nástrojům.

Excel přitom regulární výrazy umí, ale jen ve svém VBA. Takže stačí si vytvořit vlastní funkci:

#Const LateBind = True
Function PREG_REPLACE(Pattern As String, Replacement As String, Subject As Range)
 #If Not LateBind Then
 Dim RE As RegExp
 Set RE = New RegExp
 #Else
 Dim RE As Object
 Set RE = CreateObject("vbscript.regexp")
 #End If
 RE.Pattern = Pattern
 RE.Global = True
 PREG_REPLACE = RE.Replace(Subject, Replacement)
End Function

Krátká noticka – konstanta LateBind je opravdu před funkcí a zajišťuje, aby se objekt – knihovna s regulárními výrazy, nevolala vícekrát.

Použití podobné jako třeba v PHP, jen Pattern nemusíte uvozovat. Tj. např. když do A1 napíšete „Josef Novák“ a do B1 potom =PREG_REPLACE(„^(.*)\s(.*)“;“$2 $1″;A1), tak výsledkem bude „Novák Josef“ – tedy poslední slovo jste posunuli na začátek.

Excel+VBA – seřazení listů podle abecedy

Následující makro myslím nepotřebuje dalšího komentáře, vše je řečeno titulkem

Public Function SortWorksheetsByName()

 Dim lCount As Long, lCounted As Long, lCount2 As Long
 Dim lShtLast As Long


 lShtLast = Sheets.Count

 For lCount = 1 To lShtLast
 For lCount2 = lCount To lShtLast
 If UCase(Sheets(lCount2).Name) < UCase(Sheets(lCount).Name) Then
 Sheets(lCount2).Move Before:=Sheets(lCount)
 End If
 Next lCount2
 Next lCount

End Function

Excel+VBA – rychlé přepínání psaní desetiných čárek

Jak pracuji s různými zdroji dat, mám někdy na vstupu data s desetinou čárkou, jindy s tečkou a při jejich kopírování z/do Excelu tak může dojít ke zničení těchto dat.

Např. předpokládejme, že máte standardně nastavený Excel a Windows s českým nastavením regionu, tj. oddělovač tisíců je mezera a desetinný oddělovač je čárka.

A teď si představte, že do takového Excelu vložíte data z nějakého amerického webu či dokumentu, kde jsou použity desetinné tečky.

Takže si představte, že vložíte hodnotu 1.9. Na první pohled si ničeho nevšimnete, vypadá to jako normální číslo, automaticky se to zarovná doprava. Jenže pak to číslo vynásobíte 2 a Excel vám napíše 4.5!

Problém je, že jste nevložili 1,9, ale 1. září (aktuálního roku), což je v interním počítání Excelu číslo 42248 (tolik dnů uplynulo od 1.1.1900). Takže vynásobením 2 jste do 84496 dnů, což je 4. května 2131, tedy zobrazeno jako krátké datum je to 4.5.

Proto používám jeden fígl – mám vytvořené malé makro s přiřazenou klávesovou zkratkou a s ní tak rychle přepínám mezi výchozím (českým) regionálním nastavením a mezi americkým.

Sub Prepnout()

 With Application
 .DecimalSeparator = "."
 .ThousandsSeparator = ","
 End With
 Application.UseSystemSeparators = Not Application.UseSystemSeparators
 
End Sub

Excel+VBA – odlišení buněk s vzorcem

Možná to znáte – máte Excelovou tabulku a potřebujete mít sloupec, kde hodnoty počítáte nějakým vzorcem, ale zároveň je někdy potřebujete přepsat fixní hodnotou.

Jenže pak se třeba změní kurz či něco podobného a vy potřebujete nějak snadno identifikovat buňky s fixní hodnotou – můžete je dát najít (CTRL+G / Special / Constants), ale to je někdy nepohodlné, raději byste takové buňky měli rovnou nějak odlišené.

Nebo máte nějakou sdílenou tabulku a potřebujete přehledně najednou identifikovat, které buňky jsou počítány automaticky a která jsou k ručnímu vyplnění …

Vhodným způsobem, jak naformátovat nějaké buňky automaticky na základě splnění nějaké podmínky je podmíněné formátování (Home / Conditional formating) – zde můžete nadefinovat vzorec a formát buňky, který bude použit v případě, že výsledkem vzorce je kladná odpověd.

Takže se nabízí testovat, zda-li daná buňka obsahuje vzorec, jenže Excel nemá žádnou vestavěnou funkci, která by říkala, jestli je buňka konstanta či vzorec. Proto si takovou funkci musíte nejdříve přidat, a to pomocí skriptovacího jazyka VBA (co je to VBA a jak se s ním dělá je mimo záběr tohoto článku, vysvětlím případně jindy).

Public Function HASFORMULA(ByVal cell As Range) As Boolean
 ' Returns whether the cell contains a formula.
 On Error Resume Next
 HASFORMULA = cell.HASFORMULA
End Function

Tahle krátká uživatelská funkce zajistí, že když pak napíšete třeba do buňky B1 vzorec =HASFORMULA (A1), tak vám vrátí TRUE v případě, že v A1 je vzorec a FALSE když nikoliv. Tenhle vzorec pak můžete použít třeba právě v tom podmíněném formátování.