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(GLWRU8QOHVVLWKDVEHHQFKDQJHGWKH9%(ZLQGRZFRQWDLQVWKH

3URMHFW ([SORUHU ZLQGRZDQGWKH3URSHUWLHV ZLQGRZWKHVHFDQEH DFFHVVHGIURPWKH 9LHZ 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

0DNHVXUHWKDWWKH 5HODWLYH5HIHUHQFHEXWWRQRQWKH6WRS5HFRUGLQJ 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

background image

(;&(/9%$

0$&52%$6,&6

‹0DUWLQ*UHHQ

ZZZIRQWVWXIIFRP

RI

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\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%(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

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%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


Document Outline


Wyszukiwarka

Podobne podstrony:
Excel VBA Course Notes 1 Macro Basics
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