Когато работите с Excel, по -голямата част от времето ви прекарва в областта на работния лист - справяне с клетки и диапазони.
И ако искате да автоматизирате работата си в Excel с помощта на VBA, трябва да знаете как да работите с клетки и диапазони, използвайки VBA.
Има много различни неща, които можете да правите с диапазони във VBA (като избор, копиране, преместване, редактиране и т.н.).
За да покрия тази тема, ще разбия този урок на раздели и ще ви покажа как да работите с клетки и диапазони в Excel VBA, използвайки примери.
Да започваме.
Всички кодове, които споменавам в този урок, трябва да бъдат поставени във VB Editor. Отидете в раздела „Къде да поставите VBA кода“, за да знаете как работи.Ако се интересувате да научите VBA по лесния начин, вижте моя Онлайн обучение по Excel VBA.
Избор на клетка / диапазон в Excel с помощта на VBA
За да работите с клетки и диапазони в Excel с помощта на VBA, не е нужно да го избирате.
В повечето случаи е по -добре да не избирате клетки или диапазони (както ще видим).
Въпреки това е важно да преминете през този раздел и да разберете как работи. Това ще бъде от решаващо значение за вашето обучение по VBA и много концепции, обхванати тук, ще бъдат използвани в целия урок.
Така че нека започнем с един много прост пример.
Избор на единична клетка с помощта на VBA
Ако искате да изберете една клетка в активния лист (да речем A1), тогава можете да използвате кода по -долу:
Sub SelectCell () Range ("A1"). Изберете End Sub
Горният код има задължителните части „Sub“ и „End Sub“ и ред код, който избира клетка A1.
Диапазон („A1“) казва на VBA адреса на клетката, към която искаме да се обърнем.
Изберете е метод на обекта Range и избира клетките/диапазона, посочени в обекта Range. Препратките към клетките трябва да бъдат заключени в двойни кавички.
Този код би показал грешка в случай, че лист с диаграма е активен лист. Листът с диаграми съдържа диаграми и не се използва широко. Тъй като в него няма клетки/диапазони, горният код не може да го избере и в крайна сметка ще покаже грешка.
Имайте предвид, че тъй като искате да изберете клетката в активния лист, просто трябва да посочите адреса на клетката.
Но ако искате да изберете клетката в друг лист (да речем Sheet2), първо трябва да активирате Sheet2 и след това да изберете клетката в него.
Sub SelectCell () Работни листове ("Sheet2"). Активирайте диапазона ("A1"). Изберете End Sub
По същия начин можете също да активирате работна книга, след това да активирате определен работен лист в нея и след това да изберете клетка.
Sub SelectCell () Работни книги ("Book2.xlsx"). Работни листове ("Sheet2"). Активирайте диапазон ("A1"). Изберете End Sub
Обърнете внимание, че когато се позовавате на работни книги, трябва да използвате пълното име заедно с разширението на файла (.xlsx в горния код). В случай, че работната книга никога не е била запазена, не е необходимо да използвате разширението на файла.
Сега тези примери не са много полезни, но по -късно в този урок ще видите как можем да използваме същите концепции за копиране и поставяне на клетки в Excel (използвайки VBA).
Точно както избираме клетка, можем да избираме и диапазон.
В случай на диапазон, това може да бъде диапазон с фиксиран размер или диапазон с променлив размер.
В диапазон с фиксиран размер ще знаете колко голям е обхватът и можете да използвате точния размер във вашия VBA код. Но с диапазон с променлив размер, нямате представа колко голям е диапазонът и трябва да използвате малко VBA магия.
Нека да видим как да направим това.
Избор на фиксиран размер
Ето кода, който ще избере диапазона A1: D20.
Sub SelectRange () Диапазон ("A1: D20"). Изберете End Sub
Друг начин да направите това е да използвате кода по -долу:
Sub SelectRange () Диапазон ("A1", "D20"). Изберете End Sub
Горният код взема горния ляв адрес на клетката (A1) и долния десен адрес на клетката (D20) и избира целия диапазон. Тази техника става полезна, когато работите с диапазони с променлив размер (както ще видим, когато свойството End бъде обхванато по -късно в този урок).
Ако искате подборът да се извърши в друга работна книга или различен работен лист, тогава трябва да кажете на VBA точните имена на тези обекти.
Например, кодът по -долу би избрал диапазона A1: D20 в работен лист Sheet2 в работната книга Book2.
Sub SelectRange () Работни книги ("Book2.xlsx"). Работни листове ("Sheet1"). Активирайте диапазона ("A1: D20"). Изберете End Sub
Какво ще стане, ако не знаете колко редове има. Ами ако искате да изберете всички клетки, които имат стойност в него.
В тези случаи трябва да използвате методите, показани в следващия раздел (за избор на диапазон с променлив размер).
Избор на диапазон с променлив размер
Има различни начини, по които можете да изберете диапазон от клетки. Избраният от вас метод ще зависи от това как са структурирани данните.
В този раздел ще разгледам някои полезни техники, които са наистина полезни, когато работите с диапазони във VBA.
Изберете Използване на свойството CurrentRange
В случаите, когато не знаете колко редове/колони имат данните, можете да използвате свойството CurrentRange на обекта Range.
Свойството CurrentRange обхваща всички непрекъснати запълнени клетки в диапазон от данни.
По -долу е кодът, който ще избере текущия регион, който съдържа клетка A1.
Sub SelectCurrentRegion () Диапазон ("A1"). CurrentRegion. Изберете End Sub
Горният метод е добър, когато имате всички данни като таблица без празни редове/колони в нея.
Но в случай, че имате празни редове/колони във вашите данни, той няма да избере тези след празните редове/колони. На изображението по -долу кодът на CurrentRegion избира данни до ред 10, тъй като ред 11 е празен.
В такива случаи може да искате да използвате свойството UsedRange на обекта на работния лист.
Изберете Използване на свойството UsedRange
UsedRange ви позволява да се позовавате на всички клетки, които са били променени.
Така че кодът по -долу ще избере всички използвани клетки в активния лист.
Sub SelectUsedRegion () ActiveSheet.UsedRange.Select End Sub
Имайте предвид, че в случай, че имате отдалечена клетка, която е била използвана, тя ще бъде взета предвид от горния код и всички клетки, докато тази използвана клетка не бъде избрана.
Изберете Използване на крайното свойство
Сега тази част е наистина полезна.
Свойството End ви позволява да изберете последната попълнена клетка. Това ви позволява да имитирате ефекта на клавиша със стрелка надолу/нагоре или клавишите за управление надясно/наляво.
Нека се опитаме да разберем това с помощта на пример.
Да предположим, че имате набор от данни, както е показано по -долу и искате бързо да изберете последните попълнени клетки в колона А.
Проблемът тук е, че данните могат да се променят и не знаете колко клетки са запълнени. Ако трябва да направите това с клавиатура, можете да изберете клетка A1 и след това да използвате Control + клавиш със стрелка надолу и тя ще избере последната попълнена клетка в колоната.
Сега нека да видим как да направите това с помощта на VBA. Тази техника е полезна, когато искате бързо да преминете към последната запълнена клетка в колона с променлив размер
Sub GoToLastFilledCell () Диапазон ("A1"). Край (xlDown). Изберете End Sub
Горният код би скочил до последната попълнена клетка в колона А.
По същия начин можете да използвате End (xlToRight), за да преминете към последната попълнена клетка в ред.
Sub GoToLastFilledCell () Диапазон ("A1"). Край (xlToRight). Изберете End Sub
Какво ще стане, ако искате да изберете цялата колона, вместо да прескачате до последната попълнена клетка.
Можете да направите това, като използвате кода по -долу:
Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown)). Изберете End Sub
В горния код използвахме първата и последната препратка на клетката, която трябва да изберем. Без значение колко запълнени клетки има, горният код ще избере всички.
Спомнете си примера по -горе, когато избрахме диапазона A1: D20, като използваме следния ред код:
Обхват („A1 ″,„ D20 “)
Тук A1 е горната лява клетка и D20 е долната дясна клетка в диапазона. Можем да използваме същата логика при избора на диапазони с променлив размер. Но тъй като не знаем точния адрес на долната дясна клетка, използвахме свойството End, за да го получим.
В Range (“A1”, Range (“A1”). End (xlDown)), “A1” се отнася до първата клетка и Range (“A1”). End (xlDown) се отнася до последната клетка. Тъй като сме предоставили и двете препратки, методът Select избира всички клетки между тези две препратки.
По същия начин можете също да изберете цял набор от данни, който има множество редове и колони.
Кодът по -долу би избрал всички попълнени редове/колони, започвайки от клетка A1.
Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown). End (xlToRight)). Изберете End Sub
В горния код използвахме Range (“A1”). End (xlDown) .End (xlToRight), за да получим препратката към долната дясна запълнена клетка на набора от данни.
Разлика между използване на CurrentRegion и End
Ако се чудите защо да използвате свойството End, за да изберете запълнения диапазон, когато имаме свойството CurrentRegion, нека ви кажа разликата.
Със свойството End можете да посочите началната клетка. Например, ако имате вашите данни в A1: D20, но първият ред са заглавки, можете да използвате свойството End, за да изберете данните без заглавките (използвайки кода по -долу).
Sub SelectFilledCells () Range ("A2", Range ("A2"). End (xlDown). End (xlToRight)). Изберете End Sub
Но CurrentRegion автоматично ще избере целия набор от данни, включително заглавките.
Досега в този урок видяхме как да се позоваваме на редица клетки, използвайки различни начини.
Нека сега да видим някои начини, по които всъщност можем да използваме тези техники, за да свършим някаква работа.
Копирайте клетки / диапазони с помощта на VBA
Както споменах в началото на този урок, избирането на клетка не е необходимо за извършване на действия върху нея. В този раздел ще видите как да копирате клетки и диапазони, без дори да ги избирате.
Нека започнем с прост пример.
Копиране на единична клетка
Ако искате да копирате клетка A1 и да я поставите в клетка D1, кодът по -долу ще го направи.
Sub CopyCell () Диапазон ("A1"). Copy Range ("D1") End Sub
Имайте предвид, че методът за копиране на обекта диапазон копира клетката (също като Control +C) и я поставя в посочената дестинация.
В горния примерен код дестинацията е посочена в същия ред, където използвате метода Copy. Ако искате да направите кода си още по -четим, можете да използвате кода по -долу:
Sub CopyCell () Диапазон ("A1"). Дестинация за копиране: = Range ("D1") End Sub
Горните кодове ще копират и поставят стойността, както и форматирането/формулите в нея.
Както може би вече сте забелязали, горният код копира клетката, без да я избира. Без значение къде се намирате на работния лист, кодът ще копира клетка A1 и ще я постави на D1.
Също така имайте предвид, че горният код би презаписал всеки съществуващ код в клетка D2. Ако искате Excel да ви уведоми, ако вече има нещо в клетка D1, без да го презаписвате, можете да използвате кода по -долу.
Sub CopyCell () If Range ("D1") "" Тогава Response = MsgBox ("Искате ли да презапишете съществуващите данни", vbYesNo) End If If Response = vbYes then Range ("A1"). Copy Range ("D1 ") End If End Sub
Копиране на фиксиран размер
Ако искате да копирате A1: D20 в J1: M20, можете да използвате кода по -долу:
Sub CopyRange () Диапазон ("A1: D20"). Обхват на копиране ("J1") End Sub
В целевата клетка просто трябва да посочите адреса на горната лява клетка. Кодът автоматично ще копира точния копиран диапазон в местоназначението.
Можете да използвате същата конструкция, за да копирате данни от един лист в друг.
Кодът по -долу ще копира A1: D20 от активния лист в Sheet2.
Sub CopyRange () Диапазон ("A1: D20"). Копиране на работни листове ("Sheet2"). Диапазон ("A1") Краен под
Горното копира данните от активния лист. Затова се уверете, че листът с данни е активният лист, преди да стартирате кода. За да сте в безопасност, можете също да посочите името на работния лист, докато копирате данните.
Sub CopyRange () Работни листове ("Sheet1"). Диапазон ("A1: D20"). Копиране на работни листове ("Sheet2"). Range ("A1") End Sub
Хубавото на горния код е, че без значение кой лист е активен, той винаги ще копира данните от Sheet1 и ще ги постави в Sheet2.
Можете също да копирате именуван диапазон, като използвате името му вместо препратката.
Например, ако имате именуван диапазон, наречен „SalesData“, можете да използвате кода по -долу, за да копирате тези данни в Sheet2.
Sub CopyRange () Диапазон ("SalesData"). Копиране на работни листове ("Sheet2"). Диапазон ("A1") Краен под
Ако обхватът на именувания диапазон е цялата работна книга, не е нужно да сте на листа, който има именувания диапазон, за да стартирате този код. Тъй като посоченият диапазон е обхванат за работната книга, можете да получите достъп до него от всеки лист, като използвате този код.
Ако имате таблица с името Table1, можете да използвате кода по -долу, за да я копирате в Sheet2.
Sub CopyTable () Диапазон ("Таблица1 [#All]"). Копиране на работни листове ("Sheet2"). Диапазон ("A1") Краен под
Можете също да копирате диапазон в друга работна книга.
В следния пример копирам таблицата на Excel (Таблица1) в работната книга Book2.
Sub CopyCurrentRegion () Диапазон ("Таблица1 [#All]"). Копиране на работни книги ("Book2.xlsx"). Работни листове ("Sheet1"). Диапазон ("A1") Краен под
Този код ще работи само ако работната книга вече е отворена.
Копиране на диапазон с променлив размер
Един от начините за копиране на диапазони с променлив размер е да ги преобразувате в именовани диапазони или Excel таблица и да използвате кодовете, както е показано в предишния раздел.
Но ако не можете да направите това, можете да използвате свойството CurrentRegion или End на обекта range.
Кодът по -долу ще копира текущия регион в активния лист и ще го постави в Sheet2.
Sub CopyCurrentRegion () Диапазон ("A1"). CurrentRegion.Copy Работни листове ("Sheet2"). Диапазон ("A1") Краен под
Ако искате да копирате първата колона от вашия набор от данни до последната попълнена клетка и да я поставите в Sheet2, можете да използвате кода по -долу:
Sub CopyCurrentRegion () Диапазон ("A1", Диапазон ("A1"). Край (xlDown)). Копиране на работни листове ("Sheet2"). Диапазон ("A1") Краен под
Ако искате да копирате редовете, както и колоните, можете да използвате кода по -долу:
Sub CopyCurrentRegion () Диапазон ("A1", Диапазон ("A1"). Край (xlDown). Край (xlToRight)). Копиране на работни листове ("Sheet2"). Диапазон ("A1") Краен под
Обърнете внимание, че всички тези кодове не избират клетките, докато се изпълняват. Като цяло ще намерите само няколко случая, в които всъщност трябва да изберете клетка/диапазон, преди да работите върху нея.
Присвояване на диапазони на обектни променливи
Досега използвахме пълния адрес на клетките (например работни книги („Book2.xlsx“). Работни листове („Sheet1“). Диапазон („A1“)).
За да направите кода си по -управляем, можете да присвоите тези диапазони на обектните променливи и след това да ги използвате.
Например, в кода по -долу, аз съм присвоил изходния и целевия диапазон на обектните променливи и след това използвах тези променливи за копиране на данни от един диапазон в друг.
Sub CopyRange () Dim SourceRange As Range Dim DestinationRange As Range Set SourceRange = Работни листове ("Sheet1"). Диапазон ("A1: D20") Задайте DestinationRange = Работни листове ("Sheet2"). Диапазон ("A1") SourceRange.Copy DestinationRange End Sub
Започваме с обявяването на променливите като Range обекти. След това присвояваме диапазона на тези променливи с помощта на оператора Set. След като диапазонът е присвоен на променливата, можете просто да използвате променливата.
Въведете данни в следващата празна клетка (използвайки полето за въвеждане)
Можете да използвате полетата за въвеждане, за да позволите на потребителя да въвежда данните.
Да предположим например, че имате набора от данни по -долу и искате да въведете записа на продажбите, можете да използвате полето за въвеждане във VBA. Използвайки код, можем да се уверим, че той попълва данните в следващия празен ред.
Sub EnterData () Dim RefRange As Range Set RefRange = Range ("A1"). End (xlDown) .Offset (1, 0) Set ProductCategory = RefRange.Offset (0, 1) Set Quantity = RefRange.Offset (0, 2 ) Set Set = RefRange.Offset (0, 3) RefRange.Value = RefRange.Offset (-1, 0) .Value + 1 ProductCategory.Value = InputBox ("Категория на продукта") Quantity.Value = InputBox ("Количество") Amount.Value = InputBox ("Amount") End Sub
Горният код използва полето за въвеждане на VBA, за да получи входовете от потребителя, и след това въвежда входовете в посочените клетки.
Обърнете внимание, че не използвахме точни препратки към клетки. Вместо това използвахме свойството End и Offset, за да намерим последната празна клетка и да попълним данните в нея.
Този код далеч не може да се използва. Например, ако въведете текстов низ, когато полето за въвеждане пита за количество или количество, ще забележите, че Excel го позволява. Можете да използвате условие If, за да проверите дали стойността е числова или не и след това да я разрешите съответно.
Циклично преминаване през клетки / диапазони
Досега можем да видим как да избираме, копираме и въвеждаме данните в клетки и диапазони.
В този раздел ще видим как да преминем през набор от клетки/редове/колони в диапазон. Това може да бъде полезно, когато искате да анализирате всяка клетка и да извършите някакво действие въз основа на нея.
Например, ако искате да маркирате всеки трети ред в селекцията, тогава трябва да преминете през и да проверите за номера на реда. По същия начин, ако искате да подчертаете всички отрицателни клетки, като промените цвета на шрифта на червен, трябва да преминете през и да анализирате стойността на всяка клетка.
Ето кода, който ще премине през редовете в избраните клетки и ще подчертае алтернативни редове.
Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Избор за всеки Myrow
Горният код използва функцията MOD, за да провери номера на реда в селекцията. Ако номерът на реда е четен, той ще бъде маркиран в циан цвят.
Ето още един пример, при който кодът преминава през всяка клетка и подчертава клетките, които имат отрицателна стойност в нея.
Sub HighlightAlternateRows () Dim Myrange As Range Dim Mycell As Range Set Myrange = Избор за всяка Mycell в Myrange Ако Mycell <0 Тогава Mycell.Interior.Color = vbRed End Ако следващата Mycell End Sub
Обърнете внимание, че можете да направите същото като използвате условно форматиране (което е динамично и по -добър начин да направите това). Този пример е само с цел да ви покаже как циклирането работи с клетки и диапазони във VBA.
Къде да поставите VBA кода
Чудите се къде отива кодът VBA във вашата работна книга на Excel?
Excel има VBA бекенд, наречен VBA редактор. Трябва да копирате и поставите кода в кодовия прозорец на модула VB Editor.
Ето стъпките за това:
- Отидете в раздела Разработчик.
- Щракнете върху опцията Visual Basic. Това ще отвори VB редактора в задната част.
- В прозореца Project Explorer в редактора на VB щракнете с десния бутон върху всеки обект за работната книга, в който искате да вмъкнете кода.Ако не виждате Project Explorer, отидете в раздела View и щракнете върху Project Explorer.
- Отидете на Вмъкване и щракнете върху Модул. Това ще вмъкне обект на модул за вашата работна книга.
- Копирайте и поставете кода в прозореца на модула.
