Excel VBA Course Notes 1 - Macro Basics.pdf

(82 KB) Pobierz
Excel VBA File Notes: Macro Basics
(;&(/9%$
0$&52%$6,&6
([FHO9%$&RXUVH1RWHV0DFUR%DVLFV
)LOH9%$0DFURV[OV
8VLQJWKH0DFUR5HFRUGHU
2SHQ([FHODQGWKH9%(9LVXDO%DVLF(GLWRU8QOHVVLWKDVEHHQFKDQJHGWKH9%(ZLQGRZFRQWDLQVWKH
3URMHFW ([SORUHU ZLQGRZDQGWKH3URSHUWLHV ZLQGRZWKHVHFDQEH DFFHVVHGIURPWKH9LHZ PHQX
3URMHFW([SORUHU:RUNVOLNHDILOHPDQDJHU+HOSV\RXQDYLJDWHDURXQGWKHFRGHLQ\RXUZRUNERRN
3URSHUWLHV:LQGRZ 6KRZVWKHSURSHUWLHVRIWKHFXUUHQWO\DFWLYHREMHFWHJ 6KHHWRIWKHFXUUHQWZRUNERRN
HJ %RRN
([HUFLVH5HFRUGLQJD0DFUR
7KLVH[HUFLVHVKRZVZKDWKDSSHQVZKHQDPDFURLVUHFRUGHGDQGGHPRQVWUDWHVWKHGLIIHUHQFHEHWZHHQUHFRUGLQJ
DEVROXWHDQGUHODWLYHUHIHUHQFHV
2QDQHPSW\ZRUNVKHHWLQDQHZZRUNERRNVHOHFWFHOO &
6WDUWWKH0DFUR5HFRUGHU ZLWKRSWLRQWRVDYHPDFURLQ 7KLV:RUNERRN$WWKLVSRLQWWKH9%(FUHDWHVDQHZ
0RGXOHV IROGHU,WVTXLWHVDIHWRJRDQGORRNDWLW \RXUDFWLRQVZRQWEHUHFRUGHG&OLFNWKH >@ QH[WWRWKH
IROGHUDQGVHHWKDWWKH9%(KDVSODFHVDPRGXOHLQWKHIROGHUDQGQDP HGLW0RGXOH'RXEOHFOLFNWKHPRGXOHLFRQ
WRRSHQLWVFRGHZLQGRZ6ZLWFKEDFNWR([FHO
0DNHVXUHWKDWWKH5HODWLYH5HIHUHQFHEXWWRQRQWKH6WRS5HFRUGLQJ WRROEDULV127SUHVVHGLQ
6HOHFWFHOO% DQGVWRSWKHUHFRUGHU
6ZLWFKWRWKH9%(DQGORRNDWWKH FRGH
5DQJH%6HOHFW
1RZUHFRUGDQRWKHUPDFURH[DFWO\WKHVDPHZD\EXWWKLVWLPHZLWKWKH 5HODWLYH5HIHUHQFH EXWWRQSUHVVHGLQ
6ZLWFKWRWKH9%(DQGORRNDWWKHFRGH
$FWLYH&HOO2IIVHW5DQJH$6HOHFW
1RZUHFRUGDQRWKHUPDFUREXWLQVWH DGRIVHOHFWLQJFHOO%VHOHFWDEORFNRIFHOOV[VWDUWLQJDW%VHOHFWFHOOV
%)
6ZLWFKWRWKH9%(DQGORRNDWWKHFRGH
$FWLYH&HOO2IIVHW5DQJH$%6HOHFW
3OD\EDFNWKHPDFURVKDYLQJILUVWVHOHFWHGDGLIIHUHQWFHOOWKDQ&IRU0DF URDQG0DFURWKHVWDUWLQJFHOOPXVW
EHLQURZRUEHORZ VHHVWHSEHORZ
0DFUR DOZD\VPRYHVWKHVHOHFWLRQWR%
0DFUR PRYHVWKHVHOHFWLRQWRDFHOOURZVXSDQGFROXPQWRWKHOHIWRIWKHVHOHFWHGFHOO
0DFUR DOZD\VVHOHFWVDEORFNRIVL [FHOOVVWDUWLQJURZVXSDQGFROXPQWRWKHOHIWRIWKHVHOHFWHGFHOO
5XQ0DFUREXWIRUFHDQHUURUE\VHOHFWLQJDFHOOLQURZRUDERYH7KHPDFURWULHVWRVHOHFWDQRQ H[LVWHQWFHOO
EHFDXVHLWVFRGHLVWHOOLQJLWWRVHOHFWDFHOOURZVDERYHWKHVW DUWLQJSRLQWDQGWKDWVRIIWKHWRSRIWKHVKHHW
3UHVV'HEXJ WREHWDNHQWRWKHSDUWRIWKHPDFURWKDWFDXVHGWKHSUREOHP
127(:KHQWKH9%(LVLQ'HEXJPRGHWKHOLQHRIFRGHWKDWFDXVHGWKHSUREOHPLVKLJKOLJKWHGLQ\HOORZ<RX
PXVWUHVHWWKHPDFUR EHIRUH\RXFDQSURFHHG&OLFNWKH 5HVHW EXWWRQRQWKH9%(WRROEDURUJRWR 5XQ ± 5HVHW
7KH\HOORZKLJKOLJKWLQJGLVDSSHDUVDQGWKH9%(FRPHVRXWRI'HEXJPRGH
,WLVLPSRUWDQWWRWU\DQGDQWLFLSDWHXVHUHUURUOLNHWKLV7KHVLPSOHVWZD\LVWRPRGLI\WKHFRG HWRVLPSO\LJQRUH
HUURUVDQGPRYHRQWRWKHQH[WWDVN'RWKLVE\DGGLQJWKHOLQH«
2Q(UURU5HVXPH1H[W
«LPPHGLDWHO\DERYHWKHILUVWOLQHRIWKHPDFURXQGHUQHDWKWKHOLQH 6XE 0DFUR
5XQ0DFUR DVEHIRUHVWDUWLQJWRRKLJKRQWKHVKHHW7KLVWLPHWKH OLQH\RXW\SHGWHOOV([FHOWRLJQRUHWKHOLQHRI
FRGHWKDWLWFDQWH[HFXWH7KHUHLVQRHUURUPHVVDJHDQGWKHPDFURH[LWVKDYLQJGRQHDOOLWFDQ8VHWKLVPHWKRG
RIKDQGOLQJHUURUVZLWKFDXWLRQ7KLVLVDYHU\VLPSOHPDFUR$PRUHFRPSOH[PDFURZRXOGSUR EDEO\QRWSHUIRUP
DVH[SHFWHGLIHUURUVZHUHVLPSO\LJQRUHG$OVRWKHXVHUKDVQRLGHDWKDWVRPHWKLQJKDVJRQHZURQJ
0RGLI\WKHFRGHRI0DFURWRLQFOXGHDPRUHVRSKLVWLFDWHGHUURUKDQGOHUWKXV
0DUWLQ*UHHQ
ZZZIRQWVWXIIFRP
RI
99515809.005.png 99515809.006.png
(;&(/9%$
0$&52%$6,&6
6XE 0DFUR
2Q(UURU*R7R (UURU+DQGOHU
$FWLYH&HOO2IIVHW5DQJH$6HOHFW
([LW6XE
(UURU+DQGOHU
0VJ%R[<RXPXVWVWDUWEHORZ5RZ
(QG6XE
7KLVWLPHWKHXVHULVSUHVHQWHGZLWKDGLDORJER[ZKHQVRPHWKLQJJRHVZURQJ,IWKHUHLVQRHUURUWKHOLQH
([LW 6XE FDXVHVWKHPDFURWRILQLVKDIWH ULWKDVGRQHLWVMRE RWKHUZLVHWKHXVHUZRXOGVHHWKHPHVVDJHHYHQLI
WKHUHZHUHQRHUURU
,PSURYLQJ5HFRUGHG0DFURV
7KHJRRGZD\WROHDUQWKHEDVLFVRI9%$LVWRUHFRUGDPDFURDQGVHHKRZ([FHOZULWHVLWVRZQFRGH2IWHQWKRXJK
UHFRUGHGPDFURVFRQWD LQPXFKPRUHFRGHWKDQLVQHFHVVDU\7KHIROORZLQJH[HUFLVHVGHPRQVWUDWHKRZ\RXFDQ
LPSURYHDQGVWUHDPOLQHFRGHWKDWKDVEHHQSURGXFHGE\DUHFRUGHGPDFUR
([HUFLVH,PSURYLQJRQ5HFRUGHG0DFURV
7KLVH[HUFLVHVKRZVWKDWZKHQPDFURVDUHUHFRUGHGRIWHQPRUHFRGHLVJHQHUDWHGWKDQQHFHVVDU\,WGHPRQVWUDWHV
WKHXVHRIWKH:LWKVWDWHPHQWWRSUpFLVWKHFRGH
6HOHFWDQ\FHOORUEORFNRIFHOOV
6WDUWWKHPDFURUHFRUGHUDQGFDOOWKHPDFUR)RUPDW&HOOV7KH5HODWLYH5HIHUHQFHVVHWWLQJZLOOQRWEHUHOHYDQW
*RWR )RUPDW ± &HOOV ± )RQW DQGFKRRVH7LPHV1HZ5RPDQ DQG5HG
*RWR3DWWHUQV DQGFKRRVH<HOORZ
*RWR$OLJQPHQW DQGFKRRVH+RUL]RQWDO&HQWHU
*RWR1XPEHU DQGFKRRVH&XUUHQF\
&OLFN2. DQGVWRSWKHUHFRUGHU
&OLFNWKH8QGR EXWWRQRU&WUO=WRXQGR\RXUFKDQJHVWRWKHZRUNVKHHW
6HOHFWDEORFNRIFHOOVDQGUXQWKH )RUPDW&HOOV PDFUR1RWHWKDWLWFDQQRWEHXQGRQH7\SHLQWKHFHOOVWRFKHFN
WKHUHVXOWRIWKHIRUPDWWLQJ
/RRNDWWKHFRGH
6XE )RUPDW6HOHFWLRQ
6HOHFWLRQ1XPEHU)RUPDW
:LWK 6HOHFWLRQ
+RUL]RQWDO$OLJQPHQW [O&HQWHU
9HUWLFDO$OLJQPHQW [O%RWWRP
:UDS7H[W )DOVH
2ULHQWDWLRQ
6KULQN7R)LW )DOVH
0HUJH&HOOV )DOVH
(QG:LWK
:LWK 6HOHFWLRQ)RQW
1DPH 7LPHV1HZ5RPDQ
)RQW6W\OH 5HJXODU
6L]H
6WULNHWKURXJK )DOVH
6XSHUVFULSW )DOVH
6XEVFULSW )DOVH
2XWOLQH)RQW )DOVH
6KDGRZ )DOVH
8QGHUOLQH [O8QGHUOLQH6W\OH1RQH
&RORU,QGH[
(QG:LWK
:LWK 6HOHFWLRQ,QWHULRU
&RORU,QGH[
3DWWHUQ [O6ROLG
3DWWHUQ&RORU,QGH[ [O$XWRPDWLF
(QG:LWK
(QG6XE
0DUWLQ*UHHQ
ZZZIRQWVWXIIFRP
RI
99515809.007.png 99515809.008.png
(;&(/9%$
0$&52%$6,&6
1RWHDOOWKHH[WUDLQVWUXFWLRQVWKDWKDYHEHHQUHFRUGHG'HOHWHOLQHVRIFRGHVRWKDWRQO\ WKHIROORZLQJUHPDLQV
6XE )RUPDW6HOHFWLRQ
6HOHFWLRQ1XPEHU)RUPDW
:LWK 6HOHFWLRQ
+RUL]RQWDO$OLJQPHQW [O&HQWHU
(QG:LWK
:LWK 6HOHFWLRQ)RQW
1DPH 7LPHV1HZ5RPDQ
&RORU,QGH[
(QG:LWK
:LWK 6HOHFWLRQ,QWHULRU
&RORU,QGH[
(QG:LWK
(QG6XE
5XQWKHPDFURWRWHVWWKHHGLWHGFRGH,WVWLOOZRUNVDVEHIRUH
1RZPRGLI\WKHFRGHHYHQIXUWKHU
6XE )RUPDW6HOHFWLRQ
:LWK 6HOHFWLRQ
1XPEHU)RUPDW
+RUL]RQWDO$OLJQPHQW [O&HQWHU
)RQW1DPH 7LPHV1HZ5RPDQ
)RQW&RORU,QGH[
,QWHULRU&RORU,QGH[
(QG:LWK
(QG6XE
7HVWWKHPDFUR(YHU\WKLQJVWLOOZRUNVDQGWKHFRGHZLOOUXQPXFKIDVWHU
7U\UHFRUGLQJWKHVDPHPDFURXVL QJWRROEDUEXWWRQVLQVWHDGRIJRLQJWRWKHGLDORJER[
&KDQJHWKH)RQWWR7LPHV1HZ5RPDQ
&KDQJHWKH)RQW&RORXUWR 5HG
&KDQJHWKH)LOO&RORUWR <HOORZ
&OLFNWKH&HQWHU EXWWRQ
&OLFNWKH&XUUHQF\ EXWWRQ
/RRNDWWKHFRGH<RXVWLOOJHWORWVRIVWXIIWKDW\RX GRQ¶WQHFHVVDULO\ZDQW([FHOLVUHFRUGLQJDOOWKH GHIDXOW
VHWWLQJV0RVWRIWKHVHDUHVDIHWRGHOHWH
([SHULPHQWZLWKHGLWLQJGLUHFWO\LQWRWKHFRGHWRFKDQJHFRORXUVWKHIRQWWKHQXPEHUIRUPDWHWF
([HUFLVH:DWFKD0DFUR%HLQJ5HFRUGHG
7KLVH[HUFLVHVKRZVWKDW\RXFDQOHDUQE\ZDWFKLQJWKHPDFUREXLOGDVLWLVEHLQJUHFRUGHG,WLVDOVRDQH[DPSOHRI
ZKHQVRPHWLPHVWKH:LWKVWDWHPHQWLVQ¶WDSSURSULDWH
2SHQWKHILOH9%$[OV
:KLOVWWKLVZRUNVKHHWLVYLVXDOO\2.DQGFDQEHXQGHUVWRRGE\WKHXVHU WKHSUHVHQFHHPSW\FHOOVFDQFDXVH
SUREOHPV7U\ILOWHULQJWKHGDWDDQGVHHZKDWKDSSHQV*RWR 'DWD ± )LOWHU ± $XWRILOWHU DQGILOWHUE\5HJLRQRU
0RQWK,WLVFOHDUWKDW([FHOGRHVQRWPDNHWKHVDPHDVVXPSWLRQVWKDWWKHXVHUGRHV7KHHPSW\FHOOVQHHGWREH
ILOOHG
7LOHWKH([FHODQG9%(ZLQGRZVYHUWLFDOO\VRWKH\DUHVLGH E\VLGH
6HOHFWDQ\FHOOZLWKLQWKHGDWD,ILWLVDQHPSW\FHOOLWPXVWEHDGMDFHQWWRDFHOOFRQWDLQLQJGDWD
6WDUWWKHPDFURUHFRUGHUDQGFDOOWKHPDFUR )LOO(PSW\&HOOV6HWWRUHFRUG5HODWLYH5HIHUHQFHV
,QWKH9%(ZLQGRZILQGDQGGRXEOH FOLFNWKHPRGXOH0RGXOHIRUWKHFXUUHQWZRUNERRNWRRSHQWKHHGLWLQJSDQH
WKHQVZLWFKRIIWKH3URMHFW([SORUHUZLQGRZDQGWKH3URSHUWLHVZLQGRZRQO\WRPDNHVSDFH
5HFRUGWKHQHZPDFURDVIROORZV
6WHS&WUO WRVHOHFWWKHFXUUHQWUHJLRQ
6WHS(GLW ± *R7R ± 6SHFLDO ± %ODQNV ± 2. WRVHOHFWDOOWKHHPSW\FHOOVLQWKHFXUUHQWUHJLRQ
6WHS7\SH >8S$UURZ@ WKHQSUHVV&WUO(QWHU WRSODFH\RXUW\SLQJLQWRDOOWKHVHOHFWHGFHOOV
6WHS&WUO WRVHOHFWWKHFXUUHQWUHJLRQDJDLQ
6WHS&WUO& WRFRS\WKHVHOHFWLRQ DQ\PHWKRGZLOOGR
6WHS(GLW ± 3DVWH 6SHFLDO ± 9DOXHV ± 2. WRSDVWHWKHGDWDEDFNLQWRWKHVDPHSODFHEXWGLVFDUGLQJWKH
IRUPXODV
0DUWLQ*UHHQ
ZZZIRQWVWXIIFRP
RI
99515809.001.png 99515809.002.png
(;&(/9%$
0$&52%$6,&6
6WHS(VF WRFRPHRXWRI&RS\0RGH
6WHS 6WRS5HFRUGLQJ
/RRNDWWKHFRGH
6XE )LOO(PSW\&HOOV
6HOHFWLRQ&XUUHQW5HJLRQ6HOHFW
6HOHFWLRQ6SHFLDO&HOOV[O&HOO7\SH%ODQNV6HOHFW
6HOHFWLRQ)RUPXOD5& 5>@&
6HOHFWLRQ&XUUHQW5HJLRQ6HOHFW
6HOHFWLRQ&RS\
6HOHFWLRQ3DVWH6SHFLDO3DVWH [O9DOXHV2SHUDWLRQ [O1RQH6NLS%ODQNV B
)DOVH 7UDQVSRVH )DOVH
$SSOLFDWLRQ&XW&RS\0RGH )DOVH
(QG6XE
1RWHWKHXVHRIWKHVSDFH DQGXQGHUVFRUH³B´WRGHQRWHWKHVSOLWWLQJRIDVLQJOHOLQHRIFRGHRQWRDQHZOLQH
:LWKRXW WKLV([FHOZRXOGWUHDWWKHFRGHDVWZRVHSDUDWHVWDWHPHQWV
%HFDXVHWKLVPDFURKDVEHHQUHFRUGHGZLWKZHOOWKRXJKWRXWFRPPDQGVWKHUHLVOLWWOHXQQHFHVVDU\FRGH,QWKH
3DVWH6SHFLDO HYHU\WKLQJDIWHUWKHZRUG³[O9DOXHV´FDQEHGHOHWHG
7U\RXWWKHPDFUR 7KHQXVHWKH$XWR)LOWHUWRRODQGQRWHWKHGLIIHUHQFH
0DUWLQ*UHHQ
ZZZIRQWVWXIIFRP
RI
99515809.003.png 99515809.004.png

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin