win32com LineStyle Excel

Question

Luckily I found this site: https://www.linuxtut.com/en/150745ae0cc17cb5c866/

(There are many linetypes defined
Excel Enum XlLineStyle)

(xlContinuous    =  1
 xlDashDot       =  4
 xlDashDotDot    =  5
 xlSlantDashDot  = 13
 xlDash          = -4115
 xldot           = -4118
 xlDouble        = -4119
 xlLineStyleNone = -4142)

I run with try and except +/- 100.000 times set lines because I thought anywhere should be this [index] number for put this line in my picture too but they wasn't - why not?

How can I set this line?

Why are there some line indexes in a such huge negative range and not just 1, 2, 3...?

How can I discover things like the "number" for doing things like that?

Why is this even possible, to send apps data in particular positions, I want to step a little deeper in that, where can I learn more about this?

Answer 1

(1) You can't find the medium dashed in the linestyle enum because there is none. The line that is drawn as border is a combination of lineStyle and Weight. The lineStyle is xlDash, the weight is xlThin for value 03 in your table and xlMedium for value 08.

(2) To figure out how to set something like this in VBA, use the Macro recorder, it will reveal that lineStyle, Weight (and color) are set when setting a border.

(3) There are a lot of pages describing all the constants, eg have a look to the one @FaneDuru linked to in the comments. They can also be found at Microsoft itself: https://learn.microsoft.com/en-us/office/vba/api/excel.xllinestyle and https://learn.microsoft.com/en-us/office/vba/api/excel.xlborderweight. It seems that someone translated them to Python constants on the linuxTut page.

(4) Don't ask why the enums are not continuous values. I assume especially the constants with negative numbers serve more that one purpose. Just never use the values directly, always use the defined constants.

(5) You can assume that numeric values that have no defined constant can work, but the results are kind of unpredictable. It's unlikely that there are values without constant that result in something "new" (eg a different border style).

As you can see in the following table, not all combination give different borders. Setting the weight to xlHairline will ignore the lineStyle. Setting it to xlThick will also ignore the lineStyle, except for xlDouble. Ob the other hand, xlDouble will be ignored when the weight is not xlThick.

Sub border()
    With ThisWorkbook.Sheets(1)
        With .Range("A1:J18")
            .Clear
            .Interior.Color = vbWhite
        End With
        
        Dim lStyles(), lWeights(), lStyleNames(), lWeightNames
        lStyles() = Array(xlContinuous, xlDash, xlDashDot, xlDashDotDot, xlDot, xlDouble, xlLineStyleNone, xlSlantDashDot)
        lStyleNames() = Array("xlContinuous", "xlDash", "xlDashDot", "xlDashDotDot", "xlDot", "xlDouble", "xlLineStyleNone", "xlSlantDashDot")
        lWeights = Array(xlHairline, xlThin, xlMedium, xlThick)
        lWeightNames = Array("xlHairline", "xlThin", "xlMedium", "xlThick")
 
        Dim x As Long, y As Long
        For x = LBound(lStyles) To UBound(lStyles)
            Dim row As Long
            row = x * 2 + 3
            .Cells(row, 1) = lStyleNames(x) & vbLf & "(" & lStyles(x) & ")"
            
            For y = LBound(lWeights) To UBound(lWeights)
                Dim col As Long
                col = y * 2 + 3
                If x = 1 Then .Cells(1, col) = lWeightNames(y) & vbLf & "(" & lWeights(y) & ")"
                With .Cells(row, col).Borders
                    .LineStyle = lStyles(x)
                    .Weight = lWeights(y)
                End With
            Next
        Next
    End With
End Sub