4 chyby, kterým se můžete vyhnout při programování maker Excelu pomocí VBA

Microsoft Excel je již velmi schopný nástroj pro analýzu dat, ale se schopností automatizovat opakující se úkoly s makry Jak odesílat e-maily z tabulky Excel pomocí skriptů VBA Jak odesílat e-maily z tabulky Excel pomocí skriptů VBA Naše šablona kódu vám pomůže nastavit automatizované e-maily z aplikace Excel pomocí skriptů Collaboration Data Objects (CDO) a VBA. napsáním jednoduchého kódu v (VBA) je to mnohem silnější. Při nesprávném použití však může VBA způsobit mnoho problémů.

I když nejste programátor, VBA nabízí jednoduché funkce, které vám umožní přidat některé opravdu působivé funkce do tabulek, takže nenechávejte zatím!

Ať už jste guru VBA, který vytváří dashboardy v Excelu, vytvořte si vlastní řídicí panel počasí v této třídě Master Class společnosti Google. Vytvořte si vlastní řídicí panel počasí v této třídě Master Class společnosti Google. Chcete změnit svůj účet Google Drive na inteligentní systém analýzy počasí , a žít fantazii Star Trek? OK! , nebo nováček, který ví, jak psát jednoduché skripty, které provádějí základní výpočty buněk, můžete sledovat jednoduché programovací techniky, které vám pomohou zvýšit pravděpodobnost psaní čistého a bezchybného kódu..

Začínáme s VBA

Pokud jste v programu VBA v programu Excel dříve nenaprogramovali, je povolení vývojářských nástrojů ve skutečnosti velmi snadné. Jen jdi na Soubor > Možnosti a pak Přizpůsobte pás karet. Stačí přesunout Vývojář skupina příkazů z levého podokna doprava.

Ujistěte se, že je zaškrtnuto políčko a nyní se v nabídce Excel zobrazí karta Vývojář.

Nejjednodušší způsob, jak se dostat do okna editoru kódů, je kliknout na ikonu Zobrazit kód tlačítko pod Řízení v nabídce Vývojář.

1. Hrozná proměnná jména

Nyní, když jste v okně kódu, je čas začít psát VBA kód Jak si můžete vytvořit svůj vlastní jednoduchý program s VBA Jak si můžete vytvořit svůj vlastní jednoduchý program s VBA Pro ty z vás, kteří by opravdu rádi byli schopni psát vaše vlastní aplikace, ale nikdy jste nikdy nenapsali jediný řádek kódu, budu vás procházet tím, jak si udělám svůj…. Prvním důležitým krokem ve většině programů, ať už je to ve VBA nebo v jiném jazyce, je definování vašich proměnných.

Během mých několika desetiletí psaní kódu jsem narazil na mnoho myšlenkových škol, pokud jde o konvence s proměnným pojmenováním a naučil se pár věcí tvrdě. Zde jsou rychlé tipy pro vytváření názvů proměnných:

• Ať jsou co nejkratší.
• Ať jsou co nejvíce popisné.
• Předmluvte je s proměnným typem (boolean, integer atd.).
• Nezapomeňte použít správný rozsah (viz níže).

Zde je ukázka obrazovky z programu, který často používám pro volání WMIC Windows z Excelu ke shromažďování informací o PC. Jak zobrazit všechny informace o vašem počítači pomocí jednoduchého skriptu Excel VBA Jak zobrazit všechny informace o vašem počítači pomocí jednoduchého skriptu Excel VBA Excel a 10 minut práce vám poskytne podrobnější informace o vašem počítači, než jste si kdy mysleli. Zní to příliš dobře, aby to byla pravda? To je Windows, pokud víte, jak ji používat. .

Pokud chcete použít proměnné uvnitř jakékoli funkce uvnitř modulu nebo objektu (vysvětlím to níže), musíte ji deklarovat jako “veřejnost” proměnná předběžnou deklarací s Veřejnost. V opačném případě jsou proměnné deklarovány tak, že je před slovo uvedou Ztlumit.

Jak vidíte, je-li proměnná celé číslo, je přednastavena int. Pokud je to řetězec, pak str. To vám později pomůže při programování, protože vždy budete vědět, jaký typ dat proměnná obsahuje, pouhým pohledem na jméno. Také si všimnete, že pokud existuje něco jako řetězec, který obsahuje jméno počítače, pak se volá proměnná strComputerName.

Vyhněte se vytváření velmi spletitých nebo matoucích názvů proměnných, kterým rozumíte pouze vy. Usnadněte dalšímu programátorovi, aby za vámi přišel a pochopil, co to všechno znamená!

Další chybou, kterou lidé dělají, je ponechat názvy listů jako výchozí “List1”, “List2”, atd. ... Tím se program ještě více zmatí. Místo toho pojmenujte listy tak, aby dávaly smysl.

