Add Border to Cell Range.

[C_Haines_ENG]

I have tried everything VBA related to doing this and nothing fruitful has come up without providing me some sort of error. The code below is what seems to create the smallest error, however it doesn't seem to work none the less. 

.Range("B2:E2").Borders(xlEdgeBottom).Weight = xlMedium

I would like to be able to add a thick border to the bottom of a cell range.

As a bonus, does anyone know how to center and change the width of cells? Or bold cells?

[BM_Ashraf]

Hi,

The code you shared is working fine .

Sub Test_XL()

Dim excelApp As excel.Application
Dim ws As WorkSheet
Dim owb As Workbook

On Error Resume Next

Set excelApp = GetObject(, "Excel.Application")

' Try to connect to a running instance of Excel.
    If Err Then
        Err.Clear
        ' Couldn't connect so start Excel.  It's started invisibly.
        Set excelApp = CreateObject("Excel.Application")
        If Err Then
            MsgBox "Cannot access excel."
        End If
    End If
    
  excelApp.Visible = True
  excelApp.DisplayAlerts = False

  Set owb = excelApp.Workbooks.Add

  Set ws = owb.Worksheets(1)

    With ws
    
    .Range("B2:E2").value = "MechDS" & "Email:zezo7790@gmail.com"
    .Range("B2:E2").Font.Bold = True
    .Range("B2:E2").Columns.AutoFit
    .Range("B2:E2").Borders(xlEdgeBottom).Weight = xlThick
    End With
  
  'excelApp.Quit
  
End Sub

[C_Haines_ENG]

Your code does not seem to work for me, ive posted the code ive written below:

'define the file to create/open
xlsPath = "C:\BOM Calculator\Test.xlsx"

'define Excel Application object
excelApp = CreateObject("Excel.Application")
'set Excel to run visibly, change to false if you want to run it invisibly
excelApp.Visible = True
'suppress prompts (such as the compatibility checker)
excelApp.DisplayAlerts = False



'check for existing file 
If Dir(xlsPath) <> "" Then
'workbook exists, open it
excelWorkbook = excelApp.Workbooks.Open(xlsPath)
ExcelSheet = excelWorkbook.Worksheets(1)
Else
'workbook does NOT exist, so create a new one
excelWorkbook = excelApp.Workbooks.Add
End If

']


'Insert data into Excel.
With excelApp
	
	If .Range("A1").Value < 2
		
		RowNum = 2
	   .Range("A1").Value = 2

	Else 
		
		RowNUM = .Range("A1").Value
		
	End If
	
			.Range("B" & RowNum) = iProperties.Value(modelName,"Project", "Part Number")
			.Range("C" & RowNum) = iProperties.Value(modelName, "Project", "Description")
		
End With   

'set all of the columns to autofit
excelApp.Columns.AutoFit   
'save the file
excelWorkbook.SaveAs(xlsPath)

''close the workbook and the Excel Application
''uncomment if you want to close the xls file at the end
excelWorkbook.Close
excelApp.Quit
excelApp = Nothing     

[tyler.warner]

Beginning at line 26, you're inserting the data into the Excel application. Review MechDS' code from above. You want to be working on a specific worksheet. You had defined the worksheet if there was an existing one (line 17) but didn't create one otherwise and then you're not actually using it in your code.

Here's the full documentation for the borders & cells:

Excel Object Model on Borders (link) 

Excel Object Model on Cells (link) 

[C_Haines_ENG]

Where do I have to declare this, I keep getting random errors.

.Worksheets(1).Range("B" & RowNum & ":" & "G" & RowNum).Borders(xlEdgeBottom).Weight = xlMedium

The documentation states that this is acceptable but it still doenst work.  

[tyler.warner]

What are the errors that you're getting? Please provide images.

[A.Acheson · Accepted solution]

Hi @C_Haines_ENG 

Implementing in VB.NET is a little harder than VBA. You will have to access objects in more depth adding the enumeration. 

For Example this is how to access your original snippet in VB.NET and here is the link to the Weight enumeration 

and the Border Enumeration

.Range("B2:E2").Borders(XlBordersIndex.xlEdgeBottom).Weight = XlBorderWeight.xlMedium

 Here is the working method in VB.Net

AddReference "Microsoft.Office.Interop.Excel" 'To use excel 
Imports Microsoft.Office.Interop 'To use excel 
Imports Microsoft.Office.Interop.Excel 'To use excel

Dim excelApp As Excel.Application = GetObject(, "Excel.Application")
 Try
         excelApp = CreateObject("Excel.Application")
Catch
	  MsgBox ("Cannot access excel.")
End Try

excelApp.Visible = True
excelApp.DisplayAlerts = False
  
Dim oWb As Workbook = excelApp.Workbooks.Add
Dim oWs As Worksheet = oWb.Worksheets(1)

With oWs
.Range("B2:E2").Value = "This is a new Excel value"
.Range("B2:E2").Columns.AutoFit
Try
.Range("B2:E2").Borders(XlBordersIndex.xlEdgeBottom).Color = RGB(255, 0, 0)
.Range("B2:E2").Borders(XlBordersIndex.xlEdgeBottom).Weight = XlBorderWeight.xlMedium
Catch
	 MessageBox.Show ("Error applying border.","iLogic")
End Try
End With

[C_Haines_ENG]

Here is the error im receiving:

Error on line 110 in rule: BOMCalc, in document: 751434.dwg

Exception from HRESULT: 0x800A03EC

.Range("B" & RowNum & ":" & "G" & RowNum).Borders(xlEdgeBottom).Weight = xlMedium

Below is how "excelApp" is being defined.

define Excel Application object
excelApp = CreateObject("Excel.Application")
'set Excel to run visibly, change to false if you want to run it invisibly
excelApp.Visible = True
'suppress prompts (such as the compatibility checker)
excelApp.DisplayAlerts = False



'check for existing file 
If Dir(xlsPath) <> "" Then
'workbook exists, open it
'excelWorkbook = excelApp.Workbooks.Open(xlsPath)
excelWorkbook = excelApp.Workbooks.Open(xlsPath)
ExcelSheet = excelWorkbook.Worksheets(1)
Else
'workbook does NOT exist, so create a new one
excelWorkbook = excelApp.Workbooks.Add
End If

[C_Haines_ENG]

Hey you solved it!

.Range("B2:E2").Borders(XlBordersIndex.xlEdgeBottom).Weight = XlBorderWeight.xlMedium

That did the trick. Thank you!

Is there a form where I can see all the different options? Like outside boxes, line weights etc? 

Would you also know how to format colums/cells, id like to center a column but the code ive found seems to have the same problems.

[A.Acheson · Accepted solution]

Here is the RangeHorizontalAlignment and here is how a handy post discussing how to declare the value xlCenter

.Range("B2:E2").HorizontalAlignment = Excel.Constants.xlCenter

 Regarding a form unfortunately I don't. Internet searching is your best bet. Find it in VBA then check the documentation for how to implement in VB.NET

[C_Haines_ENG]

Double win, thanks a ton!