txheem
Hauv tsab xov xwm no, koj yuav kawm paub dab tsi qhov tsis tu ncua thiab hloov pauv hauv macros, qhov twg lawv tuaj yeem siv tau, thiab dab tsi yog qhov txawv ntawm cov ntaub ntawv sib txawv. Nws kuj tseem yuav qhia tau tias yog vim li cas thiaj li xav tau, yog tias koj tuaj yeem sau qhov sib txawv thiab tsis hloov nws.
Zoo li lwm yam lus programming, cov ntaub ntawv tuaj yeem khaws cia hauv qhov sib txawv lossis qhov tsis tu ncua (ob qho tib si feem ntau hu ua cov ntim cov ntaub ntawv). Qhov no yog qhov sib txawv tseem ceeb ntawm cov tswv yim no. Tus qub tuaj yeem hloov pauv raws li qhov tshwm sim hauv qhov program. Nyob rau hauv lem, tas li yog teem ib zaug thiab tsis hloov lawv tus nqi.
Constants tuaj yeem pab tau yog tias koj xav siv tib tus nqi loj ntau zaus. Es tsis txhob luam tus lej, koj tuaj yeem sau lub npe ntawm qhov tas li. Piv txwv li, koj tuaj yeem siv qhov "Pi" tas li los khaws Pi, uas yog tus nqi tas li. Nws yog qhov loj heev, thiab txhua zaus nws nyuaj heev los sau nws lossis tshawb nrhiav thiab luam nws. Thiab yog li ntawd, nws txaus los sau ob lub cim, thiab ib puag ncig tau siv tus lej xav tau.
Tus neeg siv Excel yuav tsum tshaj tawm qhov hloov pauv yog tias nws xav tau hloov pauv tus nqi khaws cia hauv lawv ib ntus. Piv txwv li, koj tuaj yeem teeb tsa qhov sib txawv hu ua sVAT_Rate, uas yuav khaws tus nqi VAT tam sim no rau cov khoom. Yog tias nws hloov pauv, koj tuaj yeem kho nws sai sai. Qhov no yog qhov tshwj xeeb tshaj yog rau cov neeg ua lag luam hauv Tebchaws Meskas, qhov twg qee cov khoom yuav tsis raug VAT tag nrho (thiab cov se no kuj txawv ntawm lub xeev mus rau lub xeev).
Cov hom ntaub ntawv
Txhua lub thawv cov ntaub ntawv tuaj yeem yog ib qho ntawm ntau hom. Ntawm no yog ib lub rooj piav qhia txog hom qauv ntawm cov ntaub ntawv ua tiav. Muaj ntau ntawm lawv, thiab nws yuav zoo li ib tug pib pib uas lawv rov ua dua ib leeg. Tab sis qhov no yog kev xav tsis meej. Nyeem rau kom paub tias vim li cas kev qhia meej cov ntaub ntawv raug yog qhov tseem ceeb heev.
Nws tsis pom zoo kom siv hom ntaub ntawv uas siv ntau qhov chaw hauv lub cim xeeb rau cov lej me. Piv txwv li, rau tus lej 1, nws txaus los siv hom Byte. Qhov no yuav muaj txiaj ntsig zoo rau kev ua haujlwm ntawm kev ua haujlwm tau zoo, tshwj xeeb tshaj yog rau cov khoos phis tawj tsis muaj zog. Tab sis nws tseem ceeb heev kom tsis txhob mus deb dhau ntawm no. Yog tias koj siv hom ntaub ntawv uas yog compact heev, tus nqi loj yuav tsis haum rau nws.
Tshaj tawm Constants thiab Variables
Siv cov ntaub ntawv ntim yam tsis tau tshaj tawm thawj zaug nws poob siab heev. Tom qab ntawd muaj ntau yam teeb meem tuaj yeem tshwm sim, kom tsis txhob muaj qhov yuav tsum tau sau ob peb kab me me ntawm cov lej nrog suav ntawm cov hloov pauv lossis qhov tsis tu ncua.
Txhawm rau tshaj tawm qhov sib txawv, Dim nqe lus yog siv. Piv txwv li, zoo li no:
Dim Variable_Name As Integer
Variable_Name yog lub npe ntawm qhov sib txawv. Tom ntej no, Raws li tus neeg teb xov tooj tau sau, qhia txog hom ntaub ntawv. Hloov cov hlua "Variable_Name" thiab "Integer", koj tuaj yeem ntxig koj tus kheej lub npe thiab hom ntaub ntawv.
Constants kuj tuaj yeem raug tshaj tawm, tab sis koj yuav tsum xub qhia lawv tus nqi. Ib qho kev xaiv yog:
Const iMaxCount = 5000
Hauv kev ncaj ncees, qee zaum koj tuaj yeem ua yam tsis tau tshaj tawm qhov sib txawv, tab sis qhov no lawv yuav cia li muab hom Variant. Txawm li cas los xij, qhov no tsis pom zoo rau cov laj thawj hauv qab no:
- Variant tau ua tiav qeeb dua, thiab yog tias muaj ntau qhov sib txawv, kev ua cov ntaub ntawv tuaj yeem ua rau qeeb qeeb ntawm cov khoos phis tawj tsis muaj zog. Nws zoo li cov vib nas this yuav txiav txim siab? Tab sis yog tias koj yuav tsum sau cov kab ntau ntawm cov lej, thiab tom qab ntawd kuj tseem khiav ntawm cov khoos phis tawj tsis muaj zog (uas tseem muag, muab cov chav ua haujlwm niaj hnub no xav tau ntau RAM), koj tuaj yeem nres ua haujlwm. Muaj cov xwm txheej thaum kev sau ntawv tsis zoo ntawm macros coj mus rau khov ntawm cov phau ntawv ntse uas muaj RAM me me thiab tsis tsim los ua cov haujlwm nyuaj.
- Cov npe tsis raug tso cai, uas tuaj yeem tiv thaiv tau los ntawm kev siv Cov Lus Qhia Tshwj Xeeb, uas tso cai rau koj pom qhov sib txawv tsis tau lees paub, yog tias pom muaj. Qhov no yog ib txoj hauv kev yooj yim los txheeb xyuas qhov yuam kev, vim qhov typo me ntsis ua rau tus neeg txhais lus tsis tuaj yeem txheeb xyuas qhov sib txawv. Thiab yog tias koj qhib hom kev tshaj tawm sib txawv, tus neeg txhais lus tsuas yuav tsis tso cai rau koj khiav macro yog tias pom cov ntaub ntawv ntim uas tsis tau tshaj tawm thaum pib ntawm lub module.
- Tsis txhob ua yuam kev los ntawm qhov sib txawv ntawm qhov tsis sib xws ntawm cov ntaub ntawv hom. Feem ntau, muab tus nqi ntawv rau tus lej sib txawv yuav cuam tshuam qhov yuam kev. Yog lawm, ntawm ib sab, ib hom generic tau muab rau yam tsis muaj kev tshaj tawm, tab sis yog tias lawv tau tshaj tawm ua ntej, ces qhov yuam kev tuaj yeem zam tau.
Yog li ntawd, txawm hais tias txhua yam, nws raug pom zoo kom tshaj tawm txhua qhov hloov pauv hauv Excel macros.
Muaj ib qho ntxiv uas yuav tsum nco ntsoov thaum tshaj tawm cov hloov pauv. Nws muaj peev xwm tsis muab cov txiaj ntsig rau qhov sib txawv thaum tshaj tawm nws, tab sis qhov no nws tau txais tus nqi qub. Piv txwv li:
- Cov kab yog ua rau khoob.
- Cov lej siv rau tus nqi 0.
- Qhov sib txawv ntawm hom Boolean yog thawj zaug suav tias yog cuav.
- Hnub tim yog lub Kaum Ob Hlis 30, 1899.
Piv txwv li, koj tsis tas yuav muab tus nqi 0 rau tus lej sib txawv yog tias tsis muaj tus nqi yav dhau los. Nws twb muaj tus lej no lawm.
Option Lus Qhia
Cov nqe lus no tso cai rau koj tshaj tawm tag nrho cov kev hloov pauv uas tau siv hauv VBA code thiab txiav txim siab txog qhov muaj cov ntim tsis tau tshaj tawm ua ntej tus lej khiav. Txhawm rau siv qhov tshwj xeeb no, tsuas yog sau ib kab ntawm Kev Xaiv Qhia meej code nyob rau sab saum toj ntawm macro code.
Yog tias koj xav tau suav nrog cov nqe lus no hauv koj tus lej txhua zaus, koj tuaj yeem ua li ntawd siv qhov chaw tshwj xeeb hauv VBA editor. Txhawm rau qhib qhov kev xaiv no, koj yuav tsum:
- Mus rau qhov chaw tsim kho raws txoj hauv kev - Cov cuab yeej> Xaiv.
- Hauv qhov rai uas qhib tom qab qhov no, qhib Editor tab.
- Thiab thaum kawg, khij lub npov nyob ib sab ntawm Cov Lus Tshaj Tawm Yuav Tsum Muaj Kev Tshaj Tawm.
Tom qab ua tiav cov kauj ruam no, nyem rau ntawm lub pob "OK".
Ntawd yog nws, tam sim no thaum sau txhua qhov macro tshiab, cov kab no yuav muab tso rau saum toj ntawm cov lej tau txais.
Scope ntawm Constants thiab Variables
Txhua qhov sib txawv lossis qhov tsis tu ncua tsuas muaj qhov txwv. Nws nyob qhov twg koj tshaj tawm nws.
Piv txwv tias peb muaj haujlwm Total_Cost(), thiab nws siv qhov sib txawv sVAT_Rate. Nyob ntawm txoj haujlwm hauv lub module, nws yuav muaj qhov sib txawv:
| Option Explicit Dim sVAT_Rate As Single Muaj nuj nqi Total_Cost() Ua ob . . . Xaus Kev Ua Haujlwm | Yog hais tias qhov sib txawv tau tshaj tawm nyob rau sab saum toj ntawm lub module nws tus kheej, nws propagates thoob plaws qhov module. Ntawd yog, nws tuaj yeem nyeem los ntawm txhua qhov txheej txheem. Ntxiv mus, yog tias ib qho ntawm cov txheej txheem hloov pauv tus nqi ntawm qhov sib txawv, ces tus tom ntej no tseem yuav nyeem qhov kho qhov nqi no. Tab sis nyob rau hauv lwm cov modules no kuj sib txawv yuav tseem tsis tau nyeem. |
| Option Explicit Muaj nuj nqi Total_Cost() Ua ob Dim sVAT_Rate As Single . . . Xaus Kev Ua Haujlwm | Hauv qhov no, qhov sib txawv tau tshaj tawm hauv cov txheej txheem, thiab tus neeg txhais lus yuav cuam tshuam qhov yuam kev yog tias nws siv rau lwm tus txheej txheem. |
Yog tias koj xav kom qhov sib txawv los nyeem los ntawm lwm cov modules, koj yuav tsum siv Public keywords es tsis txhob lo lus Dim. Ib yam li ntawd, koj tuaj yeem txwv lub peev xwm ntawm qhov sib txawv rau tsuas yog cov qauv tam sim no los ntawm kev siv Public Statement, uas yog sau los ntawm lo lus Dim.
Koj tuaj yeem teeb tsa qhov kev nyob ib puag ncig zoo ib yam, tab sis lo lus tseem ceeb ntawm no tau sau ua ke nrog tus neeg teb xov tooj Const.
Ntawm no yog ib lub rooj nrog ib tug piv txwv zoo ntawm yuav ua li cas nws ua hauj lwm nrog tas li thiab variables.
| Option Explicit Public sVAT_Rate As Single Public Const iMax_Count = 5000 | Hauv qhov piv txwv no, koj tuaj yeem pom tias Public lo lus tseem ceeb siv li cas los tshaj tawm qhov sib txawv, thiab koj yuav tsum sau dab tsi hauv Visual Basic editor kom tshaj tawm cov pej xeem tas li. Cov peev txheej ntawm cov ntim khoom muaj nqis no siv tau rau txhua qhov modules. |
| Option Explicit Private sVAT_Rate As Single Private Const iMax_Count = 5000 | Ntawm no, qhov sib txawv thiab qhov tsis tu ncua tau tshaj tawm siv lo lus Private. Qhov no txhais tau hais tias lawv tsuas tuaj yeem pom nyob rau hauv module tam sim no, thiab cov txheej txheem hauv lwm cov modules siv tsis tau. |
Vim li cas thiaj li yuav tsum muaj qhov tsis tu ncua thiab hloov pauv
Kev siv qhov tsis tu ncua thiab qhov hloov pauv tso cai rau koj kom nce qib ntawm kev nkag siab ntawm cov cai. Thiab yog tias cov pib tshiab feem ntau tsis muaj lus nug txog vim li cas thiaj li xav tau kev hloov pauv, ces muaj ntau qhov tsis meej meej txog qhov xav tau qhov tsis tu ncua. Thiab lo lus nug no zoo nkaus li, thaum xub thawj siab ib muag, zoo heev logic. Tom qab tag nrho, koj tuaj yeem tshaj tawm qhov hloov pauv ib zaug thiab tsis txhob hloov nws dua.
Cov lus teb hloov mus rau qhov chaw nyob rau hauv tib lub dav hlau raws li hais txog kev siv cov ntaub ntawv hom uas nyob ib qhov chaw loj hauv nco. Yog tias peb tab tom cuam tshuam nrog ntau qhov sib txawv, peb tuaj yeem hloov pauv lub thawv uas twb muaj lawm. Yog tias tus neeg siv tau sau tseg tias qee tus nqi yuav tsis hloov pauv, ib puag ncig yuav tswj tau qhov no.
Qhov no tseem ceeb tshwj xeeb tshaj yog thaum cov macro sau los ntawm ntau tus programmers. Ib tug yuav paub tias qee qhov sib txawv yuav tsum tsis txhob hloov. Thiab lwm tus tsis yog. Yog tias koj teev tus neeg teb xov tooj Const, lwm tus tsim tawm yuav paub tias tus nqi no tsis hloov.
Los yog, yog tias muaj qhov tsis tu ncua nrog ib lub npe, thiab qhov sib txawv muaj qhov sib txawv, tab sis lub npe zoo sib xws. Tus tsim tawm tuaj yeem ua rau lawv tsis meej pem. Piv txwv li, ib qho kev hloov pauv uas tsis tas yuav hloov pauv yog hu ua Variable11, thiab lwm qhov uas tuaj yeem hloov kho yog hu ua Variable1. Ib tug neeg tuaj yeem txiav txim siab, thaum sau cov lej, yuam kev hla ib chav ntxiv thiab tsis pom nws. Yog li ntawd, lub thawv rau cov nqi yuav raug hloov, uas yuav tsum tsis txhob kov.
Los yog tus tsim tawm nws tus kheej yuav hnov qab qhov hloov pauv uas nws tuaj yeem kov tau thiab qhov uas nws ua tsis tau. Qhov no feem ntau tshwm sim thaum tus lej sau rau ob peb lub lis piam, thiab nws qhov loj yuav loj. Lub sijhawm no, nws yooj yim heev kom tsis nco qab txawm tias qhov no lossis qhov hloov pauv txhais tau li cas.
Yog lawm, koj tuaj yeem ua nrog cov lus hauv qhov xwm txheej no, tab sis nws tsis yooj yim dua los qhia lo lus Const?
cov lus xaus
Cov kev hloov pauv yog ib qho tseem ceeb ntawm macro programming, uas tso cai rau koj los ua cov haujlwm nyuaj, los ntawm kev suav mus qhia rau tus neeg siv txog qee yam xwm txheej lossis kev qhia tshwj xeeb hauv cov hlwb ntawm daim ntawv nthuav qhia.
Cov khoom yuav tsum tau siv yog tias tus tsim tawm paub tseeb tias cov ntsiab lus ntawm cov ntim khoom no yuav tsis hloov pauv yav tom ntej. Nws raug nquahu kom tsis txhob siv cov hloov pauv hloov pauv, vim tias nws tuaj yeem ua yuam kev yuam kev.