Feet Inches to Decimal Values

farrissf
farrissf used Ask the Experts™
on
I have a cost estimate sheet that will require estimators to enter in a measurement that will need to multiplied to figure out the square foot value.  I would like something in VBA that would target 2 particular cells so that if the estimators want they can make the entry in a particular cell and it would convert it from any of 3 formats.  The answer should always be expressed in footage which could be the multiplied to square footage (see spreadsheet).

4' 5-1/2" =4.458 (ft)
30" = 2.5 (ft)
30 3/4" = 2.5625 (ft)

Not all of the estimators have this issue of not being able to convert this in their head so i set up a sheet that will allow them to use this if they need to or not.  I tried to be very specific on my sheet but feel free to ask any questions you have.
Converter-square-footage.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015

Commented:
Their you go..have a look Here

This gives you step by step tips about what you are trying to do..

Saurabh...

Author

Commented:
Thank you for your submission, I would really like the solution in VBA.  This eliminates the possibility of someone overwriting a formula and I eventually want to move the macro to a user box that will pop up.
Top Expert 2015

Commented:
When you say vba..what you are looking for?? Running a macro and getting a value or what exactly you are looking for??

Saurabh...
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Author

Commented:
I am looking for a macro.  Did you look at the sheet I sent a comprehensive set of requirements?  Take a look and see if that makes sense.
Roy CoxGroup Finance Manager

Commented:
Why would you want a macro, a formula would be the most efficient way. See the first block in the example where I have added a formula to produce the square footage
Converter-square-footage.xlsm

Commented:
I would suggest avoiding a macro for such a simple formula (multiplication). If your users are on their own machines and are unfamiliar with macros and security settings then it will cause more headache than it's worth and I speak from experience. You could do a few things to make the user interface more friendly and you could apply worksheet protection to prevent changes to the formula cell.

If you still prefer a macro then you might as well get the user form set up now.

Do you still want the macro or would it be acceptable without the vba?

Author

Commented:
Thank you for the comments, I have some other reasons for using macro, I like the fact that the macro can deal with syntax that I described in the requirements including the fractional measurements, because that is what the folks in the group wanted (and my boss).  As far as the security, it is not a concern as they already use several macros.  I haven't designed the user form because I am not sure how and there s some other features that need to be added to it in addition to this "converter tool".
Try this macro in the "Worksheet module"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Offset(-1) = "Convert" Then
    If Target.Offset(, 3) = "X" Then
        Target.Offset(, 1) = ft2dec(Target)
    Else
        Target.Offset(, -1) = ft2dec(Target)
    End If
End If
End Sub

Function ft2dec(x)
st = x
If InStr(st, "'") > 0 Then
    ft = Left(st, InStr(st, "'") - 1)
    st = Right(st, Len(st) - InStr(st, "'"))
End If
If InStr(st, """") > 0 Then
    If InStr(st, "-") > 0 Then
        inc = Left(st, InStr(st, "-") - 1)
        st = Right(st, Len(st) - InStr(st, "-"))
        numr = Left(st, InStr(st, "/") - 1)
        st = Right(st, Len(st) - InStr(st, "/"))
        denom = Left(st, InStr(st, """") - 1)
    ElseIf InStr(st, "/") > 0 Then
        st = 0
        numr = Left(st, InStr(st, "/") - 1)
        st = Left(st, Len(st) - InStr(st, "/"))
        denom = Left(st, InStr(st, """") - 1)
    Else
        inc = Left(st, InStr(st, """") - 1)
    End If
    ft2dec = Round(ft + (inc + numr / denom) / 12, 3)
End If
End Function

Author

Commented:
Looks good, except it doesn't like non fractional numbers on the end.  If the measurement does not end with a fraction I have to put 0/2 or some other representation for 0,  Am enclosing the worksheet with several test so you can see.  I need to be able to put whole numbers in when neccesary.  I don't mind the zeros in the "ft" place or the zero in the "in" place; however I would prefer that the double prime symbol (") denote the end  of the measurement and then I could either add the (-) followed by a fraction of an inch if necessary followed my the (").

Other than that, this may be what I am looking for...
Converter-square-footage-v3.xlsm
This should let you do away with all zeros.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Offset(-1) = "Convert" Then
     If Target.Offset(, 3) = "X" Then
         Target.Offset(, 1) = ft2dec(Target)
     Else
         Target.Offset(, -1) = ft2dec(Target)
     End If
 End If
 End Sub

 Function ft2dec(x)
 st = x
 If InStr(st, "'") > 0 Then
     ft = Left(st, InStr(st, "'") - 1)
     st = Right(st, Len(st) - InStr(st, "'"))
 End If
 If InStr(st, """") > 0 Then
     If InStr(st, "-") > 0 Then
         inc = Left(st, InStr(st, "-") - 1)
         st = Right(st, Len(st) - InStr(st, "-"))
         numr = Left(st, InStr(st, "/") - 1)
         st = Right(st, Len(st) - InStr(st, "/"))
         denom = Left(st, InStr(st, """") - 1)
     ElseIf InStr(st, "/") > 0 Then
         st = 0
         numr = Left(st, InStr(st, "/") - 1)
         st = Left(st, Len(st) - InStr(st, "/"))
         denom = Left(st, InStr(st, """") - 1)
     Else
         inc = Left(st, InStr(st, """") - 1)
         denom = 1
     End If
     ft2dec = Round(ft + (inc + numr / denom) / 12, 3)
 End If
 End Function

Author

Commented:
Works perfect!  Thank you.

Author

Commented:
Just what I needed!  Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial