Koj tuaj yeem siv Microsoft Excel tus tsim-hauv VBA tus kws tshaj lij los suav nrog Visual Basic code rau hauv koj phau ntawv ua haujlwm. Ib qho ntawm qhov zoo tshaj plaws txog Excel yog qhov nws tuaj yeem sau VBA code rau koj-qhov no yog qhov koj tab tom ua thaum koj kaw lub macro. Thaum koj sau cov lej hauv Excel, Excel khaws cov lej hauv VBA code, uas koj tuaj yeem pom thiab kho hauv Visual Basic Editor. Yog tias koj yog tus tshiab rau VBA, ua si nrog macros yog txoj hauv kev zoo los pib. Qhov no wikiHow qhia koj yuav ua li cas yooj yim tsim koj tus kheej VBA code hauv Microsoft Excel, ntxiv rau yuav ua li cas kho kom raug ntau dua cov cai hauv Visual Basic Editor.
Cov kauj ruam
Ntu 1 ntawm 3: Qhib Visual Basic Editor
Kauj Ruam 1. Pab kom tus tsim tawm tab hauv Excel
Ua ntej koj tuaj yeem qhib VBA tus khoos phis tawj thiab pib ua koj tus kheej macros, koj yuav tsum ntxiv Tus Tsim Kho tab rau koj lub cuab yeej Excel.
-
Qhov rai
- Nyem rau Cov ntaub ntawv menu thiab xaiv Kev xaiv.
- Nyem Customize Ribbon ntawm cov ntawv qhia zaub mov.
- Zuam lub thawv "Tus tsim tawm" hauv qab "Cov Tab Loj" thiab nyem rau OK.
-
macOS os
- Nyem rau Excel menu thiab xaiv Nyiam.
- Nyem Ribbon & Toolbar.
- Nyem Cov Ntsiab Tseem Ceeb.
- Zuam lub thawv tom ntej no mus rau "Developer".
- Nyem Txuag thiab tom qab ntawd kaw lub qhov rais.
Kauj Ruam 2. Nyem rau Tus Tsim Tawm tab
Tam sim no koj tau qhib lub tab, nws nyob rau sab saum toj ntawm Excel. Nov yog qhov uas koj yuav pom Visual Basic editor, nrog rau lwm yam ntxiv rau kev txhim kho.
Kauj Ruam 3. Nyem Visual Basic
Nws nyob ntawm sab laug-sab laug ntawm lub cuab yeej hauv Tus Tsim Tawm tab. Qhov no qhib koj qhov Visual Basic Editor. Ua ntej peb sau cov macro, cia peb xav tias yuav ua li cas thiaj li ua haujlwm tau zoo thiab tau tso tawm.
Kauj Ruam 4. Kawm koj txoj hauv kev ib puag ncig Visual Basic Editor
Peb yuav tsuas npog qhov pib tam sim no, raws li koj yuav tau txais kev paub ntau dua thaum koj pib ua haujlwm nrog macros.
- Cov phiaj xwm phiaj xwm, ntawm sab laug ntawm VBE, yog qhov uas koj yuav pom txhua yam qhib Excel phau ntawv ua haujlwm. Txhua daim ntawv ua haujlwm hauv phau ntawv ua haujlwm yog cov khoom sib cais, tshwm nyob rau hauv "Microsoft_Excel_Objects."
- Thaum koj tau kaw macros, lawv yuav tshwm sim raws li cov qauv hauv pawg no hauv qab "Modules" header. Tom qab ntawd koj tuaj yeem pom thiab kho cov cai hauv tus khoos phis tawm los ntawm kev qhib tus qauv.
- Txhawm rau ntxig koj tus kheej yam tsis tau sau ua ntej, nyem rau Ntxig ntawv qhia zaub mov nyob rau sab saum toj ntawm tus editor thiab xaiv Qauv.
- Nyem rau X lossis lub voj voog liab nyob rau sab saud ntawm tus kws kho kom rov qab mus rau Excel.
Ntu 2 ntawm 3: Kaw thiab Kho Macro
Kauj Ruam 1. Qhib ib daim ntawv ua haujlwm hauv Excel
Tam sim no peb yuav siv qhov piv txwv los tsim thiab hloov kho VBA. Thaum koj sau cov lej hauv Excel, koj tsim VBA code uas koj tuaj yeem hloov kho hauv Visual Basic Editor. Hauv qhov piv txwv no, peb yuav sau cov lej uas ntxiv cov lej ntawm A10 thiab B10, tso tawm cov txiaj ntsig hauv C10, thiab tom qab ntawd luam tus nqi rau D10. Pib los ntawm kev qhib phau ntawv ua haujlwm dawb thiab nkag mus rau ob qho lej lej rau hauv A10 thiab B10.
Kauj Ruam 2. Nyem rau Tus Tsim Tawm tab
Nws nyob saum qhov screen.
Koj tseem tuaj yeem qhib tus editor los ntawm nias Alt + F11 ntawm lub PC, lossis Xaiv + F11 (los yog Fn + Xaiv + F11) ntawm Mac.
Kauj Ruam 3. Nyem Sau Macro
Nws nyob ntawm Developer tab nyob rau sab saum toj ntawm lub vijtsam. Qhov no qhib Cov Ntaub Ntawv Macro sib tham.
Kauj Ruam 4. Sau lub npe rau lub macro
Nov yog qee yam uas piav qhia txog qhov macro yog rau, xws li Sum_and_Copy.
Yuav tsis muaj qhov chaw nyob hauv lub npe macro
Kauj Ruam 5. Nkag mus rau cov keyboard luv rau lub macro
Qhov luv uas koj nkag mus yuav yog ib txoj hauv kev uas koj tuaj yeem khiav nrawm. Piv txwv li, Ctrl + L.
Tus yuam sij luv yuav tsum yog tus tsiaj ntawv
Kauj Ruam 6. Xaiv qhov chaw khaws cov macro
Yog tias koj tsis siv macro nyob txhua qhov tab sis hauv phau ntawv no, xaiv Phau ntawv no. Cia peb xaiv qhov kev xaiv tam sim no.
- Yog tias koj xav siv tus lej thiab tuaj yeem pom thiab kho nws cov ntawv VBA hauv txhua phau ntawv ua haujlwm, xaiv Phau Ntawv Macro Tus Kheej. Qhov no yuav tshwm los ua phau ntawv cais hauv Visual Basic Editor.
- Koj tseem tuaj yeem sau cov lus piav qhia yog tias koj xav tau, xws li "Luam theej tag nrho kev muag khoom rau D10."
Kauj Ruam 7. Nyem OK kom pib kaw
Thaum koj pib kaw, txhua yam koj ua yuav raug ntes thiab ntxiv rau qhov siv cov lej VBA.
Kauj Ruam 8. Ua kom tiav cov kev ua uas koj xav sau
Tsuas yog ua cov kev ua uas koj xav kom txuag rau macro. Hauv peb qhov piv txwv, peb tab tom ntxiv A10 rau B10, tso tawm cov txiaj ntsig hauv C10, thiab tom qab ntawd theej rau D10. Nov yog cov kauj ruam:
- Nyem rau Tsev tab.
- Nyem tus nas hauv C10.
- Nyem rau "AutoSum" ntawv qhia zaub mov thiab xaiv SUM.
- Xovxwm Nkag mus los yog Rov qab los ntxiv qhov xaiv qhov tseem ceeb.
- Highlight C10 thiab nias Ctrl + C. (PC) lub Cmd + C. (Mac) los theej tawm.
- Nyem D10 thiab nias Ctrl + V. (PC) lub Cmd + V. (Mac) rau muab tshuaj txhuam.
Kauj Ruam 9. Nyem rau Tus Tsim Tawm tab thiab xaiv Nres Cov Ntaub Ntawv
Nov yog qhov kev xaiv uas hloov pauv "Kaw Macro," uas koj tau xaiv ua ntej. Excel tsis kaw koj cov haujlwm.
Kauj Ruam 10. Nyem Macros hauv qhov toolbar
Qhov no nthuav tawm cov npe ntawm txhua macros hauv txhua phau ntawv haujlwm uas koj tau qhib.
Kauj Ruam 11. Xaiv koj lub macro tshiab thiab nyem Kho kom raug
Qhov no qhib VBA tus lej rau koj lub macro hauv Visual Basic Editor. Tam sim no koj tuaj yeem pom dab tsi koj lub ntsej muag zoo li hauv VBA code. Thaum koj khaws cov macros thiab tshawb xyuas cov cai, koj yuav kawm paub ntau ntxiv txog Visual Basic.
- Raws li peb tau hais, macros tau txais kev cawmdim raws li cov qauv-koj yuav tsum tam sim no pom ntu "Cov Qauv" hauv Cov Vaj Huam Sib Luag Haujlwm uas muaj koj lub macro.
- Koj tseem tuaj yeem nyem Visual Basic hauv lub cuab yeej qhib qhov Visual Basic Editor, txoj cai-nias tus qauv koj xav kho, thiab tom qab ntawd nyem Kho kom raug.
Kauj Ruam 12. Kho VBA cov cai ntawm koj lub macro
Cia peb hais tias peb xav kom cov luam theej duab tag nrho rau E10 tsis yog D10. Ntau dua li rov sau dua cov macro, peb tsuas tuaj yeem kho qhov chaws hauv Visual Basic Editor. Hauv peb qhov piv txwv, nrhiav ib feem ntawm VBA cov cai hauv tus kws kho ntawv uas hais tias "D10" thiab hloov nws nrog "E10."
Kauj Ruam 13. Nyem rau Run tab thiab xaiv Run sub/Userform
Qhov no khiav koj cov kho tshiab macro kho tshiab.
- Koj tseem tuaj yeem nias F5 lossis nyem lub pob ua si hauv lub toolbar kom ua haujlwm macro.
- Txhawm rau pom cov txiaj ntsig, kaw Visual Basic Editor kom rov qab mus rau koj daim ntawv ua haujlwm.
Ntu 3 ntawm 3: Tsim Kom Yooj Yim Khawm Khawm
Kauj Ruam 1. Qhib daim ntawv ua haujlwm dawb hauv Excel
Lwm txoj hauv kev kom kawm paub txog VBA yog txhawm rau tso cai los ntawm hauv Excel. Hauv qhov piv txwv no, peb yuav tsim thiab kho lub pob uas coj tus yam ntxwv thaum raug nias.
Kauj Ruam 2. Nyem rau Tus Tsim Tawm tab
Nws yog nyob rau sab saum toj ntawm Excel.
Kauj Ruam 3. Nyem rau Insert khawm
Nws yog lub cuab yeej icon ntawm lub cuab yeej. Qhov no qhib cov ntawv qhia zaub mov.
Kauj Ruam 4. Nyem rau Command Button icon hauv ntu “Active X Control”
Nws yog thawj qhov kev xaiv hauv ntu thib ob.
Thaum koj tuav koj tus cursor nas hla thawj lub cim hauv ntu ntawd, nws yuav hais "Command Button," uas yog qhov koj paub tias koj tab tom ua haujlwm nrog khawm raug
Kauj Ruam 5. Nyem rau ntawm tes tsis muaj dab tsi
Qhov no tsim lub pob tshiab hu ua "Command Button" hauv koj phau ntawv ua haujlwm. Koj tuaj yeem muab tus lej rau lub pob no uas ua tiav qee yam haujlwm txhua lub sijhawm koj nyem lub pob. Hauv peb qhov piv txwv, peb yuav ua lub pob ntxig lo lus "wikiHow" rau hauv cell A1.
Cov Hom Tsim khawm hauv toolbar tam sim no yuav tsum tau teeb. Yog tias nws tsis yog, nyem nws kom pom nws tam sim no.
Kauj Ruam 6. Txoj cai-nias qhov hais kom ua khawm thiab xaiv Saib Txoj Cai
Qhov no qhib qhov chaws rau khawm hauv Visual Basic Editor.
- Koj tseem tuaj yeem nyem lub pob ib zaug thiab xaiv Saib Txoj Cai hauv toolbar.
- Ntau dua li ntxig cov cai raws li tus qauv zoo li kaw lub macro, qhov no ntxiv cov cai rau cov khoom rau daim ntawv ua haujlwm uas koj tso lub pob. Koj tuaj yeem tuaj yeem saib thiab kho qhov chaws no los ntawm kev qhib Visual Basic Editor, txoj cai-nyem rau ntawm daim ntawv uas muaj lub pob, thiab xaiv Saib Txoj Cai.
Kauj Ruam 7. Ntxig cov cai luam "wikiHow" rau hauv cell A1
Cov cai uas twb muaj lawm yog rau khawm. Txhawm rau khawm ua qee yam, peb yuav tsum ntxiv peb cov lej ntawm ob kab ntawm cov cai uas twb muaj lawm. Luam thiab paste cov cai hauv qab no ntawm ob kab uas twb muaj lawm:
-
Ntau ("A1"). Tus nqi = "Nyob Zoo"
Kauj Ruam 8. Kaw qhov Visual Basic editor
Txhawm rau ua qhov no, tsuas yog nyem lub pob X lossis lub voj voog liab nyob saum lub qhov rai. Qhov no coj koj rov qab mus rau koj daim ntawv ua haujlwm.
Kauj Ruam 9. Nyem Tsim Qauv hauv toolbar
Qhov no tawm ntawm Kev Tsim Qauv-koj yuav tsis tuaj yeem nyem lub pob yog tias koj tseem nyob hauv Hom Tsim.
Kauj Ruam 10. Nyem qhov Command Button
Tam sim no koj yuav tsum pom lo lus "wikiHow" hauv cell A1 ntawm koj daim ntawv.
Txhawm rau kho qhov chaws dua, nyem Hom Tsim, xaiv lub pob, thiab tom qab ntawd nyem Saib Txoj Cai nyob rau hauv lub toolbar lossis right-click nws thiab xaiv Saib Txoj Cai.
Lub tswv yim
- Yog tias koj yog tus tshiab rau VBA, sim kawm chav pib, lossis tshawb xyuas cov yeeb yaj kiab pib hauv YouTube.
- Raws li koj tau txais kev paub ntau nrog VBA, txheeb xyuas cov npe nrov Excel VBA cov neeg siv rooj sab laj Reddit's /r /vba thiab /r /excel.
