Програмиране на макроси в Excel: Пълно ръководство стъпка по стъпка

Informatec Digital » Програмиране » Програмиране на макроси в Excel: Пълно ръководство стъпка по стъпка

Ако се окажете, че изпълнявате едни и същи задачи в Excel многократно, макросите са най-добрият ви приятел за пестене на време. С тях можете да автоматизирате цели поредици от действия (научете повече за програмирането с VBA ), от форматиране до импортиране на данни или извършване на изчисления, и да ги задействате, когато пожелаете, с едно щракване или клавишна комбинация.

Когато създавате макрос, Excel записва вашите кликвания и натискания на клавиши и ги изпълнява точно отново. След това можете да промените кода, за да прецизирате изпълнението и да се консултирате с ресурси за Excel . Обичайна практика е да се започне с макро рекордера и след това да се усъвършенства с VBA, постигайки по-голям контрол, гъвкавост и нулеви грешки поради небрежност.

Какво точно е макрос в Excel?

Макросът е списък с инструкции, които Excel изпълнява в определен ред . Може да бъде толкова просто, колкото прилагането на процентен стил, или толкова сложно, колкото подготовката на отчет с обобщени таблици , форматирането му, създаването на работни листове и изпращането на имейл в Outlook. Макросите се запазват във вашата работна книга и могат да се изпълняват, когато е необходимо.

Не е нужно да знаете как да програмирате, за да започнете: рекордерът преобразува действията ви във VBA код. Въпреки това, някои познания за VBA ще ви позволят да създавате персонализирани функции, формуляри и дори мини-приложения в Excel.

Защо да използваме макроси? Реални предимства

Основната цел на макроса е да се избегнат повтарящи се задачи и да се минимизират грешките . Ако подготвяте един и същ отчет всеки месец, макросът ще ви спести механичните стъпки и ще ви позволи да се съсредоточите върху това, което е важно.

Предимствата му включват спестяване на време, последователно изпълнение (без пропуснати стъпки) и възможност за създаване на преки пътища и бутони за ускоряване на процесите . Ако използвате Excel само от време на време, може да не си струва; но ако го използвате ежедневно, ще забележите разликата още от първия ден.

Активиране на раздела „Разработчик“ (Windows и Mac)

Разделът „Разработчик“ е скрит по подразбиране. В Windows щракнете с десния бутон върху лентата и изберете „Персонализиране на лентата“. Отметнете „Разработчик“ и щракнете върху OK. След това ще видите раздела „Разработчик“ до „Изглед“.

На Mac отидете на Excel > Предпочитания > Ленти с инструменти и лента. Под „Основни раздели“ изберете „Разработчик“ и запазете. Това ще ви даде достъп до рекордера, VBA и контролите . Ако сте начинаещи в Excel, прегледайте основното ръководство за Excel , за да се запознаете с лентата и нейните опции.

Как да създавате макроси с Recorder

Диктофонът е идеален за начало. В „Разработчик“ > „Запис на макрос“ (или Alt+T+M+R в Windows) задайте описателно име. Запомнете: името трябва да започва с буква, без интервали или символи.

Изберете къде да запазите: „Тази работна книга“ ще работи в повечето случаи. Ако искате да я държите винаги достъпна, изберете „Лична работна книга с макроси“ и Excel ще я създаде/запази в Personal.xlsb , който се отваря скрит с Excel.

По желание, добавете пряк път. За да избегнете конфликти (например, загуба на Ctrl+Z), се препоръчва да използвате бързи клавиши с Ctrl+Shift. Можете също така да напишете кратко, ясно описание (много полезно, когато имате много макроси).

Щракнете върху OK и изпълнете действията, които ще бъдат записани. Например: изберете A1 и приложете формат „Процент“. Избягвайте ненужни щраквания или промени в селекцията, които не са част от предвидения процес, защото записващото устройство записва почти всичко.

Когато приключите, спрете записа. За да го стартирате, отидете на Разработчик > Макроси (или Alt+F8), изберете макроса си и щракнете върху Изпълни. Ще видите, че Excel повтаря стъпките в абсолютно същия ред.

Изпълнявайте, редактирайте и управлявайте вашите макроси

От „Разработчик“ > „Макроси“ (Alt+F8) можете да изпълнявате, редактирате или изтривате макроси. Ако изберете „Редактиране“, редакторът на Visual Basic ще се отвори със записания код, който можете да почистите и оптимизирате (често включва излишни стъпки).

Присвояването на макрос на бутон или форма е много удобно: вмъкнете форма, щракнете с десния бутон > Присвои макрос, изберете макроса и сте готови. Когато щракнете върху формата, той ще се изпълни мигновено.

За да го поддържате леснодостъпен в лентата с инструменти за бърз достъп, отворете менюто на лентата с инструменти (стрелка надолу) > Още команди > под „Команди, налични в“, изберете „Макроси“, добавете вашите и променете иконата, ако желаете. Сега ще имате постоянен бутон, дори когато не сте в режим за разработчици.

