Chuong III- IV VBA for Excel-Auto CAD

T NG HO THIT K CU NG

Lp trnh trn ng dng nn

3. CHNG III Microsoft Excel v lp trnh trn Microsoft Excel3.1. Microsoft ExcelMicrosoft Excel l mt phn mm chuyn x l bng tnh ca hng phn mm ni ting Microsoft. Excel thc s l mt cng c rt mnh m phc v cng tc tnh ton, lp bng biu Giao din ca chng trnh Excel nh sau:

Vi cc bi ton t n gin n phc tp, ta u c th s dng Excel gii quyt mt cch d dng vi rt nhiu tnh nng sn c: Kh nng t chc d liu mnh m vi h thng cc , vng d liu, cc bng tnh; Kh nng x l d liu nh truy vn, lc, tnh ton vi h thng rt phong ph cc hm c bn cng nh cc hm chc nng chuyn bit; Kh nng lp bo co vi cch t chc bng biu v h thng biu tng i hon chnh; Kh nng in n vi nhiu la chn khc nhau. Vi cch t chc ging nh bng tnh thng thng, Excel l mt phn mm bng tnh trc quan v rt d s dng. Tuy nhin, nhng chc nng phong ph cng khng th no p ng c nhu cu ht sc a dng ca thc tin. V bng cch kt hp cc tnh nng sn c ca Excel, cng vi mt t kin thc v lp trnh, ta c th gii quyt c nhng bi ton mt cch d dng.-1-



lp trnh trn Excel, ta c th thc hin theo nhiu cch khc nhau: Thng qua b cng c lp trnh Visual Studio Tools for Office (VSTO): theo cch ny, ngi s dng c th lp trnh to ra cc ng dng dng add-in (ng dng b sung trong Excel) bng cc ngn ng c h tr trong b phn mm Microsoft Visual Studio. Thng qua mi trng lp trnh VBA c tch hp sn trong Microsfot Excel: ngi s dng c th lp trnh m rng Excel mt cch d dng vi ngn ng lp trnh VB.

3.2. Lp trnh trn Excel bng VBA3.2.1. Cch to v s dng MacroMacro l tp hp cc lnh v hm c lu tr trong mt mun ca VBA nhm thc hin mt nhim v no . Mi khi ngi dng thc thi Macro th nhim v c ci t trong Macro s c thc hin. iu ny ht sc hu ch khi cn thc hin nhng thao tc lp i, lp li nhiu ln hoc khi ngi dng mun to ra mt s tnh nng mi cho ng dng nn. 3.2.1.1. To Macro to Macro, ta c th thc hin theo hai cch chnh sau: To Macro kch bn, To Macro s dng VBA. To Macro kch bn l cch n gin nht to Macro. Theo cch ny, Excel lu li tt c cc lnh trong mi bc khi ngi dng thc hin mt nhim v no . Khi ngi dng thc thi Macro kch bn, tt c cc lnh thc hin s c thc hin li ng theo trnh t nh trong qu trnh to Macro kch bn. to Macro kch bn, ta lm theo cc bc sau: 1 2 Trong trnh n Tools, chn MacroRecord New Macro Trong mc Macro name, nhp tn ca Macro

CH Tn Macro phi c bt u bng ch ci thng thng, cc k t cn li c th l kiu ch hoc kiu ch s hoc du gch di. Khng c s dng du cch t tn Macro, mun phn cch t, ta c th dng du gch di thay cho du cch (xem hnh). Khng c t tn Macro trng vi tn ca vng c nh ngha trong Workbook hin hnh

-2-



3

4 5 6 7

Nu mun thc thi Macro bng cch nhn phm tt, nhp mt ch ci thng thng vo Shortcut Key. Sau , thc thi Macro, ta ch cn nhn t hp phm CTRL+Ch ci (vi ch vit thng) hoc CTRL+SHIFT+Ch ci (vi ch vit hoa). Ch ci t lm phm tt khng c php l s hay cc k t c bit nh @ hoc #. Phm tt ny s lm v hiu ho cc phm tt ging nh vy c t trc trong Excel mi khi m Workbook c cha Macro. Trong mc Store Macro In, chn ni s lu tr Macro. Nu mun Macro c hiu lc mi khi s dng Excel, ta chn vo mc Personal Macro Workbook. Nu cn m t thm v Macro ny, ta s nhp thm cc thng tin vo mc Description. Chn OK. Trong qu trnh to Macro kch bn, nu mun lu a ch tng i so vi hin , chn vo biu hnh, ta lm nh sau: trn thanh cng c Stop Recording tng Relative Reference . K t thi im y, a ch s c lu tng i so vi hin hnh cho n khi thot khi Excel hoc chn mt ln na vo biu tng Relative Reference . Thc hin cc thao tc m sau ny s c lp li khi Macro kch bn thc thi (y l phn ni dung chnh ca Macro kch bn). Trn thanh cng c Stop Recording, nhn chut vo biu tng Stop Recording hon thnh vic to Macro kch bn. Lc ny Macro sn sng s dng.

8 9

3.2.1.2. S dng Macro s dng Macro c to trc, ta c th thc thi theo nhiu cch khc nhau: Thc thi Macro theo cch thng thng; Thc thi bng cch dng phm tt gn cho Macro; Thc thi bng cch nhn chut vo mt nt lnh hay mt iu khin ho; Thc thi bng cch nhn chut vo mt i tng ho; Thc thi thng qua nt lnh trn thanh cng c. Chi tit v cc cch thc thi Macro xin tm hiu thm trong ti liu Microsoft Office Excel Help c ci t sn cng Excel. y ch trnh by cch thc thi Macro theo cch thng thng. thc thi Macro theo cch thng thng, ta thc hin theo cc bc sau: 1 2 M Workbook c cha Macro; Trn trnh n Tools chn MacroMacros hin th hp thoi Macro

-3-



3 4

Trong mc Macro name, nhp vo tn Macro to t trc hoc chn t danh sch s xung; Nhn chut vo nt Run bt u thc thi Macro. n phm ESC kt thc qu trnh thc thi Macro.

CH Do Macro c cha m lnh thc thi c kh nng cha cc on m nguy him lm nh hng n h thng, nn mc nh Excel lun t ch an ninh mc cao nht, v do s lm v hiu ho Macro c cha trong Workbook ca Excel. V vy, thc thi Macro cha trong Workbook, ngi s dng phi thit lp li ch an ninh cho Excel mc Trung bnh (Medium) hoc Thp (Low). Ngi s dng nn t mc Trung bnh m bo tnh an ton cho h thng. thit lp ch an ninh cho Excel, trong trnh n Tools, chn MacroSecurity; trong th Security Level, chn mc an ninh ph hp v nhn OK.

3.2.2. To Macro vi VBAMacro kch bn l hnh thc Macro n gin nht. Thc cht, Macro kch bn chnh l Macro c vit bng VBA, tuy nhin tt c cc dng m lnh u c t ng pht sinh tng ng vi tng thao tc m ngi dng thc hin khi tin hnh ghi li Macro. iu ny to nn tnh d s dng cho ngi dng, ch cn nm mt t kin thc v Macro, ngi s dng c th to dng ngay mt Macro p ng nhu cu ca bn thn. Tuy nhin, to ra Macro c kh nng tu bin v nhiu tnh nng nng cao hn, ngi dng cn phi c kin thc v lp trnh v tn dng mi trng pht trin ng dng tch hp sn trong Microsoft Excel, chnh l VBA IDE. Mi Macro tng ng l mt chng trnh con trong VBA. to mi mt Macro s dng VBA, cn thc hin nhng bc sau: 1 Trong trnh n Tools, chn mc MacroVisual Basic Editor khi ng VBA IDE;

-4-



2

Trong trnh n Insert, chn mc Module to mt m-un mi, l ni s cha Macro;

3

Trong trnh n Insert, chn mc Procedure hin th hp thoi Add Procedure;

4

Trong hp thoi Add Procedure, in tn chng trnh vo mc Name, chn kiu chng trnh con l Sub v phm vi l Public

-5-



5 6 7

Vit m lnh cho Macro trong ca s m lnh ca VBA IDE bng ngn ng Visual Basic; thc thi Macro ngay t trong ca s m lnh, n phm F5; Sau khi hon thnh qu trnh vit m lnh cho Macro, chn mc Close and Return to Microsoft Excel trong trnh n File tr v mn hnh chnh ca Excel.

CH Do Macro l mt chng trnh con dng th tc, nn Macro c th c hin th v thc thi thng qua trnh n ToolsMacroMacros th khi to Macro bc 4, trong hp thoi Add Procedure, ngi dng phi chn kiu chng trnh con l Sub v phm vi l Public.

3.2.3. To hm mi trong Excel bng VBA3.2.3.1. Khi nim v hm trong Excel Hm l nhng cng thc c nh ngha sn trong Excel thc hin tnh ton da trn cc s liu u vo, gi l tham s, theo mt trnh t c lp trnh sn nhm thc hin cc php tnh t n gin n phc tp. hiu r hn v cu trc ca mt hm, ta tm hiu v hm ROUND c sn trong Excel, l hm dng lm trn s:

-6-



1 2 3

4

