none
Translate Excel numberformat property into VB.net to populate a visio label RRS feed

  • Question

  • I'm developing VSTO programs for both Excel and Visio.  I store a numberformat from a cell in Excel, and then I want to apply that number format to numbers that I display both on a Windows form and in a Visio diagram.

    Is there an easy way to translate Excel's numberformat property (i.e., 0.00, 0%, DD/MM/YYYY) into VB.Net terms?  Like to display a formatted number on a Window's form, or a formatted number in a shape's text in Visio?


    Gina

    Wednesday, February 12, 2014 12:15 AM

Answers

  • Hello Gina,

    It looks like your question is not related to VSTO at all. The questions is common for VB.NET programming instead.

    .NET framework provides the Format method of the System.String class for formatting strings. So, then you may easily use the formatted string on your form and Visio. You can read more about this in the Formatting Types and Custom Numeric Format Strings articles in MSDN.

    • Marked as answer by Gina1111 Wednesday, February 12, 2014 12:45 PM
    Wednesday, February 12, 2014 6:43 AM
  • Hi Gina,

    Do you want to apply the same format in Windows form controls e.g. label according to the format of a specified cell in Excel?

    If yes, I’m afraid there is no easy way to achieve the goal.

    We can use Range.NumberFormat Property to get the format, but Excel format is not the same as .Net format.

    For example, format m/d/yyyy in Excel is ‘m/d/yyyy;@’

    In Excel character ‘m’ means minute and month, refer to Format numbers as dates or times.

    In .Net format character ‘m’ only means minute, ‘M’ represents month.

    So you need to check whether the cell formats could be used in Windows form, if no, change the format to match .Net formats (As Eugene mentioned: Custom Numeric Format Strings).

    >> or a formatted number in a shape's text in Visio <<

    How do you format shape’s text in Visio? I only found we can format shapes from Format text in shapes and drawings. From my understanding, there is no such option in Visio.


    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.

    • Edited by George HuaModerator Wednesday, February 12, 2014 9:57 AM
    • Marked as answer by Gina1111 Wednesday, February 12, 2014 12:45 PM
    Wednesday, February 12, 2014 8:42 AM
    Moderator

All replies

  • Hello Gina,

    It looks like your question is not related to VSTO at all. The questions is common for VB.NET programming instead.

    .NET framework provides the Format method of the System.String class for formatting strings. So, then you may easily use the formatted string on your form and Visio. You can read more about this in the Formatting Types and Custom Numeric Format Strings articles in MSDN.

    • Marked as answer by Gina1111 Wednesday, February 12, 2014 12:45 PM
    Wednesday, February 12, 2014 6:43 AM
  • Hi Gina,

    Do you want to apply the same format in Windows form controls e.g. label according to the format of a specified cell in Excel?

    If yes, I’m afraid there is no easy way to achieve the goal.

    We can use Range.NumberFormat Property to get the format, but Excel format is not the same as .Net format.

    For example, format m/d/yyyy in Excel is ‘m/d/yyyy;@’

    In Excel character ‘m’ means minute and month, refer to Format numbers as dates or times.

    In .Net format character ‘m’ only means minute, ‘M’ represents month.

    So you need to check whether the cell formats could be used in Windows form, if no, change the format to match .Net formats (As Eugene mentioned: Custom Numeric Format Strings).

    >> or a formatted number in a shape's text in Visio <<

    How do you format shape’s text in Visio? I only found we can format shapes from Format text in shapes and drawings. From my understanding, there is no such option in Visio.


    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.

    • Edited by George HuaModerator Wednesday, February 12, 2014 9:57 AM
    • Marked as answer by Gina1111 Wednesday, February 12, 2014 12:45 PM
    Wednesday, February 12, 2014 8:42 AM
    Moderator
  • Why isn't this a VSTO question?  I'm working on VSTO applications for Excel and Visio, which means it uses VB.Net to develop an addin for Excel and Visio--an addin which allows users to click a button in the ribbon to bring up Windows forms that help them work in Excel.  This is a problem particular to translating Excel's numberformat into something VB.Net and Windows forms can understand.


    Gina

    Wednesday, February 12, 2014 12:33 PM
  • You format a shape's text in Visio by setting the values of the Shapesheet fields, using values for section, row, and cell.  For example:

    Shape.CellsSRC(section#, row#, cell#).formulaU = 10

    It takes a while to figure out what all the integers for each cell mean, but Microsoft provides this info in help guides.  For example, the cell for character style requires 1 for bold, 2 for italic, 3 for bold italic, etc.  The cell for character color requires an integer to set the color. 


    Gina

    Wednesday, February 12, 2014 12:39 PM
  • Hi Gina,

    VSTO is just base classes for your code. It is based on the .Net framework and don't provide anything for translating number formats. VSTO is responsible for loading your add-in, to get your add-in working and etc. Windows Forms is not a part of VSTO too.

    > This is a problem particular to translating Excel's numberformat into something VB.Net and Windows forms can understand.

    As you said, the task is related to Excel or VB.NET (.Net framework), not VSTO.


    Wednesday, February 12, 2014 12:42 PM
  • I guess I assumed that people who work on VSTO applications are most qualified to answer questions that use VB.Net to program addins for Excel or other Office programs.   On forums about Excel, people use VBA, and on forums about VB.Net programming, people aren't necessarily trying to program an addin for Excel.  But people working on VSTO are trying to program addins for Office programs.

    But I'll try future questions on those forums to see if they can help.

    Thank you,
    Gina


    Gina

    Wednesday, February 12, 2014 12:49 PM