使用ListColumnork处理DataBodyRange和Total属性.docx

e800，国内最具活力的IT门户网站。【e800编译】此示例演示在Microsoft Excel 2010中如何使用ListColum对象的DataBodyRange和Total属性。此代码段是Office 2010的101项VBA代码示例中的一部分。与其它示例一样，这些将可以直接写入您的代码中。每块示例代码包含约5至50行的代码，分别演示了一个独特的功能或功能集，在VBA或VB以及C中（在Visual Studio 2010中创建）。每个示例之中都会包含代码以及相应注释，这样您就可以直接运行获取预期的结果，或者是根据代码注释提示来调整环境，运行示例代码。Microsoft Office 2010提供了你所需要的工具来创建功能强大的应用程序。Microsoft Visual Basic Application（VBA）代码示例可以帮助你创建自己的应用程序，以执行特定功能或者以此为出发点实现更为复杂的功能。实例代码在Excel 2010中新建一个工作簿，将下列代码复制到Sheet1类模块，并将光标放在ListColumnDemo，然后按F8执行调试，然后按住Shift+ F8单步执行代码。并列放置VBA和Excel窗口，这样你就可以边运行边查看结果。Sub ListColumnDemo() Step over this procedure. Its not terribly interesting.FillRandomData Set up the list object.Dim lo As ListObjectSet lo = ListObjects.Add( _SourceType:=xlSrcRange, _Source:=Range(A1:F13), _XlListObjectHasHeaders:=xlYes)lo.Name = SampleDatalo.ShowTotals = True Retrieve a reference to the second column in the ListObject:Dim lc As ListColumnSet lc = lo.ListColumns(2) Retrieve a reference to the ListColumns data, excluding headers and footers.Dim rng As RangeSet rng = lc.DataBodyRange Do some things with the data body range:rng.Borders.Color = vbRedrng.FormatConditions.AddDatabar Retrieve the total for the column and display it in the totals row. Obviously, there are other ways to do this:Dim totalValue As LongtotalValue = WorksheetFunction.Sum(rng) You should verify that the Total property isnt Nothing-it will be, if the list object isnt displaying its totals row. In this case, the totals row is definitely visible, so this check is extraneous.If Not lc.Total Is Nothing Thenlc.Total.Value = totalValueEnd IfEnd SubSub FillRandomData() No need to stop through this procedure.Range(A1, F1).Value = Array(Month, North, South, East, West, Total) Fill in twelve rows with random data.Dim i As IntegerDim j As IntegerFor i = 1 To 12Cells(i + 1, 1).Value = MonthName(i, True)For j