none
HorizontalAlignment

    Question

  • Hello,

    (Disclaimer: I am new to VB .Net)

    I am trying to create an application that automates a spreadsheet (creates worksheets, populates and formats). I want to center the column heading text.

    This seems to work:

    xlsheet.range("1:1").HorizontalAlignment = -4108

    Why does this work (using -4108)? I suspect this is not the obvious way to do this.

     

    Monday, February 06, 2006 7:23 PM

Answers

  • Hi,

    The code that you wrote works because it is valid syntax, but it isn't a good way to code.  Your question illustrates the problem; I can't tell what this code does by reading it because I have no idea what -4108 is supposed to mean. 

    A better way to write this code would be to replace -4108 with symbol that has meaning to humans but that the compiler will still evaluate as -4108.  As it turns out, Excel has already defined a symbolic constant to represent this value: xlCenter.

    So a better way to write that code would be something like:

    xlsheet.range("1:1").HorizontalAlignment = Constants.xlCenter

    Note that the example above assumes that you have the Excel namespace imported.

    I'm not sure how you came up with the definition for xlCenter (-4108), but the help topic for the property hints at its existence.  In the example, they use xlLeft.  With that information, the approach I would take would be to go to the View menu, choose Object Browser, and then type xlLeft in the search combo box.  That would have shown me that xlLeft was a member of Microsoft.Office.Interop.Excel.Constants.  Clicking on "Constants" enables me to browse the class and I would have found all of the constants available to me.  Since they are pretty self explanatory, I would have seen xlCenter and understood that was what I needed to use to get the HorizontalAlignment property to align center.

    Sincerely,

    Geoff Darst

    Microsoft VSTO Team

    Monday, February 06, 2006 10:17 PM

All replies

  • Hi,

    The code that you wrote works because it is valid syntax, but it isn't a good way to code.  Your question illustrates the problem; I can't tell what this code does by reading it because I have no idea what -4108 is supposed to mean. 

    A better way to write this code would be to replace -4108 with symbol that has meaning to humans but that the compiler will still evaluate as -4108.  As it turns out, Excel has already defined a symbolic constant to represent this value: xlCenter.

    So a better way to write that code would be something like:

    xlsheet.range("1:1").HorizontalAlignment = Constants.xlCenter

    Note that the example above assumes that you have the Excel namespace imported.

    I'm not sure how you came up with the definition for xlCenter (-4108), but the help topic for the property hints at its existence.  In the example, they use xlLeft.  With that information, the approach I would take would be to go to the View menu, choose Object Browser, and then type xlLeft in the search combo box.  That would have shown me that xlLeft was a member of Microsoft.Office.Interop.Excel.Constants.  Clicking on "Constants" enables me to browse the class and I would have found all of the constants available to me.  Since they are pretty self explanatory, I would have seen xlCenter and understood that was what I needed to use to get the HorizontalAlignment property to align center.

    Sincerely,

    Geoff Darst

    Microsoft VSTO Team

    Monday, February 06, 2006 10:17 PM
  • Thanks Geoff,

    I agree, not a good way to code. That was my problem, I didn't know where to look up the available options in the object browser (under Microsoft.Office.Interop.Excel.Constants).

    I got the -4108 (and the values for xlLeft and xlRight) by assigning the value of HorizontalAlignment to a variable (after altering it in the worksheet), then displaying that variable.

    Monday, February 06, 2006 10:34 PM
  • I had the same problem in developing an Add-in for Excel.

    I solved in the following manner:
    Defining a range
    Defining a style with HorizontalAlignment
    Applying the style to the range.

    Some code:

    Defining app.
    private Excel.Application app = Globals.ThisAddIn.Application;
    The range:
    Excel.Worksheet _sheet = app.ActiveWorkbook.ActiveSheet as Excel.Worksheet;
    Excel.Range _header = _sheet.get_Range("A1:C1", System.Type.Missing);
    _header.MergeCells = true;
    _header.Value2 = "Header";
    The style:
    Excel.Style _style = Globals.ThisWorkbook.Styles.Add("Header", missing);
                        _style.Font.Name = "Verdana";
                        _style.Font.Size = 14;
                        _style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                        _header.Style = "Header"; 
    Just for your (and potentially others) interest.



    Thursday, July 30, 2009 9:58 PM
  • Context:  an hta application that formats an Excel spreadsheet.  The VBScript in the hta cannot make use of the xl constants.  Use -4131 in place of HorizontalAlignment of a cell, -4108 in place of VerticalAlignment of a cell.   One might construct a common external VBScript file that would "revariable" the xl constants to provide standard and standardized coding throughout a collection of hta applications.

    The integer values can be obtained by running msgbox(xlHAlignCenter) in a subroutine in a VBA IDE.  Put all the xl constant names in an array and do a Debug.print to obtain a listing.
    Sunday, September 27, 2009 7:11 PM
  • the negative and positive values you used are valid but a more user friendly method is to use the constant name.

    See this link for all the Excel Constants and their associated values

    http://msdn.microsoft.com/en-us/library/aa221100%28office.11%29.aspx

    Its better to see "xlCenter" than a number that means nothing to you.

     

    Wednesday, May 19, 2010 3:59 PM