4 pogreške koje možete izbjeći kad programirate Excel makronaredbe s VBA

Microsoft Excel je već vrlo sposoban alat za analizu podataka, ali s mogućnošću automatizacije ponavljajućih zadataka makronaredbama Kako slati e-poštu s Excel proračunske tablice pomoću VBA skripti Kako slati e-poštu s Excel proračunske tablice pomoću VBA skripti Naš predložak koda pomoći će vam da postavite nadogradite automatske poruke e-pošte iz programa Excel koristeći objekte podataka kolaboracije (CDO) i VBA skripte. pisanjem jednostavnog koda u (VBA) to je mnogo snažnije. Međutim, ako se koristi pogrešno, VBA može uzrokovati puno problema.

Čak i ako niste programer, VBA nudi jednostavne funkcije koje vam omogućavaju da dodate neke zaista impresivne funkcionalnosti u svoje proračunske tablice, tako da ne odlazite još samo!

Bez obzira jeste li VBA guru, koji stvara nadzorne ploče u Excel-u Izradite vlastitu nadzornu ploču vremenske prognoze. Na ovoj Google proračunskoj tablici Master Class izradite vlastitu vremensku nadzornu ploču U ovoj Master klasi proračunske tablice Želite li svoj račun na Google disku pretvoriti u inteligentni sustav analize vremena , i živjeti fantaziju Star Trek? U redu! ili novajlija, koja samo zna pisati jednostavne skripte koje rade osnovne proračune ćelija, možete slijediti jednostavne tehnike programiranja, koje će vam pomoći poboljšati izglede za pisanje čistog i bez greške.

Početak rada s VBA

Ako prije niste programirali u VBA u Excelu, omogućavanje alata za razvojne programere to je zapravo prilično jednostavno. Samo idi na Datoteka > Opcije i onda Prilagodite vrpcu. Samo premjesti razvijač naredbena skupina s lijevog okna na desno.

Provjerite je li potvrdni okvir omogućen, a sada će se na vašem Excel izborniku pojaviti kartica Developer.

Najlakši način da u ovom trenutku uđete u prozor uređivača koda je samo da kliknete na Pogledaj kod gumb ispod kontrole u izborniku programera.

1. Grozna imena promjenjivih

Sad kad ste u prozoru s kôd, vrijeme je da počnete pisati VBA kôd Kako možete napraviti vlastitu jednostavnu aplikaciju s VBA Kako možete napraviti svoju vlastitu jednostavnu aplikaciju s VBA Za one od vas koji bi zaista voljeli pisati vaš vlastiti zahtjev, ali nikad prije niste upisali niti jedan redak koda, provest ću vas kroz izradu vašeg…. Prvi važan korak u većini programa, bilo da se radi o VBA ili bilo kojem drugom jeziku, je definiranje vaših varijabli.

Kroz mojih nekoliko desetljeća pisanja koda naišao sam na mnoge škole razmišljanja kada je u pitanju konvencionalno imenovanje konvencija i naučio sam neke stvari na teži način. Evo kratkih savjeta za stvaranje imena varijabli:

• Učinite ih što kraćim.
• Učinite ih što je moguće opisnijim.
• Predgovor s promjenjivom vrstom (boolean, integer, itd ...).
• Ne zaboravite koristiti pravi opseg (vidi dolje).

Evo primjera snimke zaslona iz programa koji često koristim za upućivanje WMIC Windows poziva iz Excel-a radi prikupljanja podataka o računalu Kako vidjeti sve podatke o vašem računalu pomoću jednostavne skripte VBA Excel-a Kako vidjeti sve informacije na računalu pomoću jednostavnog Excela VBA Script Script Excel i 10 minuta rada pružit će vam detaljnije informacije o vašem računalu nego što ste ikada mislili da je moguće. Zvuči previše dobro da bi bilo istinito? To je Windows, ako ga znate koristiti. .

Ako želite koristiti varijable unutar bilo koje funkcije unutar modula ili objekta (objasnit ću ovo u nastavku), tada to morate proglasiti kao “javnost” varijablu prethodivši deklaraciji sa Javnost. Inače se varijable deklariraju tako da ih učinite riječju zamagliti.

Kao što vidite, ako je varijabla cijeli broj, ima je predznak int. Ako je to niz, onda str. To će vam pomoći kasnije dok programirate jer ćete uvijek znati kakvu vrstu podataka sadrži varijabla, samo pogledavanjem imena. Također ćete primijetiti da ako postoji nešto poput niza koji sadrži ime računala, tada se naziva varijabla strComputerName.

Izbjegavajte stvaranje vrlo zbrkanih ili konfuznih imena varijabli koje samo vi razumijete. Olakšajte drugom programeru da dođe iza vas i shvati što sve to znači!

Još jedna pogreška koju ljudi čine je ostavljanje naziva lista kao zadanih “Sheet1”, “Sheet2”, itd. ... To dodaje dodatnu zbrku u program. Umjesto toga, listove imenovajte tako da imaju smisla.

