Dynamically setting of cells' NumberFormat in VBA excel when the uncertainty calculation in consideration [closed]

Question

I need dynamically set via VBA NumberFormat to imported txt file values, as just below : Imported values number type is "General" so I want to change to "Number" but preserve the decimal point

12.324 ............................ 3 decimal points
2.12 ................................ 2 decimal points
0.00123 ...........................5 decimal points
12.1234567 .................... 7 decimal points

and all the data in the same column.

Code is simple:

    Range("A1").Numberformat ="##0.0##"
    Range("A2").Numberformat ="#0.0#"
    Range("A3").Numberformat ="#0.0####"
    Range("A4").Numberformat ="##0.0######"

so I need dynamically set "#"

Ok, I get the solution.

  res_dec_point = Len(Split(res_val & ",", ",")(1))
  kes_dec_point = Len(Split(kes_val & ",", ",")(1))

  MyNumberFormat_res = "0."
  For i_num = 1 To res_dec_point
    MyNumberFormat_res = MyNumberFormat_res & "0"
  Next i_num
  
  MyNumberFormat_kes = "0."
  For i_num = 1 To kes_dec_point
    MyNumberFormat_kes = MyNumberFormat_kes & "0"
  Next i_num

Thank you https://stackoverflow.com/users/10968406/bdra

Answer 1

In a nutshell, as I understand it, your code needs to read decimal numbers formatted as string values, then write them to destination cells formatted as decimal numbers. Each destination cell needs to be formatted with as many decimal places as are present in the original string value.

You did not include your code in the question, so I will not provide completed code in response, but in outline here is a way to approach it:

1. Identify the number of decimal places in the original string value. Use the Instr function to locate the decimal point, then count the number of numerals to the right of the decimal point.

2. Number formats themselves are expressed as string values. For instance, in Range("A1").Numberformat ="0.0", the "0.0" part is a string. You can utilize this to your advantage. Either look up or dynamically generate a string value that expresses the required number format for each cell.

3. Assign the number format to the destination cell.

Here is a snippet that generates and assigns a number format with a variable number of decimal places:

Dim MyNumberFormat As String, NumberOfDecimals As Integer
    
MyNumberFormat = "0."
    
For i = 1 To NumberOfDecimals
    MyNumberFormat = MyNumberFormat & "0"
Next

MyWorksheet.Cells(1, 1).NumberFormat = MyNumberFormat

I believe this answers the core of your question. You will need to build it out with your own code, define what happens if NumberOfDecimals is 0, etc.

Answer 2

The formatting of a value does not change the value

In this example we calculate 2/3, and show it using 'General format', and using 3,4, or 5 decimals.

On the last line we substract 0.666 from the values in the first line. The green values are all in 'General format'.

Conclusion: The formatting does not change the value!