Excel VBA:应用程序定义或对象定义的错误 [英] Excel VBA: Application defined or Object defined error
问题描述
这是代码:
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屋!