使用Displayat方法设置数据范围的格式.docx

e800，国内最具活力的IT门户网站。【e800编译】此示例演示在Microsoft Excel 2010中如何利用DisplayFormat类对一定范围的数据设置条件格式。此代码段是Office 2010的101项VBA代码示例中的一部分。与其它示例一样，这些将可以直接写入您的代码中。每块示例代码包含约5至50行的代码，分别演示了一个独特的功能或功能集，在VBA或VB以及C中（在Visual Studio 2010中创建）。每个示例之中都会包含代码以及相应注释，这样您就可以直接运行获取预期的结果，或者是根据代码注释提示来调整环境，运行示例代码。Microsoft Office 2010提供了你所需要的工具来创建功能强大的应用程序。Microsoft Visual Basic Application（VBA）代码示例可以帮助你创建自己的应用程序，以执行特定功能或者以此为出发点实现更为复杂的功能。实例代码在Excel 2010中新建工作簿，将代码复制到Sheet1类模块，然后按F5全速运行程序。检查在Excel中的单元格的格式，然后检查代码运行结果。 Excel 2010Sub TestDisplayFormat() From the documentation: Actions such as changing the conditional formatting or table style of a range can cause what is displayed in the current user interface to be inconsistent with the values in the corresponding properties of the Range object. Use the properties of the DisplayFormat object to return the values as they are displayed in the current user interface. In Excel 2010, in a new workbook, copy all this code into the Sheet1 class module. Place the cursor in this procedure, and then press F5 to run the procedure full speed. Examine the formatting of the cells in Excel, and then examine the results of running the procedure in the Immediate window. This example places some simple data into Sheet1, and then applies conditional formatting. Then, it compares the values of various formatting properties of the range to the formatting properties of the Range.DisplayFormat object:Range(A1).Value = -20Range(A2).Value = 10Dim rng As RangeSet rng = Range(A1:A2) Set up the conditional formatting:rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:=0With rng.FormatConditions(1)With .Font.Bold = True.Italic = True.Color = vbRedEnd WithWith .Borders.LineStyle = xlContinuous.Weight = xlThinEnd WithWith .Interior.PatternColorIndex = xlAutomatic.Color = vbYellowEnd WithEnd With Now compare formatting information for the range, and what its actually displaying (using the DisplayFormat property). Clearly, without the DisplayFormat property, you have no way to find out the formatting that appears to the user. The DisplayFormat property is an instance of the DisplayFormat class and contains many different members. This code only investigates the properties that might have been changed by conditional formatting. A1 has had conditional formatting changes because its negative. A2 has not.CompareRangeAndDisplayFormat Range(A1)CompareRangeAndDisplayFormat Range(A2)End SubPrivate Sub CompareRangeAndDisplayFormat(rng As Range) Show off the differences in properties that have been changed by conditional formatting:Debug.PrintDebug.Print rng.Address & =Debug.Print Interior color has been modified for negative values. Therefore, note that for cells that arent changed by conditional formatting, the range values and the DisplayFormat values are the same. For those that have been altered by conditional formatting, theyre different. This code doesnt check out all the properties; it just works through enough to prove that the properties of the Range object may not match those of the Range.DisplayFormat object:Debug.Print rng.Interior.Color: & rng.Interior.ColorDebug.Print rng.DisplayFormat.Interior.Color: & rng.DisplayFormat.Interior.ColorDebug.PrintDebug.Print rng.Font.Color: & rng.Font.ColorDebug.Print rng.DisplayFormat.Font.Color: & rng.DisplayFormat.Font.ColorDebug.PrintDebug.Print rng.Font.Italic: & rng.Font.ItalicDebug.Print rng.DisplayFormat.Font.Italic: & rng.DisplayFormat.Font.ItalicDebug.PrintDebug.Print rng.Font.Color: & rng.Font.ColorDebug.Print rng.DisplayFormat.Font.Color: & rn