Šajā apmācībā mēs uzzināsim par Excel VBA funkciju
1) Kas ir Visual Basic programmā Excel?
2) Kā lietot VBA programmā Excel?
3) Kā izveidot lietotāja definētu funkciju?
4) Kā uzrakstīt makro?
Kā uzrakstīt VBA kodu
Excel nodrošina lietotājam plašu gatavu funkciju kolekciju, kas ir vairāk nekā pietiekami, lai apmierinātu vidusmēra lietotāju. Daudz ko citu var pievienot, instalējot dažādus pieejamos papildinājumus. Lielāko daļu aprēķinu var veikt, izmantojot sniegto, taču nepaiet ilgs laiks, kad jūs vēlēsities, lai būtu kāda funkcija, kas veic konkrētu darbu, un jūs nevarat atrast neko piemērotu sarakstā. Jums ir nepieciešams UDF. UDF (lietotāja definēta funkcija) ir vienkārši funkcija, kuru jūs izveidojat pats, izmantojot VBA. UDF bieži sauc par "pielāgotajām funkcijām". UDF var palikt koda modulī, kas pievienots darbgrāmatai, un tādā gadījumā tas vienmēr būs pieejams, kad šī darbgrāmata ir atvērta. Varat arī izveidot savu pievienojumprogrammu, kas satur vienu vai vairākas funkcijas, kuras varat instalēt programmā Excel tāpat kā komerciālu pievienojumprogrammu. UDF var piekļūt arī ar koda moduļiem. Bieži vien izstrādātāji izveido UDF, lai tie darbotos tikai saskaņā ar VBA procedūras kodu, un lietotājs nekad nezina par to esamību. Tāpat kā jebkura funkcija, UDF var būt tik vienkārša vai sarežģīta, cik vēlaties. Sāksim ar vieglu…
Funkcija taisnstūra laukuma aprēķināšanai
Jā, es zinu, ka tu to varētu izdarīt savā galvā! Koncepcija ir ļoti vienkārša, lai jūs varētu koncentrēties uz tehniku. Pieņemsim, ka jums ir nepieciešama funkcija, lai aprēķinātu taisnstūra laukumu. Jūs pārlūkojat Excel funkciju kolekciju, taču nav nevienas piemērotas. Šis aprēķins jāveic:
AREA = LENGTH x WIDTH
Atveriet jaunu darbgrāmatu un pēc tam atveriet Visual Basic Editor (Rīki> Makro> Visual Basic Editor vai ALT+F11).
Jums būs nepieciešams modulis, kurā rakstīt savu funkciju, tāpēc izvēlieties Ievietot> Modulis. Tukšā moduļa tipā: Funkciju apgabals un nospiediet ENTER. Visual Basic redaktors aizpilda rindu jūsu vietā un pievieno beigu funkcijas rindu tā, it kā jūs izveidotu apakšprogrammu. Līdz šim tas izskatās šādi…
Funkciju apgabals () Beigu funkcija
Novietojiet kursoru starp iekavām aiz laukuma. Ja kādreiz esat aizdomājies, kam domātas kronšteini, jūs drīz to uzzināsit! Mēs precizēsim "argumentus", kas būs nepieciešami mūsu funkcijai (an arguments ir informācija, kas nepieciešama aprēķina veikšanai). Tips Garums kā dubultā, platums kā dubultā un noklikšķiniet uz tukšās rindas zem tā. Ņemiet vērā, ka, rakstot, tiek parādīts ritināšanas lodziņš, kurā uzskaitītas visas lietas, kas atbilst jūsu rakstītajam.
Šo funkciju sauc Automātiskais dalībnieku saraksts. Ja tas arī neparādās, tas ir izslēgts (ieslēdziet to plkst Rīki> Iespējas> Redaktors) vai arī, iespējams, esat pieļāvis drukas kļūdu agrāk. Tā ir ļoti noderīga jūsu sintakses pārbaude. Atrodiet vajadzīgo vienumu un veiciet dubultklikšķi uz tā, lai to ievietotu kodā. Jūs varat to ignorēt un vienkārši ierakstīt, ja vēlaties. Jūsu kods tagad izskatās šādi…
Funkcijas apgabals (garums kā dubultā, platums kā dubultā) beigu funkcija
Argumentu datu veida deklarēšana nav obligāta, bet ir jēga. Jūs varējāt rakstīt Garums, platums un atstāja to kā tādu, taču brīdinājums programmai Excel par gaidāmo datu veidu palīdz jūsu kodam darboties ātrāk un uztver ievades kļūdas. The dubultā datu tips attiecas uz skaitli (kas var būt ļoti liels) un pieļauj frakcijas. Tagad par pašu aprēķinu. Tukšajā rindā vispirms nospiediet TAB taustiņu, lai ievilktu kodu (atvieglotu lasīšanu) un ierakstītu Platība = garums * platums. Šeit ir pabeigts kods…
Funkciju apgabals (garums kā dubultā, platums kā dubultā) Platība = garums * platuma beigu funkcija
Jūs pamanīsit, ka rakstīšanas laikā parādīsies vēl viena Visual Basic redaktora palīdzības funkcija, Automātiska ātrā informācija…
Šeit tas nav aktuāli. Tās mērķis ir palīdzēt rakstīt funkcijas VBA, pastāstot, kādi argumenti ir nepieciešami. Jūs varat pārbaudīt savu funkciju uzreiz. Pārslēdzieties uz Excel logu un ievadiet skaitļus garumam un platumam atsevišķās šūnās. Trešajā šūnā ievadiet savu funkciju tā, it kā tā būtu viena no iebūvētajām. Šajā piemērā šūna A1 satur garumu (17) un šūnu B1 platumu (6.5). C1 es ierakstīju = apgabals (A1, B1) un jaunā funkcija aprēķināja laukumu (110,5)…
Dažreiz funkcijas argumenti var būt neobligāti. Šajā piemērā mēs varētu izveidot Platums arguments nav obligāts. Pieņemsim, ka taisnstūris ir kvadrāts, kura garums un platums ir vienādi. Lai lietotājam nebūtu jāievada divi argumenti, mēs varētu ļaut viņam ievadīt tikai garumu un funkcijai izmantot šo vērtību divas reizes (ti, reizināt garumu x garumu). Tātad funkcija zina, kad to var izdarīt, mums ir jāiekļauj IF paziņojums lai palīdzētu tai izlemt. Mainiet kodu tā, lai tas izskatās šādi…
Funkciju apgabals (garums kā dubultā, izvēles platums kā variants) Ja trūkst (platums), tad apgabals = garums * garums cits apgabals = garums * platums beigas, ja beigu funkcija
Ņemiet vērā, ka platuma datu tips ir mainīts uz Variants lai pieļautu nulles vērtības. Funkcija tagad ļauj lietotājam ievadīt tikai vienu argumentu, piem. = apgabals (A1). IF paziņojums funkcijā pārbauda, vai ir iesniegts arguments Platums, un attiecīgi aprēķina …
Funkcija degvielas patēriņa aprēķināšanai
Man patīk kontrolēt automašīnas degvielas patēriņu, tāpēc, pērkot degvielu, es atzīmēju nobraukumu un to, cik daudz degvielas nepieciešams, lai uzpildītu degvielu. Šeit Apvienotajā Karalistē degviela tiek pārdota litros. Automašīnas milometrs (labi, tātad tas ir odometrs) ieraksta attālumu jūdzēs. Un, tā kā esmu pārāk vecs un stulbs, lai mainītos, es saprotu tikai MPG (jūdzes uz galonu). Tagad, ja jūs domājat, ka tas viss ir mazliet skumji, kā būtu ar to. Ierodoties mājās, es atveru programmu Excel un ievadu datus darblapā, kas aprēķina MPG un attēlo automašīnas veiktspēju. Aprēķins ir jūdžu skaits, ko automašīna nobraukusi kopš pēdējās uzpildīšanas, dalīts ar izmantotās degvielas galonu skaitu…
MPG = (MILES THIS FILL - MILES LAST FILL) / GALONI DEGVIELAS
bet tāpēc, ka degviela ir litros un galonā ir 4,546 litri …
MPG = (MILES THIS FILL - MILES LAST FILL) / DEGVIELAS LITRI x 4,546
Lūk, kā es uzrakstīju funkciju …
Funkcija MPG (StartMiles kā vesels skaitlis, FinishMiles kā vesels skaitlis, litri kā viens) MPG = (FinishMiles - StartMiles) / litri * 4.546 Beigu funkcija
un šeit tas izskatās darblapā …
Ne visas funkcijas veic matemātiskus aprēķinus. Šeit ir informācija, kas sniedz informāciju…
Funkcija, kas piešķir dienas nosaukumu
Man bieži jautā, vai ir datuma funkcija, kas nedēļas dienu norāda kā tekstu (piemēram, pirmdiena). Atbilde ir nē*, bet izveidot to ir diezgan viegli. (*Papildinājums: vai es teicu nē? Pārbaudiet zemāk esošo piezīmi, lai redzētu funkciju, kuru aizmirsu!). Programmā Excel ir funkcija WEEKDAY, kas nedēļas dienu atgriež kā skaitli no 1 līdz 7. Jūs varat izvēlēties, kura diena ir 1, ja jums nepatīk noklusējuma (svētdiena). Zemāk redzamajā piemērā funkcija atgriež "5", kas, manuprāt, nozīmē "ceturtdiena".
Bet es negribu redzēt skaitli, es gribu redzēt "ceturtdienu". Es varētu mainīt aprēķinu, pievienojot funkciju VLOOKUP, kas atsaucās uz tabulu, kur kaut kur ir ciparu saraksts un atbilstošs dienu nosaukumu saraksts. Vai arī es varētu visu padarīt patstāvīgu ar vairākiem ligzdotiem IF paziņojumiem. Pārāk sarežģīti! Atbilde ir pielāgota funkcija…
Funkcija DayName (ievades datums kā datums) Dim DayNumber kā vesels skaitlis DayNumber = Nedēļas diena (InputDate, vbSunday) Atlasiet Case DayNumber Case 1 DayName = "Sunday" Case 2 DayName = "Monday" Case 3 DayName = "Tuesday" Case 4 DayName = "Wednesday" 5. gadījums DayName = "Thursday" Case 6 DayName = "Friday" Case 7 DayName = "Saturday" End Atlasiet beigu funkciju
Es esmu nosaucis savu funkciju par “DayName”, un tas prasa vienu argumentu, ko es saucu par “InputDate”, kam (protams) ir jābūt datumam. Lūk, kā tas darbojas…
- Funkcijas pirmā rinda deklarē mainīgo, ko esmu nosaucis par “DayNumber” un kas būs vesels skaitlis (t.i., vesels skaitlis).
- Funkcijas nākamā rinda piešķir šim mainīgajam vērtību, izmantojot Excel WEEKDAY funkciju. Vērtība būs skaitlis no 1 līdz 7. Lai gan noklusējuma vērtība ir 1 = svētdiena, skaidrības labad esmu to iekļāvusi.
- Visbeidzot a Lietas izklāsts pārbauda mainīgā vērtību un atgriež atbilstošo teksta daļu.
Lūk, kā tas izskatās darblapā…
Piekļuve savām pielāgotajām funkcijām
Ja darbgrāmatai ir pievienots VBA koda modulis, kas satur pielāgotas funkcijas, šīs funkcijas var viegli risināt tajā pašā darbgrāmatā, kā parādīts iepriekš minētajos piemēros. Jūs izmantojat funkcijas nosaukumu tā, it kā tā būtu viena no Excel iebūvētajām funkcijām.
Jūs varat arī atrast funkcijas, kas uzskaitītas funkciju vednī (dažreiz to sauc par rīku Ielīmēt). Izmantojiet vedni, lai ievietotu funkciju parastajā veidā (Ievietot> Funkcija).
Ritiniet uz leju funkciju kategoriju sarakstu, lai atrastu Lietotājs definēts un atlasiet to, lai redzētu pieejamo UDF sarakstu …
Jūs varat redzēt, ka lietotāja definētajām funkcijām trūkst citu aprakstu, izņemot nelietderīgo ziņojumu “Palīdzība nav pieejama”, taču varat pievienot īsu aprakstu…
Pārliecinieties, vai atrodaties darbgrāmatā, kurā ir funkcijas. Iet uz Rīki> Makro> Makro. Jūs šeit neredzēsit savas funkcijas, bet Excel par tām zina! Iekš Makro nosaukums lodziņā dialoglodziņa augšpusē ierakstiet funkcijas nosaukumu un pēc tam noklikšķiniet uz dialoglodziņa Iespējas pogu. Ja poga ir pelēka, vai nu esat nepareizi uzrakstījis funkcijas nosaukumu, vai arī atrodaties nepareizā darbgrāmatā, vai arī tās nav! Tiek atvērts cits dialoglodziņš, kurā varat ievadīt īsu funkcijas aprakstu. Klikšķis labi lai saglabātu aprakstu un (šeit ir mulsinošais bits) noklikšķiniet Atcelt lai aizvērtu dialoglodziņu Makro. Neaizmirstiet saglabāt darbgrāmatu, kurā ir šī funkcija. Nākamreiz, kad apmeklēsit funkciju vedni, jūsu UDF būs apraksts …
Tāpat kā makro, lietotāja definētās funkcijas var izmantot jebkurā citā darbgrāmatā, ja vien ir atvērta darbgrāmata, kurā tās atrodas. Tomēr tā nav laba prakse. Funkcijas ievadīšana citā darbgrāmatā nav vienkārša. Funkcijas nosaukumam jāpievieno tās resursdatora darbgrāmatas nosaukums. Tas nav grūti, ja paļaujaties uz funkciju vedni, bet neveikli rakstīt manuāli. Funkciju vednis parāda visu UDF pilnu nosaukumu citās darbgrāmatās …
Ja atverat darbgrāmatu, kurā izmantojāt funkciju, laikā, kad darbgrāmata, kurā ir funkcija, ir aizvērta, šūnā, kurā izmantojāt funkciju, tiks parādīts kļūdas ziņojums. Excel to ir aizmirsis! Atveriet funkcijas resursdatora darbgrāmatu, pārrēķiniet, un viss atkal ir kārtībā. Par laimi ir labāks veids.
Ja vēlaties rakstīt lietotāja definētas funkcijas izmantošanai vairākās darbgrāmatās, labākā metode ir izveidot Excel Pievienot. Uzziniet, kā to izdarīt apmācībā Izveidot Excel pievienojumprogrammu.
Papildinājums
Man tiešām vajadzētu zināt labāk! Nekad, nekad, nesaki nekad! Pateicis, ka nav funkcijas, kas norādītu dienas nosaukumu, tagad atcerējos to, kas to var. Paskaties uz šo piemēru…
Funkcija TEKSTS atgriež šūnas vērtību kā tekstu noteiktā skaitļa formātā. Tātad piemērā es varēju izvēlēties = TEKSTS (A1, "ddd") lai atgrieztos "Thu", = TEKSTS (A1, "mmmm") lai atgrieztos "septembris" utt. Excel palīdzībā ir vēl daži šīs funkcijas izmantošanas piemēri.
Ja jums patika mūsu emuāri, kopīgojiet to ar saviem draugiem Facebook. Un arī jūs varat sekot mums Twitter un Facebook.
Mēs labprāt uzklausītu jūsu viedokli, dariet mums zināmu, kā mēs varam uzlabot, papildināt vai ieviest jauninājumus mūsu darbā un uzlabot to jūsu labā. Rakstiet mums uz e -pasta vietne