VBA：格式化单元格#VALUE错误 [英] VBA: Format cell on #VALUE Error

问题描述

我创建了一个函数来搜索范围上的特定值并返回相邻特征。

I've created a function that searches a specific value on a range and returns adjacent characteristics.

Function Busca(valor As String)
    Dim bus(0 To 1)
    bus(0) = Worksheets("Sheet2").Range("A1:A10").Find(valor, LookAt:=xlWhole). _
        Offset(0, 1)
    bus(1) = Worksheets("Sheet2").Range("A1:A10").Find(valor, LookAt:=xlWhole). _
        Offset(0, 2)
    Busca = bus
End Function

如果 valor 在 A1：A10 中没有匹配，则该函数返回 {＃VALUE，＃VALUE} ，可以，但我想返回，而不是像 {无匹配，} ，给定颜色中的无匹配单元格。我试过数据验证，错误处理和如果 然后与 ActiveCell.Interior.ColorIndex 无效。另外，如果错误被更正，我希望单元格返回透明。

If there is no match for valor in A1:A10, the function returns {#VALUE,#VALUE}, wich is ok, but I'd like to return instead something like {"No match", ""}, with the "No match" cell in a given color. I've tried data validation, error handling and If Then with ActiveCell.Interior.ColorIndexto no avail. Also, if the error is corrected, I'd like the cell to go back to transparent.

我想一个事件处理程序可以做的伎俩，但我是新的在VBA，还有很多我不明白的东西。

I guess an event handler could do the trick, but I'm new at VBA and there are still a lot of things that I don't understand.

编辑

如建议，要清楚：

如果没有找到匹配项，我如何使输出单元格更改颜色，并且没有如果找到一个匹配（在VBA中），颜色是如何？

How do I make the output cell(s) to change color if no match is found, and to have no color if a match is found (in VBA)?

推荐答案

根据新的信息，这是一个完全重写的答案提问者。

This is a completely rewritten answer in the light of new information from the questioner.

如果我理解正确，您希望创建一个自定义函数来改变单元格的颜色。 Microsoft自定义功能帮助中的以下文本表示您不能：

If I understand correctly, you wish to create a Custom Function that will change the colour of a cell. The following text from Microsoft's Help on custom functions says you cannot:

您可以在自定义函数中使用的VBA关键字数量较小
比您可以在宏中使用的数字。自定义函数不允许
执行任何其他操作，而不是向
工作表中的公式返回一个值，也可以使用另一个VBA宏或函数中使用的表达式。
例如，自定义函数无法调整窗口大小，在
单元格中编辑公式，或更改
单元格中的文本的字体，颜色或图案选项。如果在函数
过程中包含此类action代码，函数将返回#VALUE！错误。

The number of VBA keywords you can use in custom functions is smaller than the number you can use in macros. Custom functions are not allowed to do anything other than return a value to a formula in a worksheet or to an expression used in another VBA macro or function. For example, custom functions cannot resize windows, edit a formula in a cell, or change the font, color, or pattern options for the text in a cell. If you include "action" code of this kind in a function procedure, the function returns the #VALUE! error.

我知道可能符合您要求的两种方案。第三种选择是在一阵灵感之后添加的。

I know of two alternatives that might meet your requirements. A third alternative was added later after a flash of inspiration.

替代方案1：提供允许值的下拉列表

将光标定位到具有受限制值的单元格。从工具栏中选择数据，然后选择验证。将显示一个数据验证表单。

Position the cursor to a cell which is to have restricted values. Select Data from the toolbar then Validation. A Data Validation form is displayed.

如果尚未选择设置选项卡。单击允许：下的框，然后选择列表。在源框中输入：= $ A $ 1：$ A $ 10。 （=是必需的，如果您想要创建此单元格的副本，则$ s很重要。）单击确定。

Select the Settings tab if not already selected. Click the box under "Allow:" and select List. In the Source box enter: "=$A$1:$A$10". (The "=" is required. The $s are important if you want to be able to create copies of this cell.) Click OK.

当用户将光标到该单元格，它们可以输入允许值，也可以从列表中选择允许的值。尝试输入任何其他值将导致错误消息。数据验证表单中的其他选项卡允许您输入帮助消息和您自己的错误消息。

When the user positions the cursor to that cell they can either enter a permitted value or can select a permitted value from the list. An attempt to enter any other value will result in an error message. The other tabs within the Data Validation form allow you to enter a help message and your own error message.

由于源框中的$ s，您可以复制

Because of the $s in the source box, you can copy the original cell and its validation to other cells.

替代方案2：条件格式

如果允许的值是范围，条件格式将满足您的要求。例如，假设允许的范围为10到20。

If the permitted values are a range, conditional formatting will meet your requirements. For example suppose the permitted range is 10 to 20.

从工具栏中选择格式化条件格式。

Select Format from the toolbar then Conditional Formatting.

显示条件1的框。 之间已经显示。在右侧的框中输入10和20。

Boxes for condition 1 are displayed. "Between" is already displayed. In the boxes to the right enter 10 and 20.

单击添加以显示条件2.替换by小于在下一个框中输入10单击格式，单击颜色，选择红色，单击确定。

Click Add to display boxes for condition 2. Replace Between" by "Less than". In the next box enter 10. Click Format. Click Colour. Select Red. Click OK.

单击添加以显示条件3。在下一个框中输入20.单击格式。单击颜色。选择红色。单击确定。

Click Add to display boxes for condition 3. Replace Between" by "Greater than". In the next box enter 20. Click Format. Click Colour. Select Red. Click OK.

单击确定接受条件格式。

Click OK to accept the conditional formatting.

用户可以输入任何他们喜欢的内容单元格，但除非是10到20之间，否则为红色。

The user can enter anything they like into the cell but it will be red unless it is between 10 and 20.

您可以根据需要创建任意数量的格式化单元格。

You can create as many copies of the formatted cell as you wish.

工作表更改事件

我以前应该考虑过事件。我相信这正是你想要的。

I should have thought of events before. I believe this does exactly what you want.

在VBA编辑器中，Project Explorer通常在屏幕左侧可见。如果不是，请单击 Ctrl + R 。

Within the VBA Editor, the Project Explorer is normally visible on the left of the screen. If it is not, Click Ctrl+R.

右键单击工作表的行，其中所选值为被控制。点击查看代码。

Right click the line for the worksheet within which selected values are to be controlled. Click View Code.

代码区域将以工作表为标题，否则为空白。您可以在此区域中放置各种例程，但相关例程是工作表事件例程。这些是当事件如工作表激活或停用时将被调用的例程。您想要的事件是当用户更改单元格时调用的更改。这个例程的吸引力是它可以做任何你想要的。

The code area will be headed Worksheet but will otherwise be blank. You can place various routines in this area but the relevant routines are the worksheet event routines. These are routines that will be called whenever an event such as worksheet activated or deactivated occurs. The event you want is Change which is called whenever the user changes a cell. The attraction of this routine is that it can do anything you want.

将下面的Worksheet_Change例程复制并粘贴到工作表代码区域。

Copy and Paste the Worksheet_Change routine below into the worksheet code area.

其参数是用户更改的单元格的地址。

Its parameter is the address of the cell that the user has changed.

TgtRngList设置为您要巡视的范围列表。我设置为C1：C1000，F1：F1000和A1。您将不得不将其更改为要巡视的范围。

TgtRngList is set to a list of the ranges you wish to patrol. I have set this to C1:C1000, F1:F1000 and A1. You will have to change this to the ranges you want to patrol.

OKValueList设置为巡视范围允许值的列表。他们可能在某个地方，但我认为在这里更容易定义。将列表更改为任何您想要的。

OKValueList is set to a list of the permitted values for the patrolled ranges. They could be in a sheet somewhere but I think it is easier to define them here. Change the list to whatever you want.

代码检查在其中一个巡逻区域中更改的单元格。如果是，则检查其是否具有允许的值。该检查的结果导致单元格设置为黑色或红色。

The code checks for the changed cell being in one of the patrolled area. If it is, it is checked to have a permitted value. The result of that check causes the cell to be set to black or red.

Option Explicit
Sub Worksheet_Change(ByVal ChangedCell As Range)

  ' This routine is called whenever the user changes a cell.
  ' It is not called if a cell is changed by Calculate

  Dim ColChanged As Integer
  Dim InxOV As Integer
  Dim InxTR As Integer
  Dim OKValueList() As Variant
  Dim Patrolled As Boolean
  Dim RowChanged As Integer
  Dim TgtColLeft As Integer
  Dim TgtColRight As Integer
  Dim TgtRngPartList() As String
  Dim TgtRngList() As Variant
  Dim TgtRngPart As String
  Dim TgtRowBottom As Integer
  Dim TgtRowTop As Integer
  Dim ValueChanged As String
  Dim ValueOK As Boolean

  ' Fill TgtRngList withe ranges that are to be patrolled by this routine
  TgtRngList = Array("C1:C1000", "F1:F1000", "A1")

  ' Fill OKValueList with the permitted values for these cells.
  OKValueList = Array("V1", "V2", "V3", "V4", "V5", _
                      "V6", "V7", "V8", "V9", "V10")

  ColChanged = ChangedCell.Column
  RowChanged = ChangedCell.Row

  Patrolled = False
  For InxTR = LBound(TgtRngList) To UBound(TgtRngList)
    TgtRngPartList = Split(TgtRngList(InxTR), ":")
    ' Decode top left of range
    TgtRngPart = TgtRngPartList(LBound(TgtRngPartList))
    TgtRowTop = Range(TgtRngPart).Row
    TgtColLeft = Range(TgtRngPart).Column
    If LBound(TgtRngPartList) = UBound(TgtRngPartList) Then
      ' There is no colon so single cell range
      TgtRowBottom = TgtRowTop
      TgtColRight = TgtColLeft
    Else
      TgtRngPart = TgtRngPartList(UBound(TgtRngPartList))
      TgtRowBottom = Range(TgtRngPart).Row
      TgtColRight = Range(TgtRngPart).Column
    End If
    If RowChanged >= TgtRowTop And RowChanged <= TgtRowBottom And _
       ColChanged >= TgtColLeft And ColChanged <= TgtColRight Then
      ' This is a patrolled cell
      Patrolled = True
      Exit For
    End If
  Next
  If Patrolled Then
    With ActiveSheet
      ValueChanged = .Cells(RowChanged, ColChanged).Value
      ' Check value against permitted list
      ValueOK = False
      For InxOV = LBound(OKValueList) To UBound(OKValueList)
        If ValueChanged = OKValueList(InxOV) Then
          ValueOK = True
          Exit For
        End If
      Next
      If ValueOK Then
        ' Set cell black
        .Cells(RowChanged, ColChanged).Font.Color = RGB(0, 0, 0)
      Else
        ' Set cell red
        .Cells(RowChanged, ColChanged).Font.Color = RGB(255, 0, 0)
      End If
    End With
  End If

End Sub

希望这有帮助。

这篇关于VBA：格式化单元格#VALUE错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！