CODEWITHSUNDEEP
vbaexcel
RossC
RossC

Reputation: 1230

Excel VBA formatting a closed Worksheet Error 1004 "Application Defined or Object Defined Error"

I have written a big pile of VBA code and unfortunately the simplest part of it will not work. All I need is to open a specific workbook (name will not change) and draw borders in it. My Sub to write to it works fine, but as soon as I try and draw any borders it draws them in the Worksheet that the VBA code is associated with, not the one it has opened. The below code was my attempt to fix it (I've moved it to its own Sub for convenience) but the error 1004 kicks in on ActiveSheet.Range(rRng).BorderAround xlContinuous

I don't doubt this is something obvious, but I cannot for the life of me see where it is. E.g. xl0.ActiveSheet.Range(rangeAA) = CardDataInputMode this works absolutely fine for me in the WriteToSheet Sub I created, but adding the next line: ActiveSheet.Range(rRng).BorderAround xlContinuous will not work. Out of curiosity I added xl0.ActiveSheet.Range("A:AS").Columns.AutoFit to the Write Sub and it AutoFits fine too. I'm going around in circles!

Here is the entire Sub, anything else needed please let me know! Many thanks. 

Sub OutlineCells()
    Dim xl0 As New Excel.Application
    Dim xlw As New Excel.Workbook
    Dim rRng As Range
    Dim row As Range
    Dim cell As Range

    Set xlw = xl0.Workbooks.Open(Application.ThisWorkbook.Path & "\Outputs\MasterCardTestCaseTemplate.xlsx")
    xlw.Worksheets("Sheet1").Activate

    Set rRng = Sheet1.Range("A1:AS25")

    'Clear existing
    'rRng.Borders.LineStyle = xlNone

    For Each row In rRng.Rows
        For Each cell In row.Cells
            'Apply new borders
            xlw.ActiveSheet.Range(rRng).BorderAround xlContinuous  ' <--- ERROR HERE
            xlw.ActiveSheet.Range(rRng).Borders(xlInsideHorizontal).LineStyle = xlContinuous
            xlw.ActiveSheet.Range(rRng).Borders(xlInsideVertical).LineStyle = xlContinuous
        Next cell
    Next row

    xlw.Save
    xlw.Close

    Set xl0 = Nothing
    Set xlw = Nothing

End Sub

Upvotes: 1

Views: 877

Answers (1)

David Zemens
David Zemens

Reputation: 53623

You're using your range object(s) incorrectly -- you have it in parentheses which causes an evaluation, and since the range object's default property is its .Value, this line:

xl0.ActiveSheet.Range(rRng).BorderAround xlContinuous

Is essentially this:

xl0.ActiveSheet.Range(rRng.Value).BorderAround xlContinuous

This would only not raise an error if rRng represents a single cell and that cell's .Value is a valid Address string.

Now, you could do this to force the .Address property:

xl0.ActiveSheet.Range(rRng.Address).BorderAround xlContinuous

But it would be better to qualify rRng to be part of the xlW workbook. Change this so that it qualifies based on the xlw workbook:

Set rRng = xlw.ActiveSheet.Range("A1:AS25")

Then you can use that range directly:

For Each row In rRng.Rows
    For Each cell In row.Cells
        'Apply new borders
        rRng.BorderAround xlContinuous  ' <--- ERROR HERE
        rRng.Borders(xlInsideHorizontal).LineStyle = xlContinuous
        rRng.Borders(xlInsideVertical).LineStyle = xlContinuous
    Next cell
Next row

Now that I look at it, you don't need that nested loop at all, just omit both For Each loops, I think this should do the same results:

'## DELETE THIS For Each row In rRng.Rows   
'## DELETE THIS   For Each cell In row.Cells
        'Apply new borders
        rRng.BorderAround xlContinuous  ' <--- ERROR HERE
        rRng.Borders(xlInsideHorizontal).LineStyle = xlContinuous
        rRng.Borders(xlInsideVertical).LineStyle = xlContinuous
 '## DELETE THIS    Next cell
 '## DELETE THIS Next row

Upvotes: 3

Related Questions