Excel 2007与之后的范围 [英] Range.Interior.Color Different Between Excel 2007 and Later

问题描述

我看到Range.Interior.Color在某些情况下返回不同的数字，这取决于它是在Excel 2007还是Excel 2010或2013中运行。

是否预期？我很惊讶。

Range.Interior.Color是单元格的背景颜色（Fill Color）。在立即窗格中，您可以这样阅读：

？ActiveCell.Interior.Color

并设置它像这样：

ActiveCell.Interior.Color = 10921638

示例：

示例1：

（这些是相同的颜色，尽管他们的Range.Interior.Color是不同的。）

• Excel 2007：10855845


• Excel 2010/2013：10921638

示例2：

• Excel 2007：14922893


• Excel 2010/2013：14857357

示例3：

• Excel 2007：14211288


• Excel 2010/2013：14277081

任何建议？现在，我使用条件编译来设置一个数字或另一个的常量，这取决于VBA常数VBA7，它为Excel 2010或更高版本返回True，Excel 2007及更早版本为False：

 ＃如果VBA7然后
'Excel 2010或更高版本：
 Const NO_SHADING_COLOR As Long = 16777215 
 Const MAIN_HEADER_COLOR As Long = 10921638'dark灰色[in xl2007 s / b 10855845] 
 Const SUB_HEADER_COLOR As Long = 14857357'light blue [in xl2007 s / b 14922893] 
 Const SUBSUB_HEADER_COLOR As Long = 14277081'medium gray [in xl2007 s / b 14211288 ] 
 #Else 
'Excel 2007或更早版本：
 Const NO_SHADING_COLOR As Long = 16777215 
 Const MAIN_HEADER_COLOR As Long = 10855845'dark gray 
 Const SUB_HEADER_COLOR As Long = 14922893 'light blue 
 Const SUBSUB_HEADER_COLOR As Long = 14211288'中等灰色
 #End如果
  

更新：

是的，我明白RGB可以使用Range.Interior.Color的内嵌，并且可以从Range.Interior.Color中提取RGB数字。但是我们可以一整天都能做到这一点，依照Excel版本，还可以获得不同的RGB数字，这样可以有效地回到原来的问题。

对于任何给定的范围.Interior.Color number，是的，相当于一定数量的RGB数字。但关键是依赖于Excel版本，在某些情况下，您可以在不改变单元格颜色的同一个单元格中获得不同的Range.Interior.Color数字。如果您将该数字提取为RGB数字，则根据Excel版本，您只需具有不同的RGB数字集，这不同于根据版本不同的Range.Interior.Color数字。

这些单元格颜色由用户设置，使用Excel的用户界面设置单元格的填充颜色。此项目中的VBA代码未设置颜色。 VBA代码只获取颜色，并根据正在处理的单元格中的颜色进行分支。

除了Application.Version之类的明显异常之外，它不正常对象属性从版本到版本随机更改。版本之间对象属性的一致性是允许VBA代码在不同版本之间工作的重要部分。如果不是这样，我们必须条件编译几乎所有的Excel VBA代码。

解决方案

更新： p>

我无法在新的工作簿中重现此问题，因此可能是工作簿损坏。通常Range.Interior.Color在版本之间是可靠的，代表所有颜色。

FWIW，此工作簿是由另一个人发送给我的，而该人在Excel中Macintosh当我在Windows上，所以在一个平台上创建工作簿，然后在另一个平台上使用它可能是腐败的一个因素，如果有的话（即使这应该正常工作）。

I'm seeing that Range.Interior.Color returns different numbers for the same color in some cases, depending on whether it is running in Excel 2007, or Excel 2010 or 2013.

Is that expected?? I'm surprised.

Range.Interior.Color is the background color ("Fill Color") of the cell. In the Immediate pane, you can read it like this:

?ActiveCell.Interior.Color

And set it like this:

ActiveCell.Interior.Color = 10921638

Examples:

Example 1:

(these are the same color though their Range.Interior.Color are different.)

• Excel 2007: 10855845
• Excel 2010/2013: 10921638

Example 2:

• Excel 2007: 14922893
• Excel 2010/2013: 14857357

Example 3:

• Excel 2007: 14211288
• Excel 2010/2013: 14277081

Any suggestions? For now, I'm using conditional compliling to set constants for one number or the other depending on VBA constant VBA7, which returns True for Excel 2010 or later and False for Excel 2007 and earlier:

#If VBA7 Then
    'Excel 2010 or later:
    Const NO_SHADING_COLOR As Long = 16777215
    Const MAIN_HEADER_COLOR As Long = 10921638 'dark gray [in xl2007 s/b 10855845]
    Const SUB_HEADER_COLOR As Long = 14857357 'light blue [in xl2007 s/b 14922893]
    Const SUBSUB_HEADER_COLOR As Long = 14277081 'medium gray [in xl2007 s/b 14211288]
#Else
    'Excel 2007 or earlier:
    Const NO_SHADING_COLOR As Long = 16777215
    Const MAIN_HEADER_COLOR As Long = 10855845 'dark gray
    Const SUB_HEADER_COLOR As Long = 14922893 'light blue
    Const SUBSUB_HEADER_COLOR As Long = 14211288 'medium gray
#End If

UPDATE:

Yes, I understand that RGB can be used intead of Range.Interior.Color, and that RGB numbers can be extracted from Range.Interior.Color. But we can do that all day long and still get different sets of RGB numbers depending on Excel version, which effectively takes us back to the original problem.

For any given Range.Interior.Color number, yes it is equivalent to a certain set of RGB numbers. But the point is that depending on Excel version, in some cases you get different Range.Interior.Color numbers for the very same cell without changing the cell's color. If you extract that number to RGB numbers, then you just have different sets of RGB numbers depending on Excel version, which is no better than having the different Range.Interior.Color numbers depending on version.

These cell colors are being set by the user, using Excel's user interface for setting the cell's "Fill Color". The VBA code in this proejct does not set the colors. The VBA code only GETS the colors, and branches according to the color found in the cell being processed.

Aside from obvious exceptions like Application.Version, it isn't normal for object properties to randomly change like this from version to version. Consistency of object properties between versions is an important part of what allows VBA code to work across different versions. If it were not so, we'd have to conditional-compile virtually all our Excel VBA code.

解决方案

Update:

I haven't been able to reproduce this problem in a new workbook, so it may be workbook corruption. Normally Range.Interior.Color is reliable across versions, for all colors represented.

FWIW, this workbook was sent to me by another person, and that person is on Excel for Macintosh while I am on Windows, so creating the workbook on one platform and then using it on another may have been a factor in the corruption, if any (even though that should work fine).

这篇关于Excel 2007与之后的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！