Doubt in the macro to print zero after decimal places.

[raviprakash]

Hi,
I have a code which converts inch dimensions to mm. The problem is it won't print the zero after decimals like if the output is 1.20, it will print 1.2.

Please check the below code and let me know how to overcome this problem.

Function inchTomm(max As Double, min As Double, convtype As String)
  Dim decimals As Integer
  Dim tolerance As Double
  Dim significant_digits As Integer
   
  tolerance = max - min
   
   
   
  significant_digits = Len(CStr(max)) - Len(CStr(Application.WorksheetFunction.RoundUp(max, 0)))
   
  tolerance = Application.WorksheetFunction.RoundUp(tolerance, significant_digits)
   
  If tolerance >= 0 And tolerance < 0.0004 Then
  decimals = 4
  Else
  If tolerance >= 0.0004 And tolerance < 0.004 Then
  decimals = 3
  Else
  If tolerance >= 0.004 And tolerance < 0.04 Then
  decimals = 2
  Else
  If tolerance >= 0.04 And tolerance < 0.4 Then
  decimals = 1
  Else
  If tolerance >= 0.4 Then
  decimals = 0
  End If
  End If
  End If
  End If
  End If
   
  If convtype = "max" Or convtype = "Max" Or convtype = "MAX" Then
  inchTomm = Application.WorksheetFunction.RoundDown(max * 25.4, decimals)
  Else
  If convtype = "min" Or convtype = "Min" Or convtype = "MIN" Then
  inchTomm = Application.WorksheetFunction.RoundUp(min * 25.4, decimals)
  '& " d: " & decimals & " t: " & tolerance
  End If
  End If
End Function
&

If anyone knows, please help me.

Thanks in advance.

Regards,
Ravi

 

[Hui]

Select the cell/s where you call the Function
Ctrl+1
Select Number Tab
Select Custom
in the Type dialog enter: #,##0.00

enjoy

The functions job is to return a Number
The cell's job is to display it as you require

 

[raviprakash]

@Hui ,It is working fine. But, we need to do it manually.We are trying to have it in macro itself.

 

[Hui]

Functions cannot change the format of a cell, but you can return the answer as a String
eg:

Function inchTomm(max As Double, min As Double, convtype As String) As String
  Dim decimals As Integer
  Dim tolerance As Double
  Dim significant_digits As Integer
   
  tolerance = max - min
 
  significant_digits = Len(CStr(max)) - Len(CStr(Application.WorksheetFunction.RoundUp(max, 0)))
   
  tolerance = Application.WorksheetFunction.RoundUp(tolerance, significant_digits)
   
  If tolerance >= 0 And tolerance < 0.0004 Then
  decimals = 4
  Else
  If tolerance >= 0.0004 And tolerance < 0.004 Then
  decimals = 3
  Else
  If tolerance >= 0.004 And tolerance < 0.04 Then
  decimals = 2
  Else
  If tolerance >= 0.04 And tolerance < 0.4 Then
  decimals = 1
  Else
  If tolerance >= 0.4 Then
  decimals = 0
  End If
  End If
  End If
  End If
  End If
   
  If LCase(convtype) = "max" Then
  inchTomm = Application.WorksheetFunction.RoundDown(max * 25.4, decimals)
   
  Else
  If LCase(convtype) = "min" Then
  inchTomm = Application.WorksheetFunction.RoundUp(min * 25.4, decimals)
  End If
  End If
 
  Select Case decimals
  Case 0
  inchTomm = Format(CStr(inchTomm), "#,##0")
  Case 1
  inchTomm = Format(CStr(inchTomm), "#,##0.0")
  Case 2
  inchTomm = Format(CStr(inchTomm), "#,##0.00")
  Case 3
  inchTomm = Format(CStr(inchTomm), "#,##0.000")
  Case 4
  inchTomm = Format(CStr(inchTomm), "#,##0.0000")
  Case Else
  inchTomm = Format(CStr(inchTomm), "#,##0.00")
  End Select

End Function

 

[Hui]

can you please post a normal example of a functions use?
eg: is =inchTomm(100.0025, 100,"min") ok?

 

[Hui]

also simplifying your code

Function inchTomm(max As Double, min As Double, convtype As String) As String
  Dim decimals As Integer
  Dim tolerance As Double
  Dim significant_digits As Integer
   
  tolerance = max - min
 
  significant_digits = Len(CStr(max)) - Len(CStr(Application.WorksheetFunction.RoundUp(max, 0)))
   
  tolerance = Application.WorksheetFunction.RoundUp(tolerance, significant_digits)
   
  If tolerance >= 0 And tolerance < 0.0004 Then
  decimals = 4
  ElseIf tolerance >= 0.0004 And tolerance < 0.004 Then
  decimals = 3
  ElseIf tolerance >= 0.004 And tolerance < 0.04 Then
  decimals = 2
  ElseIf tolerance >= 0.04 And tolerance < 0.4 Then
  decimals = 1
  ElseIf tolerance >= 0.4 Then
  decimals = 0
  End If
   
  If LCase(convtype) = "max" Then
  inchTomm = Application.WorksheetFunction.RoundDown(max * 25.4, decimals)
  Else
  inchTomm = Application.WorksheetFunction.RoundUp(min * 25.4, decimals)
  End If
 
  Select Case decimals
  Case 0
  inchTomm = Format(CStr(inchTomm), "#,##0")
  Case 1
  inchTomm = Format(CStr(inchTomm), "#,##0.0")
  Case 2
  inchTomm = Format(CStr(inchTomm), "#,##0.00")
  Case 3
  inchTomm = Format(CStr(inchTomm), "#,##0.000")
  Case 4
  inchTomm = Format(CStr(inchTomm), "#,##0.0000")
  Case Else
  inchTomm = Format(CStr(inchTomm), "#,##0.00")
  End Select

End Function

 

[raviprakash]

@Hui , Thanks a lot.
Problem solved.
The code is working fine.

 

[Hui]

Note that it is returning text, not a number
You can deal with that if it is required in further calculations
just use =Value(cell reference where the =InchTomm function is)