Tímto způsobem, když odkazujete na název listu v kódu Excel VBA Použití automatizace VBA k automatizaci relací aplikace Internet Explorer z tabulky Excel Použití automatizace VBA k automatizaci relací aplikace Internet Explorer z tabulky Excel Jeho integrace se systémem Windows umožňuje kontrolu aplikace Internet Explorer v řadě překvapivé způsoby, jak používat skript jazyka VBA (Visual Basic for Applications) z jakékoli aplikace, která jej podporuje, jako je Word, Outlook nebo Excel. , máte na mysli jméno, které dává smysl. Ve výše uvedeném příkladu mám list, ve kterém vytáhnu informace o síti, takže list zavolám “Síť”. Nyní v kódu, kdykoli chci odkazovat na síťový list, mohu to udělat rychle, aniž bych vyhledával, jaké číslo listu je.

2. Breaking namísto opakování

Jeden z nejčastějších problémů, který má novější programátor, má 6 životních návyků, které by vás mohly naučit programování dnes 6 Životních návyků, které by vás mohly programovat vás naučit dnes Vše důležité, co potřebujete vědět o životě úspěšného života, můžete získat z počítačového programu. Nevěříš mi? Číst dál. když začnou psát kód, správně pracuje se smyčkami. A protože tolik lidí, kteří používají Excel VBA, je velmi nováčkem v kódování, je špatné opakování epidemie.

Smyčkování je v Excelu velmi běžné, protože často zpracováváte datové hodnoty po celém řádku nebo sloupci, takže je musíte zpracovat všechny smyčky. Noví programátoři často chtějí jednoduše vypuknout ze smyčky (buď pro smyčku For nebo Look), když je splněna určitá podmínka.

Můžete ignorovat složitost výše uvedeného kódu, stačí si uvědomit, že uvnitř příkazu IF existuje možnost ukončit smyčku For, pokud je podmínka splněna. Zde je jednodušší příklad:

Pro x = 1 až 20 Pokud x = 6, pak Ukončete pro y = x + intRoomTemp Další i 

Noví programátoři používají tento přístup, protože je to snadné. Když nastane podmínka, na kterou čekáte, abyste ukončili smyčku, je pokušení z ní okamžitě vyskočit, je silné, ale nedělejte to.

Častěji než ne, kód, který přijde po tom “přestávka” je důležité zpracovat, a to i naposledy přes smyčku před ukončením. Mnohem čistší a profesionálnější způsob, jak zvládnout podmínky, kdy chcete nechat smyčku v polovině, je zahrnout tuto výstupní podmínku do něčeho podobného, ​​jako je prohlášení.

Zatímco (x> = 1 AND x<=20 AND x6) For x = 1 To 20 y = x + intRoomTemp Next i Wend 

To umožňuje logický tok vašeho kódu, s posledním průchodem, když x je 5, a pak elegantně opouští, jakmile se smyčka For počítá do 6. Není třeba zahrnovat nepříjemné příkazy EXIT nebo BREAK mid-loop.

3. Nepoužíváte pole

Další zajímavá chyba, kterou noví programátoři VBA Základy počítačového programování 101 - Proměnné a typy dat Základy počítačového programování 101 - Proměnné a typy dat Po představení a mluvení o objektově orientovaném programování dříve a odkud pochází jeho jmenovec, jsem si myslel, že je čas, abychom projít absolutními základy programování jiným způsobem než jazykem. Tato ... značka se pokouší zpracovat vše uvnitř četných vnořených smyček, které se během procesu výpočtu filtrují dolů mezi řádky a sloupci.

I když to může fungovat, mohlo by to také vést k velkým problémům s výkonem, pokud musíte neustále provádět stejné výpočty na stejných číslech ve stejném sloupci. Opakování tohoto sloupce a extrahování hodnot pokaždé není jen náročné na programování, je to zabiják na vašem procesoru. Účinnějším způsobem zpracování dlouhých seznamů čísel je využití pole.

Pokud jste nikdy dříve nepoužívali pole, nebojte se. Představte si pole jako zásobník na kostky ledu s určitým počtem “kostky” můžete vložit informace. Kostky jsou očíslovány od 1 do 12, a tak jste vy “dát” data do nich.

Pole můžete snadno definovat zadáním Dim arrMyArray (12) jako celé číslo.

Tím se vytvoří “zásobník” s 12 sloty pro vás k vyplnění.

Zde by mohl vypadat kód opakování řádků bez pole:

Sub Test1 () Dim x As Integer intNumRows = Range ("A2", Range ("A2"). End (xldown)). Rows.Count Range ("A2"). Vyberte pro x = 1 do intNumRows If Range (" Hodnota „& str (x)) < 100 then intTemp = (Range("A" & str(x)).value) * 32 - 100 End If ActiveCell.Offset(1, 0).Select Next End Sub 

V tomto příkladu je kód zpracováván skrz každou jednotlivou buňku v rozsahu a provádí výpočet teploty.

Později v programu, pokud byste někdy chtěli provést další výpočet na stejných hodnotách, budete muset tento kód duplikovat, zpracovat všechny tyto buňky a provést nový výpočet.

Nyní, pokud místo toho použijete pole, pak můžete uložit 12 hodnot v řádku do svého vhodného úložného pole. Pak později, kdykoli chcete provádět výpočty proti těmto číslům, jsou již v paměti a připraveni jít.

Sub Test1 () Dim x As Integer intNumRows = Range ("A2", Range ("A2"). End (xldown)). Rows.Count Range ("A2"). Vyberte pro x = 1 To intNumRows arrMyArray (x- 1) = Rozsah (hodnota „A“ a str (x)).) ActiveCell.Offset (1, 0) .Vyberte Next End Sub 

“x-1” pro směřování na prvek pole je nutné pouze proto, že smyčka For začíná na 1. Prvky pole musí začínat na 0.

Ale jakmile budete mít celé pole načtené s hodnotami z řádku, později v programu můžete pomocí pole seskupit všechny výpočty, které chcete.

Sub TempCalc () Pro x = 0 Na UBound (arrMyArray) arrMyTemps (y) = arrMyArray (x) * 32 - 100 Další End Sub 

Tento příklad prochází celým řádkem (UBound vám dává počet datových hodnot v poli), provádí výpočet teploty a poté jej vkládá do jiného pole s názvem arrMyTemps.

Uvidíte, jak jednodušší je druhý výpočetní kód. A od této chvíle, kdykoli chcete provést více výpočtů na stejné sadě čísel, je vaše pole již předinstalované a připravené k provozu.

4. Použití příliš mnoho odkazů

Ať už programujete v plnohodnotném jazyce Visual Basic nebo VBA, budete muset zahrnout “Reference” pro přístup k určitým funkcím, jako je přístup k databázi Access nebo zápis výstupu do textového souboru.

Odkazy jsou trochu podobné “knihovny” plné funkcí, na které můžete klepnout, pokud tento soubor povolíte. Odkazy najdete v pohledu vývojáře kliknutím na Nástroje v nabídce a poté klepnutím na Reference.

V tomto okně najdete všechny aktuálně vybrané reference pro váš aktuální projekt VBA.

Podle mých zkušeností se zde uvedené výběry mohou měnit z jedné instalace aplikace Excel na jinou a z jednoho počítače na druhé. Hodně záleží na tom, co s tímto počítačem udělali jiní lidé, zda byly přidány nebo povoleny určité doplňky nebo funkce, nebo zda někteří programátoři mohli použít určité reference v minulých projektech.

Důvod, proč je dobré tento seznam zkontrolovat, je ten, že zbytečné odkazy plýtvají zdroji systému. Pokud nepoužíváte žádnou manipulaci se soubory XML, proč vybrat Microsoft XML? Pokud nekomunikujete s databází, odeberte Microsoft DAO. Pokud nepíšete výstup do textového souboru, odeberte jej Microsoft Scripting Runtime.

Pokud si nejste jisti, co tyto vybrané reference dělají, stiskněte F2 a uvidíte Průzkumník objektů. V horní části tohoto okna si můžete vybrat referenční knihovnu, kterou chcete procházet.

Po výběru se zobrazí všechny objekty a dostupné funkce, o kterých se můžete dozvědět více.

Například když kliknu na knihovnu DAO, rychle se vyjasní, že jde o připojení a komunikaci s databázemi.

Snížení počtu referencí, které používáte ve svém programovacím projektu, je jen dobrý smysl a pomůže vám, aby vaše aplikace fungovala efektivněji.

Programování v Excelu VBA

Celá myšlenka, jak skutečně napsat kód v Excelu, ušetřit čas s textovými operacemi v Excelu, ušetřit čas s textovými operacemi v Excelu, dokáže dělat magii s čísly a zvládne stejně dobře znaky. Tato příručka ukazuje, jak analyzovat, převádět, nahrazovat a upravovat text v tabulkách. Tyto základy vám umožní provádět komplexní transformace. děsí mnoho lidí, ale tento strach opravdu není nutný. Visual Basic for Applications je velmi jednoduchý jazyk, který se lze naučit, a pokud budete postupovat podle výše uvedených základních běžných postupů, zajistíte, aby byl váš kód čistý, účinný a snadno srozumitelný.

Kódujete ve VBA? Jaké lekce jste se naučili během let, které můžete sdílet s ostatními čtenáři, kteří se poprvé učí VBA? Sdílejte své tipy v sekci komentářů níže!

Obrazové kredity: Počítač od www.BillionPhotos.com přes Shutterstock, Barevné tácky Ice Cube od hahauk přes Shutterstock.com