Cu trc. Mt hm bt u bng du bng =, tip sau l tn hm, du ngoc n (, danh sch cc tham s cch nhau bng du phy , v cui cng l du ngoc n ). Tn hm. n phm SHIFT+F3 hin th danh sch tt c cc hm trong Excel. Cc tham s. Tham s c th l s, ch, gi tr logic nh TRUE hoc FALSE, mng, gi tr li nh #NA, hoc tham chiu n mt khc. Tham s truyn vo phi c kiu thch hp vi kiu ca tng tham s ca hm. Tham s truyn vo c th l mt hng s, cng thc, hoc l mt hm bt k. Ch thch hm. Ch thch hm dng th hin cu trc v danh sch cc tham s ca hm, hin ln khi ta nhp vo tn hm. Ch thch hm ch xut hin i vi nhng hm c xy dng sn trong Excel.

C rt nhiu hm c xy dng sn trong Excel v c nhm theo tng lnh vc nh: ton hc, thng k, x l chui, x l d liu thi gian Tuy nhin trong nhiu trng hp, cc hm c sn ny khng p ng c cc yu cu ca bi ton c th, v th vic xy dng hm mi, nhm p ng c yu cu tnh ton l vic rt cn thit.

-7-



3.2.3.2. To hm mi bng VBA Xy dng hm mi trong Excel l hon thon c th v c Excel h tr ti a nh cho php s dng ton b cc hm c t trc, cho php tn dng ton b giao din ca Excel. Cng c to hm mi trong Excel l VBA v ngi dng phi tun theo mt trnh t nht nh. lm r hn vn ny, ta s tm hiu thng qua v d n gin di y, l xy dng mt hm mi tn l Dien_Tich, c nhim v tnh din tch ca mt hnh ch nht. Phn tch nhim v trn, ta nhn thy: cn phi to c mt hm mi, sao cho khi s dng trong Excel khng c s khc bit so vi cc hm c sn, ngha l s nhp vo cng thc c dng =Dien_Tich(Tham_s, Tham_s), trong Tham_s l chiu di v chiu rng ca hnh ch nht, c th l mt s, cng thc khc hoc mt tham chiu n mt khc trong bng tnh. Kiu d liu ca Tham_s l kiu s thc, v gi tr tr v ca hm cng phi c kiu d liu l s thc nhm m bo tnh tng qut ca hm. to mt hm mi, ta thc hin theo cc bc sau: 1 2 3 Khi ng VBA IDE. Trong trnh n Tools, chn mc MacroVisual Basic Editor; Trong trnh n Insert, chn mc Module to mt m-un mi, ni s cha hm do ngi dng nh ngha. Trong trnh n Insert, chn mc Procedure hin th hp thoi Add Procedure. Sau in tn hm vo mc Name, chn kiu chng trnh con l Function v phm vi l Public. Cui cng chn OK;

4

Chng trnh s t ng pht sinh on m lnh nh sau: Public Function Dien_Tich() End Function Thay on m lnh trn bng on m lnh sau: Public Function Dien_Tich(Rong As Double, Cao As Double) As Double Dien_Tich = Rong*Cao End Function-8-



5 6

Trong trnh n File, chn mc Close and Return to Microsoft Excel quay tr v mn hnh chnh ca Excel; Lc ny, hm m ta va xy dng, c tn l Dien_Tich, c th c s dng bnh thng nh cc hm khc ca Excel.

CH : Cc bc to hm mi cng tng t nh cc bc to Macro phn trc. Tuy nhin, do hm cn phi c gi tr tr v nn khi khai bo kiu chng trnh con cho hm, ngi dng cn phi chn l Function (khc vi khi to Macro, phi chn l Sub).

Tt c cc hm do ngi dng nh ngha thc hin theo cc bc nh trn ch c phm vi hiu lc trong ti liu (workbook) c cha hm m thi. Do vy, khi c nhu cu to lp cc hm mi s dng cho tt c cc ti liu trong Excel th ngi dng phi to hm trong mt tp ti liu c bit c tn l Personal.xls. Tt c cc hm hoc Macro khi c to v lu trong tp Personal.xls u c th c s dng li trong tt c cc phin lm vic tip sau ca Microsoft Excel. Cch thc to Macro v hm mi trong tp Personal.xls hon ton tng t nh cch thc to Macro v hm mi nh c trnh by. iu quan trng l mc nh tp Personal.xls lun c thuc tnh n, nn ngi dng s khng th nhn thy c tp ti liu ny. hin th c tp ti liu n ny, ngi dng c th thc hin theo nhiu cch khc nhau. Trong khun kh ca ti liu ny, xin c gii thiu phng thc sau: 1 2 Trong trnh n Tools ca Microsoft Excel, chn mc MacroRecord New Macro Trong hp thoi Record Macro, ta nhp tn Macro bt k, trong mc Store macro in bt buc phi chn l Personal Macro Workbook, sau chn OK. Nhn ngay chut vo nt Stop Recording kt thc.

