method 'range' of object'_global' failed 2

[vttech]

I am trying to reuse the code below

Public Function ColorBorder(chgcell As String)
    Range(chgcell).Select
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 3
    End With
end Function

It will work the first time but if I run it again
I get the following error

method 'range' of object'_global' failed

I read somewhere that I have to
Modify the code so that each call to an Excel object, method, or property is qualified with the appropriate object variable. But I don't have a clue what that means are how to do it.

I think the line I have to edit is Range(chgcell).Select
Can someone help me out? thanks

Newbie in search of knowledge

 

[ThomasLafferty]

If chgcell is a named range, it will generate the error you mentioned when called. Replace this:

    Range(chgcell).Select

With this:

    Application.Goto "chgcell"

Hope this helps...

Tom

Live once die twice; live twice die once.

 

[vttech]

I used the function ColorBorder like this

ColorBorder("C11")

this way I can apply it to varoius single cells

so I guess it's not a named range

Newbie in search of knowledge

 

[SkipVought]

Hi,

How are you using this function?

Better yet, do not select anything...

Public Function ColorBorder(chgcell As String)
    With Range(chgcell).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 3
    End With
end Function

I'd acutally prefer this...

Public Function ColorBorder(rng As Range)
    With rng.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 3
    End With
end Function

Skip,

_[red][/red]

 

[vttech]

skip took your advise and changed code to

Public Function ColorBorder(chgcell As Range)
    
    With chgcell.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 3
    End With
    chgcell.Borders(xlDiagonalDown).LineStyle = xlNone
    chgcell.Borders(xlDiagonalUp).LineStyle = xlNone
    With chgcell.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With chgcell.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With chgcell.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With chgcell.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
End Function

but know I get type mismatch and it brings me to this line

ColorBorder ("c11")

this is how I pass the cell to the function

Newbie in search of knowledge

 

[firefytr]

You need to pass it as a range now, not a string...

Call ColorBorder(sheets("YourSheetName").Range("C11"))

And you could shorten your code up a bit by taking out all those With/End With statements, they only create shadow objects anyway ...

Public Function ColorBorder(chgcell As Range)
    chgcell.Font.Name = "Arial"
    chgcell.Font.FontStyle = "Bold"
    chgcell.Font.Size = 10
    chgcell.Font.Strikethrough = False
    chgcell.Font.Superscript = False
    chgcell.Font.Subscript = False
    chgcell.Font.OutlineFont = False
    chgcell.Font.Shadow = False
    chgcell.Font.Underline = xlUnderlineStyleNone
    chgcell.Font.ColorIndex = 3
    chgcell.Borders(xlDiagonalDown).LineStyle = xlNone
    chgcell.Borders(xlDiagonalUp).LineStyle = xlNone
    chgcell.Borders(xlEdgeLeft).LineStyle = xlContinuous
    chgcell.Borders(xlEdgeLeft).Weight = xlMedium
    chgcell.Borders(xlEdgeLeft).ColorIndex = xlAutomatic
    chgcell.Borders(xlEdgeTop).LineStyle = xlContinuous
    chgcell.Borders(xlEdgeTop).Weight = xlMedium
    chgcell.Borders(xlEdgeTop).ColorIndex = xlAutomatic
    chgcell.Borders(xlEdgeBottom).LineStyle = xlContinuous
    chgcell.Borders(xlEdgeBottom).Weight = xlMedium
    chgcell.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
    chgcell.Borders(xlEdgeRight).LineStyle = xlContinuous
    chgcell.Borders(xlEdgeRight).Weight = xlMedium
    chgcell.Borders(xlEdgeRight).ColorIndex = xlAutomatic
End Function

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[SkipVought]

It might end up with more Lines of Codes but the With...End With can help to make your performance better if used properly. Each time, the same object tree must be evaluated completely. With...End With reduces the number of evaluations...

Public Function ColorBorder(chgcell As Range)
    With chgcell
        With .Font
            .Name = "Arial"
            .Style = "Bold"
            .Size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = 3
        End With
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
    End With
End Function

Skip,

_[red][/red]

 

[SkipVought]

...and it also help me quickly visually identify what properties & methods are related when there are several.

I use alot of spatial features in the editor to format my code for reasons of documentation and understanding.

Skip,

_[red][/red]

 

[firefytr]

No Skip, it will not make your code faster. The object(s) are still evaluated with each line, right along with the shadow object created upon initializing. Formatting? Yes, that would be the only advantage in my book. Plus if you have a rather large object (in terms of character length) it's nice sometimes. But it certainly will not speed up your code.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[SkipVought]

Zack,

Geez, I dunno.

I just ran a test on 2 columns of 65536 row of data, and the With...End With ran 3.9% faster, for what it's worth.

Sub testall()
    t1 = Now
    test1
    t2 = Now
    test2
    t3 = Now
    With Sheets("Results")
        .[A1] = t2 - t1
        .[A2] = t3 - t2
    End With
End Sub
Sub test1()
    Dim r As Range
    For Each r In ActiveSheet.UsedRange
        r.Font.Bold = True
        r.Font.Color = vbRed
        r.Font.Italic = True
        r.Font.Size = 12
        r.Font.Name = "Courier"
        r.Font.Underline = True
        r.Borders(xlDiagonalDown).LineStyle = xlNone
        r.Borders(xlDiagonalUp).LineStyle = xlNone
        r.Borders(xlEdgeLeft).LineStyle = xlContinuous
        r.Borders(xlEdgeLeft).Weight = xlThin
        r.Borders(xlEdgeLeft).ColorIndex = xlAutomatic
        r.Borders(xlEdgeTop).LineStyle = xlContinuous
        r.Borders(xlEdgeTop).Weight = xlThin
        r.Borders(xlEdgeTop).ColorIndex = xlAutomatic
        r.Borders(xlEdgeBottom).LineStyle = xlContinuous
        r.Borders(xlEdgeBottom).Weight = xlThin
        r.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
        r.Borders(xlEdgeRight).LineStyle = xlContinuous
        r.Borders(xlEdgeRight).Weight = xlThin
        r.Borders(xlEdgeRight).ColorIndex = xlAutomatic
        r.Interior.ColorIndex = 6
        r.Interior.Pattern = xlSolid
        r.HorizontalAlignment = xlCenter
        r.VerticalAlignment = xlBottom
        r.WrapText = False
        r.Orientation = 0
        r.AddIndent = False
        r.IndentLevel = 0
        r.ShrinkToFit = False
        r.ReadingOrder = xlContext
        r.MergeCells = False
    Next
End Sub
Sub test2()
    Dim r As Range
    For Each r In ActiveSheet.UsedRange
        With r
            .Font.Bold = True
            .Font.Color = vbRed
            .Font.Italic = True
            .Font.Size = 12
            .Font.Name = "Courier"
            .Font.Underline = True
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
            With .Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With .Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With .Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With .Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
            With .Interior
                .ColorIndex = 6
                .Pattern = xlSolid
            End With
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
    Next
End Sub

Skip,

_[red][/red]

 

[firefytr]

Yes, I tested at 1-2% as well, which is hardly negotiable in my book. Notice I did not say it will speed your code up, just shorten it up. There shouldn't be much speed gain there. It is of simplicity, efficiency and understanding (given that everyone is on the same page and we're all edg-ewe-mac-ay-ted ).

Here is a good read:

http://tinyurl.com/j9qkf,

a post by Matt Curland (who just happens to be the designer of Intellisense in VB).

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci