Objektiv - Mësoni operatorët bazë dhe zbatoni konstruksionet bazë të programimit në gjuhën VBA.

4.1 Fazat themelore të punës me programet vba në Excel

Për të përgatitur dhe ekzekutuar një program VBA, duhet të bëni sa më poshtë:

në Excel, zgjidhni komandën nga menyja Shërbimi - Makro - RedaktuesVizualebazë;

në dritaren që shfaqet, zgjidhni komandën nga menyja Fut – Moduli... Krijohet një modul, d.m.th., në terma të thjeshtë, hapet një dritare në të cilën mund të futni tekstin e programit.

Në fillim të modulit, mund të specifikohet një udhëzim OpsioniE qartë... Nëse specifikohet, atëherë të gjitha variablat e përdorur në program do të duhet të deklarohen në deklaratë Dim(për më shumë detaje shih nënseksionin 4.3).

Në disa raste, në varësi të cilësimeve të mjedisit VBA, udhëzimi OpsioniE qartë tregohet në fillim të modulit automatikisht. Nëse programuesi dëshiron të përdorë variabla pa i deklaruar ato në një deklaratë Dim, pastaj udhëzimi OpsioniE qartë duhet hequr.

Për të ekzekutuar programin për ekzekutim, zgjidhni komandën nga menyja Run - Run Sub / UserForm.

4.2 Shembulli më i thjeshtë i një programi vba

Shembulli 4.1- Programi që ngre numrin e specifikuar a në shkallën e caktuar b.

Dim a Si Single, b Si Single

a = InputBox ("Fut bazën:")

b = InputBox ("Fut një eksponent:")

x = a ^ b 'Përhapja

MsgBox ("Rezultati është" & x)

Këtu është fjala Nën tregon fillimin e procedurës; emri i saj në këtë rast është abetare4_1 ... Një program VBA përbëhet gjithmonë nga një ose më shumë procedura (në këtë rast, një).

Karakteri '(thonjë e vetme) shënon fillimin e një komenti. Teksti i komentit mund të jetë çdo gjë.

Dim- operatori i deklarimit të ndryshores. Në këtë rast, tregohet se variablat a dhe b janë të llojit Beqare... ato. mund të jenë numra të plotë dhe thyesorë. Llojet e të dhënave dhe deklaratat e variablave do të diskutohen më në detaje në seksionin 4.3.

Kutia hyrëse- një funksion për futjen e vlerës së një ndryshoreje. Linjë a = Kutia hyrëse("Fut bazën:”) do të thotë se është futur vlera e ndryshores a; mesazhi shfaqet në ekran "Fut bazën:"... Linjë x = a^ b- Operatori i caktimit: llogaritet vlera e anës së djathtë (në këtë rast, ndryshorja a ngritur në pushtet b), dhe rezultati i caktohet variablit të specifikuar në të majtë (në këtë rast, ndryshores x). Linjë MsgBox("Rezultati është" &x) do të thotë që mesazhi shfaqet në ekran "Rezultati është i barabartë" dhe vlerën e ndryshores x.

Shënim - Nënshkruani & në funksion MsgBoxështë menduar për lidhjen e disa artikujve të të dhënave që duhet të shfaqen në ekran, në këtë shembull - vargjet "Rezultati është i barabartë" dhe e ndryshueshme x... Në mënyrë të ngjashme shenjë & mund të përdoret në funksion Kutia hyrëse(shembuj të një përdorimi të tillë do të jepen më poshtë). Përpara shenjës & dhe duhet të ndiqet nga hapësira.

Deklarata të shumta VBA mund të vendosen në një rresht. Për ta bërë këtë, ato ndahen me dy pika. Pra, programi nga shembulli 4.1 mund të shkruhet, për shembull, si më poshtë:

Shembulli i parë i një programi VBA

Dim a Si Single, b Si Single

a = InputBox ("Fut bazën:"): b = InputBox ("Fut eksponentin:")

x = a ^ b: MsgBox ("Rezultati është" & x) 'Shprehni dhe nxirrni rezultatin

Ky shembull përdor një procedurë të quajtur nënprogram. Kjo procedurë fillon me fjalën Nën... Ekziston gjithmonë të paktën një nënprogram në një program VBA. Përveç kësaj, ekziston një lloj tjetër procedure në VBA - funksionet. Një procedurë funksioni fillon me fjalën Funksioni... Përdorimi i procedurave të tilla do të diskutohet në nënseksionin 4.9.

(Visual Basic për aplikim)

Gjuhë programimi bazë - e nivelit të lartë (përkthyes)

Visual - përmban mjete vizuale për zhvillimin e programeve (kodet) që thjeshtojnë punën e përdoruesit, ju lejon të regjistroni kodet duke përdorur një regjistrues makro.

Aplikacioni është një aplikacion në sistemin softuer MSOffice së bashku me aplikacionet Word, Access, PowerPoint.

Pse keni nevojëVBA?

Kombinon (integron) aplikacionet, ju lejon të menaxhoni punën e aplikacioneve të tjera pa lënë Excel, të vendosni objekte nga aplikacione të tjera;

Veprimet e disponueshme për përdoruesin në fletën e punës përbëjnë 10% të të gjitha aftësive të Excel, VBA ju lejon të automatizoni punën e projektit tuaj.

VBA i përket numrit të gjuhëve të orientuara nga objekti, domethënë objektet përdoren gjatë zhvillimit të projekteve. Përkufizimi: kombinimi i të dhënave dhe kodit në një tërësi të vetme Për shembull, elementi "Button" dhe kodi përkatës i lidhur me këtë buton do të sigurojë një kalim në një fletë tjetër të librit të punës.

Objektet kryesoreVBA:

Aplikacioni (vetë aplikacioni Excel)

Libri i punës (libri i punës është skedari juaj)

Funksioni i fletës së punës (magjistari i funksionit)

Fletë pune

Gama

Grafiku

Stili

Kufiri

E brendshme (ngjyra e sfondit)

Fonti

Shumë prej disa objekteve përbëjnë familjet–Librat e punës, fletët e punës, grafikët.

Objektet kanë veti (veprimet mbi objektet) dhe metoda (veprimet e vetë objekteve).

Le të fillojmë të njihemi me mjetet e vizualizimit për zhvillimin e projektit në VBA. Ky është Mjedisi i Integruar i Zhvillimit të Aplikacioneve. Për të hyrë në këtë mjedis, zgjidhni artikullin e menusë ServiceMacroEditorVBA ose shtypni njëkohësisht tastet ALT dhe F11.

Komponentët e redaktuesit VBA shfaqen në ekran:

Dritarja e projektit - Projekti VBA

Dritarja e vetive

Dritarja e kodit

Dritarja e Formës së Përdoruesit

Shiritat e veglave

Dritarja Project – VBAProject (Fig. 1) tregon strukturën e projektit (skedarit) tuaj. Kjo dritare aktivizohet në redaktuesin VBA duke zgjedhur komandën ViewProjectExplorer ose duke shtypur butonin ProjectExplorer ose duke shtypur Ctrl + R

Oriz. 1 Dritarja e projektit.

Fig. 2 Mjedisi i integruar i zhvillimit të aplikacionit

Dritarja e kodit është për ruajtjen e kodit të lidhur me objektin. Çdo objekt ka dritaren e vet, kështu që çdo fletë pune ka dritaren e vet të kodit, Libri i punës ka dritaren e vet.

Krijimi i funksioneve të personalizuara

Funksionet e personalizuara i shtohen listës standarde të funksionit të fletës së punës. Këto funksione krijohen në një modul të veçantë që shoqëron objektin WorkSheetFunction. Ky modul i shtohet projektit duke përdorur komandën InsertModule dhe do të shfaqet në dritaren e projektit në nivelin e aplikacionit tuaj. Të gjitha kodet e shkruara në këtë modul do të shfaqen në kategorinë Funksionet e Përcaktuara nga Përdoruesi të Funksionit Wizard.

Pra, ne shtojmë një modul në projektin tonë (InsertModule) dhe shkruajmë tekstin e programit në dritaren e kodit të këtij moduli:

y = Cos ((x + 2) / 2) ^ 2 + Exp (-2 * x) / (x ^ 2 + 1) ^ 0,5

Pastaj kalojmë në fletën e punës "1 grafik", në qelizën c2 do ta quajmë funksionin e ri të shtuar në masterin e funksioneve - y (x). Puna me këtë funksion nuk ndryshon nga puna me ndonjë funksion tjetër. Në hapin e parë, duhet të zgjidhni y (x) në kategorinë "Funksionet e përcaktuara nga përdoruesi", në hapin e dytë, specifikoni qelizën A2 si argumentet x. Si rezultat, formula = y (A2) do të shkruhet në qelizën C2. Zvarriteni këtë formulë në të gjithë gamën A2: A17 siç tregohet në Fig. 3. Sigurisht, rezultati duhet të jetë i njëjtë me atë që keni marrë duke llogaritur këtë funksion me mjetet e zakonshme të një flete pune.

Fig 3. Funksioni y (x), i llogaritur në mënyrën e zakonshme dhe duke përdorur një funksion të personalizuar.

Operatorët Bazë Vba - Operatorët e Kushtëzuar

Operatorët e kushtëzuar kanë 2 forma shënimi:

1) Një rresht

NËSE< условие>PASTAJ<оператор 1>

IF, THEN, TJETER - fjalë të pandryshueshme shërbimi, në kllapa këndore< >teksti i përdoruesit, kjo është ajo që shkruani në përputhje me detyrën, në kllapa katrore është një pjesë opsionale, teksti mund të mungojë. Kjo formë zakonisht përdoret në rastin e veprimeve të thjeshta, për shembull, kur llogaritni modulin e një numri y = х = abs (s), mund të përdorni operatorin e mëposhtëm:

NËSE x> 0 ATËHERË y = x TJETER y = -x

2) Në disa rreshta. Në këtë rast, deklarata e kushtëzuar përfundon domosdoshmërisht me deklaratën "ENDIF"

NËSE<условие>PASTAJ

<оператор 1>

<оператор 2>

<оператор 3>

<оператор 4>

Kjo formë përdoret për llogaritjet komplekse, për shembull, kur llogaritni rrënjët e një ekuacioni kuadratik. Le të jetë e nevojshme të gjenden rrënjët e ekuacionit a * x 2 + b * x + c = 0. Siç e dini, nëse b 2 -4 * a * c ≥ 0, atëherë rrënjët llogariten me formulën
, nëse b 2 -4 * a * c ≤ 0, atëherë nuk ka rrënjë në diapazonin e numrave realë. Operatori i kushtëzuar që zbaton këtë algoritëm duket si ky:

NËSE b ^ 2 -4 * a * c> = 0 ATHESH

X1 = (-b + (b ^ 2 - 4 * a * c) ^ (1/2)) / (2 * a)

X2 = (-b + (b ^ 2 + 4 * a * c) ^ (1/2)) / (2 * a)

X1 = "PA ZGJIDHJE"

X2 = "PA ZGJIDHJE"

Shembuj të shkrimit të funksioneve të personalizuara në vba

Shembulli 1.

F
bashkimi y (x)

y = Cos ((x + 2) / 2) ^ 2 + Exp (-2 * x) / (x ^ 2 + 1) ^ 0,5

Funksioni i Fundit

Shembulli 2

Funksioni z (x)

Nëse x< 0 Then

z = (1 + x + x ^ 2) / (1 + x ^ 2)

Nëse x< 1 Then

z = (1 + 2 * x / (1 + x ^ 2)) ^ (1/2)

z = 2 * Abs (0,5 + Sin (x))

Funksioni i Fundit

Leksioni 2

Vetitë, metodat dhe ngjarjet e objekteve VBA. Një shembull i një procedure duke përdorur vetitë individuale të objektit Application.

Elementet e kontrollit

Llojet e ndryshueshme në VBA

Operatorët e ciklit

Një shembull i një programi të funksionit të tabelimit

Vetitë, metodat dhe ngjarjet e objekteve VBA. Një shembull i një procedure duke përdorur vetitë individuale të objektit Application.

Le të vazhdojmë njohjen tonë me modelin e objektit VBA. Të gjitha objektet janë të organizuara në një strukturë hierarkike në varësi të objektit "Aplikacion" të nivelit të lartë.

Aplikacion . Librat e punës .

Nëse libri i punës (skedari) "Grafika" është aktiv, atëherë mjafton të tregohet

Fletët e punës ("Përmbajtja"). Gama ("A1").

Nëse jeni duke punuar në fletën "Përmbajtja", atëherë lidhja do të duket si Gama ("A1").

Të gjitha objektet kanë veti, metoda dhe ngjarje.

Pronës kjo është disa karakteristika të një objekti (ngjyra, forma, emri, vendndodhja, dukshmëria, etj.) Vlera e objektit vendoset si më poshtë:

Objekti.Pronësia = vlera e pronës

Metodaështë një veprim që kryhet në një objekt (hap, mbyll, fshij). Rregulli i mënyrës së shkrimit:

Nje objekt. Metoda

Ngjarjaështë një veprim i njohur nga një objekt (klikim i mausit, klik i dyfishtë, shtypje tasti).

Thelbi i programimit në VBA përbëhet nga dy koncepte - një ngjarje dhe një përgjigje ndaj saj. Nëse përdoruesi ndikon në sistem (shtypja e një butoni), që është një ngjarje, atëherë me anë të VBA është e mundur të programohet një përgjigje - një veprim përgjigje.

Këtu janë disa nga vetitë, metodat dhe ngjarjet e objekteve kryesore.

Vetitë e metodës së aplikacionit ju lejojnë të vendosni programatikisht vlerat e shumë prej opsioneve në dritaren Tools / Options. Për shembull, mund të përdorni procedurat e mëposhtme për të ndryshuar pamjen e paracaktuar të dritares së Excel, për të ndryshuar titullin dhe për të çaktivizuar veprimin zvarrit dhe lësho të formulave.

Këto procedura regjistrohen në modulin "Ky libër" dhe ekzekutohen kur hapet dhe mbyllet libri i punës, domethënë kur ekzekutohen ngjarjet Open dhe BeforeClose:

Nën-libër pune privat_open ()

“Titulli i fletores së punës

Application.Caption = "Kisa dhe Osya ishin këtu"

