EXCEL VBA Step-by-Step GuideTo Learning Excel Programming Language For Beginners ( PDF )

《EXCEL VBA Step-by-Step GuideTo Learning Excel Programming Language For Beginners ( PDF )》由会员分享，可在线阅读，更多相关《EXCEL VBA Step-by-Step GuideTo Learning Excel Programming Language For Beginners ( PDF )（106页珍藏版）》请在凡人图书馆上搜索。

1、EXCEL VBA Step-By -Step Guide To Lea rning Ex celPro g ra mming La ng ua g e Fo r Beg inners Jaso n Jay Co p y rig h t 2 0 1 7 b y Jaso n Jay - All rig h ts reserv ed . If y o u wo u ld lik e to sh are th is b o o k with an o th er p erso n , p lease p u rch ase anad d itio n al co p y fo r each rec

2、ip ien t. Th an k y o u fo r resp ectin g th e h ard wo rk o fth is au th o r. Oth erwise, th e tran smissio n , d u p licatio n o r rep ro d u ctio n o f an y o fth e fo llo win g wo rk in clu d in g sp ecific in fo rmatio n will b e co n sid ered anilleg al act irresp ectiv e o f if it is d o n e

3、electro n ically o r in p rin t. Th is ex ten d sto creatin g a seco n d ary o r tertiary co p y o f th e wo rk o r a reco rd ed co p y an d iso n ly allo wed with ex p ress written co n sen t fro m th e Pu b lish er. All ad d itio n alrig h t reserv ed .TABLE OF CONTENTEXCEL VBA 1In tro d u ctio n

4、5CHAPTER 1 7 VBA Dev elo p er TAB 7Accesin g to th e Dev elo p er TAB 7Qu iz 1 8CHAPTER 2 9 Macro s 9Creatin g a Macro 9Relativ e Referen ces 1 1Ru n n in g th e Macro 1 2Sav in g a Macro -En ab led Wo rk b o o k 1 2Qu iz 2 1 4CHAPTER 3 1 6 Startin g with VBA 1 6Wh at if I n eed to fill th e cells u

5、 p to 1 0 0 ? 1 9In sert Fo rm Bu tto n 2 1Simp le th in g s a Macro can t d o . 2 2In sert Activ eX Bu tto n 2 2Variab les, Do an d Lo o p . 2 3Wh at is th e ad v an tag e o f d eclarin g v ariab les as b y te, in teg er o r an y o th er? 3 1APPs Perfo rman ce 3 5MSGBOX 3 9If an d Select Case 4 0Qu

6、 iz 3 4 3CHAPTER 4 4 5Pro ject: Creatin g a Simp le Calcu lato r u sin g Activ eX 4 5Wh at is a Mo d u le? 4 5Ho w to create a Mo d u le 4 6Ad d in g Letters? 5 8Qu iz 4 5 9CHAPTER 5 6 0Pro ject: Calcu lato r u sin g Fo rms 6 0Rev iew 6 0FORMS 6 1Co mmman d Bu tto n s co d e 6 5Ex cel Fo rmu las o n

7、 VBA 7 4Co mb in in g VBA an d a Sp read sh eet 7 5Startin g with Declaratio n s: 7 8Op en an d Clo se d eclaratio n s: Disp lay in g a fo rm with o u t lo o k in g an y sp read sh eet 8 1Macro Secu rity 8 7Co mmen ts 8 9Th e wh o le co d e fo r Calcu lato r Pro ject 9 4Co mman d Bu tto n s Ord er 1

8、 0 1Ad d in g Passwo rd to VBA Co d e. 1 0 2Qu iz 5 1 0 4In teractin g with o th er Ap p licatio n s. 1 0 5Op en in g o th er ap p s fro m Ex cel 1 0 5Sen d in g an Ou tlo o k e-mail fro m Ex cel: 1 0 8Ex ercises So lu tio n s: 1 1 1An swers Ch ap ter 1 1 1 1An swers Ch ap ter 2 1 1 2An swers Ch ap