-9-



3

n phm ALT+F11 khi ng VBA IDE. Trong ca s Project Explorer xut hin thm mt d n c tn l Personal.xls. Nhn p chut vo nhnh Module1, khi trong ca s m lnh s xut hin dng m lnh nh sau:

Sub Ten_Macro_bat_ky() ' ' Ten_Macro_bat_ky Macro 'Macro recorded 1/8/2007 by TTH ' End Sub

4 5 6

Xo ton b on m lnh trn. Sau tin hnh to Macro hoc hm mi trong Module1 ca tp Personal.xls. Trong trnh n File ca VBA IDE, chn mc Save PERSONAL.XLS lu nhng thay i vo tp Personal.xls. Vi nhng ln to hm sau, ngi dng ch cn khi ng VBA IDE s thy tp Personal.xls c hin th sn, khng cn phi thc hin li cc bc t bc 2 n bc 3.

CH : Mc nh, tp Personal.xls c lu trong th mc sau: C:\Documents and Settings\\Application Data\Microsoft\Excel\XLSTART

3.2.4. H thng cc i tng trong Excel3.2.4.1. M hnh i tng ca Microsoft Excel to ci nhn tng quan cho ngi lp trnh, Microsoft cung cp cho ngi lp trnh m hnh i tng c s dng trong Microsoft Excel. M hnh i tng y c trnh by trong ti liu hng dn ca Excel. y xin c gii thiu li m hnh i tngvi mt s i tng thng c s dng trong lp trnh m rng Excel.

- 10 -



Application

Workbooks

Workbook

Worksheets Rows Worksheet Columns Range Ch gii: Tp i tng i tng Cells 3.2.4.2. Gii thiu v cc i tng trong Excel i tng l mt thc th i din cho mt thnh phn no ca ng dng, chng hn nh i tng bng tnh (Worksheet), (Cell), Mi mt i tng c h thng cc phng thc v thuc tnh. truy cp vo cc thuc tnh v phng thc ca i tng, ta s dng c php .. Trong VB, ta cn phi khai bo v gn i tng trc khi s dng cc phng thc ca i tng hay thay i thuc tnh ca i tng. Ly v d gn gi tr cho A1 ca bng tnh hin hnh, ta thc hin nh sau: ThisWorkbook.ActiveSheet.Range("A1").Value = 15 Tp i tng thc cht cng l mt i tng, nhng i tng ny c th cha nhiu loi i tng tng t nhau. Mc d vy, cc i tng trong tp i tng vn c th c x l bng cc k thut nh nhau. Tp i tng cng c cc phng thc v thuc tnh. V d nh tp i tng Workbooks cha tt c cc i tng Workbook ang c m trong Excel, ng thi cng c phng thc Add thm Workbook mi. truy cp vo mt i tng trong tp i tng, ta s dng cc ch s hoc tn ca i tng cn truy cp theo c php: (ch_s/tn_i_tng).. Ly v d khi ta mun ng ti liu u tin trong tp i tng ti liu (Workbook), ta thc hin nh sau: Sub CloseFirst() Workbooks(1).Close End Sub H thng i tng ca Microsoft Excel rt phong ph, nhng trong khun kh ca chng trnh, ch gii thiu mt s i tng thng dng, bao gm:

- 11 -



a. i tng ng dng (Application) i tng Application c hiu l ton b chng trnh Excel m ngi dng ang lm vic trn . Mi ln chy chng trnh Excel l mt i tng Application s c to ra. i tng Application c cha nhiu thit lp cho ng dng (chng hn nh cc la chn trong trnh n ToolsOptions) v rt nhiu i tng trong ng dng (chng hn nh cc ti liu ang c m - Workbooks, hay bng tnh hin hnh ActiveSheet). Vic to mi mt i tng Application tng ng vi vic khi ng chng trnh Excel. on m lnh sau s khi ng chng trnh Excel t chng trnh ngoi v s m mt ti liu trong Excel Set xl = CreateObject("Excel.Sheet") xl.Application.Workbooks.Open "newbook.xls" Cn bn trong Excel (trong VBA dnh cho Excel), ta b qua bc khi ng Excel v thc hin m mt ti liu nh sau : Workbooks.Open "newbook.xls" Mt s thuc tnh trong i tng Application thng c s dng nh sau:Thuc tnh ActiveCell ActiveSheet ActiveWindow ActiveWorkbook Workbooks M t tr v i tng kiu Range th hin hin hnh ca ti liu hin hnh. Tr v i tng th hin bng tnh hin hnh ca ti liu hin hnh. Nu khng c bng tnh hin hnh, thuc tnh ny s tr v gi tr Nothing. Tr v i tng kiu Window th hin ca s hin hnh. Nu khng c ca s no m s tr v gi tr Nothing. Tr v i tng kiu Workbook, th hin ti liu trong ca s hin hnh. S tr v gi tr Nothing nu khng c ca s no c m. Tr v tp i tng Workbooks th hin tt c cc ti liu ang m. Ta c th truy cp vo mt ti liu bt k thng qua ch s hoc tn ca ti liu.