"Ngjyra e sfondit të gamës A1: D1 - E kuqe

"Kufijtë e diapazonit A1: D1 - vijë me pika

"Zvarritja e CellDragAndDrops është anuluar

Aplikimi.CellDragAndDrop = Falsе

"Shiriti i formulës është hequr

Aplikimi.DisplayFormulaBar = False

"Shiritat e lëvizjes janë hequr

Aplikimi.DisplayScrollBars = False

"Stili i lidhjes R1C1 është vendosur

Aplikimi.Stil Reference = xlR1C1

"Nën-Libri Privat i Punës_BeforeClose (Anulo si Boolean)

"Zvarritja e CellDragAndDrops është restauruar

Aplikimi.CellDragAndDrop = E vërtetë

"Shiriti i formulës është restauruar

Aplikimi.DisplayFormulaBar = E vërtetë

"Shiritat e lëvizjes janë restauruar

Aplikimi.DisplayScrollBars = E vërtetë

"Stili i lidhjes A1 është restauruar

Aplikimi.ReferenceStyle = xlA1

Pak e dinë që versioni i parë i produktit të njohur Microsoft Excel u shfaq në 1985. Që atëherë, ajo ka kaluar nëpër disa modifikime dhe është në kërkesë në mesin e miliona përdoruesve në mbarë botën. Në të njëjtën kohë, shumë njerëz punojnë me vetëm një pjesë të vogël të aftësive të këtij procesori të fletëllogaritjes dhe madje nuk e kanë idenë se si mund të lehtësohet jeta e tyre duke programuar në Excel.

Çfarë është VBA

Programimi në Excel kryhet duke përdorur gjuhën e programimit Visual Basic for Application, e cila fillimisht është ndërtuar në procesorin më të famshëm të tabelave nga Microsoft.

Specialistët i atribuojnë avantazheve të tij lehtësinë krahasuese të zhvillimit. Siç tregon praktika, edhe përdoruesit që nuk kanë aftësi programuese profesionale mund të zotërojnë bazat e VBA. Një nga veçoritë e VBA është ekzekutimi i skriptit në mes të aplikacioneve të zyrës.

• përsëri shkoni në rreshtin "Macros";
• zgjidhni "Macro 1" në listë;
• shtypni "Run" (i njëjti veprim fillon duke filluar kombinimin e tasteve "Ctrl + hh").

Si rezultat, ndodh veprimi, i cili u krye në procesin e regjistrimit të makro.

Ka kuptim të shihet se si duket kodi. Për ta bërë këtë, kthehuni te rreshti "Macros" dhe klikoni "Ndrysho" ose "Enter". Si rezultat, ata përfundojnë në një mjedis VBA. Në fakt, vetë kodi makro ndodhet midis rreshtave Sub Macro1 () dhe End Sub.

Nëse kopjimi është kryer, për shembull, nga qeliza A1 në qelizën C1, atëherë një nga rreshtat e kodit do të duket si Gama (“C1”). Zgjidhni. Në përkthim duket si "Range (" C1 "). Zgjidhni", me fjalë të tjera, transferohet në VBA Excel, në qelizën C1.

Pjesa aktive e kodit plotësohet nga komanda ActiveSheet.Paste. Do të thotë të shkruani përmbajtjen e qelizës së zgjedhur (në këtë rast, A1) në qelizën e theksuar C1.

Shembulli 2

Sythet VBA ju ndihmojnë të krijoni makro të ndryshme në Excel.

Sythet VBA ju ndihmojnë të krijoni një shumëllojshmëri makrosh. Supozoni se kemi një funksion y = x + x2 + 3x3 - cos (x). Kërkohet të krijohet një makro për të marrë grafikun e saj. Kjo mund të bëhet vetëm duke përdorur sythe VBA.

Vlerat fillestare dhe përfundimtare të argumentit të funksionit merren si x1 = 0 dhe x2 = 10. Përveç kësaj, është e nevojshme të futni një konstante - vlerën për hapin e ndryshimit të argumentit dhe vlerën fillestare për numëruesin.

Të gjithë shembujt e makrove VBA Excel janë krijuar duke përdorur të njëjtën procedurë siç është paraqitur më sipër. Në këtë rast të veçantë, kodi duket si ky:

Bëni Ndërsa x1< x2 (цикл будeт выполняться пока вeрно выражeниe x1 < x2)

y = x1 + x1 ^ 2 + 3 * x1 ^ 3 - Cos (x1)

Qelizat (i, 1) .Vlera = x1 (vlera e x1 shkruhet në qelizë me koordinatat (i, 1))

Qelizat (i, 2) .Vlera = y (vlera e y shkruhet në qelizë me koordinatat (i, 2))

i = i + 1 (numëruesi është aktiv);

x1 = x1 + shag (argumenti ndryshohet nga madhësia e hapit);

