Excel VBA: Application defined or Object defined error

Question

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.

You probably should have a check that colIndex hasn't exceeded lastCol within the Do While loop. Otherwise if there is a "[" without a matching "]" then the loop won't stop where you want it to. A specific error number and message would make things easier to diagnose — barrowc
– barrowc, Commented Nov 15, 2015 at 22:58
Can you avoid the error with Do While .Cells(rowIndex, colIndex).Value <> "]" And colIndex <= lastCol? If this is the case then ] is never found and you've tried to run off the right side of the worksheet. Perhaps there is a leading/trailing space with ] or ` ]`. That will produce the 1004 error. — user4039065
– user4039065, Commented Nov 15, 2015 at 23:00
Thank you for the tip, the error goes away when I add that last bit in. I didn't think I needed a boundary check because there would always be a matching ]. Turns out the error was due to a " ]" instead of a "]". That extra space threw the error. — Mav
– Mav, Commented Nov 15, 2015 at 23:29
Do you want to speed it up? Using a pair of MATCH functions is much faster than looping through the cells and you can wildcard the search. — user4039065
– user4039065, Commented Nov 15, 2015 at 23:33

user4039065 · Accepted Answer · 2015-11-17 02:15:03Z

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.}

<3 So this may be more efficient but code is harder to read/understand (at least for me XD). Regardless, thank you, gonna study it...

Collectives™ on Stack Overflow

Excel VBA: Application defined or Object defined error

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related