CH : Vi cc phng thc v thuc tnh tr v cc kiu i tng ph bin, chng hn nh hin hnh (thuc tnh ActiveCell), ta c th truy cp m khng cn s dng i tng Application trc. Chng hn nh, thay v vit on m Application.ActiveCell.Font.Bold=True , ta c th vit ActiveCell.Font.Bold = True

b. i tng ti liu (Workbook) i tng ti liu (Workbook) th hin mt ti liu Excel. Mi mt ti liu ang c m trong Excel tng ng vi mt i tng Workbook. i tng Workbook l mt phn t ca tp i tng Workbooks. Tp i tng Workbooks cha tt c cc ti liu hin ang c m trong Excel. truy cp vo i tng Workbook, ta c th thc hin thng qua: Thuc tnh Workbooks y chnh l thuc tnh ca i tng Application nh c cp.

- 12 -



truy cp, ta s dng Workbooks(tn/ch_s), trong l tn hoc ch s ca mt i tng Workbook. V d sau s kch hot ti liu u tin. Workbooks(1).Activate Ch s y chnh l th t to hoc m cc ti liu. Workbooks(1) l ti liu u tin, cn Workbooks(Workbooks.Count) l ti liu cui cng c to hoc m. Ch s ny tnh n tt c cc ti liu, ngay c ti liu c n i. Thuc tnh ActiveWorkbook y cng chnh l mt thuc tnh ca i tng Application. Thuc tnh ny th hin ti liu hin hnh trong Excel. V d sau s thay i tn tc gi ca ti liu hin hnh ActiveWorkbook.Author = "TDHTKC" Thuc tnh ThisWorkbook Thuc tnh ny tr v i tng kiu Workbook, ni m dng m lnh Visual Basic c thc thi. Trong hu ht cc trng hp, thuc tnh ny v thuc tnh ActiveWorkbook l nh nhau. to mi, m, hoc ng ti liu, ta phi s dng mt s phng thc ca tp i tng Workbooks: Workbooks.Add: thm mt ti liu mi vo Excel v a vo tp i tng Workbooks. Workbooks.Open ng_dn: m mt ti liu c c ch nh v a vo tp i tng Workbooks. Workbooks.Close: ng tt c cc ti liu ang m trong Excel. Workbooks(ch_s/tn).Close: ng mt ti liu c cho trc. Mt s thuc tnh v phng thc thng c s dng ca i tng Workbook:Phng thc Activate Close Protect Unprotect([mt_khu]) Save SaveAs M t Kch hot ti liu lm ti liu hin hnh. ng ti liu. Bt ch bo v, v do khng th chnh sa ti liu. Tt ch bo v. Khi ti liu c t mt khu, ta phi thm vo tham s . Lu tt c cc thay i trong ti liu. Lu tt c cc thay i trong ti liu sang mt tp mi.

Chi tit v tham s trong cc phng thc, xin xem thm ti liu hng dn ca Microsoft Excel.- 13 -



V d sau s to mt ti liu mi v nhc ngi dng nhp tn tp, sau s lu li ti liu: Set NewBook = Workbooks.Add Do fName = Application.GetSaveAsFilename Loop Until fName False NewBook.SaveAs Filename:=fNameThuc tnh ActiveSheet Name Saved Worksheets M t Tr v bng tnh hin hnh ca ti liu. Tr v tn ca ti liu. Kim tra xem ti liu c lu hay cha. Tr v gi tr TRUE nu ti liu c lu, ngc li l FALSE. Tr v tp i tng Worksheets, cha tt c cc bng tnh trong ti liu.