Si rezultat i ekzekutimit të kësaj makro në Excel, marrim dy kolona, ​​e para prej të cilave përmban vlerat për x dhe e dyta për y.

Pastaj një grafik vizatohet duke i përdorur ato në një mënyrë standarde për Excel.

Shembulli 3

Për të zbatuar sythe në VBA Excel 2010, si dhe në versione të tjera, së bashku me ndërtimin e përmendur tashmë Do while, përdoret For.

Konsideroni një program që krijon një kolonë. Në secilën nga qelizat e tij do të shkruhen katrorët e numrit të rreshtit përkatës. Përdorimi i konstruksionit For do t'ju lejojë ta shkruani atë shumë shpejt, pa përdorur një numërues.

Së pari ju duhet të krijoni një makro siç përshkruhet më sipër. Tjetra, ne shkruajmë vetë kodin. Ne konsiderojmë se jemi të interesuar për vlerat për 10 qeliza. Kodi duket si ky.

Për i = 1 deri në 10 Më pas

Komanda përkthehet në gjuhën "njerëzore" si "Përsëriteni nga 1 në 10 në hapat e një".

Nëse detyra është të marrim një kolonë me katrorë, për shembull, të gjithë numrat tek nga diapazoni nga 1 në 11, atëherë shkruajmë:

Për i = 1 deri në 10 hapi 1 Tjetra.

Këtu hapi është një hap. Në këtë rast, është e barabartë me dy. Si parazgjedhje, mungesa e kësaj fjale në lak do të thotë që hapi është i vetëm.

Rezultatet e fituara duhet të ruhen në qelizat me numrin (i, 1). Pastaj, në çdo fillim të ciklit me rritjen e i me madhësinë e hapit, numri i linjës do të rritet automatikisht. Kështu, kodi do të optimizohet.

Në përgjithësi, kodi do të duket si ky:

Për i = 1 në 10 Hapi 1 (ju thjesht mund të shkruani Për i = 1 në 10)

Qelizat (i, 1) .Vlera = i ^ 2 (d.m.th. vlera e katrorit i shkruhet në qelizën (i, 1))

Tjetra (në një farë kuptimi luan rolin e një numëruesi dhe do të thotë një fillim tjetër i ciklit)

Nëse gjithçka është bërë në mënyrë korrekte, duke përfshirë regjistrimin dhe ekzekutimin e një makro (shih udhëzimet e mësipërme), atëherë sa herë që thirret, do të merret një kolonë e madhësisë së specifikuar (në këtë rast, e përbërë nga 10 qeliza).

Shembulli 4

Në jetën e përditshme, shumë shpesh ekziston nevoja për të marrë këtë apo atë vendim, në varësi të disa kushteve. Ju nuk mund të bëni pa to në VBA Excel. Shembuj të programeve ku zgjidhet rrjedha e mëtejshme e algoritmit dhe nuk paracaktohet fillimisht, më së shpeshti përdorin Nëse… Pastaj (për raste komplekse) Nëse… Pastaj… FUND Nëse ndërtimi.

Le të shqyrtojmë një rast specifik. Supozoni se duhet të krijoni një makro për "Excel" në mënyrë që qeliza me koordinatat (1,1) të shkruhet:

1, nëse argumenti është pozitiv;

0 nëse argumenti është zero;

1 nëse argumenti është negativ.

Krijimi i një makro të tillë për "Excel" fillon në mënyrë standarde, përmes përdorimit të tasteve "hot" Alt dhe F11. Pastaj shkruhet kodi i mëposhtëm:

x = Qelizat (1, 1) .Vlera (kjo komandë cakton x vlerën e përmbajtjes së qelizës me koordinatat (1, 1))

Nëse x> 0, atëherë qelizat (1, 1) .Vlera = 1

Nëse x = 0, atëherë qelizat (1, 1) .Vlera = 0

Nëse x<0 Then Cells(1, 1).Value = -1

Mbetet për të ekzekutuar makro dhe për të marrë vlerën e nevojshme për argumentin në "Excel".

Funksionet VBA

Siç mund ta keni vënë re, programimi në procesorin më të famshëm të spreadsheet-it të Microsoft nuk është edhe aq i vështirë. Sidomos nëse mësoni se si të përdorni funksionet VBA. Të gjitha në këtë gjuhë programimi, krijuar posaçërisht për të shkruar aplikacione në Excel dhe Word, rreth 160 funksione. Ato mund të ndahen në disa grupe të mëdha. Kjo:

