Функциите като VLOOKUP, COUNTIF, SUMIF се наричат функции на работен лист. Като цяло функциите, които са предварително дефинирани в Excel и готови за използване в работен лист, са функции на работен лист. Не можете да променяте или виждате кода зад тези функции във VBA.
От друга страна, дефинираните от потребителя функции и функции, специфични за VBA като MsgBox или InputBox, са VBA функции.
Всички знаем как да използваме VBA функции във VBA. Но какво ще стане, ако искаме да използваме VLOOKUP във VBA. Как да направим това? В тази статия ще изследваме точно това.
Използване на функции на работен лист във VBA
За достъп до функцията на работния лист използваме клас Application. Почти всички функции на работния лист са изброени в класа Application.WorksheetFunction. И като използвате точков оператор, можете да получите достъп до всички тях.
Във всяка подточка напишете Application.WorksheetFunction. И започнете да пишете името на функцията. Intellisense на VBA ще покаже името на наличните за използване функции. След като сте избрали името на функцията, тя ще поиска променливите, като всяка функция в Excel. Но ще трябва да предадете променливи във разбираем формат VBA. Например, ако искате да предадете диапазон A1: A10, ще трябва да го предадете като обект на диапазон като Range ("A1: A10").
Така че нека използваме някои функции на работния лист, за да го разберем по -добре.
Как да използвате функцията VLOOKUP във VBA
За да демонстрирам как можете да използвате функция VLOOKUP във VBA, тук имам примерни данни. Трябва да покажа името и града на дадения идентификационен номер в полето за съобщение, използвайки VBA. Данните са разпределени в диапазон A1: K26.
Натиснете ALT+F11, за да отворите VBE и да поставите модул.
Вижте кода по -долу.
Sub WsFuncitons () Dim loginID като String Dim name, city As String loginID = "AHKJ_1-3357042451" 'Използване на функцията VLOOKUP за получаване на име на даден идентификатор в име на таблица = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26" ), 2, 0) „Използване на функцията VLOOKUP за получаване на града с даден идентификатор в таблицата city = Application.WorksheetFunction.VLookup (loginID, Range (" A1: K26 "), 4, 0) MsgBox (" Name: "& name & vbLf & "Град:" & град) End Sub
Когато стартирате този код, ще получите този резултат.
Можете да видите колко бързо VBA отпечатва резултата в поле за съобщение. Сега нека разгледаме кода.
Как работи?
1.
Dim loginID като низ
Затъмнено име, град As String
Първо декларирахме две променливи от низов тип за съхраняване на резултата, върнат от функцията VLOOKUP. Използвал съм променливи тип низ, защото съм сигурен, че резултатът, върнат от VLOOKUP, ще бъде стойност на низ. Ако се очаква функцията на вашия работен лист да върне номер, дата, диапазон и т.н. тип стойност, използвайте този вид променлива за съхраняване на резултата. Ако не сте сигурни какъв вид стойност ще бъде върната от функцията на работния лист, използвайте променливи тип вариант.
2.
loginID = "AHKJ_1-3357042451"
След това използвахме променливата loginID за съхраняване на справочна стойност. Тук сме използвали твърдо кодирана стойност. Можете също да използвате препратки. Например. Можете да използвате Range ("A2"). Value, за да актуализирате динамично стойността за търсене от диапазон A2.
3.
name = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 2, 0)
Тук използваме функцията VLOOKUP, за да получим. Сега, когато оправите функцията и отворите скобите, тя ще ви покаже необходимите аргументи, но не толкова описателни, колкото показва в Excel. Вижте сами.
Трябва да запомните как и каква променлива трябва да използвате. Винаги можете да се върнете към работния лист, за да видите подробностите за описателната променлива.
Тук стойността за търсене е Arg1. За Arg1 използваме loginID. Таблицата за търсене е Arg2. За Arg2 използвахме Range ("A1: K26"). Имайте предвид, че не използвахме директно A2: K26, както правим в Excel. Индексът на колоните е Arg3. За Arg3 използвахме 2, тъй като името е във втората колона. Типът за търсене е Arg4. Използвахме 0 като Arg4.
city = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 4, 0)
По същия начин получаваме името на града.
4.
MsgBox ("Име:" & име & vbLf & "Град:" & град)
Накрая отпечатваме име и град, използвайки Messagebox.
Защо да използвате функцията за работен лист във VBA?
Функциите на работния лист притежават мощност на огромни изчисления и няма да е умно да пренебрегвате силата на функциите на работния лист. Например, ако искаме стандартното отклонение на набор от данни и искате да напишете цял код за това, това може да ви отнеме часове. Но ако знаете как да използвате функцията за работен лист STDEV.P във VBA, за да получите изчислението наведнъж.
Sub GetStdDev () std = Application.WorksheetFunction.StDev_P (Range ("A1: K26")) End Sub
Използване на множество функции на работен лист VBA
Да речем, че трябва да използваме индексно съвпадение, за да извлечем някои стойности. Сега как бихте изкривили формулата във VBA. Това предполагам ще напишеш:
Sub IndMtch () Val = Application.WorksheetFunction.Index (result_range, _ Application.WorksheetFunction.Match (lookup_value, _ lookup_range, match_type)) End Sub
Това не е грешно, но е продължително. Правилният начин за използване на множество функции е чрез използване на блок С. Вижте примера по -долу:
Sub IndMtch () С Application.WorksheetFunction Val = .Index (result_range, .Match (lookup_value, lookup_range, match_type)) val2 = .VLookup (arg1, arg2, arg3) val4 = .StDev_P (числа) Край с End Sub
Както можете да видите, използвах With block, за да кажа на VBA, че ще използвам свойствата и функциите на Application.WorksheetFunction. Така че не е нужно да го определям навсякъде. Току -що използвах оператора на точки за достъп до функциите INDEX, MATCH, VLOOKUP и STDEV.P. След като използваме оператора End With, нямаме достъп до функции, без да използваме напълно квалифицирани имена на функции.
Така че, ако трябва да използвате множество функции на работен лист във VBA, използвайте с block.
Не всички функции на работния лист са достъпни чрез Application.WorksheetFunction
Някои функции на работния лист са директно достъпни за използване във VBA. Не е необходимо да използвате обект Application.WorksheetFunction.
Например функции като Len (), който се използва за получаване на броя на символите в низ, ляво, дясно, средно, подрязване, отместване и т.н. Тези функции могат да се използват директно във VBA. Ето един пример.
Sub GetLen () Strng = "Здравей" Debug.Print (Len (strng)) End Sub
Вижте, тук използвахме функцията LEN, без да използваме обект Application.WorksheetFunction.
По същия начин можете да използвате други функции като ляво, дясно, средно, char и т.н.
Sub GetLen () Strng = "Здравей" Debug.Print (Len (strng)) Debug.Print (ляво (strng, 2)) Debug.Print (дясно (strng, 1)) Debug.Print (Средно (strng, 3, 2)) End Sub
Когато стартирате горния под, той ще се върне:
5 Той ще
Така че да, момчета, ето как можете да използвате функцията за работен лист на Excel във VBA. Надявам се, че бях достатъчно обяснителен и тази статия ви помогна. Ако имате въпроси относно тази статия или нещо друго, свързано с VBA, попитайте в секцията за коментари по -долу. Дотогава можете да прочетете за други свързани теми по -долу.
Какво е CSng функция в Excel VBA | Функцията SCng е функция VBA, която преобразува всеки тип данни в число с плаваща запетая с една точност ("като се има предвид, че това е число"). Използвам най -вече функцията CSng за преобразуване на текстово форматирани числа в действителни числа.
Как да получите текст и номер в обратна посока чрез VBA в Microsoft Excel | За да обърнем число и текст, използваме цикли и функция mid в VBA. 1234 ще бъде преобразуван в 4321, „вие“ ще бъде преобразуван в „uoy“. Ето фрагмента.
Форматирайте данни с персонализирани формати на числа, използвайки VBA в Microsoft Excel | За да промените формата на номера на конкретни колони в Excel, използвайте този фрагмент VBA. Той прикрива числовия формат на посочения формат с едно щракване.
Използване на събитие за смяна на работен лист за стартиране на макрос, когато се направи някаква промяна | Така че, за да стартираме вашия макрос винаги, когато листът се актуализира, използваме събитията от работния лист на VBA.
Стартирайте макрос, ако на листа са направени промени в посочен диапазон | За да стартирате вашия макро код, когато стойността в определен диапазон се промени, използвайте този VBA код. Той открива всяка промяна, направена в определения диапазон и ще задейства събитието.
Най -простият VBA код за маркиране на текущия ред и колона с помощта | Използвайте този малък VBA фрагмент, за да маркирате текущия ред и колона на листа.
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност | Бъдете по -бързи в задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Функцията VLOOKUP в Excel | Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
COUNTIF в Excel 2016 | Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel | Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.