none
Creating styles with borders from VSTO RRS feed

  • Question

  • Hi,

    I am developing an Excel addin in C#, using VSTO. In this addin, I create some styles and add them to the current workbook. Everything works fine until I try to set some borders on my style.

    This is the code I use:

    var style = workbook.styles.Add("My style");
    
    style.IncludeAlignment = false;
    style.IncludeFont = false;
    style.IncludeNumber = false;
    style.IncludeProtection = false;
    style.IncludePatterns = false;
    
    style.IncludeBorder = true;
    foreach (XlBordersIndex borderIndex in new[] { 
                    XlBordersIndex.xlEdgeLeft, 
                    XlBordersIndex.xlEdgeRight, 
                    XlBordersIndex.xlEdgeTop, 
                    XlBordersIndex.xlEdgeBottom })
    {
        style.Borders[borderIndex].LineStyle = XlLineStyle.xlContinuous;
    }

    I would expect this code to create a style with the four borders set, but it seems that only the left edge is set (I can see that by looking at the style object from the debugger after the loop, and in Excel by editing "My style").

    I tried recording a VBA macro to see the code generated by Excel and I obtained this:

    ActiveWorkbook.Styles.Add Name:="My style"
    With ActiveWorkbook.Styles("My style")
        .IncludeNumber = False
        .IncludeFont = False
        .IncludeAlignment = False
        .IncludeBorder = True
        .IncludePatterns = False
        .IncludeProtection = False
    End With
    With ActiveWorkbook.Styles("My style").Borders(xlLeft)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With ActiveWorkbook.Styles("My style").Borders(xlRight)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With ActiveWorkbook.Styles("My style").Borders(xlTop)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With ActiveWorkbook.Styles("My style").Borders(xlBottom)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With

    This macro works as expected. I noticed that it uses xlTop, xlLeft, etc. instead of xlEdgeTop, xlEdgeLeft, but I can't find any documentation about these constants and they are not available in the VSTO enumeration XlBordersIndex. From what I found, it seems that the latter represents the edge of the range, while the former represents the edge of each cell, but I'm not sure about this and I think the difference does not make much sense when talking about styles, which applies to a single cell AFAICT.

    Why do I have a different behavior between my C# addin and this VBA macro? How can I create a style with borders from my VSTO code?

    Thanks,

    Luc Touraille




    Wednesday, May 13, 2015 9:14 AM

Answers

  • Hello Luc,

    I was able to reproduce the issue. Please try the following code and let me know if it resolves the issue.

    var style = Globals.ThisAddIn.Application.ActiveWorkbook.Styles.Add("Mystyle");
    
                style.IncludeAlignment = false;
                style.IncludeFont = false;
                style.IncludeNumber = false;
                style.IncludeProtection = false;
                style.IncludePatterns = false;
    
                style.IncludeBorder = true;
    
                foreach (XlBordersIndex borderIndex in new[] { 
                  (XlBordersIndex)Constants.xlTop, 
                   (XlBordersIndex)Constants.xlRight, 
                    (XlBordersIndex)Constants.xlLeft, 
                    (XlBordersIndex)Constants.xlBottom })
                {
                    style.Borders[borderIndex].LineStyle = XlLineStyle.xlContinuous;
                }

    Please let me know if you have any concerns.

    Regards,

    Anush

    • Proposed as answer by AnushRudaa Wednesday, May 20, 2015 8:47 PM
    • Marked as answer by LucTouraille Thursday, May 21, 2015 7:03 AM
    Wednesday, May 20, 2015 8:47 PM

All replies

  • Hi Luc Touraille,

    I am able to reproduce the issue too and since the issue is complex I'm trying to involve some senior engineers into this issue and it will take some time.

    Your patience will be greatly appreciated.
     
    Sorry for any inconvenience and have a nice day!
     
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, May 18, 2015 9:12 AM
    Moderator
  • Hi luc

    You might have more success discussing this topic in the application-specific Excel for Developers forum, where you'll find the application specialists, but...

    On inspecting the Excel object model via the Object Browser in the VBA IDE (F2) I find that xlTop, etc. belong to the Excel.Constants object, which means they're in the scope of the Excel namespace (NOT Excel.Application).

    If that doesn't help, would you like us to move your question to the Excel forum?


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, May 20, 2015 6:33 PM
    Moderator
  • Hello Luc,

    I was able to reproduce the issue. Please try the following code and let me know if it resolves the issue.

    var style = Globals.ThisAddIn.Application.ActiveWorkbook.Styles.Add("Mystyle");
    
                style.IncludeAlignment = false;
                style.IncludeFont = false;
                style.IncludeNumber = false;
                style.IncludeProtection = false;
                style.IncludePatterns = false;
    
                style.IncludeBorder = true;
    
                foreach (XlBordersIndex borderIndex in new[] { 
                  (XlBordersIndex)Constants.xlTop, 
                   (XlBordersIndex)Constants.xlRight, 
                    (XlBordersIndex)Constants.xlLeft, 
                    (XlBordersIndex)Constants.xlBottom })
                {
                    style.Borders[borderIndex].LineStyle = XlLineStyle.xlContinuous;
                }

    Please let me know if you have any concerns.

    Regards,

    Anush

    • Proposed as answer by AnushRudaa Wednesday, May 20, 2015 8:47 PM
    • Marked as answer by LucTouraille Thursday, May 21, 2015 7:03 AM
    Wednesday, May 20, 2015 8:47 PM
  • This workaround seems to work, thanks.

    Just to be clear, this means that the Border object I get when calling Borders on a Style is not the same as the Border I get when calling Borders on a Range, is that right? They cannot be accessed in the same way, since style borders are indexed with Constants while range borders are indexed with XlBordersIndex. Perhaps the documentation of Border should explain this difference?

    Anyway thanks for your help!

    Thursday, May 21, 2015 7:03 AM