V d sau s hin th thng bo nu ti liu cha c lu: If Not ActiveWorkbook.Saved Then MsgBox "Ti liu vn cha c lu." End If c. Tp i tng Sheets Tp i tng Sheets cha tt c cc i tng biu (Chart) v bng tnh (Workbook) trong ti liu. Tp i tng Sheets rt hu dng khi ta mun truy cp n i tng m khng cn phi bit r i tng l biu hay bng tnh. V d sau s in tt c cc biu v bng tnh Sheets.PrintOut Ta s dng phng thc Add to mt trang mi. V d sau s thm hai trang biu vo ti liu hin hnh v t sau trang th 2 trong ti liu Sheets.Add type:=xlChart, count:=2, after:=Sheets(2) d. i tng bng tnh (Worksheet) Mt i tng bng tnh (Worksheet) th hin mt bng tnh trong ti liu Excel. Trong mt ti liu c th c nhiu bng tnh khc nhau, tt c cc bng tnh ny u l phn t ca tp i tng Worksheets, v n lt mnh, Worksheets cng l mt thuc tnh ca i tng Workbook. i tng Worksheet c truy cp thng qua i tng Workbook, ni cha i tng Worksheet, thng qua cc thuc tnh sau Thuc tnh Worksheets truy cp, ta s dng Worksheets(tn/ch_s), trong l tn hoc ch s ca mt i tng Worksheet. V d sau s n bng tnh u tin trong ti liu hin hnh: Worksheets(1).Visible = False

- 14 -



Ch s y chnh l th t ca bng tnh trn thanh cng c cha th bng tnh. Worksheets(1) l bng tnh u tin (bng tnh bn tri nht), cn Worksheets(Worksheets.Count) l bng tnh cui cng. Ch s ny tnh n tt c cc bng tnh, ngay c cc bng tnh c n i. Thuc tnh ActiveWorksheet Khi mt bng tnh ang hin hnh, ta c th s dng thuc tnh ny tham chiu n bng tnh .V d sau s s dng phng thc Activate kch hot Sheet1 t hng trang in v tin hnh in bng tnh: Worksheets("Sheet1").Activate ActiveSheet.PageSetup.Orientation = xlLandscape ActiveSheet.PrintOut thm mt i tng Worksheet, ta phi s dng cc phng thc Add c trong tp i tng Worksheets, . Bng tnh mi c thm vo s l bng tnh hin hnh. Di y l mt s phng thc v thuc tnh thng c s dng ca i tng Worksheet:Phng thc M t Activate Calculate Copy Delete Move Paste PrintOut Protect Unprotect Thuc tnh Cells Columns Index Name PageSetup Kch hot bng tnh lm bn tnh hin hnh Thc hin qu trnh tnh ton cho ton b bng tnh hoc cho mt vng c ch nh trc. Sao chp bng tnh n mt v tr khc trong ti liu. Xo bng tnh. Di chuyn bng tnh n v tr mi trong ti liu. Dn ni dung trong b nh m vo bng tnh. In ni dung ca bng tnh. Bt ch bo v bng tnh, v do ta khng th chnh sa ni dung bng tnh c na. Tt ch bo v bng tnh. M t Tr v i tng kiu Range th hin tt c cc trong bng tnh. Tr v i tng kiu Range th hin tt c cc ct trong bng tnh. Tr v ch s ca i tng Worksheet trong tp i tng Worksheets. Tn ca bng tnh. Tr v i tng PageSetup, ni cha thit lp v trang in ca bng tnh.

- 15 -



Range Rows Visible

Tr v i tng kiu Range th hin mt hoc mt vng no trong bn tnh. Tr v i tng kiu Range th hin tt c cc hng trong bn tnh. Thit lp hin th/n bng tnh.

e. i tng biu (Chart v ChartObject) i tng biu th hin mt biu trong ti liu. Biu c th nm trong mt trang ti liu ring (i tng Chart) hoc c th c nhng trong mt bn tnh (i tng ChartObject). truy cp vo i tng biu , ta s dng mt s thuc tnh v phng thc c tr v i tng kiu Chart sau: Thuc tnh ChartObjects V d sau thit lp mu t cho biu nhng trong bng tnh c tn Sheet1 Worksheets("Sheet1").ChartObjects(1).Chart. _ ChartArea.Interior.Pattern = xlLightDown Tp i tng Charts V d sau s thay i mu ca d liu trong trang biu u tin Charts(1).SeriesCollection(1).Interior.Color=RGB(255,0,0) Thuc tnh ActiveChart V d sau s kch hot mt trang biu , sau thit lp tiu ca biu thng qua thuc tnh ActiveChart Charts(1).Activate With ActiveChart .HasTitle = True .ChartTitle.Text = "January Sales" End With Thuc tnh ActiveSheet V d sau s kch hot mt trang biu , sau thit lp li kiu ca biu thng qua thuc tnh ActiveSheet Charts("chart1").Activate ActiveSheet.Type = xlLine f. i tng vng d liu (Range). Thuc tnh Columns, Rows, Cells Mi bng tnh c to thnh t tp hp cc (Cells). Cc chnh l ni ngi s dng thc hin cc cng vic tnh ton, x l v trnh by d liu ca mnh. Trong qu trnh lm vic ngi dng c th lm vic vi khng ch mt , m cn c th vi tp hp cc hay chnh xc hn l lm vic vi mt vng d liu (Range). Vng d liu c th c th hin theo nhiu cch khc nhau: Mt hoc nhiu tnh (Cells); Dng d liu (Rows); Ct d liu (Columns);- 16 -