• Funksionet matematikore. Duke i zbatuar ato në argumentim, ata marrin vlerën e kosinusit, logaritmit natyror, pjesës integrale etj.
• Funksionet financiare. Falë pranisë së tyre dhe përdorimit të programimit në Excel, ju mund të merrni mjete efektive për shlyerjet e kontabilitetit dhe financiare.
• Funksionet e përpunimit të vargjeve. Këto përfshijnë Array, IsArray; LBound; UBound.
• Funksionet VBA Excel për vargun. Ky është një grup mjaft i madh. Ai përfshin, për shembull, funksionet Space për të krijuar një varg me një numër të plotë hapësirash, ose Asc për të kthyer karakteret në kodin ANSI. Të gjitha ato përdoren gjerësisht dhe ju lejojnë të punoni me vargje në Excel, duke krijuar aplikacione që lehtësojnë shumë punën me këto tabela.
• Funksionet e konvertimit të tipit të të dhënave. Për shembull, CVar kthen vlerën e argumentit Expression duke e kthyer atë në llojin e të dhënave Variant.
• Funksionet për të punuar me datat. Ato zgjerojnë ndjeshëm aftësitë standarde të Excel. Pra, funksioni WeekdayName kthen emrin (të plotë ose të pjesshëm) të ditës së javës me numrin e tij. Kohëmatësi është edhe më i dobishëm. Ai jep numrin e sekondave që kanë kaluar nga mesnata në një moment të caktuar të ditës.
• Funksionet për konvertimin e një argumenti numerik në sisteme të ndryshme numrash. Për shembull, tetori jep numra oktalë.
• Funksionet e formatimit. Më e rëndësishmja prej tyre është Formati. Ai kthen një Variant me një shprehje të formatuar sipas udhëzimeve të dhëna në përshkrimin e formatit.
• etj.

Studimi i vetive të këtyre funksioneve dhe zbatimi i tyre do të zgjerojë ndjeshëm fushën e zbatimit të "Excel".

Shembulli 5

Le të përpiqemi të kalojmë në zgjidhjen e problemeve më komplekse. Për shembull:

Jepet një dokument në letër i raportit të nivelit aktual të mbështetjes së ndërmarrjes. Kërkohet:

• të zhvillojë pjesën e tij shabllon me anë të procesorit të tabelës Excel;
• krijoni një program VBA që do të kërkojë të dhënat fillestare për plotësimin e tij, do të kryejë llogaritjet e nevojshme dhe do t'i plotësojë ato në qelizat përkatëse të shabllonit.

Le të shqyrtojmë një nga zgjidhjet.

Krijimi i shabllonit

Të gjitha veprimet kryhen në një fletë standarde në Excel. Qelizat e lira rezervohen për futjen e të dhënave sipas muajit, vitit, emrit të kompanisë së konsumatorit, sasisë së produkteve, nivelit të tyre dhe qarkullimit. Meqenëse numri i kompanive (shoqërive) për të cilat hartohet raporti nuk është i caktuar, qelizat për futjen e vlerave sipas rezultateve dhe emrit të plotë të specialistit nuk janë të rezervuara paraprakisht. Një emër i ri i caktohet fletës së punës. Për shembull, "O raporton".

E ndryshueshme

Për të shkruar një program për mbushjen automatike të shabllonit, është e nevojshme të zgjidhni një përcaktim. Ato do të përdoren për variablat:

• NN- numri i rreshtit aktual të tabelës;
• TP dhe TF - qarkullimi i planifikuar dhe aktual;
• SF dhe SP - shuma aktuale dhe e planifikuar e kostove;
• IP dhe IF - niveli i planifikuar dhe aktual i mbështetjes.

Le të shënojmë me të njëjtat shkronja, por me "parashtesën" Itog, grumbullimin e totalit për këtë kolonë. Për shembull, ItogTP - i referohet një kolone tabele të titulluar "qarkullimi i planifikuar".

Zgjidhja e një problemi duke përdorur programimin VBA

Duke përdorur emërtimet e prezantuara, marrim formulat për devijimet. Nëse keni nevojë të bëni llogaritjen në% të emrave (F - P) / P * 100, dhe në shumë - (F - P).

Rezultatet e këtyre llogaritjeve mund të futen më së miri menjëherë në qelizat përkatëse në tabelën Excel.

Për totalet faktike dhe të parashikuara, ato merren duke përdorur formulat ItogP = ItogP + P dhe ItogF = ItogF + F.

Për devijime përdorni = (ItogF - ItogP) / ItogP * 100, nëse llogaritja kryhet në përqindje, dhe në rastin e vlerës totale - (ItogF - ItogP).

Rezultatet shkruhen përsëri menjëherë në qelizat përkatëse, kështu që nuk ka nevojë t'i caktoni ato në variabla.

Para se të filloni programin e krijuar, duhet të ruani librin e punës, për shembull, nën emrin "Report1.xls".

Butoni "Krijo tabelën e raportit" duhet të shtypet vetëm 1 herë pas futjes së informacionit të kokës. Duhet të dihen edhe rregulla të tjera. Në veçanti, butoni "Shto rresht" duhet të shtypet çdo herë pas futjes së vlerave për çdo lloj aktiviteti në tabelë. Pasi të keni futur të gjitha të dhënat, duhet të shtypni butonin "Finish" dhe më pas të kaloni në dritaren "Excel".

