[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
lumberjack_jeff
Newbie

Joined: 04 Mar 2012
Posts: 1

Posted: Sun Mar 04, 2012 5:46 pm    Post subject: convert decimal feet to Feet and inches

A VB script is available from MrExcel
http://www.mrexcel.com/articles/excel-feet-to-inches.php

to convert decimal feet to feet, inches and sixteenths - and vice versa.

I don't know how to convert vb scripts to OOo macros. Can anyone help?

this script (LenText) converts 2.135 feet to 2' 1 5/8"

 Code: Public Function LenText(FeetIn As Double)     ' This function will change a decimal number of feet to the text string     ' representation of feet, inches, and fractional inches.     ' It will round the fractional inches to the nearest 1/x where x is the denominator.     ' Copyright 1999 MrExcel.com     Denominator = 32 ' must be 2, 4, 8, 16, 32, 64, 128, etc.     NbrFeet = Fix(FeetIn)     InchIn = (FeetIn - NbrFeet) * 12     NbrInches = Fix(InchIn)     FracIn = (InchIn - NbrInches) * Denominator     Numerator = Application.WorksheetFunction.Round(FracIn, 0)     If Numerator = 0 Then         FracText = ""     ElseIf InchIn >= (11 + (31.4999999 / 32)) Then         NbrFeet = NbrFeet + 1         NbrInches = 0         FracText = ""     ElseIf Numerator = Denominator Then         NbrInches = NbrInches + 1         FracText = ""        Else         Do             ' If the numerator is even, divide both numerator and divisor by 2             If Numerator = Application.WorksheetFunction.Even(Numerator) Then                 Numerator = Numerator / 2                 Denominator = Denominator / 2             Else                 FracText = " " & Numerator & "/" & Denominator                 Exit Do             End If         Loop        End If     LenText = NbrFeet & "' " & NbrInches & FracText & """" End Function

this script (feet) converts 2' 1 5/8" to 2.135 feet

 Code: Public Function feet(LenString As String)     Dim FootSign As Integer     Dim InchSign As Integer     Dim SpaceSign As Integer     Dim FracSign As Integer     Dim InchString As String     Dim Word2 As String     ' Copyright 1999, 2005 MrExcel.com     LenString = Application.WorksheetFunction.Trim(LenString)     'The find function returns an error when the target is not found     'Resume Next will prevent VBA from halting execution.     On Error Resume Next     FootSign = Application.WorksheetFunction.Find("'", LenString)     If IsEmpty(FootSign) Or FootSign = 0 Then         ' There are no feet in this expression         feet = 0         FootSign = 0     Else         feet = Val(Left(LenString, FootSign - 1))     End If     ' Handle the case where the foot sign is the last character     If Len(LenString) = FootSign Then Exit Function     ' Isolate the inch portion of the string     InchString = Application.WorksheetFunction.Trim(Mid(LenString, FootSign + 1))     ' Strip off the inch sign, if there is one     InchSign = Application.WorksheetFunction.Find("""", InchString)     If Not IsEmpty(InchSign) Or InchSign = 0 Then         InchString = Application.WorksheetFunction.Trim(Left(InchString, InchSign - 1))     End If       ' Do we have two words left, or one?     SpaceSign = Application.WorksheetFunction.Find(" ", InchString)     If IsEmpty(SpaceSign) Or SpaceSign = 0 Then         ' There is only one word here.  Is it inches or a fraction?         FracSign = Application.WorksheetFunction.Find("/", InchString)         If IsEmpty(FracSign) Or FracSign = 0 Then             'This word is inches             feet = feet + Val(InchString) / 12         Else             ' This word is fractional inches             feet = feet + (Val(Left(InchString, FracSign - 1)) / Val(Mid(InchString, FracSign + 1))) / 12         End If     Else         ' There are two words here.  First word is inches         feet = feet + Val(Left(InchString, SpaceSign - 1)) / 12         ' Second word is fractional inches         Word2 = Mid(InchString, SpaceSign + 1)         FracSign = Application.WorksheetFunction.Find("/", Word2)         If IsEmpty(FracSign) Or FracSign = 0 Then             ' Return an error             feet = "VALUE!"         Else             If FracSign = 0 Then                 feet = "VALUE!"             Else                 feet = feet + (Val(Left(Word2, FracSign - 1)) / Val(Mid(Word2, FracSign + 1))) / 12             End If         End If     End If End Function
Robert Tucker
Moderator

Joined: 16 Aug 2004
Posts: 3407
Location: Manchester UK

 Posted: Mon Mar 05, 2012 1:25 am    Post subject: For decimal feet to fraction see pitonyak's post here: http://www.oooforum.org/forum/viewtopic.phtml?t=14674 with it's link to http://www.pitonyak.org/oo.php Click and download "OpenOffice.org Macro document"._________________OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
karolus

Joined: 22 Jun 2011
Posts: 210

Posted: Mon Mar 05, 2012 3:18 am    Post subject:

Hallo
You can do the job with Python:

 Code: # -*- coding: utf-8 -*- import re ft_re = re.compile(r'''(\d+)' (\d+) (\d+)/(\d+)"''') def to_feet_fraction( number ):         """ convert decimalvalue like 1.789 into         stringrepresentation of feet and inch-fractions:         1.789 → 1' 9 15/32"     """     delta = 10**-3  #adjust delta     if abs( number - round(number,0)) <= delta:         return "{0:n}'".format(round(number,0))         denominator = 512 # 2, 4, 8, 16,32,64...     feet, inchpart = divmod(number, 1)     inch = inchpart * 12     fixin, fracin = divmod( inch, 1)     fracin *= denominator     nominator = round(fracin,0)     while not nominator % 2:         nominator /=2         denominator /= 2             return '''{0:n}' {1:n} {2:n}/{3:n}"'''.format(feet, fixin,                                                   nominator, denominator) def ft_fraction_to_decimal( ft_string ):         """convert 'feet inch-fraction' Stringrepresentation to Decimalvalue"""         dec = ft_re.match(ft_string)     ft, inch, nom, denom = map(float,dec.groups())     erg = ft + (inch + nom / denom) /12     return erg #___for test____ # print ft_fraction_to_decimal(to_feet_fraction( 0.789 ))

To use as Calc-function :
1. build a Dummy-bridge via Basic see http://www.oooforum.org/forum/viewtopic.phtml?t=59534&start=0&postdays=0&postorder=asc&highlight=
2. or implement it as Calc-addin see http://www.biochemfusion.com/doc/Calc_addin_howto.html

Karo
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum