Excel: Check for borders of cells in all workbooks (vb.net)

  • Thread starter Thread starter Glen Vermeylen
  • Start date Start date
G

Glen Vermeylen

Hi,

For a project at school we have to automate the assignment of seats in
classrooms to students during the exams.
The lady who previously did everything manually kept the layouts of the
classrooms in an excel-document: 1 sheet per classroom, and she marked
the cells which represent the seats with a border.
The problem however is that she sometimes merged cells together to get a
better layout.

Problems with current code:
-Very, very slow: vb takes 30 minutes to read 63 classrooms (the check
for the border is the bottleneck)
-If there is a merged area (ie. A1:A4) with a border, then A1, A2, A3
and A4 will be flagged as a seat when it actually just has to be A1


"cell" is a custom helper object to make traveling the cells easier: I
can set it to a row and column, it gives a range back as a string, and I
can move it to the left, right, up, down, etc...

My current code (checking the borders inside a sheet):
(I hope the layout remains somewhat readable)

'Read per row, a maximum of 47 rows
For row As Integer = 1 To 47
'read per column
'numCols= worksheet.UsedRange.Columns.Count
For col As Integer = 1 To numCols
'Check border of cells
Dim borders As Excel.Borders _
= CType(worksheet.Range(cell.getRange), _

Excel.Range).MergeArea.Borders
'borders.LineStyle = 1 gives error at a mergearea
If borders.Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = 1 _
And borders.Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = 1 _
And borders.Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = 1 _
And borders.Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = 1 _
Then
'Current cell is a seat
End If 'Border = 1
cell.goRight()
Next 'Read per column
cell.setCol("A")
cell.goDown()
Next 'Read per row
 
Small correction of comment in code:
'borders.LineStyle = 1 gives error at a mergearea
If borders.Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = 1 _
And borders.Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = 1 _
And borders.Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = 1 _
And borders.Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = 1 _
Then
Click to expand...

* 'Borders.Linestyle = 1' does NOT give an error, but gives false when
it checks a mergeArea. But it is however more than twice as fast than
checking each border seperately (is there a way to drop further checks
in an "AND" when the first one is false? Or can this only be done
through nested if's?).
* Checking each border separately returns each cell inside a mergeArea
with a border as a seat (this would be because of my own "Cell" object)
 
Back
Top