Mukati
Muchikamu chino, iwe uchadzidza kuti zvipi zvinogara uye zvinosiyana zviri mumacros, pazvinogona kushandiswa, uye ndeupi musiyano mukuru pakati pemhando dzakasiyana dze data. Izvo zvakare zvicharatidzwa kuti nei zvinoramba zvichidikanwa, kana iwe uchikwanisa kungonyora shanduko uye usamboichinja.
Kufanana nemimwe mitauro yekuronga, data inogona kuchengetwa mumhando kana zvimisikidzo (zvese zviri zviviri zvinowanzonzi midziyo yedata). Uyu ndiwo mutsauko mukuru pakati pepfungwa idzi. Iyo yekutanga inogona kuchinja zvichienderana nezvinoitika muchirongwa. Zvakare, macons anoiswa kamwe chete uye haachinje kukosha kwawo.
Constants inogona kubatsira kana iwe uchida kushandisa iyo yakakura kukosha kwakafanana kakawanda. Panzvimbo pekukopa nhamba, unogona kungonyora zita rekugara. Semuenzaniso, unogona kushandisa nguva dzose "Pi" kuchengeta Pi, inova yakakosha. Ihombe kwazvo, uye nguva imwe neimwe yakaoma kuinyora kana kutsvaga nekuikopa. Uye saka, zvakakwana kunyora mavara maviri, uye nharaunda inoshandisa otomatiki nhamba inodiwa.
Mushandisi weExcel anoda kuzivisa zvinochinjika kana achida kushandura kukosha kwakachengetwa mazviri nguva nenguva. Semuenzaniso, unogona kuseta shanduko inonzi sVAT_Rate, iyo inochengetedza iyo ikozvino VAT mwero wechigadzirwa. Kana ikachinja, unogona kukurumidza kuigadzirisa. Izvi zvinonyanya kubatsira kune avo vanoita bhizinesi muUnited States, uko zvimwe zvinhu zvinogona kunge zvisiri pasi peVAT zvachose (uye mutero uyu unosiyanawo nenyika nenyika).
Dhata Dhata
Imwe neimwe mudziyo wedata inogona kuve imwe yemhando dzinoverengeka. Heino tafura inotsanangura mhando dzemhando dzeruzivo rwakagadziriswa. Kune dzakawanda dzadzo, uye zvingaita senge kune anotanga pakutanga kuti dzinodzokorora mumwe nemumwe. Asi uku kunzwa kwekunyepedzera. Verenga kuti udzidze kuti nei kudoma chaiyo mhando yedata kwakakosha.
Hazvikurudzirwi kushandisa rudzi rwe data rinotora nzvimbo yakawanda mundangariro yenhamba diki. Somuenzaniso, nokuda kwenhamba 1, zvakakwana kushandisa rudzi rweByte. Izvi zvichava nemhedzisiro yakanaka pakuita kweiyo executable module, kunyanya pamakomputa asina simba. Asi zvakakosha kuti usaenda kure zvakanyanya pano. Kana iwe ukashandisa rudzi rwe data iro rakanyanya compact, kukosha kwepamusoro kunogona kusakwana mairi.
Kuzivisa Constants uye Variables
Kushandisa mudziyo wedata usina kutanga wazvizivisa kunoodzwa mwoyo zvakanyanya. Zvadaro matambudziko akawanda anogona kumuka, kudzivirira izvo zvakakosha kunyora mitsetse miduku miduku yekodhi nekuverengwa kwezvinhu zvakasiyana-siyana kana zvigadziriswa.
Kuzivisa shanduko, chirevo cheDim chinoshandiswa. Semuenzaniso, seizvi:
Dim Variable_Name As Integer
Variable_Name ndiro zita rekuchinja. Tevere, iyo Sezvo opareta yakanyorwa, ichiratidza rudzi rwe data. Panzvimbo petambo "Variable_Name" uye "Integer", unogona kuisa zita rako pachako uye data data.
Constants inogona zvakare kuziviswa, asi iwe unofanirwa kutanga wataura kukosha kwavo. Imwe yesarudzo ndeiyi:
Const iMaxCount = 5000
Mukururamisira, mune dzimwe nguva unogona kuita pasina kuzivisa shanduko, asi mune ino kesi ivo vanozongopihwa mhando Variant. Nekudaro, izvi hazvikurudzirwe nekuda kwezvikonzero zvinotevera:
- Variant inogadziriswa zvishoma nezvishoma, uye kana paine akawanda akasiyana, kugadzirisa ruzivo kunogona kudzikiswa zvakanyanya pamakomputa asina simba. Zvingaita sekuti iwo masekonzi achasarudza? Asi kana iwe uchifanira kunyora nhamba huru yemitsara yekodhi, uye wozoimhanyisa pamakombiyuta asina simba (ayo achiri kutengeswa, zvichipiwa kuti mahofisi ehofisi emazuva ano anoda RAM yakawanda), unogona kumisa zvachose basa racho. Pane zviitiko apo kunyorwa zvisina kunaka kwe macros kwakatungamira mukutonhora kwe smartbooks ane mashoma RAM uye asina kugadzirwa kuita mabasa akaomarara.
- Misprints mumazita inobvumirwa, iyo inogona kudziviswa nekushandisa Option Explicit statement, iyo inobvumira iwe kuwana undeclared variable, kana imwe yakawanikwa. Iyi inzira iri nyore yekuona zvikanganiso, sezvo typo diki ichiita kuti muturikiri atadze kuziva shanduko. Uye kana iwe ukabatidza shanduko yekuzivisa modhi, muturikiri haangokubvumidza iwe kumhanyisa macro kana midziyo yedata yakawanikwa isina kuziviswa pakutanga kwemodule.
- Dzivisa kukanganisa kunokonzerwa nemhando dzakasiyana dzisingaenderane nerudzi rwe data. Kazhinji, kupa kukosha kwemavara kune nhamba yakazara kunokanda kukanganisa. Hongu, kune rumwe rutivi, rudzi rwegeneric runopihwa pasina chiziviso, asi kana dzikaziviswa pachine nguva, ipapo kukanganisa kwakasarudzika kunogona kudzivirirwa.
Naizvozvo, kunyangwe zvese, zvinokurudzirwa zvakanyanya kuzivisa ese akasiyana muExcel macros.
Pane chimwezve chinhu chekuchengeta mundangariro kana uchizivisa zvinosiyana. Zvinogoneka kuti usape chero kukosha kune chinoshanduka kana uchichizivisa, asi mune iyi kesi inowana yakasarudzika kukosha. Semuyenzaniso:
- Mitsetse inoitwa isina chinhu.
- Nhamba dzinotora kukosha 0.
- Mabhii emhando yeBoolean pakutanga anoonekwa semanyepo.
- Zuva rekutanga ndiDecember 30, 1899.
Semuenzaniso, haufanirwe kugovera kukosha 0 kune imwe nhamba yakasiyana kana pasina kukosha kwakambotaurwa. Atova nenhamba iyi.
Sarudzo Yakajeka Chirevo
Ichi chirevo chinokutendera iwe kuzivisa ese akasiyana anoshandiswa muVBA kodhi uye kuona kuvepo kwechero midziyo isina kuziviswa kodhi isati yaitwa. Kuti ushandise chimiro ichi, ingonyora mutsara weSarudzo Yakajeka kodhi kumusoro chaiko kweiyo macro kodhi.
Kana iwe uchida kuisa chirevo ichi mukodhi yako nguva dzese, unogona kuzviita uchishandisa yakakosha kuseta muVBA mupepeti. Kuti uite iyi sarudzo, unofanira:
- Enda kunzvimbo yekusimudzira munzira - Zvishandiso> Sarudzo.
- Muhwindo rinovhurwa mushure meizvi, vhura iyo Editor tab.
- Uye pakupedzisira, tarisa bhokisi riri padyo ne Inoda Variable Declaration chinhu.
Paunenge wapedza matanho aya, tinya bhatani rekuti "OK".
Ndizvozvo, ikozvino pakunyora yega yega macro, mutsara uyu unozoiswa kumusoro kwekodhi otomatiki.
Kuwanda kweConstants uye Variables
Chimwe nechimwe chakasiyana kana chinogara chiripo chine chiyero chakaganhurirwa. Zvinoenderana nekwaunozvizivisa.
Ngatitii tine basa Total_Cost(), uye inoshandisa shanduko sVAT_Rate. Zvichienderana nechinzvimbo mumodule, ichava nenzvimbo yakasiyana:
| Sarudzo Yakajeka Dim sVAT_Rate As single Basa Rakazara_Mutengo() Sezvo Kaviri . . . Pedza Basa | Kana shanduko ikaziviswa pamusoro pemodule pachayo, inopararira mukati meiyo module. Ndiko kuti, inogona kuverengwa nemaitiro ese. Uyezve, kana imwe yemaitiro yakashandura kukosha kwekusiyana, ipapo iyo inotevera ichaverengawo kukosha uku kwakagadziriswa. Asi mune mamwe mamodule iyi shanduko haisati ichizoverengwa. |
| Sarudzo Yakajeka Basa Rakazara_Mutengo() Sezvo Kaviri Dim sVAT_Rate As single . . . Pedza Basa | Muchiitiko ichi, shanduko inoziviswa mukati mekuita, uye muturikiri anokanda kukanganisa kana ikashandiswa mune imwe nzira. |
Kana iwe uchida kuti shanduko iverengerwe nemamwe ma module, iwe unofanirwa kushandisa iyo Public keyword pane iyo Dim keyword. Saizvozvo, iwe unogona kudzikisira chiyero chekuchinja kune yazvino module uchishandisa iyo Public statement, iyo inonyorwa pachinzvimbo chezwi Dim.
Iwe unogona kuseta chiyero chezvimiro nenzira yakafanana, asi kiyi izwi pano rakanyorwa pamwe chete neConst opareta.
Heino tafura ine muenzaniso wakanaka wekuti inoshanda sei neanogara uye akasiyana.
| Sarudzo Yakajeka Veruzhinji sVAT_Rate SeMumwechete Public Const iMax_Count = 5000 | Mumuenzaniso uyu, unogona kuona mashandisirwo ezwi rePublic keyword kuzivisa shanduko, uye izvo zvaunoda kunyora muVisual Basic edhita kuti uzivise ruzhinji nguva dzose. Iyo chiyero chezvikoshi izvi zvinoshanda kune ese ma module. |
| Sarudzo Yakajeka Yakavanzika sVAT_Rate SeMumwechete Private Const iMax_Count = 5000 | Pano, zvinosiyana uye zvigadziriso zvinoziviswa uchishandisa iyo Yakavanzika kiyi. Izvi zvinoreva kuti ivo vanogona kungoonekwa mukati meiyo module yazvino, uye maitiro mune mamwe ma module haagone kuashandisa. |
Sei zvisingaperi uye zvinoshanduka zvichidiwa
Kushandiswa kwezvigadziriswe uye zvinoshanduka zvinokubvumira kuti uwedzere chiyero chekunzwisisa kwekodhi. Uye kana vanotanga kazhinji vasina mibvunzo pamusoro pekuti sei mabhii anodiwa, saka pane zvakawanda zvisinganzwisisike maererano nekudiwa kweanogara. Uye mubvunzo uyu unoratidzika, pakutanga, une musoro. Mushure mezvose, iwe unogona kuzivisa shanduko kamwechete uye usazoichinja zvakare.
Mhinduro inoshanduka kuve kune imwe nzvimbo mundege imwechete maererano nekushandiswa kwemhando dzedata dzinotora nzvimbo yakakura mundangariro. Kana isu tiri kubata nehuwandu hukuru hwemhando dzakasiyana, tinogona netsaona kushandura mudziyo uripo. Kana mushandisi akarayira kuti imwe kukosha haizombofa yakachinja, ipapo nharaunda inongozvidzora izvi.
Izvi zvinonyanya kukosha kana iyo macro yakanyorwa nevanoverengeka programmers. Mumwe anogona kuziva kuti zvimwe zvakasiyana hazvifanirwe kuchinja. Uye imwe yacho haisi. Kana iwe ukatsanangura Const opareta, mumwe mugadziri achaziva kuti kukosha uku hakuchinji.
Kana, kana pane chisingaperi chine zita rimwe chete, uye shanduko ine zita rakasiyana, asi rakafanana. Mugadziri anogona kungovavhiringa. Semuenzaniso, imwe vhezheni isingade kuchinjwa inonzi Variable11, uye imwe inogona kupepetwa inonzi Variable1. Munhu anogona otomatiki, kana achinyora kodhi, netsaona kusvetuka imwe unit uye asingazvione. Nekuda kweizvozvo, mudziyo wemakoshero uchashandurwa, uyo usingafanirwe kubatwa.
Kana kuti mugadzirisi pachake anogona kukanganwa kuti ndeapi akasiyana-siyana aanogona kubata uye aasingakwanise. Izvi zvinowanzoitika kana iyo kodhi inonyorwa kwemavhiki akati wandei, uye saizi yayo inova yakakura. Munguva ino, zviri nyore kwazvo kukanganwa kunyange izvo izvi kana izvo zvakasiyana zvinorevei.
Ehe, iwe unogona kuita nezvakataurwa mumamiriro ezvinhu aya, asi hazvisi nyore here kutsanangura izwi rekuti Const?
mhedziso
Zvinosiyana-siyana chinhu chakakosha chemacro programming, iyo inokutendera iwe kuti uite mashandiro akaomarara, kubva pakuverenga kusvika pakuzivisa mushandisi nezve zvimwe zviitiko kana kutsanangura chaiwo kukosha mumaseru espredishiti.
Constants inofanirwa kushandiswa kana mugadziri achiziva chokwadi kuti zviri mukati memidziyo iyi hazvishanduke mune ramangwana. Zvinokurudzirwa kuti usashandise zvinoshanduka panzvimbo, sezvo zvichikwanisika kukanganisa kukanganisa.