9、ter 3 1 1 3An swers Ch ap ter 4 1 1 4An swers Ch ap ter 5 1 1 5 Intro ductio n If y o u alread y k n o w h o w to u se Micro so ft Ex cel b u t th erere a fewth in g s y o u can t d o , it is time to learn th e stro n g est fu n ctio n ality it h as, Visu alBasic fo r Ap p licatio n s (VBA).Visu al

10、Basic fo r Ap p licatio n s is a p ro g rammin g lan g u ag e in co rp o ratedin Micro so ft Ex cel, Access, Po werPo in t an d ev en Wo rd , wh ich let y o u d o allth in g s y o u alread y k n o w ab o u t th em an d mu ch mo re. Fo r ex amp le, y o u wan tth at ev ery time y o u o p en a sp ecifi

11、c Micro so ft Wo rd file it writesau to matically th e cu rren t d ate two lin es b elo w wh ere y o u left last time. Ormay b e y o u wan t a wh o le sp read sh eet o f Ex cel with o u t fo rmu las o n it an d stillap p ly in g th em as if th ey were th ere. Ho w wo u ld y o u d o th at? All th ese

12、 th in g san d mu ch mo re are d o n e with Visu al Basic fo r Ap p licatio n s fo r Micro so ftOffice.Lo o k at th e ex amp le b elo w:It lo o k s lik e a p ro g ram mad e fo r an aly sis, an d it d o es. Gu ess wh atp ro g ram it is? Pro b ab ly y o u are th in k in g it is n o t an y Micro so ft

13、Officep ro g ram, b u t let me tell y o u th at it was mad e with Micro so ft Ex cel, h o wwo u ld y o u d o so meth in g lik e th at with o u t p ro g rammin g ? Th eres n o way !Yo u ll learn mu ch mo re th an th at an d will b e ab le to create y o u r o wnp ro g rams u sin g Visu al Basic fo r A

14、p p licatio n s (VBA).If y o u n eed a v ery sp ecific p ro g ram fo r y o u r b u sin ess an aly sis,so meth in g fo r p erso n al u se, o r ev en ju st fo r h av in g fu n , y o u n eed Visu alBasic fo r Ap p licatio n s n o w! CHAPTER 1VBA Dev elo per TAB Ev ery p ro g rammin g lan g u ag e h as

15、similarities b etween th eirfu n d amen tals. Th e fu n ctio n s IF, Th en , Lo o p , Clo se, Op en , are ju st so me o fth em. Well learn th e b asic o n es first; it will b e n ecessary to u n d erstan d h o wVBA wo rk s.Well u se Micro so ft Ex cel 3 6 5 fo r th is in stru ctio n s an d ex amp le

16、s, h o wev er,fro m Micro so ft Ex cel 2 0 0 7 o n ward s it will wo rk th e same.Accesing to the Dev elo per TAB Micro so ft Ex cel d o esn t sh o w th e Dev elo p er TAB b y d efau lt. It o n ly h asFile, Ho me, In sert, etc. Bu t th eres n o o n e called Dev elo p er. To access to th isTAB th ere

17、 are d ifferen t o p tio n s, b u t well sh o w th e easiest o n e.1 . Rig h t click to th e Rib b o n (an y p art in sid e th e red b o x , ex cep t th eb u tto n s). 2 . Select Cu sto mize th e Rib b o n 3 . En ab le th e Dev elo p er ch eck b o x an d click th e OK b u tto n .4 . Yo u sh o u ld s

18、ee th e Dev elo p er TAB av ailab le n o w. Quiz 1 1 . Ho w y o u Access the Dev elo per TAB?a) It is av ailab le b y d efau lt in Ex cel.b ) Rig h t click o n th e Rib b o n , Cu sto mize th e Rib b o n , en ab le th eCh eck b o x fo r Dev elo p er an d Accep t.c) Go to file, Op tio n s, Ad v an ce

19、d an d En ab le th e Dev elo p er TAB. CHAPTER 2Ma cro s Crea ting a Ma croYo u ll see a few o p tio n s av ailab le in th e Dev elo p er TAB, b y n o w well startto u se th e Reco rd Ma cro b u tto n .A Macro is an au to mated seq u en ce wh ich will ap p ly ev ery time y o u p lay it.Lets see a p

20、ractical ex amp le o f it:Imag in e th at in y o u r jo b y o u d o th e same p ro cess ev ery mo rn in g . It tak esso me v alu ab le time an d ev en y o u re g ettin g b o red o f th at.Th e p ro cess is th e fo llo win g :a) Yo u receiv e a Micro so ft Ex cel file fro m y o u r b o ss with so me

21、d ataan d y o u n eed to write th e d ate u sin g Year, Mo n th an d Day in d ifferen tco lu mn s. Yo u d o th is b ecau se it is th e fo rmat y o u r jo b n eed s an d y o u v e b een ad d in gth e same v alu es ev ery d ay fo r a few y ears. In th is case an semi-au to mated p ro cess wo u ld b e

22、h elp fu l. Ex cel g iv es th ato p tio n to all o f u s with Macro s. A Macro is a semi-au to mated p ro cess wh ichlet y o u ru n a sp ecific task u sin g a sh o rtcu t.To create a Macro , fo llo w th e seq u en ce b elo w:a) Click th e Reco rd a Macro Bu tto n .b ) Write a n ame fo r y o u r Macr

23、o . (Need ed )c) A sh o rtk ey wh ich ev ery time y o u p ress will Ru n th e Macro . Becarefu l, d o n t ad d Ctrl + C o r Ctrl + v , o th erwise it wo n t co p y o r p astean y mo re, b u t ru n th e Macro . In case y o u wan t a mo re sp ecific sh o rtcu t,h o ld th e sh ift k ey as y o u p ress

24、a letter. Fo r ex amp le, ctrl + sh ift + c. Tomak e it wo rk , d o n t p ress ctrl as y o u ad d a sh o rt cu t. (Op tio n al)d ) Sto re Macro in : Perso n al wo rk b o o k : Will b e av ailab le fo r all th efiles y o u o p en with Ex cel o n th at co mp u ter; New Wo rk b o o k will b eav ailab l

25、e fo r a n ew file o n ly . Th is wo rk b o o k , will ap p ly o n ly to th ecu rren t o p en file. (Need ed to ch o o se o n e)e) Write a d escrip tio n ab o u t wh at th at Macro d o es. (Op tio n al)f) Click Ok . g ) Start d o in g ev ery th in g y o u alway s d o , wh ich wo u ld b e ad d in g t

26、h ecu rren t d ate in th is case. h ) On ce y o u fin ish , g o b ack to th e Reco rd Macro Bu tto n , wh ich n o w iscalled Sto p Reco rd in g . Press it an d n o w sh o u ld b e sav ed .Th is wo u ld b e a v ery simp le Macro , it o n ly ad d s th e cu rren t d ate, b u t wh atwo u ld y o u th in

27、k if y o u also n eed to imp o rt d ata fro m a web p ag e wh ich isu p d ated ev ery h o u r, an d n eed to classify it u sin g a few ch arts, an d y o u d o th esame p ro cess sev eral times a d ay . No d o u b t, a g o o d Macro wo u ld b e u sefu l.Th e p ro cess to g et it an y Macro , is th e

28、same wev e fo llo wed . Th eres o n ly animp o rtan t th in g to co n sid er wh en creatin g o n e, it is to ch o o se b etween u sin gRela tive Referen ces o r n o t. Rela tiv e ReferencesTh e Relativ e Referen ces b u tto n is ju st b elo w th e Reco rd a Macro Bu tto n .On ce y o u click o n it,

29、it remain s activ e u n til y o u click o n it ag ain . It is u sed toreco rd macro s in wh ich th e p ro cess sh o u ld b e ap p lied to d ifferen t ran g esin stead o f o n e alread y set.Its fu n ctio n ality is v ery u sefu l. A macro reco rd ed with o u t relativ e referen ceswill alway s rep e

