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

Question

I need to dynamically set the NumberFormat, via VBA, to imported text 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 to dynamically set "#".

The reason for setting the number after decimal point is very important for calculating the uncertainty result. In calculations, there is a need for the resolution which means how many digits after the decimal point appears on the measurement device that doing calibrations.

---

I found a 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.