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

e800，国内最具活力的IT门户网站。 HYPERLINK  【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 ListObject Set lo = ListObjects.Add( _ SourceType:=xlSrcRange, _ Source:=Range(A1:F13), _ XlListObjectHasHeaders:=xlYes) lo.Name = SampleData lo.ShowTotals = True Retrieve a reference to the second column in the ListObject: Dim lc As ListColumn Set lc = lo.ListColumns(2) Retrieve a reference to the ListColumns data, excluding headers and footers. Dim rng As Range Set rng = lc.DataBodyRange Do some things with the data body range: rng.Borders.Color = vbRed rng.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 Long totalValue = 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 Then lc.Total.Value = totalValue End If End Sub Sub 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 Integer Dim j As Integer For i = 1 To 12 Cells(i + 1, 1).Value = MonthName(i, True) For j = 2 To 5 Cells(i + 1, j) = Round(Rnd * 100) Next j Next i Range(F2, F13).Formula