Na ovaj način, kada se spominjete ime lista u vašem Excel VBA kodu Korištenje VBA za automatizaciju Internet Explorer sesija iz Excelove proračunske tablice Korištenjem VBA za automatizaciju sesija Internet Explorera iz Excelove proračunske tablice Njegova integracija s Windowsom omogućuje kontrolu Internet Explorera u više iznenađujuće načine korištenja skripte Visual Basic for Applications (VBA) iz bilo koje aplikacije koja je podržava, kao što su Word, Outlook ili Excel. , odnosite se na ime koje ima smisla. U gornjem primjeru imam list na kojem povlačim podatke o mreži, pa ih zovem “Mreža”. Sada kod, u bilo kojem trenutku kada želim uputiti na mrežni list, mogu to brzo učiniti bez traženja broja lista.

2. Razbijanje umjesto petlje

Jedan od najčešćih problema koji noviji programer ima 6 životnih navika koje vas danas može naučiti 6 životnih navika koje vas danas može naučiti Programiranje Sve što trebate znati o uspješnom životu, možete dobiti iz računalnog programa. Ne vjerujete mi? Nastavi čitati. kad počnu pisati kod pravilno se bave petljama. A budući da toliko mnogo ljudi koji koriste Excel VBA kodiraju novorođenčad, loše petlje je epidemija.

Looping je u programu Excel vrlo čest jer često obrađujete vrijednosti podataka ispod cijelog retka ili stupca, tako da ih morate petljati da biste ih obradili. Novi programeri često žele odmah izbiti iz petlje (bilo za petu For, bilo dok pogledati) kad je određeni uvjet istinit.

Možete zanemariti složenost gornjeg koda, samo imajte na umu da unutar interne izjave IF postoji mogućnost izlaska iz petlje For ako je uvjet istinit. Evo jednostavnijeg primjera:

Za x = 1 do 20 ako je x = 6, tada izađite za y = x + intRoomTemp Sljedeće i 

Novi programeri koriste ovaj pristup jer je to jednostavno. Kada se pojavi stanje koje čekate kako biste izašli iz petlje, snažno je iskušenje da odmah iskočite iz njega, ali nemojte to činiti.

Češće nego ne, kod koji dolazi nakon toga “pauza” Važno je obraditi, čak i posljednji put kroz petlju prije izlaska. Mnogo čistiji i profesionalniji način da se bave uvjetima u kojima želite ostaviti petlju na pola puta, jeste samo da taj uvjet izlaska uključite u nešto poput izjave "Dok"..

Dok je (x> = 1 I x<=20 AND x6) For x = 1 To 20 y = x + intRoomTemp Next i Wend 

To omogućuje logičan tijek koda, s posljednjim postupkom kad je x 5, a zatim lijepo izlazi iz kruga For za brojanje do 6. Nema potrebe za uključivanjem nespretnih naredbi EXIT ili BREAK u sredini.

3. Ne upotrebljavamo nizove

Još jedna zanimljiva pogreška koju novi VBA programeri Osnove računalnog programiranja 101 - Varijable i DataTypes Osnove računalnog programiranja 101 - Varijable i DataTypes Nakon uvođenja i razgovora o objektno orijentiranom programiranju prije i odakle dolazi njegov imenjak, mislio sam da je vrijeme da proći kroz apsolutne osnove programiranja na nejezički specifičan način. Ovaj ... make pokušava obraditi sve unutar brojnih ugniježđenih petlji koje se filtriraju kroz redove i stupce tijekom računanja.

Iako ovo može raditi, to bi moglo dovesti i do većih problema s performansama, ako morate stalno izvoditi iste izračune na istim brojevima u istom stupcu. Prelistavanje kroz ovaj stupac i vađenje vrijednosti svaki put nije samo mučno programirati, to je ubojica na vašem procesoru. Učinkovitiji način za obradu dugih popisa brojeva je korištenje matrice.

Ako nikada prije niste koristili niz, ne bojte se. Zamislite niz kao ladicu za ledene kocke s određenim brojem “kocke” možete staviti podatke. Kocke su označene brojevima od 1 do 12, a tako ste i vi “staviti” podatke u njih.

Niz možete lako definirati samo ako upišete Dim arrMyArray (12) kao Integer.

Ovo stvara “ladica” s 12 utora na raspolaganju.

Evo kako može izgledati kôd petlje za retke bez niza:

Pod test1 () Dim x kao cijeli broj intNumRows = domet ("A2", raspon ("A2"). Kraj (xldown)). Retci.broj opsega ("A2"). Odaberite za x = 1 za intNumRows ako raspon (" Vrijednost "& str (x)) < 100 then intTemp = (Range("A" & str(x)).value) * 32 - 100 End If ActiveCell.Offset(1, 0).Select Next End Sub 

U ovom primjeru kod se obrađuje dolje kroz svaku pojedinu ćeliju u rasponu i vrši proračun temperature.

