Excel VBA(宏)精简(三)

Excel VBA（宏）精简（三）工作表操作在文件操作中已经讲了Excel工作簿文件的打开,新建,保存和关闭了,这里再讲文件下的表格操作.1)新建与删除新建工作表,图表或宏表.新建的工作表将成为活动工作表.Sheets.Add(Before,After,Count,Type)说明: BeforeVariant类型,可选.指定工作表对象,新建的工作表将置于此工作表之前.AfterVariant类型,可选.指定工作表对象,新建的工作表将置于此工作表之后. CountVariant类型,可选.要新建的工作表的数目.默认值为1. TypeVariant类型,可选.指定工作表类型.可为以下XlSheetType常量之一:xlWorksheet,xlChart,xlExcel4MacroSheet或xlExcel4IntlMacroSheet.默认值为xlWorksheet.如果Before和After两者均省略,则新建的工作表将插入到活动工作表之前.示例:Sheets.Addafter:=Sheets(Sheets.Count)该语句可以在最后一个工作表后增加一个新表删除工作表Sheets("工作表名").Delete示例:在工作表末新建一个和删除倒数第二个表. SubMySht()Application.DisplayAlerts=False'关闭删除确认对话框Sheets.Addafter:=Sheets(Sheets.Count)Sheets(Sheets.Count-1).DeleteApplication.DisplayAlerts=True'开启确认对话框EndSub2)隐藏与显示使用工作表的visible属性来设置工作表是否隐藏或显示隐藏Sheets("sheet2").Visible=False显示Sheets("sheet2").Visible=True 3)保护与撤销保护工作表使其不至被修改.Sheets("工作表名").Protect(Password)撤销工作表保护使其被修改.Sheets("工作表名").Unprotect(Password)PasswordVariant类型,可选.为一个字符串,该字符串为工作表或工作簿指定区分大小写的密码.如果省略本参数,不用密码就可以取消对该工作表或工作簿的保护.否则,必须指定密码,通过密码来取消对该工作表或工作簿的保护.示例:Sheets("sheet1").Protect"123456"Sheets("sheet1").Unprotect"1 23456"如果要对工作进行详细保护设置,可参考ExcelVBA参考,这里仅讲简单的参数设置.单元格和区域操作Excel数据的计算归根到底还是对表的单元进行的,所以单元格和区域操作非常重要的,这一节就详细介绍这方面的内容.一,如何引用单元格和区域1)用Range属性引用单元格和单元格区域使用Range属性来引用A1引用样式中的单元格或单元格区域及进行属性更改和赋值.如例句:Sheets("Sheet1").Range("A1:D5").Font.Bold=TrueSheets("Sheet1").Range("A1:D5").Value=10000Range对象既可表单个单元格,也可表单元格区域.下面说明Range对象最常用方法.引用含义Range("A1")单元格A1Range("A1:B5")从单元格A1到单元格B5的区域Range("C5:D9,G9:H16")多块选定区域Range("A:A")A列Range("1:1")第一行Range("1:5")从第一行到第五行的区域Range("1:1,3:3,8:8")第1,3和8行Range("A:C")从A列到C列的区域Range("A:A,C:C,F:F")A,C和F列Range("单元格区域名0")命名的单元格区域2)用Cells属性引用单元格可用Cells属性通过行列编号来引用单个单元格.下例中Cells(6,1)返回Sheet1上的单元格A6,然后将Value属性设置为10.如:Worksheets("Sheet1").Cells(6,1).Value=10因为可用变量替代行列编号,所以Cells属性非常适合单元格区域中循环且速度很快.如下例所示:SubCC()DimCounterAsIntegerForCounter=1To20Worksheets("S heet1").Cells(Counter,3).Value=CounterNextCounterEndSub3)用Rows或Columns属性引用行列可用Rows属性或Columns属性来处理整行或整列.下例中,用Rows(1)返回Sheet1上的第一行,然后将单元格区域的Font对象的Bold属性设置为True.如:Worksheets("Sheet1").Rows(1).Font.Bold=True下表举例说明了使用Rows和Columns属性的一些行和列的引用.引用含义Rows(1)第一行Rows工作表上所有的行Columns(1)第一列Columns("A")第一列Columns工作表上所有的列4)用Union方法合并区域后引用若要同时处理若干行或列及区域,请创建一个对象变量并使用Union方法,将对多个区域组合起来.下例将活动工作簿中第一张工作表上的第一行,第三行和第五行的字体设置为加粗.SubSeveralRows()Worksheets("Sheet1").ActivateDimmyUnionAsRangeSetmyUnion=Union(Rows(1),Rows(3),Rows(5))myUnion.Fon t.Bold=TrueEndSub5)用括号[]引用单元格和区域可用方括号将A1引用样式或命名区域括起来,作为Range属性的快捷方式.这样就不必键入单词"Range"或使用引号,如下例所示:Worksheets("Sheet1").[A1:B5].ClearContents[MyRange].Value= 306)用Offset相对其他单元格来引用单元格处理相对于另一个单元格的某一单元格的常用方法是使用Offset属性.下例中,将位于活动工作表上活动单元格下一行和右边三列的单元格的内容设置为双下划线格式.如:ActiveCell.Offset(1,3).Font.Underline=xlDouble7)用Selection属性引用活动区域Select方法激活工作表和工作表上的对象;而Selection属性返回代表活动工作簿中活动工作表上的当前选定区域的对象.在成功使用Selection属性之前,必须先激活工作簿,并激活或选定工作表,然后用Select方法选定单元格区域(或其他对象).宏录制器经常创建使用Select方法和Selection属性的宏.下0Sub过程是用宏录制器创建的,该过程演示了Select方法和Selection属性在一起使用的方法.SubMacro1()Sheets("Sheet1").SelectRange("A1").SelectActiveCell.FormulaR1C1="Name"Range("B1").SelectActiveCell.FormulaR1C1="Address"Range("A1:B1").SelectSelection.Font.Bold=TrueEndSub8)区域中循环引用单元格方法使用VBA时,经常需要对某一单元格区域内的每个单元格运行同一段语句.为达到这一目的,可组合循环语句和一个或多个方法来标识每个单元格,一次针对一个单元格,并执行该操作.8.1)For...Next循环语句与Cells属性配合使用使用Cells属性时,可用循环计数器(或其他变量或表达式)来替代单元格索引编号.下例中变量counter代替行号.此过程在单元格区域C1:C20中循环,将绝对值小于0.01的单元都置为0.SubRoundToZero1()ForCounter=1To20IfAbs(Cells(counter,3).value)<0.01ThenCells(counter,3)=0 NextEndSub8.2)ForEach...Next循环语句和Range属性配合使用示例如下:SubRoundToZero2()ForEachcInWorksheets("Sheet1").Range("A 1:D10").CellsIfAbs(c.Value)<0.01Thenc.Value=0NextEndSub二,如何处理单元格和区域1处理三维区域如果要处理若干工作表上相同位置的单元格区域,可用Array函数选定两张或多张工作表.下例设置三维单元格区域的边框格式.SubFormatSheets()Sheets(Array("Sheet2","Sheet3","Sheet5")). SelectRange("A1:H1").SelectSelection.Borders(xlBottom).LineStyle =xlDoubleEndSub2处理活动单元格ActiveCell属性返回代表活动单元格的Range对象.可对活动单元格应用Range对象的任何属性和方法,如下例所示.SubSetValue()Worksheets("Sheet1").ActivateActiveCell.Value=35EndSub注意只有活动单元格所在的工作表处于活动状态时,才能处理该活动单元格.3选择活动单元格周围的单元格CurrentRegion属性返回由空白行和空白列所包围的单元格区域.下例中,选定区域扩充到与活动单元格相邻的包含数据的单元格中,然后用"货币"样式设置该区域的格式.SubRegion()Worksheets("Sheet1").ActivateActiveCell.CurrentRe gion.SelectSelection.Style="Currency"EndSub4单元格和区域赋值用赋值号=赋值,如给A1赋值为10:Range(A1).Value=10(由于Value是默认属性,所以可以直接省略)区域赋值,如Range(A1:D5)=105单元格和区域赋予公式进行计算把公式字符串赋予给单元格或区域的Formula属性,如下例:注意一点是公式字符串中要有开头的等号=.SubEnterFormula()Worksheets("Sheet1").Range("D6").Formula= "=SUM(D2:D5)"EndSub三,单元格和区域的定位1)使用单元格的End属性来定位边界如:数据区域的最下行的确定:Sheets(1).cells(65536,1).End(xlup).Row数据区域的最右列的确定:Sheets(1).cells(1,1).End(xltoright).Column选定包含单元格"B4"的区域在B列中顶端的单元格:Range("B4").End(xlUp).Select选定包含单元格"B4"的区域在第4行中最右端的单元格:Range("B4").End(xlToRight).Select将选定区域从单元格"B4"延伸至第四行最后一个包含数据的单元格:Range("B4",Range("B4").End(xlToRight)).Select2)善用已用区域UsedRange对象来获得区域范围如:需要获得先前表格已用区域的最右下角单元格位置SubLoc()temp=[a1]:[a1]=1'使用A1单元格,保证已用区域从A1开始irow=edRange.Rows.Count'右下单元格行icol=edRange.Columns.Count'右下单元格列[a1]=temp'还原A1的值Cells(irow,icol).Select'选中右下角单元格Endsub四,单元格和区域的保护与锁定1)工作表选择改变事件过程中保护,如下:不许可用户选择及改动保护单元格区域,示例中保护B1:B10PrivateSubWorksheet_SelectionChange(ByValTargetAsRang e)DimrngAsRangeSetrng=Range("b1:b10")IfIntersect(Target,rng)I sNothingThenExitSub'选择单元不在保护区内就退出MsgBox"不可以选取单元格!"&Target.Address'选择单元在保护区内就改变选择ActiveSheet.Range("a1").SelectEndSub2)通过行列的隐藏来保护本示例隐藏工作表"Sheet1"的第5行和C列SubSetHide() WithWorksheets("Sheet1")'使用With…EndWith语句块,提高效率.Unprotect'撤销保护,如果原先未保护则不需该句.Rows(5).Hidden=True'隐藏第5行.Columns("C").Hidden=True'隐藏C列.Protect'启动保护,如果原先未保护则不需该句EndwithEndSub3)通过锁定区域来保护,可限定未锁定区域内输入SubSetLock()ActiveSheet.UnprotectCells.Locked=True'锁定全表Range("a1:b10").Locked=false'解锁区域A1:B10,限定该区域为可输入区ActiveSheet.ProtectEndSub第四节图表的操作Excel让人喜爱的原因就是它有强大得计算功能和数图相关联的数据作图功能.本节就介绍下VBA代码作图方面内容.通过代码作重复的图,可以大大提高效率,比如我的作品GeoPlot().Excel图的代码绘制,最好是学习录制的宏来分析VBA代码的作用,最后优化而成通用程序块.一.新建及类型1)设置一个对象等于新增图表对象就建立好了一个新图1.1)新图赋值给对象变量Setmychart=Sheets(1).ChartObjects.Add(ChrLeft,ChrTop,ChrWidth ,ChrHeight)说明:增加一个嵌入图表,其左上角的位置坐标为(ChrLeft,ChrTop),长宽为ChrWidth×ChrHeight,单位为磅.1磅为1/72英寸和0.035厘米,字体大小通常用磅数量度.1.2)直接增加一个图对象到图对象集ChartsCharts.Add2)图的类型可以更改Chart对象的属性ChartType来实现2.1)图表对象变量的使用mychart.Chart.ChartType=xlXYScatterLines'散点图折线类型2.2)未设置对象变量的图它的类型更改需要在增加它后,连续操作让它处于激活状态(ActiveChart),这样可通过当前活动图ActiveChart来使用.如:ActiveChart.ChartType=xlPie'饼图3)图表有两大类,一是嵌入工作表上的图,另一类是作为新工作表图,其设置如下:3.1)嵌入工作表上的图ActiveChart.LocationWhere:=xlLocationAsObject,Name:="Sheet1"或如1.1生成新图时就定义为生成嵌入图ChartObjects.Add3.2)新工作表图ActiveChart.LocationWhere:=xlLocationAsNewSheet或用图表对象变量属性更改mychart.locationwhere:=xlLocationAsNewSheet二.设置图表的数据一般是使用Excel表格上数据,所以可以通过设置Chart图表对象的数据来源属性来获得作图需要的数据.如:ActiveChart.SetSourceDateSource:=Sheets("Sheet1").Range("A 1:B6"),PlotBy:=xlColumns这里的PlotBy:=xlColumns表示按列绘制数据;也可以按行绘制,其参数为PlotBy:=xlRows.另外一种数据设置是对图的横,纵坐标数据分别赋值.如:mychart.chart.SeriesCollection.NewSeries'增加一个系列数据mychart.chart.SeriesCollection(1).XValues=Array(45,100)'横坐标数据组或单元对象mychart.chart.SeriesCollection(1).Values=Array(50,180)'纵坐标数据组或单元对象三.图表格式设置对于图的格式设置,一般是录制一个宏,再删除不需要的语句,这样是开发者最省事的方法.在本节的示例中有详细的格式设置,可以更改坐标轴的名0,大小范围,刻度大小等.对不清楚的图的类型名0,格式参数,我们都可以通过录制宏来了解学习.时刻记住,录制宏来分析代码,是最好的学习方法.四.散点图增加一个系列和增加文字标签1)用图表的系列集合对象的方法NewSeries,就可以增加图的系列,然后再对新系列进行设置,如下:mychart.chart.SeriesCollection.NewSeries'增加一个系列数据mychart.chart.SeriesCollection(1).XValues=Array(45,100)'横坐标数据组或单元对象mychart.chart.SeriesCollection(1).Values=Array(50,180)'纵坐标数据组或单元对象2)对于散点图,我们可以增加文字标签于图上,方法如下:增加一个新的系列,不过数据就一点,不显示数据点,而仅显示它的数据标签即可.如下示例.Withmychart.chart.SeriesCollection(2).MarkerStyle=xlNone'不显示点标记.Points(1).HasDataLabel=True'数据标记及显示文字标签.Points(1).DataLabel.Text="标签文字"'标签文字.DataLabels.Position=xlLabelPositionCenter'位置风格,居中EndWith五.实例1)生成一个散点嵌入图于工作表窗口中心,绘制一条直线(45,50)―(100,180),在第一点显示文字标签"Test",此外还加一个点(80,100).代码和详细注释如下:SubDrawChart()'*******************************图表对象的定义和生成***********************************'定义对象变量,以便设置它为图表(chart)对象及图位置大小变量DimmychartAsObject,mysheetAsObjectDimChrLeftAsLong,ChrTopAsLong,ChrWidthAsLong,ChrHeightAsLon gOnErrorResumeNextApplication.ScreenUpdating=FalseChrWidth=250:ChrHeight=250ChrLeft=Abs(Windows().Width-ChrWidth)/2 ChrTop=Abs(Windows().Height-ChrHeight)/2'计算图表在窗口中心坐标Setmychart=Sheets(1).ChartObjects.Add(ChrLeft,ChrTop,ChrWidth ,ChrHeight)Withmychart.Chart.ChartType=xlXYScatterLines'散点折线图类型.SeriesCollection.NewSeries'增加一次投点,画条直线.SeriesCollection(1).XValues=Array(45,100).SeriesCollection(1).Values=Array(50,180).SeriesCollection(1).Points(1).HasDataLabel=True'点1是否显示数据标签.SeriesCollection(1).Points(1).DataLabel.Text="Test"'点1的标签文字.SeriesCollection.NewSeries'增加一次投点,就投个点(80,100).SeriesCollection(2).XValues=80.SeriesCollection(2).Values=100EndWith'*******************************图表风格定义***********************************Withmychart.Chart.ChartArea.Font.Size=10'图表字符的大小.HasLegend=False'图是否显示图例.Axes(xlCategory,xlPrimary).HasTitle=True'X轴是否有坐标名.Axes(xlCategory,xlPrimary).AxisTitle.Characters.Text="X".Axes(xlValue,xlPrimary).HasTitle=True.Axes(xlValue,xlPrimary).AxisTitle.Characters.Text="Y"'Y轴坐标名"Y".PlotArea.Interior.ColorIndex=xlNone'定义投图区为透明EndWithWithmychart.Chart.Axes(xlCategory).MinimumScale=0'定义X坐标轴最小值.MaximumScale=200'定义X坐标轴最大值.MinorUnit=10'定义次刻度线单位.MajorUnit=50'定义主刻度线单位.CrossesAt=0'X轴交汇点设为0.MajorTickMark=xlInside'主刻度线方向朝内.MinorTickMark=xlInside'次刻度线方向朝内.HasMajorGridlines=False'是否显示主网格线.HasMinorGridlines=False'是否显示次网格线EndWithWithmychart.Chart.Axes(xlValue).MinimumScale=0'定义y坐标轴最小值.MaximumScale=200'定义y坐标轴最大值.MinorUnit=10'定义次刻度线单位.MajorUnit=50'定义主刻度线单位.CrossesAt=0'Y轴交汇点设为0.MajorTickMark=xlInside'主刻度线方向朝内.MinorTickMark=xlInside'次刻度线方向朝内.HasMajorGridlines=False'是否显示主网格线.HasMinorGridlines=False'是否显示次网格线EndWithSetmychart=Nothing'删除图表对象变量Application.ScreenUpdating=True'恢复屏幕刷新EndSub2)根据工作表一的A1:B10数据生成柱状图嵌入表一,位置为数据右下角位置. SubPic2()OnErrorResumeNextApplication.ScreenUpdating=FalseCharts.AddActiveChart.ChartType=xlColumnClusteredActiveChart.SetSourceDataSource:=Sheets("Sheet1").Range("A1:B 10"),PlotBy:=xlColumnsActiveChart.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).TopApplication.ScreenUpdating=TrueEndSub。