mm to inch conversion VBA

[Lumpy]

I use some specialized CAD software to design timber structures. It supports
some export to Access and has some limited Access database functionality that
the developer has provided. In German.

The module below converts a integer mm value that gets exported from the CAD
software into a table. It converts it into what I would call "architectural"
format. I need to make a very similar function that converts to what I would
call "fractional" format. Where:

28.25 = Decimal format
2'-4 1/4" = Architectural format
28 1/4" = Fractional format

Seems like it should be pretty easy but there is some rounding involved and
I don't understand the VBA code too well. Add in german variable and I'm
lost.

If someone just commented this code, I could probably hack together what I
need.

Thanks,
Kurt

Option Compare Database
Function MMToInch$(MMm)

Aantalmm = MMm + 1 / 16


AantalInch = Fix(Aantalmm / 25.4)

AantalFeet = Fix(AantalInch / 12)

AantalRestInch = AantalInch - AantalFeet * 12


FractieInch = Aantalmm / 25.4 - AantalInch

' test = FractieInch * 32
TelLer = Fix(FractieInch * 16 + 0.5)
If TelLer = 16 Then
TelLer = 0
AantalRestInch = AantalRestInch + 1
If AantalRestInch = 12 Then
AantalFeet = AantalFeet + 1
AantalRestInch = 0
End If
End If

Noemer = 16
Gedaan = False
Do
If TelLer = Fix(TelLer / 2) * 2 Then
TelLer = TelLer / 2
Noemer = Noemer / 2
Else
Gedaan = True
End If
Loop Until Gedaan Or TelLer = 0



If AantalFeet > 0 Then
Inch$ = Mid$(Str$(AantalFeet), 2) + "' "
Else
Inch$ = ""
End If

If AantalRestInch > 0 Then
Inch$ = Inch$ + Mid$(Str$(AantalRestInch), 2)
End If

If TelLer > 0 Then
Inch$ = Inch$ + " " + Mid$(Str$(TelLer), 2) + "/" +
Mid$(Str$(Noemer), 2)
End If

If TelLer > 0 Or AantalRestInch > 0 Then
Inch$ = Inch$ + Chr$(34)
End If

MMToInch$ = Inch$




End Function

 

[John Nurick]

Hi Lumpy,

It looks more like Dutch than German to me... Anyway, try this (but test it
more thoroughly than I have<g>:

Function mmToInchesFractions(millimetres As Long) As String

Dim DecimalInches As Double
Dim Inches As Long
Dim Sixteenths As Long
Dim Buffer As String
Dim Separator As String
Dim InchSign As String

Separator = " "
InchSign = ChrW(&H2033) 'Unicode double prime;
'use ASCII " if not available

DecimalInches = Abs(millimetres / 25.4)
Inches = Fix(DecimalInches)
Sixteenths = CLng((DecimalInches - Inches) * 16)
Select Case Sixteenths
Case 0
Buffer = CStr(Inches)
Case 2, 6, 10, 14
Buffer = CStr(Inches) & Separator & CStr(Sixteenths / 2) & "/8"
Case 8
Buffer = CStr(Inches) & Separator & "1/2"
Case 16 'can result from rounding
Buffer = CStr(Inches + 1)
Case 4, 12
Buffer = CStr(Inches) & Separator & CStr(Sixteenths / 4) & "/4"
Case Else
Buffer = CStr(Inches) & Separator & CStr(Sixteenths) & "/16"
End Select
If millimetres < 0 Then Buffer = "-" & Buffer 'handle negative values
mmToInchesFractions = Buffer & InchSign
End Function