Kasnije u programu, ako ikada želite izvršiti neki drugi izračun na tim istim vrijednostima, morat ćete duplicirati taj kôd, obraditi kroz sve te ćelije i izvršiti novi proračun.

Sada, ako umjesto toga, koristite niz, tada možete pohraniti 12 vrijednosti u retku u svoj zgodan niz za pohranu. Zatim kasnije, kad god želite pokrenuti izračun s tim brojevima, oni su već u memoriji i spremni za rad.

Pod test1 () Dim x kao cijeli broj intNumRows = domet ("A2", raspon ("A2"). Kraj (xldown)). Retci.broj opsega ("A2"). Odaberite x = 1 za intNumRows arrMyArray (x- 1) = Raspon ("A" & str (x)). Vrijednost) ActiveCell.Offset (1, 0). Odaberite sljedeći potkraj 

“x-1” za upućivanje na element array je potrebno samo zato što petlja For počinje od 1. Elementi polja trebaju početi od 0.

Ali nakon što svoj niz sav bude nabijen vrijednostima iz retka, kasnije u programu možete jednostavno povezati sve izračune koje želite koristeći matricu..

Sub TempCalc () Za x = 0 Za UBound (arrMyArray) arrMyTemps (y) = arrMyArray (x) * 32 - 100 Sljedeći kraj Sub 

Ovaj primjer prolazi kroz cjelinu nizova redaka (UBound vam daje broj vrijednosti podataka u polju), vrši izračun temperature, a zatim ga stavlja u drugi niz zvan arrMyTemps.

Možete vidjeti koliko je jednostavniji drugi kôd za izračun. Od ovog trenutka nadalje, svaki put kada želite izvršiti više izračunavanja na istom skupu brojeva, vaš je niz već unaprijed učitan i spreman za rad.

4. Korištenje previše referenci

Bilo da programirate u punopravnom Visual Basic ili VBA, morat ćete ih uključiti “reference” za pristup određenim značajkama, poput pristupa Access bazi podataka ili pisanju rezultata u tekstnu datoteku.

Upućivanja su nekako takva “knjižnice” ispunjen funkcionalnošću koju možete dodirnuti ako omogućite tu datoteku. Reference možete pronaći u prikazu za razvojne programere klikom na alat u izborniku, a zatim kliknete na Reference.

Ono što ćete naći u ovom prozoru su sve trenutno odabrane reference za vaš trenutni VBA projekt.

Po mom iskustvu, moji se odabiri mogu mijenjati iz jedne Excelove instalacije u drugu i s jednog računala na drugo. Mnogo ovisi o tome što su drugi ljudi radili s Excelom na ovom računalu, jesu li neki dodaci ili značajke dodani ili omogućeni ili su možda neki drugi programeri koristili određene reference u prošlim projektima.

Razlog je dobro provjeriti ovaj popis zato što nepotrebne reference troše resurse sustava. Ako ne upotrebljavate nikakve manipulacije XML datotekama, zašto onda odabrati Microsoft XML odabran? ako ne komunicirate s bazom podataka, uklonite Microsoft DAO. Ako ne napišete izlaz u tekstualnu datoteku, uklonite je Microsoft Runtime skripta.

Ako niste sigurni što rade ove odabrane reference, pritisnite F2 i vidjet ćete Object Explorer. Na vrhu ovog prozora, možete odabrati referentnu biblioteku za pregled.

Nakon odabira, vidjet ćete sve objekte i dostupne funkcije, na koje možete kliknuti da biste saznali više.

Na primjer, kad kliknem na biblioteku DAO, brzo mi postaje jasno da se tu radi o povezivanju i komunikaciji s bazama podataka.

Smanjenje broja referenci koje koristite u svom programu programiranja je samo dobar smisao i pomoći će vam da se vaša cjelokupna aplikacija pokrene efikasnije.

Programiranje u Excelu VBA

Čitava ideja o stvarnom pisanju koda u programu Excel Ušteda vremena s tekstualnim operacijama u Excelovoj štednji vremena s tekstualnim operacijama u Excelu Excel može učiniti magiju brojevima i može podnijeti znakove jednako dobro. Ovaj priručnik pokazuje kako analizirati, pretvarati, zamijeniti i uređivati ​​tekst u proračunskim tablicama. Te će vam osnove omogućiti obavljanje složenih transformacija. plaši puno ljudi, ali taj strah zaista nije potreban. Visual Basic za aplikacije vrlo je jednostavan jezik za učenje, a ako slijedite gore spomenute osnovne uobičajene prakse, osigurat ćete da je vaš kôd čist, učinkovit i lak za razumijevanje..

Da li je kod u VBA? Kakve ste lekcije naučili tijekom godina koje po prvi put možete dijeliti s drugim čitateljima koji uče VBA? Podijelite svoje savjete u odjeljku s komentarima u nastavku!

Bonusi slike: Computer by www.BillionPhotos.com via Shutterstock, Colorful Ice Cube Trays by hahauk via Shutterstock.com