Vng d liu (Range). tham chiu n mt vng d liu trong bng tnh, ta c th s dng cc thuc tnh Cells, Columns, Rows, Range, Offset v phng thc Union c trong bng tnh: Thuc tnh Cells: tham chiu n mt trong bng tnh. Cells(ch_s_hng, ch_s_ct) V d, mun gn gi tr 5 cho B5 (hng th 5, ct th 2) ca bng tnh Sheet1, ta s dng on m lnh sau: Sheets(Sheet1).Cells(5,2).Value = 5 Thuc tnh Rows: tham chiu n cc dng trong bng tnh Rows (ch_s_hng) V d sau s i mu dng th 3 thnh mu Dim MySheet As Worksheet Set MySheet = Worksheets("sheet1") MySheet.Rows(3).Interior.Color = RGB(255, 0, 0) Thuc tnh Columns: tham chiu n cc ct trong bng tnh Columns (ch_s_ct) V d sau s i kiu phng ch ct 2 thnh kiu in m Dim MySheet As Worksheet Set MySheet = Worksheets("sheet1") MySheet.Columns(2).Font.Bold = True Thuc tnh Range: tham chiu n mt vng d liu trong bng tnh Thuc tnh ny c tnh tng qut hn so vi 3 kiu thuc tnh c cp trn. C nhiu cch khc nhau tham chiu n mt vng d liu trong bng tnh. - Nu vng d liu l mt tnh Range(Tn__d_liu) V d, mun gn gi tr 5 cho B5, ta s dng on m lnh sau Sheets(Sheet1).Range(B5).Value = 5 - Nu vng d liu nm gii hn trong hnh ch nht gia hai Range(tn__1, tn__2) hoc Range(tn__1 : tn__2) V d, khi cn tham chiu n vng A2:C5 ta c th s dng on m lnh sau Sheets(Sheet1).Range(A2, C5) hoc Sheets(Sheet1).Range(A2:C5)- 17 -



- Nu vng d liu l mt vng c t tn Range(tn_vng_d_liu) V d nh trong bng tnh Tai_trong, c mt vng d liu do ngi dng t tn l luc_dong_dat, th c th tham chiu ti vng d liu theo c php sau Worksheets("Tai_trong").Range("luc_dong_dat") Thuc tnh Offset: tham chiu n mt vng d liu vi mt khong dch cho trc so vi vng d liu hin ti Offset(s_hng, s_ct) V d sau thc cht s lm thay i mu nn ca vng d liu C4:D4 Worksheets("Sheet1").Activate Range("A1:B2").Offset(3, 2).Interior.Color = _ RGB(200, 0, 0) Phng thc Union: dng ni cc vng d liu khc nhau, s dng khi ta mun tham chiu n nhiu vng d liu khc nhau mt lc Union(vng_d_liu_1,vng_d_liu_2,) V d sau s chn hai vng d liu A1:B2 v D4:D5 Dim r1 As Range, r2 As Range Dim myMultiAreaRange As Range Worksheets("sheet1").Activate Set r1 = Range("A1:B2") Set r2 = Range("D4:E5") Set myMultiAreaRange = Union(r1, r2) myMultiAreaRange.Select Qu trnh tnh ton, x l d liu ch yu c thc hin trn cc vng d liu. Di y l danh sch cc phng thc v thuc tnh thng dng c trong i tng kiu Range.Phng thc Activate Clear Delete [Shift] Merge [Across] M t Kch hot mt n, ny phi nm trong mt vng la chn. la chn mt vng d liu, ta s dng phng thc Select. Xo ni dung trong vng d liu. Xo vng d liu khi bng tnh. Sau khi xo, gi tr Shift s quy nh cch thc cc khc s lp y vo cc trong vng d liu b xo Ni cc trong vng d liu. Nu bin Across l True th chng trnh ch ni cc trn cng mt hng. Nu l False th tt c cc trong vng d liu s c ni thnh mt duy nht. Mc nh, Across c gi tr False. Phng thc ny dng la chn mt vng d liu.

Select

- 18 -



Sort