Ако е необходимо да използвате повторно макроса в друга работна книга, можете да копирате модула от редактора на Visual Basic. Отворете VBE (Alt+F11), плъзнете модула в другата отворена работна книга или използвайте „Експортиране/импортиране на .bas файл“.

Програмиране на макроси в Excel с редактора на Visual Basic (VBA)

За да отидете отвъд рекордера, отворете редактора с Alt+F11. Вмъкнете модул (Insert > Module) и създайте първата си подпрограма. Основният модел е Sub Name() … End Sub.

Например, за да покажете съобщение и да запишете стойност в клетка A1, можете да започнете с нещо просто. По този начин ще разберете структурата и как да извикате макроса от Excel (Alt+F8) или да го присвоите на бутон, само с много малко редове код :

Sub Primera_Macro()
    Range("A1").Value = "Esto de las macros mola."
    MsgBox "Hola, querido amigo."
End Sub

Друг бърз пример за прилагане на процентния стил към текущата селекция. Този макрос повтаря това, което направихте с recorder-а , но го написахте директно:

Sub FormatoPorcentaje()
    Selection.Style = "Percent"
End Sub

Идеалната комбинация е: запишете, за да ускорите нещата, след което отворете VBE, за да почистите ненужните стъпки, да добавите променливи, условия или цикли. По този начин превръщате „буквален“ запис в надежден и многократно използваем макрос.

Абсолютни срещу относителни препратки

По подразбиране, рекордерът създава макроси в абсолютен режим: той винаги действа върху едни и същи клетки (например B3), независимо къде се намирате, когато го изпълнявате. Ако данните променят позицията си във вашия процес, това може да не е това, от което се нуждаете.

С „Използване на относителни препратки“, макросът работи спрямо активната клетка, когато започне изпълнението. Например, ако сте започнали запис в клетка B3 и сте въвели „Име“, „Фамилия“ и т.н., движейки се надясно, когато работите от клетка C4, той ще въведе същата информация спрямо клетки C4, D4, E4 и т.н.

Типичен пример: добавяне на заглавия към таблица, която понякога започва в клетка B3, а понякога в клетка C5. При абсолютен режим винаги ще се записва в клетка B3. При относителен режим ще се адаптира към избраната начална клетка.

Често срещани грешки при записване на процеси

Често срещан недостатък е записването на формула с „плъзгане“ например за три реда и мисленето, че тя ще работи за по-дълги таблици. Записващото устройство ще регистрира нещо еквивалентно на „ запълване на три клетки надолу “, а не „до края на таблицата“.

Решението е да промените подхода си: използвайте динамични диапазони (например, откриване на последния ред), цикли или метода CurrentRegion, или коригирайте макроса във VBA след записването му, така че да се мащабира до произволен размер.

Добри практики при записване

Преди записване, превърнете процеса ръчно няколко пъти, за да го усъвършенствате. Правете кратки, специфични записи вместо един огромен; това ги прави по-лесни за поддръжка и комбиниране.

Избягвайте ненужни кликвания, превъртане през лентата и избор на елементи, които не допринасят с нищо. Поддържайте името и описанието на макроса ясни, за да можете вие ​​или който и да е колега веднага да разпознаете предназначението му.

Изпълнявайте макроси по много начини

В допълнение към диалоговия прозорец „Макроси“ (Alt+F8), можете да стартирате макроси с помощта на фигура, бутон в лентата с инструменти за бърз достъп или като отворите работната книга. Можете дори да накарате Excel да изпълнява макроса от други приложения на Office, което ви позволява да автоматизирате задачи между приложенията (например актуализиране на таблица и изпращане на имейл чрез Outlook).

Помислете внимателно за клавишните комбинации: ако присвоите Ctrl+Z на макрос, ще загубите възможността за отмяна, докато работната книга е отворена. Затова се препоръчва да използвате комбинации от Ctrl+Shift , за да избегнете конфликти.

Запазване на работни книги с макроси

Когато даден файл съдържа VBA код, запазете го като .xlsm файл, за да може кодът да се запази и да може да се изпълни. Запазването като .xlsx файл ще унищожи VBA проекта . Ако искате глобални макроси, използвайте Личната работна книга с макроси (Personal.xlsb).

Сигурност и активиране на макроси

Макросите може да бъдат блокирани в зависимост от настройките ви за сигурност. Проверете Центъра за доверие, за да ги активирате избирателно, и никога не активирайте макроси от неизвестни източници. Разбирането на нивото ви на сигурност ви помага да балансирате защитата и производителността.

Присвояване на макроси към бутони, форми и контроли

За да позволите на потребителите да изпълняват макроси автоматично, присвоете макрос на бутон, форма или контрола. Щракнете с десния бутон върху обекта > Присвояване на макрос > избор. Можете също да поставите икона на лентата или лентата с инструменти за бърз достъп и да я свържете с макроса.

Ако използвате формуляри или ActiveX контроли, можете да им присвоите макроси или процедури за събития (например при щракване). Това позволява по-лесни за ползване интерфейси и насочвани процеси.

Полезни примери за макроси (VBA)

