txheem
Hauv VBA, zoo li hauv lwm yam lus programming, qhov hloov pauv thiab qhov tsis tu ncua yog siv los khaws cov txiaj ntsig. Raws li lub npe implies, variables tuaj yeem hloov pauv, thaum lub sijhawm khaws cov nqi ruaj khov.
Piv txwv li, ib qho tsis tu ncua Pi khw muag khoom tus nqi 3,14159265… Tus lej "Pi" yuav tsis hloov thaum lub sijhawm ua tiav ntawm qhov kev pab cuam, tab sis nws tseem yooj yim dua los khaws cov nqi li qhov tas li.
Tib lub sijhawm, peb tuaj yeem siv qhov sib txawv sVAT_Rate khaws VAT tus nqi ntawm cov khoom yuav khoom. Tus nqi sib txawv sVAT_Rate tej zaum yuav txawv nyob ntawm qhov khoom yuav.
Cov hom ntaub ntawv
Txhua qhov sib txawv thiab qhov tsis tu ncua yog ntawm cov ntaub ntawv tshwj xeeb. Cov lus hauv qab no teev cov ntaub ntawv hom siv hauv VBA nrog cov lus piav qhia thiab ntau yam ntawm cov txiaj ntsig tau:
| Cov ntaub ntawv hom | loj | Hauj lwm | Ntau ntawm qhov tseem ceeb |
|---|---|---|---|
| Byte | 1 wb | Cov lej zoo; feem ntau siv rau cov ntaub ntawv binary | los ntawm 0 rau 255 |
| Tub Npis | 2 wb | Yuav muaj tseeb los yog cuav | Tseeb los tsis tseeb |
| Integer | 2 wb | Tag nrho cov lej (tsis muaj feem feem) | los ntawm -32 txog +768 |
| ntev | 4 wb | Cov lej loj (tsis muaj feem ntau) | от -2 147 483 648 до +2 147 483 647 |
| ib | 4 wb | Tib precision floating point tooj | los ntawm -3.4e38 rau +3.4e38 |
| ob chav | 8 wb | Muab ob npaug rau precision floating point tooj | los ntawm -1.8e308 rau +1.8e308 |
| txiaj | 8 wb | Floating point tooj, nrog rau tus lej tas li ntawm cov lej lej | от -922 337 203 685 477.5808 до +922 337 203 685 477.5807 |
| Hnub | 8 wb | Hnub tim thiab lub sij hawm – Cov ntaub ntawv ntawm hom Hnub yog sawv cev los ntawm ib tug floating point tooj. Ib feem ntawm tus lej no qhia txog hnub, thiab feem ntau qhia txog lub sijhawm. | Lub Ib Hlis 1, 100 txog Lub Kaum Ob Hlis 31, 9999 |
| Nruas | 4 wb | Khoom siv | Txhua yam khoom siv |
| txoj hlua | tab tom hloov | Cim cim. Hom hlua tuaj yeem yog qhov ntev lossis qhov sib txawv. Feem ntau siv nrog qhov ntev sib txawv | Tsau ntev - txog li 65 cim. Qhov ntev kuj sib txawv - txog li 500 billion cim |
| variant | tab tom hloov | Tej zaum yuav muaj hnub tim, ntab, lossis kab cim. Hom no yog siv nyob rau hauv rooj plaub uas nws tsis paub ua ntej yam ntaub ntawv yuav nkag mus. | Number - Ob, hlua - hlua |
Pom tseeb, siv cov lus saum toj no thiab xaiv hom ntaub ntawv zoo, koj tuaj yeem siv lub cim xeeb ntau dua kev lag luam (piv txwv li, xaiv hom ntaub ntawv Integer es tsis txhob ntev or ib es tsis txhob ob chav). Txawm li cas los xij, thaum siv ntau hom ntaub ntawv compact, koj yuav tsum tau ceev faj tias koj cov cai tsis tau sim kom haum cov txiaj ntsig loj rau lawv.
Tshaj tawm qhov sib txawv thiab qhov tsis xwm yeem
Tus neeg txhais lus ceeb toom: Hais txog kev hloov pauv hauv VBA, nws tsim nyog hais txog ib qho tseem ceeb tshaj plaws. Yog tias peb tshaj tawm qhov sib txawv tab sis tsis muab tus nqi rau nws, ces nws yog pib nrog tus nqi pib:
• Cov kab ntawv sau tau pib nrog cov hlua khoob;
• tus lej — tus nqi 0;
• hom hloov pauv Tub Npis - Tsis tseeb;
• hnub – Kaum Ob Hlis 30, 1899.
Ua ntej siv qhov sib txawv lossis qhov tsis tu ncua, nws yuav tsum tau tshaj tawm. Txhawm rau ua qhov no, ntxiv cov kab hauv qab no yooj yim ntawm cov lej rau macro:
Dim Имя_Переменной As Тип_Данных
Nyob rau hauv kab saum toj no ntawm code Variable_name yog lub npe ntawm qhov sib txawv uas yuav siv tau rau hauv cov cai, thiab Cov ntaub ntawv_Type yog ib qho ntawm cov ntaub ntawv hom los ntawm lub rooj muab me ntsis ua ntej hauv kab lus no. Piv txwv li:
Dim sVAT_Rate As Single Dim i As Integer
Constants tau tshaj tawm zoo ib yam, tab sis thaum tshaj tawm tas li, lawv cov nqi yuav tsum tau qhia tam sim. Piv txwv li, zoo li no:
Const iMaxCount = 5000 Const iMaxScore = 100
Nws tsis tas yuav tshaj tawm cov hloov pauv hauv Excel. Los ntawm lub neej ntawd, txhua tus nkag mus tab sis tsis tshaj tawm cov hloov pauv hauv Excel yuav muaj hom variant thiab yuav tuaj yeem lees txais cov lej thiab cov ntawv sau nqi.
Yog li, tus programmer tuaj yeem siv qhov hloov pauv tshiab txhua lub sijhawm (txawm tias nws tsis tau tshaj tawm), thiab Excel yuav kho nws raws li qhov sib txawv ntawm hom. variant. Txawm li cas los xij, muaj ntau qhov laj thawj vim li cas qhov no yuav tsum tsis txhob ua:
- Kev siv lub cim xeeb thiab kev suav ceev. Yog tias koj tsis tshaj tawm qhov sib txawv nrog qhov qhia txog hom ntaub ntawv, ces los ntawm lub neej ntawd nws yuav raug teeb tsa rau hom variant. Hom ntaub ntawv no siv lub cim xeeb ntau dua li lwm hom ntaub ntawv. Ob peb lub bytes ntxiv rau qhov sib txawv yuav tsis zoo li ntau, tab sis hauv kev xyaum, cov kev pab cuam tuaj yeem muaj ntau txhiab tus hloov pauv (tshwj xeeb tshaj yog thaum ua haujlwm nrog arrays). Yog li ntawd, lub cim xeeb ntxiv siv los ntawm kev sib txawv xws li variant, piv rau variables ntawm hom Integer or ib, tuaj yeem ntxiv rau qhov tseem ceeb. Tsis tas li ntawd, kev ua haujlwm nrog kev sib txawv ntawm hom variant raug tua ntau qeeb dua nrog cov sib txawv ntawm lwm hom, feem, ib txhiab ntxiv hloov pauv ntawm hom variant tuaj yeem ua rau kev suav qeeb qeeb.
- Kev tiv thaiv typos hauv cov npe sib txawv. Yog tias txhua qhov sib txawv tau tshaj tawm, ces VBA nqe lus tuaj yeem siv − Option Explicit (peb yuav tham txog nws tom qab) txhawm rau txheeb xyuas txhua qhov hloov pauv tsis tau tshaj tawm. Qhov no tshem tawm qhov tshwm sim ntawm qhov yuam kev hauv qhov kev zov me nyuam vim tias sau tsis raug lub npe hloov pauv. Piv txwv li, siv lub npe sib txawv sVAT_Rate, koj tuaj yeem ua tus typo thiab, muab tus nqi rau qhov sib txawv no, sau: "VATRate = 0,175". Nws cia siab tias txij no mus, qhov sib txawv sVAT_Rate yuav tsum muaj tus nqi 0,175 - tab sis tau kawg nws tsis. Yog tias hom kev tshaj tawm yuav tsum tau ua ntawm txhua qhov kev siv tau siv, ces VBA compiler yuav qhia qhov yuam kev tam sim ntawd, vim nws yuav tsis pom qhov sib txawv. VATRate ntawm cov tshaj tawm.
- Qhov tseem ceeb ntawm qhov tseem ceeb uas tsis sib xws rau hom tshaj tawm ntawm qhov sib txawv. Yog tias koj tshaj tawm qhov sib txawv ntawm ib hom thiab sim muab cov ntaub ntawv ntawm ib hom sib txawv rau nws, koj yuav tau txais qhov yuam kev, uas, yog tias tsis kho, tuaj yeem ua rau qhov kev zov me nyuam poob. Thaum xub thawj siab ib muag, qhov no yuav zoo li yog vim li cas tsis tshaj tawm qhov sib txawv, tab sis qhov tseeb, dua li ua ntej nws hloov tawm tias ib qho ntawm cov kev hloov pauv tau txais cov ntaub ntawv tsis raug uas nws yuav tsum tau txais - ntau qhov zoo dua! Txwv tsis pub, yog tias qhov kev zov me nyuam txuas ntxiv mus, cov txiaj ntsig yuav tsis raug thiab tsis xav txog, thiab nws yuav nyuaj dua los nrhiav qhov ua rau yuam kev. Nws kuj tseem ua tau tias macro yuav "ua tiav" ua tiav. Raws li qhov tshwm sim, qhov yuam kev yuav mus tsis pom thiab ua haujlwm yuav txuas ntxiv nrog cov ntaub ntawv tsis raug!
Hauv qhov no, nws yog qhov tsim nyog los kuaj xyuas cov ntaub ntawv tsis raug thiab kho cov kev ua yuam kev hauv cov cai kom ntxov li sai tau. Rau cov laj thawj no, nws raug nquahu kom koj tshaj tawm txhua qhov hloov pauv thaum sau VBA macro.
Option Explicit
Tus neeg teb xov tooj Option Explicit ua rau txhua qhov sib txawv uas yuav raug siv hauv VBA code kom tshaj tawm, thiab chij tag nrho cov tsis tau lees paub qhov txawv raws li qhov tsis raug thaum muab tso ua ke (ua ntej kev ua tiav code pib). Kev thov tus neeg teb xov tooj no tsis yooj yim - tsuas yog sau cov kab no nyob rau sab saum toj ntawm VBA cov ntaub ntawv:
Option Explicit
Yog tias koj xav tau ib txwm ntxig Option Explicit mus rau sab saum toj ntawm txhua tus tshiab VBA module tsim, qhov no tuaj yeem ua tiav. Txhawm rau ua qhov no, koj yuav tsum qhib qhov kev xaiv Yuav Tsum Muaj Kev Tshaj Tawm Tshaj Tawm hauv VBA editor nqis.
Qhov no yog ua li no:
- Los ntawm Visual Basic Editor ntawv qhia zaub mov, nyem cuab yeej > Options
- Hauv qhov dialog uas tshwm sim, qhib lub tab editor
- Xaiv lub thawv Yuav Tsum Muaj Kev Tshaj Tawm Tshaj Tawm thiab nias OK
Thaum enabled, txoj hlua Option Explicit yuav tau cia li tso rau ntawm qhov pib ntawm txhua qhov tshiab module tsim.
Scope ntawm Variables thiab Constants
Txhua qhov kev tshaj tawm sib txawv lossis qhov tsis tu ncua muaj nws tus kheej txwv, uas yog, ib feem ntawm qhov kev pab cuam uas muaj qhov sib txawv no. Qhov Scope nyob ntawm qhov chaw tshaj tawm ntawm qhov sib txawv lossis qhov tsis tu ncua. Siv, piv txwv li, qhov sib txawv sVAT_Rate, uas yog siv rau hauv kev ua haujlwm Total_Cost. Cov lus hauv qab no tham txog ob qho kev xaiv rau kev hloov pauv hloov pauv sVAT_Ratetshaj tawm nyob rau hauv ob txoj hauj lwm sib txawv nyob rau hauv lub module:
Option Explicit Dim sVAT_Rate As Single Function Total_Cost() As Ob... End Function | Yog qhov hloov pauv sVAT_Rate tshaj tawm thaum pib ntawm lub module, ces qhov Scope ntawm qhov sib txawv no yuav yog tag nrho module (piv txwv li qhov sib txawv sVAT_Rate yuav raug lees paub los ntawm tag nrho cov txheej txheem hauv qhov module). Yog li ntawd, yog hais tias nyob rau hauv lub function Total_Cost kuj sib txawv thiab sVAT_Rate yuav muab qee tus nqi, tom qab ntawd cov haujlwm tom ntej ua tiav hauv tib lub module yuav siv qhov sib txawv sVAT_Rate nrog tib lub ntsiab lus. Txawm li cas los xij, yog tias qee qhov haujlwm nyob rau hauv lwm lub module hu ua, ces rau nws qhov sib txawv sVAT_Rate yuav tsis paub. |
Option Explicit Function Total_Cost() As Ob Chav Dim sVAT_Rate Raws li Ib Leeg ... End Function | Yog qhov hloov pauv sVAT_Rate tshaj tawm thaum pib ntawm kev ua haujlwm Total_Cost, ces nws cov peev txheej yuav raug txwv rau qhov kev ua haujlwm no nkaus xwb (piv txwv li hauv qhov ua haujlwm Total_Cost, koj tuaj yeem siv qhov sib txawv sVAT_Rate, tab sis tsis nyob sab nraud). Thaum sim siv sVAT_Rate nyob rau hauv lwm txoj kev, VBA compiler yuav qhia qhov yuam kev vim hais tias qhov sib txawv no tsis tau tshaj tawm sab nraum lub luag haujlwm. Total_Cost (muab hais tias tus neeg teb xov tooj siv Option Explicit). |
Hauv qhov piv txwv qhia saum toj no, qhov sib txawv tau tshaj tawm ntawm qib module nrog lo lus tseem ceeb Tshav. Txawm li cas los xij, nws yuav tsim nyog uas tau tshaj tawm cov hloov pauv tuaj yeem siv rau hauv lwm cov qauv. Hauv cov xwm txheej zoo li no, tshaj tawm qhov hloov pauv hloov ntawm lo lus tseem ceeb Tshav lo lus tseem ceeb yuav tsum tau siv Public.
Los ntawm txoj kev, txhawm rau tshaj tawm qhov sib txawv ntawm qib module, tsis yog lo lus tseem ceeb Tshav lo lus tseem ceeb siv tau private, uas qhia tau hais tias qhov sib txawv no yog tsim los siv rau hauv lub module tam sim no.
Koj tuaj yeem siv lo lus tseem ceeb tshaj tawm qhov tsis tu ncua. Public и private, tab sis tsis yog siv lo lus tseem ceeb Const, nrog nws.
Cov piv txwv hauv qab no qhia txog kev siv cov ntsiab lus tseem ceeb Public и private siv rau qhov sib txawv thiab qhov tsis tu ncua.
Option Explicit Public sVAT_Rate As Single Public Const iMax_Count = 5000 ... | Hauv qhov piv txwv no, lo lus tseem ceeb Public siv los tshaj tawm qhov sib txawv sVAT_Rate thiab tsis tu ncua iMax_ suav. Lub Scope ntawm cov ntsiab lus tshaj tawm nyob rau hauv no txoj kev yuav yog tag nrho cov tam sim no project. Qhov no txhais tau tias sVAT_Rate и iMax_ suav yuav muaj nyob rau hauv tej qhov project module. |
Option Explicit Private sVAT_Rate As Single Private Const iMax_Count = 5000 ... | Hauv qhov piv txwv no, tshaj tawm qhov sib txawv sVAT_Rate thiab tsis tu ncua iMax_ suav siv lo lus tseem ceeb private. Lub Scope ntawm cov ntsiab lus yog tam sim no module. Qhov no txhais tau tias sVAT_Rate и iMax_ suav yuav muaj nyob rau hauv tag nrho cov txheej txheem ntawm lub module tam sim no, tab sis yuav tsis muaj rau cov txheej txheem hauv lwm cov modules. |