Excel宏技巧IIWord格式文档下载.docx

1、Chr（10）2）Next基于VB和EXCEL的报表设计及打印在现代管理信息系统的开发中，经常涉及到数据信息的分析、加工，最终还需把统计结果形成各种形式的报表提供给领导决策参考，或进行外部交流。在VisualBasic中制作报表，通常是用数据环境设计器（DataEnvironmentDesigner）与数据报表设计器（DataReportDesigner）,或者使用第三方产品来完成。但对于大多数习惯于Excel报表的用户而言,用以上方法生成的报表在格式和功能等方面往往不能满足他们的要求。由于Excel具有自己的对象库，在VisualBasic工程中可以加以引用，通过对Excel使用OLE自动化

2、，可以创建一些外观整洁的报表，然后打印输出。这样实现了VisualBasic应用程序对Excel的控制。本文将针对一个具体实例，阐述基于VB和EXCEL的报表设计及打印过程。1）创建Excel对象Excel对象模型包括了128个不同的对象，从矩形、文本框等简单的对象到透视表，图表等复杂的对象。下面简单介绍一下其中最重要，也是用得最多的五个对象。（1）Application对象Application对象处于Excel对象层次结构的顶层，表示Excel自身的运行环境。（2）Workbook对象Workbook对象直接地处于Application对象的下层，表示一个Excel工作簿文件。（3）Wor

3、ksheet对象Worksheet对象包含于Workbook对象，表示一个Excel工作表。（4）Range对象Range对象包含于Worksheet对象，表示Excel工作表中的一个或多个单元格。（5）Cells对象Cells对象包含于Worksheet对象，表示Excel工作表中的一个单元格。如果要启动一个Excel，使用Workbook和Worksheet对象，下面的代码启动了Excel并创建了一个新的包含一个工作表的工作薄：DimzsbexcelAsExcel.ApplicationSetNewzsbexcel.VisibleTrue如要Excel不可见，可使zsbexcel.Visi

4、bleFalsezsbexcel.SheetsInNewWorkbookSetzsbworkbookzsbexcel.Workbooks.Add2）设置单元格和区域值要设置一张工作表中每个单元格的值，可以使用Worksheet对象的Range属性或Cells属性。Withzsbexcel.ActiveSheet.Cells（1,2）.value100.Cells（2,200.Cells（3,=SUM（B1:B2）.Range（A3:A9）中国人民解放军End要设置单元格或区域的字体、边框，可以利用Range对象或Cells对象的Borders属性和Font属性：Withobjexcel.Act

5、iveSheet.Range（A2:K9）.Borders边框设置.LineStylexlBorderLineStyleContinuous.WeightxlThin.ColorIndex）.Font字体设置.Size14.Bold.Italic通过对Excel单元格和区域值的各种设置的深入了解,可以创建各种复杂、美观、满足需要的、具有自己特点的报表。3）预览及打印生成所需要的工作表后，就可以对EXCEL发出预览、打印指令了。zsbexcel.ActiveSheet.PageSetup.OrientationxlPortrait设置打印方向zsbexcel.ActiveSheet.PageSe

6、tup.PaperSizexlPaperA4设置打印纸的打下zsbexcel.Caption打印预览设置预览窗口的标题zsbexcel.ActiveSheet.PrintPreview打印预览zsbexcel.ActiveSheet.PrintOut打印输出通过打印方向、打印纸张大小的设置，不断进行预览，直到满意为止，最终进行打印输出。为了在退出应用程序后EXCEL不提示用户是否保存已修改的文件,需使用如下语句：zsbexcel.DisplayAlertszsbexcel.Quit退出EXCEL如此设计的报表打印是通过EXCEL程序来后台实现的。对于使用者来说，根本看不到具体过程，只看到一张张

7、漂亮的报表轻易地被打印出来了。4）具体实例下面给出一个具体实例，它在window98、VisualBasic6.0、MicrosoftOffice97的环境下调试通过。在VB中启动一个新的StandardEXE工程，在“工程”菜单的“引用”选项下引用ExcelObjectLibrary；然后在Form中添加一个命令按钮cmdExcel；最后在窗体中输入如下代码：DimPrivatecmdExcel_Click（）Setzsbexcel.Visiblezsbexcel.SheetsInNewWorkbookSetWithzsbexcel.ActiveSheet.Range（C9）.Borders

8、.LineStyle.Weight.ColorIndexEnd.Size.Bold.ItalicEndzsbexcel.ActiveSheet.Rows.HorizontalAlignmentxlVAlignCenter水平居中zsbexcel.ActiveSheet.Rows.VerticalAlignmentxlVAlignCenter垂直居中3）.value50zsbexcel.ActiveSheet.PageSetup.OrientationxlPortraitxlLandscapezsbexcel.ActiveSheet.PageSetup.PaperSizexlPaperA4zsb

9、excel.ActiveSheet.PrintOutzsbexcel.DisplayAlertszsbexcel.QuitSetNothing提高EXCEL中VBA的效率方法1：尽量使用VBA原有的属性、方法和Worksheet函数由于Excel对象多达百多个，对象的属性、方法、事件多不胜数，对于初学者来说可能对它们不全部了解，这就产生了编程者经常编写与Excel对象的属性、方法相同功能的VBA代码段，而这些代码段的运行效率显然与Excel对象的属性、方法完成任务的速度相差甚大。例如用Range的属性CurrentRegion来返回Range对象，该对象代表当前区。（当前区指以任意空白行及空白

