txheem
Yog tias koj tseem tsis tau paub txog macro hauv Excel, ces kuv khib koj me ntsis. Qhov kev xav ntawm omnipotence thiab kev paub tias koj Microsoft Excel tuaj yeem hloov kho yuav luag mus rau qhov kawg uas yuav los rau koj tom qab paub txog macros yog qhov zoo siab.
Txawm li cas los xij, tsab xov xwm no yog rau cov neeg uas twb tau "kawm lub hwj chim" thiab tau pib siv macros ( txawv teb chaws lossis sau los ntawm lawv tus kheej - nws tsis muaj teeb meem) hauv lawv txoj haujlwm niaj hnub.
Ib qho macro yog cov lej (ntau kab) hauv Visual Basic lus uas ua rau Excel ua qhov koj xav tau: txheej txheem cov ntaub ntawv, tsim ib daim ntawv qhia, luam-paste ntau lub rooj rov ua dua, thiab lwm yam. Cov lus nug yog qhov twg yuav khaws ob peb kab ntawm cov lej no? Tom qab tag nrho, qhov twg macro khaws cia yuav nyob ntawm qhov twg nws tuaj yeem (lossis tsis tuaj yeem) ua haujlwm.
Если макрос решает небольшую локальную проблему в отдельно взятом файле (например обрабатывает внесенные в конкретный отчет данные особым образом), то логично хранить код внутри этого же файла. Kuv вопросов.
Thiab yog hais tias ib tug macro yuav tsum kuj universal thiab xav tau nyob rau hauv tej Excel workbook - zoo li, piv txwv li, ib tug macro rau hloov cov qauv rau cov nqi? Vim li cas ho tsis luam nws Visual Basic code rau hauv txhua phau ntawv txhua zaus? Tsis tas li ntawd, sai dua los yog tom qab, yuav luag txhua tus neeg siv los txiav txim siab tias nws yuav zoo rau muab tag nrho cov macros rau hauv ib lub thawv, piv txwv li muaj lawv ib txwm nyob ntawm tes. Thiab tej zaum txawm khiav tsis manually, tab sis nrog keyboard shortcuts? Qhov no yog qhov uas Tus Kheej Macro Workbook tuaj yeem pab tau zoo.
Yuav ua li cas los tsim Phau Ntawv Tus Kheej Macro
Nyob rau hauv qhov tseeb, Phau Ntawv Tus Kheej ntawm Macros (LMB) yog cov ntaub ntawv Excel tsis tu ncua hauv binary workbook format (Tus kheej.xlsb), uas tau qhib rau hauv stealth hom tib lub sijhawm li Microsoft Excel. Cov. Thaum koj nyuam qhuav pib Excel lossis qhib cov ntaub ntawv los ntawm disk, ob cov ntaub ntawv tau qhib tiag tiag - koj li thiab Personal.xlsb, tab sis peb tsis pom qhov thib ob. Yog li, tag nrho cov macro uas khaws cia hauv LMB yog muaj rau kev tshaj tawm txhua lub sijhawm thaum Excel qhib.
Yog tias koj tsis tau siv LMB, ces thaum xub thawj cov ntaub ntawv Personal.xlsb tsis muaj nyob. Txoj hauv kev yooj yim tshaj plaws los tsim nws yog sau qee qhov tsis tseem ceeb tsis tseem ceeb macro nrog lub kaw lus, tab sis qhia meej Phau Ntawv Tus Kheej raws li qhov chaw khaws cia - ces Excel yuav raug yuam kom tsim nws rau koj. Rau qhov no:
- nias lub tsim tawm (Tus tsim tawm). Yog tabs tsim tawm yog tsis pom, ces nws yuav enabled nyob rau hauv cov chaw los ntawm Cov ntaub ntawv – Kev xaiv – Ribbon teeb (Lub Tsev - Kev Xaiv - Customize the Ribbon).
- Hauv Advanced tab tsim tawm nias Macro cov ntaub ntawv (Cov ntaub ntawv Macro). Hauv qhov rai uas qhib, xaiv Personal Macro Book (Tus kheej Macro Workbook) raws li qhov chaw khaws cov ntawv sau thiab nias OK:
- Tsis txhob kaw nrog khawm Nres Txias (Stop recording) tab tsim tawm (Tus tsim tawm)
Koj tuaj yeem tshawb xyuas qhov tshwm sim los ntawm txhaj rau ntawm lub pob Visual Basic nyob rau ntawm lub tab. tsim tawm - nyob rau hauv lub qhov rais qhib editor nyob rau sab laug ces kaum ntawm lub vaj huam sib luag Project — VBA Project peb cov ntaub ntawv yuav tsum tshwm sim Tus kheej. XLSB. Nws ceg ntawm cov uas tuaj yeem nthuav dav nrog lub cim ntxiv ntawm sab laug, ncav cuag Module 1, qhov twg cov cai ntawm lub ntsiab lus macro peb nyuam qhuav kaw tau khaws cia:
Nrog koj zoo siab, koj nyuam qhuav tsim koj tus kheej Macro Phau Ntawv! Tsuas yog tsis txhob hnov qab nyem rau ntawm lub pob txuag nrog lub floppy disk nyob rau sab laug sab laug ntawm lub toolbar.
Yuav siv Tus Kheej Macro Phau Ntawv Li Cas
Ces txhua yam yog yooj yim. Txhua qhov macro koj xav tau (piv txwv li ib daim code pib nrog sub thiab xaus End Sub) tuaj yeem luam tau zoo thiab muab tso rau hauv Module 1, los yog nyob rau hauv ib tug nyias muaj nyias ib module, ntxiv nws yav tas los los ntawm cov ntawv qhia zaub mov Ntxig - Module. Khaws tag nrho cov macros nyob rau hauv ib qho module lossis muab tso rau hauv qhov sib txawv tsuas yog ib qho teeb meem ntawm saj. Nws yuav tsum zoo li no:
Koj tuaj yeem khiav cov macro ntxiv hauv lub dialog box hu nrog lub pob Ntsig (Macros) tab tsim tawm:
Nyob rau hauv tib lub qhov rais, nias lub khawm tsis (Kev xaiv), koj tuaj yeem teeb lub keyboard shortcut kom khiav macro sai sai ntawm cov keyboard. Ceev faj: keyboard shortcuts rau macros paub qhov txawv ntawm layout (los yog lus Askiv) thiab rooj plaub.
Ntxiv rau cov txheej txheem macro ib txwm nyob hauv Phau Ntawv Tus Kheej, koj tuaj yeem khaws cia kev cai macro functions (UDF = User Defined Function). Tsis zoo li cov txheej txheem, kev ua haujlwm code pib nrog nqe lus muaj nuj nqior Public Function, thiab xaus nrog Xaus Kev Ua Haujlwm:
Cov cai yuav tsum tau theej tib yam rau txhua qhov module ntawm PERSONAL.XLSB phau ntawv, thiab tom qab ntawd nws yuav tuaj yeem hu rau lub luag haujlwm hauv txoj kev ib txwm muaj, zoo li txhua tus qauv Excel ua haujlwm, los ntawm nias lub pob fx nyob rau hauv lub formula bar thiab xaiv ib tug muaj nuj nqi nyob rau hauv lub qhov rais Function Wizards hauv qeb Neeg Siv Li (User Defined):
Piv txwv ntawm cov haujlwm no tuaj yeem pom muaj ntau hauv Is Taws Nem lossis ntawm no ntawm lub xaib (ntau npaum li cov lus, kwv yees cov ntawv tshawb nrhiav, VLOOKUP 2.0, hloov Cyrillic rau kev txhais lus, thiab lwm yam)
Phau Ntawv Tus Kheej Macro nyob qhov twg?
Yog tias koj siv Phau Ntawv Tus Kheej ntawm Macros, tom qab ntawd sai lossis tom qab koj yuav muaj lub siab xav:
- qhia koj cov macros sau nrog lwm tus neeg siv
- luam thiab xa Phau Ntawv Tus Kheej mus rau lwm lub computer
- ua ib daim ntawv thaub qab
Ua li no, koj yuav tsum nrhiav PERSONAL.XLSB cov ntaub ntawv ntawm koj lub computer disk. Los ntawm lub neej ntawd, cov ntaub ntawv no tau muab cia rau hauv ib qho tshwj xeeb Excel startup folder hu ua XLSTART. Yog li txhua yam uas xav tau yog kom tau mus rau daim nplaub tshev no ntawm peb lub PC. Thiab qhov no yog qhov teeb meem me ntsis tshwm sim, vim tias qhov chaw ntawm daim nplaub tshev no nyob ntawm qhov version ntawm Windows thiab Office thiab yuav txawv. Qhov no feem ntau yog ib qho ntawm cov kev xaiv hauv qab no:
- C:Program FilesMicrosoft OfficeOffice12XLSTART
- C: Cov ntaub ntawv thiab chaw ComputerApplication DataMicrosoftExcelXLSTART
- C: Cov neeg sivkoj-account-npeAppDataRoamingMicrosoftExcelXLSTART
Xwb, koj tuaj yeem nug Excel nws tus kheej rau qhov chaw ntawm daim nplaub tshev no siv VBA. Txhawm rau ua qhov no, hauv Visual Basic editor (khawm Visual Basic tab tsim tawm) нужно открыть окно tam sim ntawd keyboard shortcut Ctrl + G, ntaus hauv cov lus txib ? Application.StartupPath thiab nias rau Sau:
Txoj kev tshwm sim tuaj yeem luam tawm thiab muab tso rau hauv kab saum toj kawg nkaus ntawm Explorer qhov rai hauv Windows thiab nyem Sau - thiab peb yuav pom ib daim nplaub tshev nrog peb Phau Ntawv Tus Kheej ntawm Macros cov ntaub ntawv:
PS
Thiab ob peb lub tswv yim nuances hauv kev nrhiav:
- Thaum siv Phau Ntawv Tus Kheej Macro, Excel yuav khiav qeeb me ntsis, tshwj xeeb tshaj yog ntawm cov PCs tsis muaj zog
- Nws tsim nyog tshem tawm Phau Ntawv Tus Kheej los ntawm cov ntaub ntawv pov tseg, qub thiab tsis tsim nyog macros, thiab lwm yam.
- Cov neeg siv koom nrog qee zaum muaj teeb meem siv Phau Ntawv Tus Kheej, tk. qhov no yog ib cov ntaub ntawv nyob rau hauv lub kaw lus zais folder
- Dab tsi yog macro thiab yuav siv lawv li cas hauv koj txoj haujlwm
- Muaj txiaj ntsig zoo rau VBA programmer
- Kev cob qhia "Programming macro hauv VBA hauv Microsoft Excel"