Add a LineStyle Border in Excel Using VB 4.0 Visual basic v5.0 includes al...
April 5, 2010 at 07:41:11
Specs: Windows XP
I am using Visual Basic 4.0 to format data in an Excel 2003 spreadsheet created in VB 4.0

The only thing I am having trouble with is not being able to add a line to the a side of a cell.

I have used the code as if I was to run it as a Macro in Excel directly although when I run it in VB 4.0 I get a "424" Object Required Error.

I believe it has something to do with either (xlEdgeRight) or xlContinuous in the below code

Dim xlApp1 As Object
Set xlApp1 = CreateObject("excel.application")

With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
End with

*** Note even though I was able set the initial excel application as an object.

I lack the ability to further define/set objects beyond the application level.

Are you able to help me get this code working?

See More: Add a LineStyle Border in Excel Using VB 4.0

Report •

April 5, 2010 at 08:10:08
I don't have VB 4.0, so the best I could do is test your code in Excel 2003 with VB 6.5.

You didn't say which line produced the error, but when I singled step through the code via F8 I got a

Run-time  Error '1004':
Application-defined or object-defined error

at this line:


That kind of makes sense since a cell isn't an application level object since it exists at the Sheet level.

The code worked when I changed the line to this:


I hope that helps.

Report •

April 5, 2010 at 08:32:39
I have tried many different combinations without success: (eg)


The Error is occuring on line 2: So it must have something to do with (xlEdgeRight)?

1 - xlApp1.range("A1").Select
2 - With Selection.borders(xlEdgeRight)
3 - .LineStyle = xlContinuous
4 - End With

Report •

April 5, 2010 at 09:43:40
First, some VBA 6.5 syntax:

re: Sheets("1").Range(...)

In VB 6.5, the quotes mean that the string between them is the name of the sheet. In the example above you would be referring to a sheet whose tab reads 1 - i.e. the number 1.

Similar to Sheets("Summary") which would be referring to a sheet whose tab reads Summary.


Without the quotes, VB 6.5 would look for the sheet in the first position of the workbook, regardless of the name displayed on the tab.


would refer to the second sheet, etc.

Now, as to your error:

If the error is occurring on this line:

With Selection.borders(xlEdgeRight)

I would guess that it has nothing specifically to do with xlEdgeRight but more with whatever VBA has Selected.

Since you are trying to set the border on the Selection, my quess is that xlApp1.range("A1") is not something that a border can be set on.

I would do some more testing and figure out exactly what gets selected by xlApp1.range("A1").Select.

Try this after that line:

MsgBox Selection.Address

If it doesn't return $A$1 then you haven't selected Range("A1").

As I mentioned in my earlier response, I get an error at this line xlApp1.Range("A1").Select since Range("A1") is not an application level object.

Range("A1").Select works
Sheets(1).Range("A1").Select works
Sheets("Sheet1").Range("A1").Select works

This code returns a bunch of MsgBoxes that all display $A$1 and then places a border on the right side of Sheet1!A1.

Sub A1_Border()

Dim xlApp1 As Object
Set xlApp1 = CreateObject("excel.application")

 MsgBox Selection.Address
 MsgBox Selection.Address
 MsgBox Selection.Address
'Border gets applied here
  With Selection.Borders(xlEdgeRight)
  .LineStyle = xlContinuous
  End With

End Sub

Report •

Related Solutions

April 5, 2010 at 15:48:17
Here's a hint:
Debug.Print xlEdgeRight
Debug.Print xlContinuous

Contrast and compare what you get in VBA to what you get in VB<whatever>.

Report •

April 5, 2010 at 16:08:45

As you are running Excel from another application, such as Word, the problem you are having is because you are using 'With Selection'

When you used 'With Selection.borders(xlEdgeRight)' this did not apply to the Excel application.
The selection object was the current selection in the calling program.

With Selection
(note the dot) and it will work.

If you use this:

Dim xlApp As Excel.Application
Set xlApp = CreateObject("excel.application")

With xlApp
    .Workbooks.Open FileName:="C:\Temp\TestTemp.xls"
    With .Range("B2").Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    .Workbooks("TestTemp.xls").Close SaveChanges:=True
End With
it works, even though the range is selected directly from the xlApp (the above code is effectively xlApp.Range("B2").Borders(xlEdgeRight )
Presumably because a workbook is open, the ActiveSheet is used as the default.

In VBA you do not need to select objects before using/changing them.
Just use With .Range("A1").Borders (xlEdgeRight )


Report •

April 5, 2010 at 20:32:06
Thanks Mr Razor2.3

This was the money shot.

Debug.Print xlEdgeRight
Debug.Print xlContinuous

Once I had the above values I was able to substitute them into the code directly.

With xlApp1.Selection.borders(10)
.LineStyle = 1
.ColorIndex = xlAutomatic
End With

Report •

April 6, 2010 at 04:42:00

The reason it worked with:

With xlApp1.Selection.borders(10)
was because you used the correct Selection object.

Try the above with (xlEdgeRight):

With xlApp1.Selection.borders(xlEdgeRight)
and it will still work.


With xlApp1
     With Selection.borders(10)
and it will fail


Report •

April 6, 2010 at 05:48:13
Hi Humar

It does not work using Visual Basic 4.0 (I tried it)
It looks like VB 4.0 has trouble picking up some of the Excel named declarations applied to a value (ie) xlEdgeRight etc.

While some are detected fine, others return NUL in VB 4.0
using debug.print ......

Although by using debug.print xlEdgeRight in Excel it returned it's actual value. So by substituting the returned Excel value in place of the named declaration normally used produces the desired result.

I think some of these named declarations are additions to office 2003 (So VB 6+ detect it fine) But VB 4.0 was still around during Office 97. A lot has changed between the 2 versions.

Report •

April 6, 2010 at 06:03:04

Thanks for that information. I see your point on the use of the 'xl' constants compared to using the actual values as suggested by Razor2.3

I still think that your original code had a problem with the way that it used Selection.

Selection and
.Selection are not the same when running a different application.

As an aside, you don't need to select an Excel cell when using VB, before doing something with it. Just use it directly.


Report •

April 6, 2010 at 06:45:25
No Worries

Report •

Ask Question