Excel Interop: unexplained result

robertb_NZ

Well-known member
Joined
May 11, 2010
Messages
146
Location
Auckland, New Zealand
Programming Experience
10+
I am writing a VB.NET application that processes an Excel spreadsheet. As each line is processed it is highlighted, and there is an option to step through the lines. I have a method to highlight the current line: -
Sub HighlightThisRow(Row As Integer)
' Current row is highlighted. Line above is un-highlighted, unless = DataRow
Dim Cell1 As String = Cell(Row, 1)
Dim Cell2 As String = Cell(Row, LastUsedColumn)
Dim xlRow As Excel.Range = xlWorkSheet.Range(Cell1, Cell2)
xlRow.Interior.Color = Highlight
xlRow.Borders.Color = Color.LightGray
If Row <> FirstDataRow Then
Cell1 = Cell(Row - 1, 1)
Cell2 = Cell(Row - 1, LastUsedColumn)
Dim xlRange As Excel.Range = xlWorkSheet.Range(Cell1, Cell2)
xlRange.Interior.Color = Normal​
End If​
End Sub​

Highlight and Normal are simply colors defined in global definitions: -
Dim Highlight As Color = Color.LightCyan
Dim Normal As Color = Color.White

This works perfectly, but why do I need the statement
xlRow.Borders.Color = Color.LightGray
Without this the statement
xlRow.Interior.Color = Highlight​
causes the cell borders to be list. However this is not the case with
xlRange.Interior.Color = Normal​
which leaves them unchanged.

My code is working, but I'm puzzled about this inconsistency. What don't I understand?

BTW moderator: what forum should we post VB Interop questions into? Is MS Office Automation considered a "Third Party"?
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,603
Location
Norway
Programming Experience
10+

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
Not exactly sure what you mean by this

I didn't either but, after your post, I realised that that was probably supposed to be "lost".
 

robertb_NZ

Well-known member
Joined
May 11, 2010
Messages
146
Location
Auckland, New Zealand
Programming Experience
10+
Thank you JohnH. The article you referred me to explained the inconsistency:
In the absence of a fill color (e.g. white) the gridlines are shown.​
So I've got caught up in the subtle differences between a Border and a Gridline, and whether Color.White is a color or the absence of a color. Although I would have expected that if White were the absence of a color you'd get the same results with Nothing, but actually you get black. Go figure!

Sorry about the typo: yes, I meant "lost", not "list", in "causes the cell borders to be list".
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,717
Location
Sydney, Australia
Programming Experience
10+
Nothing is numerically zero and white is (255,255,255) in RGB while black is (0,0,0), so it makes sense that Nothing corresponds to black. Visually though, white would be considered the absence of colour because white is the default fill.
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,603
Location
Norway
Programming Experience
10+

robertb_NZ

Well-known member
Joined
May 11, 2010
Messages
146
Location
Auckland, New Zealand
Programming Experience
10+
Technically this makes sense, but it is an odd quirk that assigning any color EXCEPT WHITE to .interior.color causes the grid lines to be be lost. I guess the real issue is that I expected .Interior to be only the interior of the cell, not including the grid lines. Let's close this off, mark it as answered, I understand what is going on and I have a perfectly acceptable solution with
xlRow.Borders.Color = Color.LightGray​

Thank you JohnH and jmcilhinney for useful comments.
 
Top Bottom