excelvbaformatnumbersdisplay

VBA - Cell format type changed from General to Number when value is displayed

I'm facing an issue for days now, and hoping that someone can help me here. Basically I'm creating a report of a calculation in a new Excel sheet but when I display the values, the decimal separators are sometimes used other times are not, even though the code is exactly the same for both cases. I attached some pictures to highlight the problem:

  1. Image 1 Image 1 On the first one you can see the userform, and that the number formats are correct (my default separator is comma).
  2. Image 2 Image 2 On the second image the problem is visible: while the cell representing the value of Max. allowable stress for TRoom uses the decimal separator, the one for TDesign textbox does not.
  3. Image 3 Image 3 The third picture is meant to highlight that there is no difference between the two codes.

The core of the problem (probably) is that the cell format changes from General to Number as soon as the value is displayed in the cell. I couldn't find a way to prevent Excel from doing that and also don't understand why it is not an issue for the row above that.

Do you have any ideas? Thank you in advance!

I tried to change the format type for the entire column and also converting the value to string when displaying it in the cell, but it doesn't seem to work.

Public Sub create_report_H(uf As UserForm)

' Adding new sheet to create report on it:
Workbooks("ASME VIII Div.1 Formed head - Macro").Activate
Call Report.add_ws_after_others

' Set worksheet:
Set ws_rep = Worksheets(ActiveSheet.Name)
ws_rep.Protect UserInterfaceOnly:=True

' A4 dimensions:
ws_rep.PageSetup.PaperSize = xlPaperA4
ActiveWindow.View = xlPageLayoutView

Application.PrintCommunication = False
With ActiveSheet.PageSetup
        .LeftHeader = "&G"
        .LeftHeaderPicture.Height = 30
        .RightHeader = "&D &I - &I &T" '"&D &B&ITime:&I&B&T"
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .CenterFooter = "&P"
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
End With
    
Application.PrintCommunication = True
    
' Page setup:
Dim last_col As String
Dim first_row_height As Double
Dim row_height As Double
last_col = "I"
row_height = 15
ws_rep.Range("H:H").NumberFormat = "General"
    
' Material:
ws_rep.Cells(9, "A").Value = "Material"
ws_rep.Cells(9, "A").Font.Size = 14

' Label:
ws_rep.Cells(10, "A").Value = "Product from:"
ws_rep.Cells(11, "A").Value = "ASTM specification:"
ws_rep.Cells(12, "A").Value = "Grade:"
ws_rep.Cells(13, "A").Value = "Max. allowable stress (TDesign):"
ws_rep.Cells(13, "A").Characters(25, 6).Font.Subscript = True
ws_rep.Cells(14, "A").Value = "Max. allowable stress (TRoom):"
ws_rep.Cells(14, "A").Characters(25, 4).Font.Subscript = True
ws_rep.Cells(15, "A").Value = "Corrosion allowance:"

' Values:
ws_rep.Cells(10, "H").Value = "Plate"
ws_rep.Cells(11, "H").Value = uf.CB_spec.Value
ws_rep.Cells(12, "H").Value = uf.CB_grade.Value
ws_rep.Cells(13, "H").Value = CStr(uf.TB_MAWS_DT.Value)
ws_rep.Cells(14, "H").Value = CStr(uf.TB_MAWS_RT.Value)
ws_rep.Cells(15, "H").Value = uf.TB_corrosion.Value
ws_rep.Range(ws_rep.Cells(10, "H"), ws_rep.Cells(15, "H")).HorizontalAlignment = xlRight
    
' Units:
ws_rep.Cells(13, "I").Value = " " & uf.Label_stress_unit_DT.Caption
ws_rep.Cells(14, "I").Value = " " & uf.Label_stress_unit_DT.Caption
ws_rep.Cells(15, "I").Value = " " & Geometry.Label_E_D.Caption
    
ws_rep.Range(Cells(9, "A"), Cells(9, last_col)).Interior.Color = RGB(255, 255, 0)
ws_rep.Range(Cells(10, "A"), Cells(15, last_col)).Interior.Color = RGB(230, 230, 230)
Solution

  • I believe this issue could be attributed to the language and locale that your Excel is set in. VBA interprets formulas and values according to the US locale configuration, regardless of the current locale configuration in your system. This tends to be an issue for some countries that have different format rules regarding values, in my case:

    These are a few instances that need to be considered when programming in VBA.

    In your case, it seems that your Excel is set up to interpret a number followed by a comma and 3 or more numbers as a thousand separator. To solve this issue, I would recommend to change the decimal and thousand separator to the desired characters.

    Instructions are available in Microsoft Support Change the character used to separate thousands or decimals

    Alternatively, if you wish EXCEL not to convert the string into a number, and keep it as a string change the line of code to the following:

    ws_rep.Cells(13, "H").Value = "'" & CStr(uf.TB_MAWS_DT.Value)

    Otherwise, you could take the value of your Userform and adapt the value to accomodate the decimal separator according to the interpretation of US locale to indicate that the value is a float and atferwards when its inserted into your worksheet it will adapt to the locale of your system:

    ws_rep.Cells(13, "H").Value = CDec(Replace(CStr(uf.TB_MAWS_DT.Value), ",", "."))

    Hope it helps!

    Edit: Second alternative