10、列的组合为边界的区域）。同样功能的VBA代码需数十行。因此编程前应尽可能多地了解Excel对象的属性、方法。充分利用Worksheet函数是提高程序运行速度的极度有效的方法。如求平均工资的例子：ForEachcInWorksheet（1）.Range（A1:A1000）Totalvaluec.valueAveragevalue/A1000）.Rows.Count而下面代码程序比上面例子快得多：Averagevalue=Application.WorksheetFunction.Average（Worksheets（1）.Range（A1:A1000）其它函数如Count,Counta,Coun

11、tif,Match,Lookup等等，都能代替相同功能的VBA程序代码，提高程序的运行速度。方法2：尽量减少使用对象引用，尤其在循环中每一个Excel对象的属性、方法的调用都需要通过OLE接口的一个或多个调用，这些OLE调用都是需要时间的，减少使用对象引用能加快VBA代码的运行。例如1使用With语句。Workbooks（1）.Sheets（1）.Range（A1:A1000）.Font.Name=PayA1000）.Font.FontStyle=Bold.则以下语句比上面的快A1000）.Font.NamePay.FontStyleBold2使用对象变量。如果你发现一个对象引用被多次使用，则

12、你可以将此对象用Set设置为对象变量，以减少对对象的访问。如：Workbooks（1）.Sheets（1）.Range（A1）.value100Workbooks（1）.Sheets（1）.Range（A2）.value200则以下代码比上面的要快：MySheetWorkbooks（1）.Sheets（1）MySheet.Range（A1）.valueMySheet.Range（A2）.value3在循环中要尽量减少对象的访问。k1000Sheets（Sheet1）.SelectCells（k,1）.valueCells（1,1）.valueThevalueSheets（Sheet1）.Sel

13、ectNext方法3：减少对象的激活和选择如果你的通过录制宏来学习VBA的，则你的VBA程序里一定充满了对象的激活和选择，例如Workbooks（XXX）.Activate、Sheets（XXX）.Select、Range（XXX）.Select等,但事实上大多数情况下这些操作不是必需的。Sheets（Sheet3）.SelectRange（A1）.valueRange（A2）.value可改为：Sheets（Sheet3）.Range（A1）.value.Range（A2）.value方法4：关闭屏幕更新如果你的VBA程序前面三条做得比较差，则关闭屏幕更新是提高VBA程序运行速度的最有效的方

14、法，缩短运行时间2/3左右。关闭屏幕更新的方法：Application.ScreenUpdate请不要忘记VBA程序运行结束时再将该值设回来：以上是提高VBA运行效率的比较有效的几种方法本示例重复最近用户界面命令。本示例必须放在宏的第一行。Application.Repeat下例中，变量counter代替了行号。此过程将在单元格区域C1:C20中循环，将所有绝对值小于0.01的数字都设置为0（零）。RoundToZero1（）Counter20SetcurCellWorksheets（Sheet1）.Cells（Counter,3）Abs（curCell.Value）curCell.Value

15、Counter述过程在单元格区域A1:D10RoundToZero2（）.Range（D10）.CellsAbs（c.Value）c.Value下述过程在工作表上运行时，将在活动单元格周围的区域内循环，将所有绝对值小于RoundToZero3（）ActiveCell.CurrentRegion.Cells下述过程在工作的空行写入数据输入（）x3从第3行开始DoWhileNot（IsEmpty（Cells（x,2）.Value）判断第2列的最后一行（即空行的上一行）+1在最后一行加一行即为空行Loop以下为写入数据Cells（x,Sheets（sheet1）.Cells（1,2）.Cells（2

16、,）.Cells（3,4）.Cells（4,）.SelectCells（2,1每写一行自动加入序列号sheet2每日结帐（）录入发生额Macro30记录的宏2002-12-18Application.ScreenUpdatingFalse关闭屏幕显示IfApplication.InputBox（请输入密码：1234Then此行与倒数3-5行设置密码Msg,Style,Title,X,MyString设置变量Msg！结帐后不能恢复，结帐吗？定义信息。StylevbYesNovbCriticalvbDefaultButton2定义按钮。Title本日结帐！定义标题。XMsgBox（Msg,Titl

17、e）vbYesThen用户按下“是”。CloseCall备份重算所有表Sheets（日报表ActiveSheet.UnprotectSelection.AutoFilterField:=1全部显示余额表Range（e6:g183,i6:k183）.Copyn6Selection.PasteSpecialPaste:=xlPasteValuesAndNumberFormats,Operation:_xlNone,SkipBlanks:=False,Transpose:=FalseActiveSheet.ProtectDrawingObjects:=True,Contents:Scenarios:=True,AllowSorting:AllowFiltering:=TrueA2.Copy银行帐F2=xlPasteValues,=xlNone,SkipBlanks:ActiveSheet.ProtectApplication.CutCopyModeFalseIfElse:MsgBox密码错误，即将退出！此行与第2行共同设置密码Application.ScreenUpdatingTrue打开屏幕显示月末结帐（）FalseIf以下三行为消息框MyString月末结帐！CloseCallL6:L183.CopyM6N6N6:S183A7:u3000K5.CopyK6