工作表操作
在文件操作中已經(jīng)講了Excel工作簿文件的打開(kāi),新建,保存和關(guān)閉了,這里再講文件下的表格操作.1)新建與刪除新建工作表,圖表或宏表.新建的工作表將成為活動(dòng)工作表.Sheets.Add(Before,After,Count,Type)說(shuō)明:
BeforeVariant類型,可選.指定工作表對(duì)象,新建的工作表將置于此工作表之前.
AfterVariant類型,可選.指定工作表對(duì)象,新建的工作表將置于此工作表之后.
CountVariant類型,可選.要新建的工作表的數(shù)目.默認(rèn)值為1.
TypeVariant類型,可選.指定工作表類型.可為以下XlSheetType常量之一:xlWorksheet,xlChart,xlExcel4MacroSheet或xlExcel4IntlMacroSheet.默認(rèn)值為xlWorksheet.如果Before和After兩者均省略,則新建的工作表將插入到活動(dòng)工作表之前.示例:Sheets.Addafter:=Sheets(Sheets.Count)該語(yǔ)句可以在最后一個(gè)工作表后增加一個(gè)新表
刪除工作表
Sheets("工作表名").Delete示例:在工作表末新建一個(gè)和刪除倒數(shù)第二個(gè)表.
SubMySht()
Application.DisplayAlerts=False'關(guān)閉刪除確認(rèn)對(duì)話框
Sheets.Addafter:=Sheets(Sheets.Count)
Sheets(Sheets.Count-1).Delete
Application.DisplayAlerts=True'開(kāi)啟確認(rèn)對(duì)話框EndSub
2)隱藏與顯示
使用工作表的visible屬性來(lái)設(shè)置工作表是否隱藏或顯示隱藏Sheets("sheet2").Visible=False顯示Sheets("sheet2").Visible=True
3)保護(hù)與撤銷
保護(hù)工作表使其不至被修改.
Sheets("工作表名").Protect(Password)
撤銷工作表保護(hù)使其被修改.
Sheets("工作表名").Unprotect(Password)
PasswordVariant類型,可選.為一個(gè)字符串,該字符串為工作表或工作簿指定區(qū)分大小寫(xiě)的密碼.如果省略本參數(shù),不用密碼就可以取消對(duì)該工作表或工作簿的保護(hù).否則,必須指定密碼,通過(guò)密碼來(lái)取消對(duì)該工作表或工作簿的保護(hù).
示例:Sheets("sheet1").Protect"123456"Sheets("sheet1").Unprotect"123456"
如果要對(duì)工作進(jìn)行詳細(xì)保護(hù)設(shè)置,可參考ExcelVBA參考,這里僅講簡(jiǎn)單的參數(shù)設(shè)置.
單元格和區(qū)域操作
Excel數(shù)據(jù)的計(jì)算歸根到底還是對(duì)表的單元進(jìn)行的,所以單元格和區(qū)域操作非常重要的,這一節(jié)就詳
細(xì)介紹這方面的內(nèi)容.
一,如何引用單元格和區(qū)域
1)用Range屬性引用單元格和單元格區(qū)域使用Range屬性來(lái)引用A1引用樣式中的單元格或單元格區(qū)域及進(jìn)行屬性更改和賦值.如例句:Sheets("Sheet1").Range("A1:D5").Font.Bold=True
Sheets("Sheet1").Range("A1:D5").Value=10000
Range對(duì)象既可表單個(gè)單元格,也可表單元格區(qū)域.下面說(shuō)明Range對(duì)象最常用方法.
引用含義Range("A1")單元格A1
Range("A1:B5")從單元格A1到單元格B5的區(qū)域
Range("C5:D9,G9:H16")多塊選定區(qū)域
Range("A:A")A列
Range("1:1")第一行
Range("1:5")從第一行到第五行的區(qū)域
Range("1:1,3:3,8:8")第1,3和8行
Range("A:C")從A列到C列的區(qū)域
Range("A:A,C:C,F:F")A,C和F列
Range("單元格區(qū)域名0")命名的單元格區(qū)域
2)用Cells屬性引用單元格
可用Cells屬性通過(guò)行列編號(hào)來(lái)引用單個(gè)單元格.下例中Cells(6,1)返回Sheet1上的單元格A6,然后將Value屬性設(shè)置為10.如:Worksheets("Sheet1").Cells(6,1).Value=10因?yàn)榭捎米兞刻娲辛芯幪?hào),所以Cells屬性非常適合單元格區(qū)域中循環(huán)且速度很快.如下例所示:SubCC()DimCounterAsIntegerForCounter=1To20Worksheets("Sheet1").Cells(Counter,3).Value=CounterNextCounterEndSub
3)用Rows或Columns屬性引用行列
可用Rows屬性或Columns屬性來(lái)處理整行或整列.下例中,用Rows(1)返回Sheet1上的第一行,然后將單元格區(qū)域的Font對(duì)象的Bold屬性設(shè)置為True.如:Worksheets("Sheet1").Rows(1).Font.Bold=True
下表舉例說(shuō)明了使用Rows和Columns屬性的一些行和列的引用.引用含義Rows(1)第一行
Rows工作表上所有的行
Columns(1)第一列
Columns("A")第一列
Columns工作表上所有的列
4)用Union方法合并區(qū)域后引用
若要同時(shí)處理若干行或列及區(qū)域,請(qǐng)創(chuàng)建一個(gè)對(duì)象變量并使用Union方法,將對(duì)多個(gè)區(qū)域組合起來(lái).下例將活動(dòng)工作簿中第一張工作表上的第一行,第三行和第五行的字體設(shè)置為加粗.SubSeveralRows()Worksheets("Sheet1").ActivateDimmyUnionAsRangeSetmyUnion=Union(Rows(1),Rows(3),Rows(5))myUnion.Font.Bold=TrueEndSub
5)用括號(hào)[]引用單元格和區(qū)域
可用方括號(hào)將A1引用樣式或命名區(qū)域括起來(lái),作為Range屬性的快捷方式.這樣就不必鍵入單詞"Range"或使用引號(hào),如下例所示:Worksheets("Sheet1").[A1:B5].ClearContents[MyRange].Value=30
6)用Offset相對(duì)其他單元格來(lái)引用單元格
處理相對(duì)于另一個(gè)單元格的某一單元格的常用方法是使用Offset屬性.下例中,將位于活動(dòng)工作表上活動(dòng)單元格下一行和右邊三列的單元格的內(nèi)容設(shè)置為雙下劃線格式.如:ActiveCell.Offset(1,3).Font.Underline=xlDouble
7)用Selection屬性引用活動(dòng)區(qū)域
Select方法激活工作表和工作表上的對(duì)象;而Selection屬性返回代表活動(dòng)工作簿中活動(dòng)工作表上的當(dāng)前選定區(qū)域的對(duì)象.在成功使用Selection屬性之前,必須先激活工作簿,并激活或選定工作表,然后用Select方法選定單元格區(qū)域(或其他對(duì)象).
宏錄制器經(jīng)常創(chuàng)建使用Select方法和Selection屬性的宏.下0Sub過(guò)程是用宏錄制器創(chuàng)建的,該過(guò)程演示了Select方法和Selection屬性在一起使用的方法.
SubMacro1()
Sheets("Sheet1").Select
Range("A1").Select
ActiveCell.FormulaR1C1="Name"
Range("B1").Select
ActiveCell.FormulaR1C1="Address"
Range("A1:B1").Select
Selection.Font.Bold=True
EndSub
8)區(qū)域中循環(huán)引用單元格方法
使用VBA時(shí),經(jīng)常需要對(duì)某一單元格區(qū)域內(nèi)的每個(gè)單元格運(yùn)行同一段語(yǔ)句.為達(dá)到這一目的,可組
合循環(huán)語(yǔ)句和一個(gè)或多個(gè)方法來(lái)標(biāo)識(shí)每個(gè)單元格,一次針對(duì)一個(gè)單元格,并執(zhí)行該操作.
8.1)For...Next循環(huán)語(yǔ)句與Cells屬性配合使用
使用Cells屬性時(shí),可用循環(huán)計(jì)數(shù)器(或其他變量或表達(dá)式)來(lái)替代單元格索引編號(hào).下例中變量
counter代替行號(hào).此過(guò)程在單元格區(qū)域C1:C20中循環(huán),將絕對(duì)值小于0.01的單元都置為0.
SubRoundToZero1()ForCounter=1To20
IfAbs(Cells(counter,3).value)<0.01ThenCells(counter,3)=0
NextEndSub
8.2)ForEach...Next循環(huán)語(yǔ)句和Range屬性配合使用示例如下:SubRoundToZero2()ForEachcInWorksheets("Sheet1").Range("A1:D10").CellsIfAbs(c.Value)<0.01Thenc.Value=0NextEndSub
二,如何處理單元格和區(qū)域1處理三維區(qū)域如果要處理若干工作表上相同位置的單元格區(qū)域,可用Array函數(shù)選定兩張或多張工作表.下例設(shè)置三維單元格區(qū)域的邊框格式.SubFormatSheets()Sheets(Array("Sheet2","Sheet3","Sheet5")).SelectRange("A1:H1").SelectSelection.Borders(xlBottom).LineStyle=xlDoubleEndSub
2處理活動(dòng)單元格
ActiveCell屬性返回代表活動(dòng)單元格的Range對(duì)象.可對(duì)活動(dòng)單元格應(yīng)用Range對(duì)象的任何屬性和方法,如下例所示.
SubSetValue()
Worksheets("Sheet1").Activate
ActiveCell.Value=35EndSub注意只有活動(dòng)單元格所在的工作表處于活動(dòng)狀態(tài)時(shí),才能處理該活動(dòng)單元格.
3選擇活動(dòng)單元格周圍的單元格
CurrentRegion屬性返回由空白行和空白列所包圍的單元格區(qū)域.下例中,選定區(qū)域擴(kuò)充到與活動(dòng)單元格
相鄰的包含數(shù)據(jù)的單元格中,然后用"貨幣"樣式設(shè)置該區(qū)域的格式.SubRegion()Worksheets("Sheet1").ActivateActiveCell.CurrentRegion.SelectSelection.Style="Currency"EndSub
4單元格和區(qū)域賦值
用賦值號(hào)=賦值,如給A1賦值為10:Range(A1).Value=10(由于Value是默認(rèn)屬性,所以可以直接省略)區(qū)域賦值,如Range(A1:D5)=10
5單元格和區(qū)域賦予公式進(jìn)行計(jì)算
把公式字符串賦予給單元格或區(qū)域的Formula屬性,如下例:注意一點(diǎn)是公式字符串中要有開(kāi)頭的等號(hào)=.SubEnterFormula()Worksheets("Sheet1").Range("D6").Formula="=SUM(D2:D5)"EndSub
三,單元格和區(qū)域的定位
1)使用單元格的End屬性來(lái)定位邊界如:數(shù)據(jù)區(qū)域的最下行的確定:Sheets(1).cells(65536,1).End(xlup).Row數(shù)據(jù)區(qū)域的最右列的確定:Sheets(1).cells(1,1).End(xltoright).Column選定包含單元格"B4"的區(qū)域在B列中頂端的單元格:Range("B4").End(xlUp).Select選定包含單元格"B4"的區(qū)域在第4行中最右端的單元格:Range("B4").End(xlToRight).Select將選定區(qū)域從單元格"B4"延伸至第四行最后一個(gè)包含數(shù)據(jù)的單元格:Range("B4",Range("B4").End(xlToRight)).Select
2)善用已用區(qū)域UsedRange對(duì)象來(lái)獲得區(qū)域范圍如:需要獲得先前表格已用區(qū)域的最右下角單元格位置SubLoc()
temp=[a1]:[a1]=1'使用A1單元格,保證已用區(qū)域從A1開(kāi)始irow=ActiveSheet.UsedRange.Rows.Count'右下單元格行icol=ActiveSheet.UsedRange.Columns.Count'右下單元格列[a1]=temp'還原A1的值Cells(irow,icol).Select'選中右下角單元格
Endsub
四,單元格和區(qū)域的保護(hù)與鎖定
1)工作表選擇改變事件過(guò)程中保護(hù),如下:不許可用戶選擇及改動(dòng)保護(hù)單元格區(qū)域,示例中保護(hù)B1:B10PrivateSubWorksheet_SelectionChange(ByValTargetAsRange)DimrngAsRangeSetrng=Range("b1:b10")IfIntersect(Target,rng)IsNothingThenExitSub'選擇單元不在保護(hù)區(qū)內(nèi)就退出MsgBox"不可以選取單元格!"&Target.Address'選擇單元在保護(hù)區(qū)內(nèi)就改變選擇ActiveSheet.Range("a1").SelectEndSub
2)通過(guò)行列的隱藏來(lái)保護(hù)
本示例隱藏工作表"Sheet1"的第5行和C列SubSetHide()
WithWorksheets("Sheet1")'使用With…EndWith語(yǔ)句塊,提高效率.Unprotect'撤銷保護(hù),如果原先未保護(hù)則不需該句.Rows(5).Hidden=True'隱藏第5行.Columns("C").Hidden=True'隱藏C列.Protect'啟動(dòng)保護(hù),如果原先未保護(hù)則不需該句Endwith
EndSub
3)通過(guò)鎖定區(qū)域來(lái)保護(hù),可限定未鎖定區(qū)域內(nèi)輸入SubSetLock()ActiveSheet.UnprotectCells.Locked=True'鎖定全表
Range("a1:b10").Locked=false'解鎖區(qū)域A1:B10,限定該區(qū)域?yàn)榭奢斎雲(yún)^(qū)
ActiveSheet.ProtectEndSub
第四節(jié)圖表的操作
Excel讓人喜愛(ài)的原因就是它有強(qiáng)大得計(jì)算功能和數(shù)圖相關(guān)聯(lián)的數(shù)據(jù)作圖功能.本節(jié)就介紹下VBA代碼作圖方面內(nèi)容.通過(guò)代碼作重復(fù)的圖,可以大大提高效率,比如我的作品GeoPlot(www.geoplot.net).Excel圖的代碼繪制,最好是學(xué)習(xí)錄制的宏來(lái)分析VBA代碼的作用,最后優(yōu)化而成通用程序塊.
一.新建及類型
1)設(shè)置一個(gè)對(duì)象等于新增圖表對(duì)象就建立好了一個(gè)新圖
1.1)新圖賦值給對(duì)象變量
Setmychart=Sheets(1).ChartObjects.Add(ChrLeft,ChrTop,ChrWidth,ChrHeight)
說(shuō)明:增加一個(gè)嵌入圖表,其左上角的位置坐標(biāo)為(ChrLeft,ChrTop),長(zhǎng)寬為ChrWidth×ChrHeight,單位為磅.1磅為1/72英寸和0.035厘米,字體大小通常用磅數(shù)量度.1.2)直接增加一個(gè)圖對(duì)象到圖對(duì)象集Charts
Charts.Add
2)圖的類型可以更改Chart對(duì)象的屬性ChartType來(lái)實(shí)現(xiàn)
2.1)圖表對(duì)象變量的使用
mychart.Chart.ChartType=xlXYScatterLines'散點(diǎn)圖折線類型
2.2)未設(shè)置對(duì)象變量的圖
它的類型更改需要在增加它后,連續(xù)操作讓它處于激活狀態(tài)(ActiveChart),這樣可通過(guò)當(dāng)前活動(dòng)
圖ActiveChart來(lái)使用.如:ActiveChart.ChartType=xlPie'餅圖
3)圖表有兩大類,一是嵌入工作表上的圖,另一類是作為新工作表圖,其設(shè)置如下:
3.1)嵌入工作表上的圖
ActiveChart.LocationWhere:=xlLocationAsObject,Name:="Sheet1"或如1.1生成新圖時(shí)就定義為
生成嵌入圖ChartObjects.Add
3.2)新工作表圖
ActiveChart.LocationWhere:=xlLocationAsNewSheet或用圖表對(duì)象變量屬性更改mychart.location
where:=xlLocationAsNewSheet
二.設(shè)置圖表的數(shù)據(jù)
一般是使用Excel表格上數(shù)據(jù),所以可以通過(guò)設(shè)置Chart圖表對(duì)象的數(shù)據(jù)來(lái)源屬性來(lái)獲得作圖需要的
數(shù)據(jù).如:ActiveChart.SetSourceDateSource:=Sheets("Sheet1").Range("A1:B6"),PlotBy:=xlColumns這里的PlotBy:=xlColumns表示按列繪制數(shù)據(jù);也可以按行繪制,其參數(shù)為PlotBy:=xlRows.
另外一種數(shù)據(jù)設(shè)置是對(duì)圖的橫,縱坐標(biāo)數(shù)據(jù)分別賦值.如:
mychart.chart.SeriesCollection.NewSeries'增加一個(gè)系列數(shù)據(jù)
mychart.chart.SeriesCollection(1).XValues=Array(45,100)'橫坐標(biāo)數(shù)據(jù)組或單元對(duì)象
mychart.chart.SeriesCollection(1).Values=Array(50,180)'縱坐標(biāo)數(shù)據(jù)組或單元對(duì)象
三.圖表格式設(shè)置
對(duì)于圖的格式設(shè)置,一般是錄制一個(gè)宏,再刪除不需要的語(yǔ)句,這樣是開(kāi)發(fā)者最省事的方法.在本節(jié)
的示例中有詳細(xì)的格式設(shè)置,可以更改坐標(biāo)軸的名0,大小范圍,刻度大小等.對(duì)不清楚的圖的類型名0,格式參數(shù),我們都可以通過(guò)錄制宏來(lái)了解學(xué)習(xí).時(shí)刻記住,錄制宏來(lái)分析代碼,是最好的學(xué)習(xí)方法.
四.散點(diǎn)圖增加一個(gè)系列和增加文字標(biāo)簽
1)用圖表的系列集合對(duì)象的方法NewSeries,就可以增加圖的系列,然后再對(duì)新系列進(jìn)行設(shè)置,如下:
mychart.chart.SeriesCollection.NewSeries'增加一個(gè)系列數(shù)據(jù)mychart.chart.SeriesCollection(1).XValues=Array(45,100)'橫坐標(biāo)數(shù)據(jù)組或單元對(duì)象mychart.chart.SeriesCollection(1).Values=Array(50,180)'縱坐標(biāo)數(shù)據(jù)組或單元對(duì)象
2)對(duì)于散點(diǎn)圖,我們可以增加文字標(biāo)簽于圖上,方法如下:增加一個(gè)新的系列,不過(guò)數(shù)據(jù)就一點(diǎn),不顯示數(shù)據(jù)點(diǎn),而僅顯示它的數(shù)據(jù)標(biāo)簽即可.如下示例.Withmychart.chart.SeriesCollection(2).MarkerStyle=xlNone'不顯示點(diǎn)標(biāo)記.Points(1).HasDataLabel=True'數(shù)據(jù)標(biāo)記及顯示文字標(biāo)簽.Points(1).DataLabel.Text="標(biāo)簽文字"'標(biāo)簽文字.DataLabels.Position=xlLabelPositionCenter'位置風(fēng)格,居中
EndWith
五.實(shí)例
1)生成一個(gè)散點(diǎn)嵌入圖于工作表窗口中心,繪制一條直線(45,50)―(100,180),在第一點(diǎn)顯示文字標(biāo)簽
"Test",此外還加一個(gè)點(diǎn)(80,100).
代碼和詳細(xì)注釋如下:
SubDrawChart()
'*******************************圖表對(duì)象的定義和生成***********************************'定義對(duì)象變量,以便設(shè)置它為圖表(chart)對(duì)象及圖位置大小變量
DimmychartAsObject,mysheetAsObject
DimChrLeftAsLong,ChrTopAsLong,ChrWidthAsLong,ChrHeightAsLong
OnErrorResumeNext
Application.ScreenUpdating=False
ChrWidth=250:ChrHeight=250
ChrLeft=Abs(Windows(ThisWorkbook.Name).Width-ChrWidth)/2
ChrTop=Abs(Windows(ThisWorkbook.Name).Height-ChrHeight)/2'計(jì)算圖表在窗口中心坐標(biāo)
Setmychart=Sheets(1).ChartObjects.Add(ChrLeft,ChrTop,ChrWidth,ChrHeight)
Withmychart.Chart
.ChartType=xlXYScatterLines'散點(diǎn)折線圖類型
.SeriesCollection.NewSeries'增加一次投點(diǎn),畫(huà)條直線
.SeriesCollection(1).XValues=Array(45,100)
.SeriesCollection(1).Values=Array(50,180)
.SeriesCollection(1).Points(1).HasDataLabel=True'點(diǎn)1是否顯示數(shù)據(jù)標(biāo)簽
.SeriesCollection(1).Points(1).DataLabel.Text="Test"'點(diǎn)1的標(biāo)簽文字
.SeriesCollection.NewSeries'增加一次投點(diǎn),就投個(gè)點(diǎn)(80,100)
.SeriesCollection(2).XValues=80
.SeriesCollection(2).Values=100
EndWith'*******************************圖表風(fēng)格定義***********************************Withmychart.Chart
.ChartArea.Font.Size=10'圖表字符的大小
.HasLegend=False'圖是否顯示圖例
.Axes(xlCategory,xlPrimary).HasTitle=True'X軸是否有坐標(biāo)名
.Axes(xlCategory,xlPrimary).AxisTitle.Characters.Text="X"
.Axes(xlValue,xlPrimary).HasTitle=True
.Axes(xlValue,xlPrimary).AxisTitle.Characters.Text="Y"'Y軸坐標(biāo)名"Y"
.PlotArea.Interior.ColorIndex=xlNone'定義投圖區(qū)為透明
EndWith
Withmychart.Chart.Axes(xlCategory)
.MinimumScale=0'定義X坐標(biāo)軸最小值
.MaximumScale=200'定義X坐標(biāo)軸最大值
.MinorUnit=10'定義次刻度線單位
.MajorUnit=50'定義主刻度線單位
.CrossesAt=0'X軸交匯點(diǎn)設(shè)為0
.MajorTickMark=xlInside'主刻度線方向朝內(nèi)
.MinorTickMark=xlInside'次刻度線方向朝內(nèi)
.HasMajorGridlines=False'是否顯示主網(wǎng)格線
.HasMinorGridlines=False'是否顯示次網(wǎng)格線
EndWith
Withmychart.Chart.Axes(xlValue)
.MinimumScale=0'定義y坐標(biāo)軸最小值
.MaximumScale=200'定義y坐標(biāo)軸最大值
.MinorUnit=10'定義次刻度線單位
.MajorUnit=50'定義主刻度線單位
.CrossesAt=0'Y軸交匯點(diǎn)設(shè)為0
.MajorTickMark=xlInside'主刻度線方向朝內(nèi)
.MinorTickMark=xlInside'次刻度線方向朝內(nèi)
.HasMajorGridlines=False'是否顯示主網(wǎng)格線
.HasMinorGridlines=False'是否顯示次網(wǎng)格線
EndWith
Setmychart=Nothing'刪除圖表對(duì)象變量
Application.ScreenUpdating=True'恢復(fù)屏幕刷新EndSub
2)根據(jù)工作表一的A1:B10數(shù)據(jù)生成柱狀圖嵌入表一,位置為數(shù)據(jù)右下角位置.
SubPic2()OnErrorResumeNext
Application.ScreenUpdating=False
Charts.Add
ActiveChart.ChartType=xlColumnClustered
ActiveChart.SetSourceDataSource:=Sheets("Sheet1").Range("A1:B10"),PlotBy:=xlColumns
ActiveChart.LocationWhere:=xlLocationAsObject,Name:="Sheet1"
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Left=ActiveSheet.Cells(11,3).Left'定位于單元格
(10,2)右下角
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Top=ActiveSheet.Cells(11,3).Top
Application.ScreenUpdating=TrueEndSub
聯(lián)系客服