30、at th e p ro cess o n th e same cells u sed wh en reco rd ed . Bu t ify o u u se relativ e referen ces, th e macro will ru n fro m th e activ e cell. Usin g th eex amp le ab o v e, wh at If y o u n eed th e d ates written o n cells F4 :H4 in stead o fB2 :D2 ? Th e o n ly th in g y o u sh o u ld d o

31、is to select F4 an d ru n th e Macro . Orselect an y cell y o u n eed an d ru n it. Bu t y o u n eed to reco rd th e Macro u sin gRelativ e Referen ces, an d th en select th e cell an d ru n it, o th erwise it wo u ld n two rk . Running the Ma croTh ere are a b u n ch o f way s to ru n a Macro . Let

32、s see th e first: Ru n th is o n e b yclick in g o n th e Macro s Bu tto n , th en click o n it to ru n it.May b e it is n o t as p ractical as we ex p ected , h o wev er, well ad d mo refu n ctio n ality an d mak e it easier to ru n in th e n ex t step . Sa v ing a Ma cro -Ena bled Wo rkbo o kOn ce

33、 y o u h av e ad d ed so me Macro s to y o u r wo rk sh eet an d try to sav e ity o u ll g et a n o tificatio n lik e th e fo llo win g : Th is mig h t b e a little trick y , b ecau se mo st p eo p le wo u ld attemp t sav in g th efile with o u t read in g th is n o tificatio n :Th e fo llo win g fe

34、a tu res ca n n o t b e sa ved in ma cro -free wo rkb o o ks:VBA p ro jectTo sa ve a file with th ese fea tu res, click No , a n d th en ch o o se a ma cro -en a b ledfile in th e File Typ e list.To co n tin u e sa vin g a s a ma cro -free wo rkb o o k, click Yes.Mo st p eo p le wo u ld ju st click

35、Yes, an d acco rd in g to th is messag e th eywo u ld n t sav e th e file with th eir Macro s, b u t as Macro free. It wo u ld mak ey o u lo se all y o u r Macro wo rk .To sav e th e Macro , ju st click No to th e messag e ab o v e, th en select sav e asEx cel Macro -En ab led Wo rk b o o k . Click

36、o n Sav e, an d it is d o n e!On ce y o u o p en it ag ain , y o u sh o u ld see a messag e say in g Ma cro s h a ve b eend isa b led , an d a Bu tto n say in g En ab le Macro s. Click o n it an d wo n t h av efu rth er p ro b lems. If y o u d o n t click o n it, y o u wo n t b e ab le to wo rk with

37、VBA, at least y o u en ab le th em o n Macro Secu rity in th e Dev elo p er TAB, o rfo llo w th e step s o n Ch ap ter 5 : Macro Secu rity . Quiz 2 1 . Wha t is a Ma cro ?a) It is an Ex cel Fo rmu lab ) It is a sh o rtcu t wh ich ru n s a reco rd ed p ro cess.c) It is a b u ilt-in p ro cess in clu d

38、 ed in Ex cel. 2 . Ho w to crea te a Ma cro ?a) Click in g o n th e Visu al Basic Bu tto nb ) Click in g o n th e Macro s Bu tto nc) Click in g o n th e Reco rd Macro Bu tto n 3 . Wha t is Rela tiv e References fo r?a) It is to Reco rd a Macro with o u t set sp ecific cells.b ) It is to Reco rd a Ma

39、cro with sp ecific cells.c) With o u t it a Macro isn t ed itab le. 4 . Ho w to Run a Ma cro ?a) Click in g o n th e Macro Bu tto nb ) Click in g o n th e Reco rd Macro Bu tto nc) Click in g o n th e Relativ e Referen ces Bu tto n 5 . Ho w to sa v e a wo rkbo o k with Ma cro s?a) Ju st sav e th e fi

40、le n o rmally , th e Macro s will b e sav ed .b ) Yo u ll g et a n o tificatio n , in wh ich we sh o u ld b e d en ied an d th enselect sav e as Macro -En ab led Wo rk b o o k .c) Yo u ll g et a n o tificatio n , in wh ich we will b e n o tified th at we aresav in g a Macro -en ab led wo rk b o o k

41、, th en ju st accep t to sav e. CHAPTER 3Sta rting with VBA Macro s are fu n d amen tal to b e in tro d u ced to VBA. Lets see wh y b y fo llo win g th e p ro cess:1 .-Create a n ew Macro with o u t relativ e referen ces.2 .-In th e p ro cess select th e cell A1 , write a n u mb er 1 , an d p ress e

42、n ter.3 .-Sto p Reco rd in g . Nex t to th e Reco rd Ma cro b u tto n , th eres an o th er o n e called Visu a l Ba sic.Click o n it an d y o u ll see a co d e lik e th is: Ran g e(A1 ).Select Activ eCell.Fo rmu laR1 C1 = 1 Ran g e(A2 ).Select Co n g rats, y o u h av e so me Visu al Basic Co d e n o

43、 w. It mean s a few o rd ers:Select Cell A1Write th e n u mb er 1Select Cell A2 It is ex actly wh at we d id . Bu t n o w, well ed it th e co d e so th at th e Macro d o esso meth in g else.If we see, th eres a p attern , wh ich is: select, write, select. So , we co u ldco n tin u e th e p attern b

44、y ad d in g a few mo re th in g s d irectly , lik e th is: Ran g e(A1 ).Select Activ eCell.Fo rmu laR1 C1 = 1 Ran g e(A2 ).Select Activ eCell.Fo rmu laR1 C1 = 2 Ran g e(A3 ).Select Activ eCell.Fo rmu laR1 C1 = 3 Ran g e(A4 ).Select No w th at y o u ad d ed th is, ru n th e macro , b u t n o w y o u

45、ll see a seco n d way toru n it. Press th e g reen b u tto n ab o v e th e co d e. On ce y o u click o n it g o to th eEx cel Sp read sh eet b y click in g o n th e Ex cel sy mb o l. Yo u ll see th at th e cellsA1 :A3 are filled with th e n u mb ers we wro te an d th e Cell A4 is selected . It may l

46、o o k v ery co mp lex , an d it is lik e th at b ecau se h u man s u se to d o th in g sth at mach in es wo u ld n t. I n eed ed to select cell A1 , A2 , A3 an d so n o n . Bu td o es Ex cel really n eed to select it to write a simp le n u mb er?Lets try it b y ad d in g th e fo llo win g co d e: Ra

47、n g e(A1 ) = 1 Ran g e(A2 ) = 2 Ran g e(A3 ) = 3 Th ats it! Ex cel can sk ip step s h u man s can t. So , it mak es it mu ch mo re fasteran d easy to acco mp lish its wo rk . Wha t if I need to fill the cells up to 1 0 0 ?No w, I th in k th at if I n eed to fo llo w th is p attern u n til A1 0 0 is

48、g o in g to b eh ard . Wh at sh o u ld I d o ?Lets ad d th is: Ran g e(A1 :A1 0 0 ) = 1 Oo p s! It ad d s a n u mb er 1 to all cells fro m A1 to A1 0 0 . Lets try an o th erth in g : Ran g e(A1 :A1 0 0 ) = 1 + 1 It ad d s a n u mb er two in stead . So , Ho w d o I tell Ex cel th at I wan t it to fil

49、lcells in seq u en ce? Th ere are sev eral way s. On e is to reco rd a Macro an d d u rin g th e p ro cess ad da n u mb er 1 to th e cell A1 , th en h o ld rig h t click o n th e small sq u are an d scro lld o wn u n til y o u select A1 0 0 . Select fill series. Go to Visu al Basic an d y o u llsee

50、a co d e lik e th is: Ran g e(A1 ).Select Activ eCell.Fo rmu laR1 C1 = 1 Selectio n .Au to Fill Destin atio n :=Ran g e(A1 :A1 0 0 ),Ty p e:=x lFillSeries Ran g e(A1 :A1 0 0 ).Select It wo rk s! I th in k we alread y h av e a g reat id ea ab o u t h o w to g et th e VBA co d ewe wan t. Reco rd a Mac