Excel VBA Course Notes 1 Macro Basics

background image

(;&(/9%$

0$&52%$6,&6

‹0DUWLQ*UHHQ

ZZZIRQWVWXIIFRP

 RI

([FHO9%$&RXUVH1RWHV0DFUR%DVLFV

)LOH9%$0DFURV[OV

8VLQJWKH0DFUR5HFRUGHU

2SHQ([FHODQGWKH9%( 9LVXDO%DVLF(GLWRU 8QOHVVLWKDVEHHQFKDQJHGWKH9%(ZLQGRZFRQWDLQVWKH

3URMHFW ([SORUHU ZLQGRZDQGWKH3URSHUWLHV ZLQGRZ WKHVHFDQEH DFFHVVHGIURPWKH 9LHZ PHQX 

3URMHFW([SORUHU:RUNVOLNHDILOHPDQDJHU+HOSV\RXQDYLJDWHDURXQGWKHFRGHLQ\RXUZRUNERRN

3URSHUWLHV:LQGRZ 6KRZVWKHSURSHUWLHVRIWKHFXUUHQWO\DFWLYHREMHFW HJ 6KHHW RIWKHFXUUHQWZRUNERRN

HJ %RRN 

([HUFLVH5HFRUGLQJD0DFUR

7KLVH[HUFLVHVKRZVZKDWKDSSHQVZKHQDPDFURLVUHFRUGHGDQGGHPRQVWUDWHVWKHGLIIHUHQFHEHWZHHQUHFRUGLQJ

DEVROXWHDQGUHODWLYHUHIHUHQFHV

 2QDQHPSW\ZRUNVKHHWLQDQHZZRUNERRNVHOHFWFHOO &

 6WDUWWKH0DFUR5HFRUGHU ZLWKRSWLRQWRVDYHPDFURLQ 7KLV:RUNERRN$WWKLVSRLQWWKH9%(FUHDWHVDQHZ

0RGXOHV IROGHU,W VTXLWHVDIHWRJRDQGORRNDWLW  \RXUDFWLRQVZRQ WEHUHFRUGHG&OLFNWKH >@ QH[WWRWKH

IROGHUDQGVHHWKDWWKH9%(KDVSODFHVDPRGXOHLQWKHIROGHUDQGQDP HGLW0RGXOH'RXEOHFOLFNWKHPRGXOHLFRQ

WRRSHQLWVFRGHZLQGRZ6ZLWFKEDFNWR([FHO

 0DNHVXUHWKDWWKH 5HODWLYH5HIHUHQFHEXWWRQRQWKH6WRS5HFRUGLQJ WRROEDULV127SUHVVHGLQ

 6HOHFWFHOO% DQGVWRSWKHUHFRUGHU

 6ZLWFKWRWKH9%(DQGORRNDWWKH FRGH

5DQJH % 6HOHFW

 1RZUHFRUGDQRWKHUPDFURH[DFWO\WKHVDPHZD\EXWWKLVWLPHZLWKWKH 5HODWLYH5HIHUHQFH EXWWRQSUHVVHGLQ

 6ZLWFKWRWKH9%(DQGORRNDWWKHFRGH

$FWLYH&HOO2IIVHW  5DQJH $ 6HOHFW

 1RZUHFRUGDQRWKHUPDFUREXWLQVWH DGRIVHOHFWLQJFHOO%VHOHFWDEORFNRIFHOOV[VWDUWLQJDW% VHOHFWFHOOV

%)

 6ZLWFKWRWKH9%(DQGORRNDWWKHFRGH

$FWLYH&HOO2IIVHW  5DQJH $% 6HOHFW

 3OD\EDFNWKHPDFURVKDYLQJILUVWVHOHFWHGDGLIIHUHQWFHOOWKDQ& IRU0DF URDQG0DFURWKHVWDUWLQJFHOOPXVW

EHLQURZRUEHORZ  VHHVWHSEHORZ

0DFUR  DOZD\VPRYHVWKHVHOHFWLRQWR%

0DFUR  PRYHVWKHVHOHFWLRQWRDFHOOURZVXSDQGFROXPQWRWKHOHIWRIWKHVHOHFWHGFHOO

0DFUR  DOZD\VVHOHFWVDEORFNRIVL [FHOOVVWDUWLQJURZVXSDQGFROXPQWRWKHOHIWRIWKHVHOHFWHGFHOO

 5XQ0DFUREXWIRUFHDQHUURUE\VHOHFWLQJDFHOOLQURZRUDERYH7KHPDFURWULHVWRVHOHFWDQRQ H[LVWHQWFHOO

EHFDXVHLWVFRGHLVWHOOLQJLWWRVHOHFWDFHOOURZVDERYHWKHVW DUWLQJSRLQWDQGWKDW VRIIWKHWRSRIWKHVKHHW

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\DERYHWKHILUVWOLQHRIWKHPDFUR XQGHUQHDWKWKHOLQH

6XE

0DFUR

 5XQ0DFUR DVEHIRUHVWDUWLQJWRRKLJKRQWKHVKHHW7KLVWLPHWKH OLQH\RXW\SHGWHOOV([FHOWRLJQRUHWKHOLQHRI

FRGHWKDWLWFDQ WH[HFXWH7KHUHLVQRHUURUPHVVDJHDQGWKHPDFURH[LWVKDYLQJGRQHDOOLWFDQ8VHWKLVPHWKRG

RIKDQGOLQJHUURUVZLWKFDXWLRQ7KLVLVDYHU\VLPSOHPDFUR$PRUHFRPSOH[PDFURZRXOGSUR EDEO\QRWSHUIRUP

DVH[SHFWHGLIHUURUVZHUHVLPSO\LJQRUHG$OVRWKHXVHUKDVQRLGHDWKDWVRPHWKLQJKDVJRQHZURQJ

 0RGLI\WKHFRGHRI0DFURWRLQFOXGHDPRUHVRSKLVWLFDWHGHUURUKDQGOHUWKXV

background image

(;&(/9%$

0$&52%$6,&6

‹0DUWLQ*UHHQ

ZZZIRQWVWXIIFRP

 RI

6XE

0DFUR

2Q(UURU*R7R

(UURU+DQGOHU

$FWLYH&HOO2IIVHW  5DQJH $ 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 EXWWRQ RU&WUO= WRXQGR\RXUFKDQJH VWRWKHZRUNVKHHW

 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

background image

(;&(/9%$

0$&52%$6,&6

‹0DUWLQ*UHHQ

ZZZIRQWVWXIIFRP

 RI

 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%(ZLQGRZV YHUWLFDOO\ VRWKH\DUHVLGH E\VLGH

 6HOHFWDQ\FHOOZLWKLQWKHGDWD,ILWLVDQHPSW\FHOOLWPXVWEHDGMDFHQWWRDFHOOFRQWDLQLQJGDWD

 6WDUWWKHPDFURUHFRUGHUDQGFDOOWKHPDFUR )LOO(PSW\&HOOV6HWWRUHFRUG5HODWLYH5HIHUHQFHV

 ,QWKH9%(ZLQGRZILQGDQGGRXEOH FOLFNWKHPRGXOH 0RGXOH IRUWKHFXUUHQWZRUNERRNWRRSHQWKHHGLWLQJSDQH

WKHQVZLWFKRIIWKH3URMHFW([SORUHUZLQGRZDQGWKH3URSHUWLHVZLQGRZ RQO\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

background image

(;&(/9%$

0$&52%$6,&6

‹0DUWLQ*UHHQ

ZZZIRQWVWXIIFRP

 RI

6WHS(VF WRFRPHRXWRI&RS\0RGH

6WHS 6WRS5HFRUGLQJ

 /RRNDWWKHFRGH

6XE

)LOO(PSW\&HOOV

6HOHFWLRQ&XUUHQW5HJLRQ6HOHFW

6HOHFWLRQ6SHFLDO&HOOV [O&HOO7\SH%ODQNV 6HOHFW

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


Document Outline


Wyszukiwarka

Podobne podstrony:
Excel VBA Course Notes 1 Macro Basics
Importowanie danych z bazy Accessa do Excela, excel + vba, excel duzo-np
Microsoft Excel Vba Examples
Autocad & Excel VBA Tutorial
Excel VBA for Applications
Anatomy And Massage Course Notes
VBA w Excel7, excel
VBA w Exce13, excel
VBA w Excelu, excel
Microsoft Excel 2010 PL Jezyk VBA i makra Akademia Excela e21vba
VBA w Exce18, excel
Excel 2003 PL Programowanie w VBA Vademecum profesjonalisty
VBA w Exce21, excel
VBA w Exce17, excel
VBA w Excel3, excel

więcej podobnych podstron