Programmatically adding a style assumes number format as “General”

 

Hello All,

Few days back, I’ve seen an interesting scenario; If you add a “Style” programmatically, without specifying the number format as anything, it is assumed to be “General”.

Something like below is fine ..

 Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oXL As Excel.Application
        Dim oWB As Excel.Workbook
       

        Dim str As String

        str = "SomeStyle"
        Try

            oXL = CreateObject("Excel.Application")
            oXL.Visible = True

            ' Get a new workbook.
            oWB = oXL.Workbooks.Add

            oXL.ActiveWorkbook.Styles.Add(str) ' Add a new style to the workbook

            'Define the style formatings

            oXL.ActiveWorkbook.Styles(str).NumberFormat = "#,##0.0" 
            With oXL.ActiveWorkbook.Styles(str).Font
                .Name = "Times New Roman"
                .Size = 15
                .Bold = True
                .Italic = True   
                .Strikethrough = False   
            End With

            With oXL.ActiveWorkbook.Styles(str)
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
                .VerticalAlignment = Excel.XlVAlign.xlVAlignTop
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .ShrinkToFit = False
                .IncludeBorder = False
            End With

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        End Try

But if I comment out the line “.NumberFormat=”#,##0.0”, then the number format for “SomeStyle” is assumed to be “General”, which is .. well .. kind of fine in most cases.

Now, imagine a scenario, where you have a lot of different number formatting in different cells, (e.g. 9.7489900 is displayed as 9.74), and then if you apply a style, which has a “General” format, it would reset the number format back .. which means, now you have 9.7489900! I’m sure you won’t like it .. if you specifically omitted NumberFormating.

The only workaround I see is .. use something like below:

    Public Sub ApplyStyle(str As String)

        Select Case str
            Case "Style1"
                With oXL.Selection.Font
                    .Name = "Times New Roman"
                    .Color = Color.Blue
                    .Strikethrough = False
                End With

                With oXL.Selection
                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignTop

                End With

            Case "Style2"

                With oXL.Selection.Font
                    .Name = "Times New Roman"
                    .Color = Color.Red
                    .Strikethrough = False
                End With

                With oXL.Selection

                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignTop
                    .NumberFormat = "General"

                    

                End With
        End Select

    End Sub

In this scenario .. you don’t really apply a style to the Workbook, you just have the code which decides as to, what is to be done @ runtime.

Okay! Bye …