What are NumberFormat Options In Excel VBA

Question

Can you please let me know what are the  NumberFormat format options in Excel VBA  As you are fully aware Excel 2010 supports the following types   I know that we can set for example Text type as   NumberFormat   quot   quot   or for number   NumberFormat    quot 0 00000 quot   Can you please let me know what are other options for types in VBA

User · Answer

Thanks to this question  and answers   I discovered an easy way to get at the exact NumberFormat string for virtually any format that Excel has to offer     How to Obtain the NumberFormat String for Any Excel Number Format    Step 1  In the user interface  set a cell to the NumberFormat you want to use     In my example  I selected the Chinese  PRC  Currency from the options contained in the  Account Numbers Format  combo box   Step 2  Expand the Number Format dropdown and select  More Number Formats         Step 3  In the Number tab  in Category  click  Custom      The  Sample  section shows the Chinese  PRC  currency formatting that I applied   The  Type  input box contains the NumberFormat string that you can use programmatically   So  in this example  the NumberFormat of my Chinese  PRC  Currency cell is as follows         -804       0 00         -804   -    0 00         -804    -              If you do these steps for each NumberFormat that you desire  then the world is yours   I hope this helps

User · Answer

In Excel  you can set a Range NumberFormat to any string as you would find in the  Custom  format selection   Essentially  you have two choices    General for no particular format  A custom formatted string  like       0   to specify exactly what format you re using

User · Answer

dovers gives us his great answer and based on it you can try use it like   public static class CellDataFormat           public static string General   get   return  General               public static string Number   get   return  0                   Your custom format          public static string NumberDotTwoDigits   get   return  0 00                public static string Currency   get   return       0 00  Red      0 00               public static string Accounting   get   return           0 00             0 00          -                            public static string Date   get   return  m d yy               public static string Time   get   return    -F400  h mm ss am pm               public static string Percentage   get   return  0 00                public static string Fraction   get   return                      public static string Scientific   get   return  0 00E 00               public static string Text   get   return                  public static string Special   get   return                   public static string Custom   get   return      0    Red      0

User · Answer

The  NET Library EPPlus implements a conversation from the string definition to the built in number  See class ExcelNumberFormat   internal static int GetFromBuildIdFromFormat string format        switch  format                case  General               return 0          case  0               return 1          case  0 00               return 2          case      0               return 3          case      0 00               return 4          case  0                return 9          case  0 00                return 10          case  0 00E 00               return 11          case                      return 12          case                        return 13          case  mm-dd-yy               return 14          case  d-mmm-yy               return 15          case  d-mmm               return 16          case  mmm-yy               return 17          case  h mm AM PM               return 18          case  h mm ss AM PM               return 19          case  h mm               return 20          case  h mm ss               return 21          case  m d yy h mm               return 22          case      0       0                return 37          case      0   Red      0                return 38          case      0 00      0 00                return 39          case      0 00  Red                     return 40          case  mm ss               return 45          case   h  mm ss               return 46          case  mmss 0               return 47          case    0 0               return 48          case                  return 49          default              return int MinValue            When you use one of these formats  Excel will automatically identify them as a standard format

User · Answer

Note this was done on Excel for Mac 2011 but should be same for Windows  Macro   Sub numberformats     Dim rng As Range   Set rng   Range  A24 A35     For Each c In rng     Debug Print c NumberFormat   Next c End Sub   Result   General     General Number      0 Currency         0 00  Red      0 00 Accounting           0 00             0 00        -            Date        m d yy Time          -F400 h mm ss am pm Percentage  0 00  Fraction          Scientific  0 00E 00 Text          Special        Custom          0    Red      0     I just picked a random entry for custom

[excel] What are .NumberFormat Options In Excel VBA?

How to Obtain the NumberFormat String for Any Excel Number Format

Examples related to excel

Examples related to vba