Phng thc ny dng sp xp d liu trong vng d liu. C rt nhiu tham s p dng cho phng thc ny, c th tham kho thm trong cc ti liu tr gip ca Excel. Hu b ni . M t Tr v hoc thit lp cng thc Range("A1").Formula = "=$A$4+$A$10" cho vng d liu. V d:

Unmerge Thuc tnh Formula FormulaArray

Tr v hoc thit lp cng thc kiu mng cho vng d liu. Nu vng d liu khng cha cng thc kiu mng, thuc tnh ny s tr v gi tr Null. V d: Range("E1:E3").FormulaArray = "=Sum(R1C1:R3C3)" Tr v gi tr True nu vng d liu l mt phn trong cng thc kiu mng. Tr v hoc thit lp tn cho vng d liu. Tr v hoc thit lp gi tr cho vng d liu.

HasArray Name Value

CH Trong Excel, ph bin c hai kiu tham chiu n a ch ca vng d liu: kiu A1 v kiu R1C1. Kiu tham chiu A1 l kiu tham chiu mc nh trong Excel. Theo cch ny, cc ct c k hiu t A n IV (gm 256 ct) v cc hng c k hiu bng s t 1 n 65536. Nu mun tham chiu n ct th 2, hng th 2, ta s dng a ch l B2. Vi kiu tham chiu R1C1, tt c ct v hng u c nh s. Theo cch ny, v tr ca mt c xc nh bng cch s dng: R-s th t hng-C-s th t ct. V d, tham chiu n ct th 2, hng th 2, ta s dng a ch l R2C2. i vi kiu tham chiu R1C1, a ch u l a ch tuyt i, nu mun s dng a ch tng i, ta s s dng cp du ngoc vung []. V d, tham chiu n mt nm cch hin ti l 2 ct v pha tri v 2 hng v pha di, ta s s dng a ch l R[2]C[-2]. Cn nu mun tham chiu n mt nm cng ct hin ti v cch hin ti 2 hng v pha trn, ta s s dng a ch l R[-2]C. Cn phi lu l vi kiu tham chiu A1, a ch ct trc a ch hng; cn i vi kiu tham chiu R1C1, a ch ct pha sau a ch hng.

3.3. Bi tp p dngTo hm tra bng mt chiu, p dng tra bng tra m un n hi yu cu:S trc xe tnh ton (xe/ng/ln) Eyc (Mpa) 50 133 100 147 200 160 500 178 1000 192 2000 207 5000 224 7000 235

Phn tch Lp s khi i vi bi ton ny, yu cu phi to c mt hm mi phc v cho vic tra bng. Theo cch suy ngh l-gic, tra bng mt chiu, ta cn phi c: Bng cc gi tr, hay ni khc, l bng tra, y l Bng tra m un n hi yu cu; S tra, y l s trc xe tnh ton.- 19 -



tra bng nhanh chng th d liu v s trc xe tnh ton phi c sp xp theo th t tng dn hoc gim dn. thc hin qu trnh tra bng, ta phi ln lt tm hai ct d liu lin k sao cho s cn tra nm trong khong gi tr hng s trc xe tnh ton ca hai ct lin k , ta gi l s X1 v X2. Tng ng vi hai gi tr X1 v X2, ta s c c gi tr hng Eyc tng ng l Y1 v Y2. Nh vy, gi tr cn tra, TraBang, s c xc nh da theo cng thc: TraBang = Y 1 +

(Y 2 Y 1) ( so _ tra X 1) ( X 2 X 1)

Vi cc trng hp c bit khc, cch thc x l nh sau: Nu s_tra=X1 hoc S_tra=X2 th gi tr tra bng s l Y1 hoc Y2 tng ng; Cn nu S_tra khng nm trong bng tra, ta s c nhiu cch x l khc nhau, nhng y, ta thng nht la chn l thng bo cho ngi s dng bit l s cn tra khng nm trong bng tra. chuyn i cch suy ngh theo l-gic nh trn thnh chng trnh, ta cn phi to mt hm c tn l TraBang vi cc tham s nh sau: Function TraBang(so_tra as Double,Vung_tra as Range) as Double Trong So_tra l s s dng tra bng, kiu Double; cn Vung_tra l mt vng d liu cha cc gi tr trong bng tra, kiu Range. V s khi ca hm tra bng c th c biu din nh sau:

- 20 -

T NG HO THIT K CU NG Bt u Khai bo bin C_th_tra=False I=1


C_th_tra= True ng

Sai

Thng bo: khng tra c Tra_bng=Null

X1=S_tra Sai I=so_tra) And _ (Vung_Tra(1, i + 1) ).Value

Chuong III- IV VBA for Excel-Auto CAD

Documents

Transcript of Chuong III- IV VBA for Excel-Auto CAD