Тези примери илюстрират често срещани задачи. Можете да ги поставите в модул (Alt+F11 > Вмъкване > Модул) и да ги адаптирате. Не забравяйте да подпишете работните книги или да коригирате настройките за сигурност, ако вашата организация го изисква, за да можете да ги изпълнявате без предупреждения.

Копиране на избраното съдържание на друго място в същия лист: Selection.Copy Destination

Sub CopiarSeleccion()
    If TypeName(Selection) = "Range" Then
        Selection.Copy Destination:=Selection.Offset(0, 2)
    Else
        MsgBox "Selecciona un rango de celdas primero."
    End If
End Sub

Отпечатване на активния лист: ActiveSheet.PrintOut

Sub ImprimirHojaActual()
    ActiveSheet.PrintOut
End Sub

Запазете файла като работна книга с активирани макроси (.xlsm): ActiveWorkbook.SaveAs

Sub GuardarComoXlsm()
    Dim ruta As String
    ruta = ThisWorkbook.Path & Application.PathSeparator & "LibroConMacros.xlsm"
    ActiveWorkbook.SaveAs Filename:=ruta, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

Приложете форматиране към диапазон (коригирайте диапазона според вашите нужди): С Range(…)

Sub FormatoRango()
    With Range("A1:D10")
        .Font.Bold = True
        .Interior.Color = RGB(230, 230, 250)
        .Borders.LineStyle = xlContinuous
    End With
End Sub

Намерете стойност в диапазон и покажете първото съвпадение: Range(…).Find

Sub BuscarValor()
    Dim celda As Range
    Dim valor As String
    valor = InputBox("¿Qué valor quieres buscar?")
    If valor = "" Then Exit Sub
    Set celda = Range("A1:D1000").Find(What:=valor, LookIn:=xlValues, LookAt:=xlPart)
    If Not celda Is Nothing Then
        MsgBox "Encontrado en: " & celda.Address
        celda.Select
    Else
        MsgBox "No se encontró el valor especificado."
    End If
End Sub

Практически случай: форматиране като процент

Запис: Стартирайте записа, дайте му име, изберете къде да го запазите и по желание задайте пряк път и описание. Изберете клетките и приложете „Процент“ от раздела „Начало“. Спрете записа и след това го пуснете отново с Alt+F8. Ако искате да го възпроизведете с минималистичен код, използвайте Selection.Style = „Процент“ във VBA.

Съвет: По време на запис избягвайте ненужното превключване между клетки. Тези движения напред-назад се записват и могат да забавят и повредят макроса.

Автоматизирайте дълги задачи: малките макроси са по-добри

Ако имате процес с много стъпки, помислете за разделянето му на няколко по-малки макроса и извикването им последователно. Това улеснява поддръжката, позволява ви да използвате повторно блокове и опростява отстраняването на грешки.

Например, един макрос за подготовка на данни, друг за форматиране, друг за генериране на разпечатка и друг за експортиране в PDF или изпращане по имейл . Можете да ги извиквате един по един или всички наведнъж.

Редактиране и почистване на записания код

Рекордерът записва „всичко“: селекции, щраквания и промени в табулациите. Когато VBE се отвори, той премахва ненужните селекции (Select/Selection), като ги замества с директни препратки (например, Range("A1").Value = ... ) и групира форматите в блокове With/End With.

В допълнение, добавя променливи и основна обработка на грешки. Няколко if оператора и прости проверки предотвратяват грешки, когато диапазонът е празен или селекцията не е според очакванията.

Често задавани въпроси

Как да създам потребителски формуляр в Excel? Отворете редактора на Visual Basic (Alt+F11), отидете на Вмъкване > Потребителски формуляр и добавете контроли (текстови полета, бутони и др.). Можете да го покажете с помощта на макрос.

Sub MostrarFormulario()
    UserForm1.Show
End Sub

Оттам, присвоете процедури на събитията на контролите (например, щракване върху бутон), за да изпълните необходимата ви логика.

Как да защитя/премахна защитата на лист с макроси? Използвайте тези процедури, като промените паролата по желание. Те ви позволяват да активирате или деактивирате защитата с едно щракване.

Sub ProtegerHoja()
    ActiveSheet.Protect Password:="segura", AllowFiltering:=True
End Sub

Sub DesprotegerHoja()
    ActiveSheet.Unprotect Password:="segura"
End Sub

Как да изтрия макрос? Отидете на Разработчик > Макроси, изберете макроса и натиснете Изтрий. Ако искате да изтриете целия код от модул, отворете VBE (Alt+F11), намерете модула в проекта, щракнете с десния бутон и изберете Премахване на модул (можете първо да го експортирате, ако искате да го запазите).

Един последен практичен съвет: Ако форматирате месечен отчет, където клиентите с непогасени салда трябва да бъдат маркирани в червено и удебелен шрифт, запишете последователността веднъж, коригирайте VBA кода, за да се приложи към правилния диапазон (използвайки откриване на последния ред или филтри) и запазете макроса в личната си работна книга с макроси. Следващия път, един прост пряк път или бутон ще е достатъчен, за да приложите форматирането мигновено, безупречно, за секунди.