Tani ju e dini se si të zgjidhni detyrat e Excel duke përdorur makro. Mundësia për të përdorur vba excel (shih shembujt e programeve më lart) mund të jetë gjithashtu e nevojshme për të punuar në mjedisin e redaktuesit të tekstit më të njohur aktualisht "Word". Në veçanti, është e mundur duke shkruar, siç tregohet në fillim të artikullit, ose duke shkruar kodin për të krijuar butona të menysë, falë të cilave shumë operacione në tekst mund të kryhen duke shtypur butonat ose përmes "Shiko" skedën dhe ikonën "Macros".

Shembujt e mëposhtëm të thjeshtë makro të Excel ilustrojnë disa nga veçoritë dhe teknikat e përshkruara në tutorialin Excel VBA.

Macro Excel: Shembulli 1

Fillimisht kjo procedurë Nënështë dhënë si shembull i përdorimit të komenteve në kodin VBA. Sidoqoftë, këtu mund të shihni gjithashtu se si deklarohen variablat, si funksionojnë referencat e qelizave Excel, duke përdorur një lak Për, operator i kushtëzuar Nëse dhe shfaqja e një kutie mesazhesh.

"Nënprocedura kërkon një qelizë që përmban vargun e specifikuar" në rangun e qelizave A1: A100 të fletës aktive Sub Find_String (sFindText As String) Dim i As Integer "Një numër i plotë i llojit Integer, i përdorur në një cikli For Dim iRowNumber As Integer" Një numër i plotë i tipit Integer për ruajtjen e rezultatit iRowNumber = 0 "Shikon nëpër qelizat A1: A100 njëra pas tjetrës derisa të gjendet një varg. Nëse gjendet një përputhje me vargun e dhënë " ruani numrin aktual të rreshtit dhe dilni nga cikli Për iRowNumber = i Dil Për Fund Nëse Next i "Ne e informojmë përdoruesin në një dritare që shfaqet nëse rreshti i kërkimit është gjetur" Nëse është specifikuar rreshti është gjetur, tregoni në cilën qelizë është gjetur përputhja Nëse iRowNumber = 0 Pastaj MsgBox "String" & sFindText & "nuk u gjet" Tjetër MsgBox "String" & sFindText & "gjendet në qelizën A" & iRowNumber Fund If End Sub

Macro Excel: Shembulli 2

Procedura e radhës Nën- një shembull i përdorimit të një lak Bej Nderkohe... Këtu mund të shihni gjithashtu se si deklarohen variablat, si të punoni me referencat e qelizave Excel dhe si të aplikoni operatorin e kushtëzuar. Nëse.

"Nënprocedura nxjerr numra Fibonacci që nuk i kalojnë 1000 Sub Fibonacci () Dim i Si numër i plotë" Numërues për të treguar pozicionin e një elementi në sekuencën Dim iFib As Integer "Ruan vlerën aktuale të sekuencës Dim iFib_Next si Integer" Ruan vlerën tjetër i sekuencës Dim iStep As Integer "Ruan madhësinë e rritjes së radhës "Inicializoni variablat i dhe iFib_Next i = 1 iFib_Next = 0" Cikli Do while do të funksionojë derisa vlera "e numrit aktual Fibonacci të kalojë 1000 Do Ndërsa iFib_Next< 1000 If i = 1 Then "Особый случай для первого элемента последовательности iStep = 1 iFib = 0 Else "Сохраняем размер следующего приращения перед тем, как перезаписать "текущее значение последовательности iStep = iFib iFib = iFib_Next End If "Выводим текущее число Фибоначчи в столбце A активного рабочего листа "в строке с индексом i Cells(i, 1).Value = iFib "Вычисляем следующее число Фибоначчи и увеличиваем индекс позиции элемента на 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub

Macro Excel: Shembulli 3

Kjo procedurë Nën sythe nëpër qelizat e një kolone A fletë aktive derisa të ndeshet me një qelizë boshe. Vlerat shkruhen në një grup. Kjo makro e thjeshtë Excel demonstron punën me vargje dinamike, si dhe përdorimin e një cikli Bëni derisa... Në këtë shembull, ne nuk do të kryejmë asnjë veprim me grupin, megjithëse në praktikën reale të programimit, pasi të dhënat janë shkruar në grup, veprime të tilla zakonisht kryhen mbi to.

