Excel VBA：应用程序定义或对象定义的错误 [英] Excel VBA: Application defined or Object defined error

问题描述

我已经写了一些代码来查找excel文件中的一组括号，并清除它们之间的单元格内容。在我收到错误消息之前，我已经编写了26-27行的代码。

这是代码：

  Sub宏（）
 Dim white As Long 
 Dim rowIndex As Long 
 Dim colIndex As Long 
 Dim lastRow As Long 
 Dim lastCol As Long 
 
 white = RGB（Red：= 255，Green：= 255，Blue：= 255）
 
 With ActiveSheet 
 
 lastRow = .Cells.Find（*，SearchOrder：= xlByRows，SearchDirection：= xlPrevious）.Row 
 lastCol = .Cells.Find（*，SearchOrder：= xlByColumns，SearchDirection：= xlPrevious）.Column 
 
对于rowIndex = 1 to lastRow 
对于colIndex = 1 To lastCol 
如果.Cells（rowIndex，colIndex）.Text =[Then 
 colIndex = colIndex + 1 
 Do While .Cells（rowIndex，colIndex）.Value<> ]
 .Cells（rowIndex，colIndex）.Font.Color = white 
 colIndex = colIndex + 1 
循环
结束如果
下一个colIndex 
 Next rowIndex 
 End with 
 End Sub 
  

此行发生错误：

  Do While Cells（rowIndex，colIndex）.Value<> ]
  

我尝试添加：

  With ActiveSheet 
  

随着。在每个单元格命令之前，但它没有改变。
非常感谢任何帮助。

解决方案

如果其中一个单元格包含 [或] 可能具有流氓前导尾随空格/不间断空格，因此应进行通配符比较。另外，工作表的
^在运行子

< sup>运行子

在上述之前的和之前，您可以看到D2回ains其 Range.Value属性（在公式栏中可见），而工作表上没有任何东西。注意：仍然可以从显示任何内容的单元格复制单元格值，但这也是使用 vbWhite 方法的注意事项。

---

¹_{对于基本VBA托盘，有预定义的RGB长型常数。 RGB（255,255,255）等于 vbWhite 。} / p>

I've written some code to look for sets of brackets in an excel file and white out the contents of the cells in between them. The code I have works for 26-27 lines before I get the error message.

Here is the code:

Sub macro()
Dim white As Long
Dim rowIndex As Long
Dim colIndex As Long
Dim lastRow As Long
Dim lastCol As Long

white = RGB(Red:=255, Green:=255, Blue:=255)

With ActiveSheet

lastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

For rowIndex = 1 To lastRow
        For colIndex = 1 To lastCol
            If .Cells(rowIndex, colIndex).Text = "[" Then
                colIndex = colIndex + 1
                Do While .Cells(rowIndex, colIndex).Value <> "]"
                    .Cells(rowIndex, colIndex).Font.Color = white
                    colIndex = colIndex + 1
                Loop
            End If
        Next colIndex
Next rowIndex
End With
End Sub

The error occurs on this line:

Do While Cells(rowIndex, colIndex).Value <> "]"

I tried adding in:

With ActiveSheet

Along with . before each Cell command but it did not make a difference. Any help is greatly appreciated.

解决方案

If one of the cells containing [ or ] may have rogue leading trailing spaces/non-breaking spaces then a wildcard comparison should be made. Additionally, the worksheet's MATCH function can locate the bracketing cells with a wildcard search more efficiently than looping through each cell row-by-row.

Sub hide_cell_values()
    Dim whiteOut As String   '<~~ using alternate method .NumberFormat ;;;
    Dim rw As Long, n As Long, f As Long, l As Long

    whiteOut = ";;;"    'custom cell number format to show nothing in cell

    With ActiveSheet
        'process row by row in the .UsedRange
        With .Range(.Cells(1, 1), .Cells.SpecialCells(xlCellTypeLastCell))
            For rw = 1 To .Rows.Count
                ' check for existance of matching pairs
                If Not IsError(Application.Match("*[*", .Rows(rw), 0)) And _
                               Application.CountIf(.Rows(rw), "*[*") = _
                               Application.CountIf(.Rows(rw), "*]*") Then
                    ' [ and ] pairs exist and match in row.
                    f = 0: l = 0
                    For n = 1 To Application.CountIf(.Rows(rw), "*[*")
                        'this looks complicated but it just references the cells between [ & ]
                        f = Application.Match("*[*", .Rows(rw).Cells.Offset(0, l), 0) + l + 1
                        ' last safety check to ensure that [ comes before ]
                        If Not IsError(Application.Match("*]*", .Rows(rw).Cells.Offset(0, f), 0)) Then
                            l = Application.Match("*]*", .Rows(rw).Cells.Offset(0, f), 0) + f - 1
                            With .Range(.Cells(rw, f), .Cells(rw, l))
                                'this is a better method of not displaying text in a cell
                                .NumberFormat = whiteOut    '<~~ e.g. ;;;
                                'the old method of white-text-on-white-background (not reliable as .Interior.Color can change)
                                '.Font.Color = vbWhite
                            End With
                        End If
                    Next n
                Else
                    ' [ and ] pairs do not match or do not exist in row. do nothing.
                End If
            Next rw
        End With
    End With

End Sub

I have opted for a custom cell number format of ;;; rather than altering the font color to RGB(255, 255, 255) (see footnote ¹). A Range.NumberFormat property of three semi-colons in a row simply shows nothing; a white font's apparent visibility is subject to the cell's Range.Interior.Color property, the worksheet backgroun or even the 'Window background' in the computer's system settings.

    
    ^{Before running sub}

    
    ^{After running sub}

In the before and after images above, you can see that D2 retains its Range.Value property (visible in the formula bar) while showig nothing on the worksheet. Note: cell values can still be copied from a cell displaying nothing but that is a caveat of using the vbWhite method as well.

---

¹ _{There are predefined RGB long type constants for the basic VBA pallette. RGB(255, 255, 255) is equal to vbWhite. Full list available at Color Constants.}

这篇关于Excel VBA：应用程序定义或对象定义的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！