содржина
Дури и ако не знаете како да програмирате, има многу места (книги, веб-страници, форуми) каде што можете да најдете готов VBA макро код за огромен број типични задачи во Excel. Според моето искуство, повеќето корисници порано или подоцна ја собираат својата лична колекција на макроа за да ги автоматизираат рутинските процеси, без разлика дали се работи за преведување формули во вредности, прикажување суми во зборови или сумирање на ќелиите по боја. И тука се појавува проблемот - макро кодот во Visual Basic треба да се складира некаде за да се користи подоцна во работата.
Најлесната опција е да го зачувате макро кодот директно во работната датотека со одење до уредникот на Visual Basic користејќи ја кратенката на тастатурата Alt+F11 и додавање на нов празен модул преку менито Вметни – Модул:
Сепак, овој метод има неколку недостатоци:
- Ако има многу работни датотеки, а макрото е потребно насекаде, како макро за конвертирање формули во вредности, тогаш ќе мора да го копирате кодот во секоја книга.
- Не смее да се заборави зачувајте датотека во макро-овозможен формат (xlsm) или во формат на бинарна книга (xlsb).
- При отворање на таква датотека макро заштита секој пат ќе издава предупредување што треба да се потврди (добро, или целосно да се оневозможи заштитата, што можеби не е секогаш пожелно).
Поелегантно решение би било да се создаде свој додаток (додаток на Excel) – посебна датотека со посебен формат (xlam) која ги содржи сите ваши „омилени“ макроа. Предностите на овој пристап:
- Ќе биде доволно поврзете го додатокот еднаш во Excel – и можете да ги користите неговите VBA процедури и функции во која било датотека на овој компјутер. Според тоа, не е потребно повторно зачувување на вашите работни датотеки во формати xlsm- и xlsb, бидејќи. изворниот код нема да биде зачуван во нив, туку во датотеката со додаток.
- заштита нема да ви пречат ниту макроата. Додатоците се, по дефиниција, доверливи извори.
- Може да направи посебен таб на лентата на Excel со убави копчиња за извршување макроа за додатоци.
- Додатокот е посебна датотека. Неговиот лесен за носење од компјутер до компјутер, споделете го со колегите или дури и продадете го 😉
Ајде да го разгледаме целиот процес на создавање на сопствен додаток на Microsoft Excel чекор по чекор.
Чекор 1. Направете дополнителна датотека
Отворете Microsoft Excel со празна работна книга и зачувајте ја под кое било соодветно име (на пример MyExcelAddin) во формат за додаток со командата Датотека - Зачувај како или клучеви F12, наведувајќи го типот на датотеката Додаток за Excel:
Ве молиме имајте предвид дека стандардно Excel складира додатоци во папката C:UsersYour_nameAppDataRoamingMicrosoftAddIns, но, во принцип, можете да наведете која било друга папка што е погодна за вас.
Чекор 2. Ние го поврзуваме креираниот додаток
Сега додатокот што го создадовме во последниот чекор MyExcelAddin мора да биде поврзан со Excel. За да го направите ова, одете во менито Датотека – Опции – Додатоци (Датотека - Опции - Додатоци), кликнете на копчето За нас (Оди) на дното на прозорецот. Во прозорецот што се отвора, кликнете на копчето преглед (Прелистајте) и наведете ја локацијата на нашата дополнителна датотека.
Ако сте направиле се како што треба, тогаш нашите MyExcelAddin треба да се појави во списокот со достапни додатоци:
Чекор 3. Додадете макроа во додатокот
Нашиот додаток е поврзан со Excel и работи успешно, но сè уште нема ниту едно макро во него. Ајде да го наполниме. За да го направите ова, отворете го уредникот на Visual Basic со кратенката на тастатурата Alt+F11 или со копче Visual Basic табот инвеститорот (програмер). Ако јазичиња инвеститорот не се гледа, може да се прикаже преку Датотека – Опции – Поставување лента (Датотека - Опции - Приспособете ја лентата).
Треба да има прозорец во горниот лев агол на уредникот Проект (ако не е видливо, тогаш вклучете го преку менито Преглед - Проект Explorer):
Овој прозорец ги прикажува сите отворени работни книги и активните додатоци на Microsoft Excel, вклучувајќи ги и нашите. VBAпроект (MyExcelAddin.xlam) Изберете го со глувчето и додајте нов модул на него преку менито Вметни – Модул. Во овој модул, ќе го складираме VBA кодот на нашите дополнителни макроа.
Можете или да го напишете кодот од нула (ако знаете да програмирате), или да го копирате од некаде готово (што е многу полесно). Ајде, за тестирање, да го внесеме кодот на едноставно, но корисно макро во додадениот празен модул:
Откако ќе го внесете кодот, не заборавајте да кликнете на копчето за зачувување (дискета) во горниот лев агол.
Нашето макро Формули за вредности, како што можете лесно да замислите, ги конвертира формулите во вредности во претходно избраниот опсег. Понекогаш овие макроа се нарекуваат и процедури. За да го извршите, треба да изберете ќелии со формули и да отворите специјално поле за дијалог Макроа од јазичето инвеститорот (Програмер - макроа) или кратенка на тастатура Alt+F8. Вообичаено, овој прозорец прикажува достапни макроа од сите отворени работни книги, но макроата за додатоци не се видливи овде. И покрај ова, можеме да го внесеме името на нашата постапка во полето макро име (Макро име)и потоа кликнете на копчето Испратена (трчај) – и нашето макро ќе работи:
| |
Овде можете исто така да доделите кратенка на тастатурата за брзо стартување на макро - копчето е одговорно за ова параметри (Опции) во претходниот прозорец Макро:
Кога доделувате копчиња, имајте на ум дека тие се чувствителни на големи букви и чувствителни на распоредот на тастатурата. Значи, ако доделите комбинација како Ctrl+Й, тогаш, всушност, во иднина ќе треба да се уверите дека сте го вклучиле распоредот и дополнително да притиснете Префрлатза да се добие големата буква.
За погодност, можеме да додадеме и копче за нашето макро во лентата со алатки за брз пристап во горниот лев агол на прозорецот. За да го направите ова, изберете Датотека – Опции – Лента со алатки за брз пристап (Датотека - Опции - Приспособете ја лентата со алатки за брз пристап), а потоа во паѓачката листа на врвот на прозорецот опцијата Макроа. После тоа нашето макро Формули за вредности може да се постави на панелот со копчето Додај (Додади) и изберете икона за неа со копчето Промени (Уреди):
Чекор 4. Додадете функции на додатокот
Но макро-процедури, исто така има функционални макроа или како што се нарекуваат УДФ (Функција дефинирана од корисникот = функција дефинирана од корисникот). Ајде да создадеме посебен модул во нашиот додаток (команда за мени Вметни – Модул) и залепете го кодот на следната функција таму:
Лесно е да се види дека оваа функција е потребна за да се извлече ДДВ од износот со вклучен ДДВ. Не Њутновиот бином, се разбира, но ќе ни помогне како пример за да ги покажеме основните принципи.
Забележете дека синтаксата на функцијата е различна од процедурата:
- се користи конструкција Функција…. Крајна функција наместо Под … Крај Под
- по името на функцијата, нејзините аргументи се означени во загради
- во телото на функцијата се вршат потребните пресметки и потоа резултатот се доделува на променлива со име на функцијата
Исто така, забележете дека оваа функција не е потребна и дека е невозможно да се изврши како претходната макро процедура низ полето за дијалог Макроа и копче Испратена. Таквата макро функција треба да се користи како стандардна функција на работниот лист (SUM, IF, VLOOKUP…), т.е. само внесете во која било ќелија, наведувајќи ја вредноста на износот со ДДВ како аргумент:
… или внесете преку стандардниот дијалог прозорец за вметнување функција (копче fx во лентата со формули), избирајќи категорија Дефинирано од корисник (Кориснички дефинирано):
Единствениот непријатен момент овде е отсуството на вообичаен опис на функцијата на дното на прозорецот. За да го додадете ќе треба да го направите следново:
- Отворете го уредувачот на Visual Basic со кратенка на тастатурата Alt+F11
- Изберете го додатокот во панелот Project и притиснете го копчето F2за да го отворите прозорецот Object Browser
- Изберете го вашиот проект за додаток од паѓачката листа на врвот на прозорецот
- Десен-клик на функцијата што се појавува и изберете команда Својства.
- Внесете опис на функцијата во прозорецот Опис
- Зачувајте ја дополнителната датотека и рестартирајте го Excel.
По рестартирањето, функцијата треба да го прикаже описот што го внесовме:
Чекор 5. Направете таб за додаток во интерфејсот
Конечниот, иако не задолжителен, но пријатен допир ќе биде создавањето на посебен таб со копче за стартување на нашето макро, кое ќе се појави во интерфејсот на Excel откако ќе го поврзете нашиот додаток.
Информациите за јазичињата што се стандардно прикажани се содржани во книгата и мора да бидат форматирани во посебен XML код. Најлесен начин да се напише и уредува таков код е со помош на специјални програми - XML уредници. Една од најзгодните (и бесплатни) е програмата на Максим Новиков Лента XML уредник.
Алгоритмот за работа со него е како што следува:
- Затворете ги сите прозорци на Excel за да нема конфликт на датотеки кога го уредуваме додатокот XML-код.
- Стартувајте ја програмата Ribbon XML Editor и отворете ја нашата датотека MyExcelAddin.xlam во неа
- Со копче јазичиња во горниот лев агол, додајте го парчето код за новата картичка:
- Треба да ставите празни наводници id нашиот таб и група (било кој единствен идентификатор), и во етикета – имињата на нашата картичка и група копчиња на неа:
- Со копче копчето. на левиот панел, додадете празен код за копчето и додадете ознаки на него:
- етикета е текстот на копчето
- слика Mso — ова е условното име на сликата на копчето. Користев икона со црвено копче наречена AnimationCustomAddExitDialog. Имињата на сите достапни копчиња (и ги има неколку стотици!) може да се најдат на голем број страници на Интернет ако ги пребарувате клучните зборови „imageMso“. За почеток, можете да одите овде.
- на Акција – ова е името на постапката за повратен повик – специјално кратко макро кое ќе го води нашето главно макро Формули за вредности. Оваа постапка можете да ја наречете како што сакате. Ќе го додадеме малку подоцна.
- Можете да ја проверите исправноста на сè што е направено користејќи го копчето со зелена ознака за проверка на горниот дел од лентата со алатки. На истото место, кликнете на копчето со флопи диск за да ги зачувате сите промени.
- Затворете го Ribbon XML Editor
- Отворете Excel, одете во уредникот на Visual Basic и додајте процедура за повратен повик во нашето макро KillFormulasтака што го извршува нашето главно макро за замена на формулите со вредности.
- Ги зачувуваме промените и, враќајќи се во Excel, го проверуваме резултатот:
Тоа е сè - додатокот е подготвен за употреба. Пополнете го со сопствени процедури и функции, додајте убави копчиња – и ќе ви биде многу полесно да користите макроа во вашата работа.
- Што се макроа, како да ги користите во вашата работа, каде да добиете макро код во Visual Basic.
- Како да направите поздравен екран кога отворате работна книга во Excel
- Што е лична макро книга и како да се користи