"Procedura Sub ruan vlerat e qelizave në kolonën A të fletës aktive në grupin Sub GetCellValues ​​() Dim iRow As Integer" Ruan numrin e rreshtit aktual Dim dCellValues ​​() Si Double "Array for ruajtja e vlerave të qelizave iRow = 1 ReDim dCellValues ​​(1 deri në 10)" Loop Do Derisa të qarkullojë nëpër qelizat e kolonës A të fletës aktive "dhe të nxjerrë vlerat e tyre në një grup derisa të ndeshet një qelizë bosh . Bëni derisa të jetë bosh (Qelizat (iRow, 1))" Kontrolloni që grupi dCellValues ​​të ketë madhësi të mjaftueshme "Nëse jo, rrisni grupin e madhësisë me 10 duke përdorur ReDim If UBound (dCellValues)< iRow Then ReDim Preserve dCellValues(1 To iRow + 9) End If "Сохраняем значение текущей ячейки в массиве dCellValues dCellValues(iRow) = Cells(iRow, 1).Value iRow = iRow + 1 Loop End Sub

Macro Excel: Shembulli 4

Në këtë shembull, procedura Nën lexon vlerat nga një kolonë A fletë pune Fleta 2 dhe kryen veprime aritmetike me to. Rezultatet regjistrohen në qelizat e kolonës A në fletën aktive të punës. Kjo makro demonstron përdorimin e objekteve të Excel. Në veçanti, ankim bëhet sipas procedurës Nën tek objekti Kolonat, dhe tregon se si arrihet ky objekt përmes objektit Fletë pune... Tregohet gjithashtu se kur i referohemi një qelize ose një sërë qelizash në fletën aktive, nuk është e nevojshme të specifikoni emrin e kësaj flete kur shkruani lidhjen.

"Nënprocedura duke përdorur një lak lexon vlerat në kolonën A të fletës së punës Sheet2," kryen veprime aritmetike me secilën vlerë dhe e shkruan rezultatin në "kolona A të fletës aktive të punës (Sheet1) Sub Transfer_ColA () Dim i As Integer Dim Col As Range Dim dVal As Double "Cakto kolonën A të fletës së punës tek ndryshorja Col Sheet 2 Set Col = Sheets (" Sheet2"). Kolonat (" A ") i = 1" Duke përdorur një lak, lexoni vlerat e qelizave në Col deri në "derisa të haset një qelizë e zbrazët Do Deri IsEmpty (Col.Cells (i)) "Ne kryejmë veprime aritmetike mbi vlerën e qelizës aktuale dVal = Col.Cells (i) .Vlera * 3 - 1" Komanda e mëposhtme shkruan rezultatin në kolonën A të fletës aktive të punës "Specifikoni emrin e fletës në lidhjen jo të nevojshme pasi kjo është fleta aktive Cells (i, 1) = dVal i = i + 1 Loop Fund Sub

Macro Excel: Shembulli 5

Kjo makro tregon shembullin e kodit VBA që monitoron një ngjarje Excel. Ngjarja që makroja është e lidhur ndodh sa herë që zgjidhet një qelizë ose një varg qelizash në fletën e punës. Në rastin tonë, kur zgjedhim një qelizë B1, një dritare mesazhi shfaqet në ekran.

"Ky kod tregon një dritare me një mesazh nëse qeliza B1 është zgjedhur në fletën aktuale të punës" Private Sub Worksheet_SelectionChange (ByVal Target As Range) "Kontrollo nëse qeliza B1 është zgjedhur nëse Target.Count = 1 Dhe Target.Row = 1 And Target. Kolona = 2 Pastaj "Nëse zgjidhet qeliza B1, kryeni veprimin e kërkuar MsgBox" Ju zgjodhët qelizën B1 "End If End Sub

Macro Excel: Shembulli 6

Kjo procedurë ilustron përdorimin e operatorëve Në Gabim dhe Vazhdoj për të trajtuar gabimet. Ky kod tregon gjithashtu një shembull të hapjes dhe leximit të të dhënave nga një skedar.

"Procedura Sub vendos argumentet Val1 dhe Val2 në vlerat e qelizave A1 dhe B1" nga libri i punës Data.xlsx i vendosur në dosjen C: \ Documents and Settings Sub Set_Values ​​(Val1 As Double, Val2 As Double) Dim DataWorkbook As Workbook On Error GoTo Error Handling " Hapni librin e punës me të dhëna Set DataWorkbook = Workbooks. Open ("C: \ Documents and Settings \ Data") "Cakto vlerat e variablave Val1 dhe Val2 nga libri i punës i specifikuar Val1 = Sheets ( " Fleta1 "). Qelizat (1, 1) Val2 = Fletët ("Fleta 1"). Qelizat (1, 2) DataWorkbook. Mbyllni Dalje Nën Trajtimi i gabimeve: "Nëse skedari nuk gjendet, përdoruesit do t'i kërkohet të vendosë atë që dëshiron. file" në dosjen e dëshiruar dhe më pas vazhdoni ekzekutimin e makros MsgBox "Skedari Data.xlsx nuk u gjet! "& _" Ju lutemi shtoni librin e punës në dosjen C: \ Documents and Settings dhe klikoni OK "Rifillo Fund Sub