none
Style properties RRS feed

  • Question

  • Hi All,

    I want to read out  the builtin styles but I fail to get the borders of the "calculation" style.

    ?  thisworkbook.Styles("Calculation").Borders(xlEdgeLeft).LineStyle returns NULL although it has borders, any idea how to read this property?

    Or more in general, read the borders of a builtin style.

    Basic code, using a class module to store..

    With ThisWorkbook
          For intCount = 1 To .Styles.Count
             If .Styles(intCount).BuiltIn Then
             Set clsStyle = New clsStyles
             
             clsStyle.Name = .Styles(intCount).Name
             clsStyle.InteriorColor = .Styles(intCount).Interior.Color
             clsStyle.FontBold = .Styles(intCount).Font.Bold
             clsStyle.FontColor = .Styles(intCount).Font.Color
             clsStyle.FontItalic = .Styles(intCount).Font.Italic
             clsStyle.FontSize = .Styles(intCount).Font.size
             clsStyle.FontName = .Styles(intCount).Font.Name
             clsStyle.FontUnderline = IIf(.Styles(intCount).Font.Underline < 0, 0, .Styles(intCount).Font.Underline)
             
             dicStyles.Add Key:=.Styles(intCount).Name, Item:=clsStyle
             '''Debug.Print .Styles(intCount).Name, .Styles(intCount).Font.Underline
             End If
          Next intCount
       End With

    Thanks for your time.

    Kind regards,

    JP Ronse


    • Edited by JP Ronse Saturday, July 8, 2017 2:26 PM
    Saturday, July 8, 2017 2:25 PM

Answers

  • Hi JP, this appears to be a serious flaw of Excel VBA. The only border properties of built-in styles that are actually populated are Color and ColorIndex. Other properties such as LineStyle, ThemeColor, TintAndShade and Weight are all blank.

    As a workaround, you could set the style of a cell to .Styles(intCount), then read the border properties of that cell in your code. Not elegant, but <shrug>.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JP Ronse Sunday, July 9, 2017 8:38 AM
    Saturday, July 8, 2017 4:02 PM

All replies

  • Hi JP, this appears to be a serious flaw of Excel VBA. The only border properties of built-in styles that are actually populated are Color and ColorIndex. Other properties such as LineStyle, ThemeColor, TintAndShade and Weight are all blank.

    As a workaround, you could set the style of a cell to .Styles(intCount), then read the border properties of that cell in your code. Not elegant, but <shrug>.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JP Ronse Sunday, July 9, 2017 8:38 AM
    Saturday, July 8, 2017 4:02 PM
  • Hi Hans,

    Thanks for the swift reply. That is indeed a serious flaw, I'll write my own definitions.

    Regards,

    JP

    Sunday, July 9, 2017 8:40 AM