Consulting

Results 1 to 13 of 13

Thread: Entering Fractions in Dimensions Calculator

  1. #1

    Entering Fractions in Dimensions Calculator

    Hi Gurus,
    I have created a short script to plug in some dimensions for building frames for some paintings I purchased. This gave me an opportunity to create the painting dimensions calculator. The userform works well with decimals but when it comes to entering fractions it won't work. I've looked all over the internet to try to find a solution to not avail. Some places indicate a formatting of "#???/???" but I tried it and it didn't work. Does anyone have any idea how to resolve this? I'm at a loss. I'm attaching the file I've created for your and other member's information.

    Thank you all
    Attached Files Attached Files

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    This assumes a single space between number and fraction "5 3/4", a decimal, integer or a fraction.
    Dim PH, TR  '.etc
    
    
    Private Sub CommandButton1_Click()
    TotalMattingOpeningHeight = PH + TR 'etc
    End Sub
    
    
    Sub Fractions()
        PH = Convert(PaintingHeight)
        TR = Convert(TopReveal)
        'etc.
    End Sub
    
    
    Function Convert(x)
        Dim Dim1, Dim2
        If InStr(1, x, " ") Then
            If InStr(1, x, "/") Then
                Dim1 = Split(x)(0)
                Dim2 = Split(Split(x)(1), "/")(0) / Split(Split(x)(1), "/")(1)
                Convert = Trim(Dim1 + Dim2)
            Else
                Convert = Trim(x)
            End If
        Else
            If InStr(1, x, "/") Then
                Dim2 = Split(x, "/")(0) / Split(x, "/")(1)
                Convert = Trim(Dim2)
            Else
                Convert = Trim(x)
            End If
        End If
    End Function
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    Thank you so much mdmackillop for responding so quickly. Your script definitely enables me to enter fractions in the textboxes, however, I haven't been able to produce results in the total textboxes. Could you review my changes to see what I might be doing wrong. Thanks again for all the help.
    Attached Files Attached Files

  4. #4
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Can you post appropriate numbers in a workbook that I can import into the form
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    Thank you

    Data
    Enter Painting Height Enter Painting Width Enter Top Reveal Enter Bottom Reveal Enter Left Reveal Enter Right Reveal Enter Matting Border Enter Expansion Space Enter Rabbet Depth Enter Total Width of Moulding
    17 21 3/4 1/2 3/4 1/2 1/2 3 1/16 5/16 2 3/4
    Result:

    Total Matting Opening Height
    Total Matting Opening Width Total Matting Height Total Matting Width Total Inside Frame Height Total Inside Frame Width Total Cutting Height Total Cutting Width
    18 1/4 22 3/4 24 1/4 28 3/4 24 5/16 28 13/16 29 33 11/16

  6. #6
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Just a matter of changing strings to numbers and sharing variables
     Option Explicit
     
     Dim PH As Single, PW As Single, TR As Single, BR As Single
     Dim LR As Single, RR As Single, MB As Single, ES As Single
     Dim RD As Single, TWOM As Single
    
    
    Private Sub CommandButton1_Click()
    
    
    Call Fractions
      
        TotalMattingOpeningHeight = PH + TR + BR
        TotalMattingOpeningWidth = PW + LR + RR
        TotalInsideFrameHeight = PH + TR + BR + (MB * 2) + (ES * 2)
        TotalInsideFrameWidth = PW + LR + RR + (MB * 2) + (ES * 2)
        TotalCuttingHeight = PH + TR + BR + (MB * 2) + (ES * 2) + ((TWOM * 2 - RD) * 2)
        TotalCuttingWidth = PW + LR + RR + (MB * 2) + (ES * 2) + ((TWOM * 2 - RD) * 2)
        
    End Sub
     
     
    Sub Fractions()
        PH = Convert(PaintingHeight)
        PW = Convert(PaintingWidth)
        TR = Convert(TopReveal)
        BR = Convert(BottomReveal)
        LR = Convert(LeftReveal)
        RR = Convert(RightReveal)
        MB = Convert(MattingBorder)
        ES = Convert(ExpansionSpace)
        RD = Convert(DepthOfRabbet)
    End Sub
     
    Function Convert(x)
        Dim Dim1, Dim2
        If InStr(1, x, " ") Then
            If InStr(1, x, "/") Then
                Dim1 = Split(x)(0)
                Dim2 = Split(Split(x)(1), "/")(0) / Split(Split(x)(1), "/")(1)
                Convert = Trim(Dim1 + Dim2)
            Else
                Convert = Trim(x)
            End If
        Else
            If InStr(1, x, "/") Then
                Dim2 = Split(x, "/")(0) / Split(x, "/")(1)
                Convert = Trim(Dim2)
            Else
                Convert = Trim(x)
            End If
        End If
    End Function
    
    
    Private Sub ResetButton_Click()
    Unload UserForm1
    UserForm1.Show
    End Sub
    
    
    Private Sub QuitButton_Click()
    UserForm1.Hide
    End Sub
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  7. #7
    Wow, This calculate the fractions perfectly. However, I noticed that the "Total" fields are still showing in decimals. I attempted to have them show as fractions but to no avail. Am I asking to much if you could show me how to convert those numbers to fractions as well. Thanks again for all your help.

  8. #8
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    I've adapted code found here to convert back to fractions. Some results in my test are different from yours as posted above.
    Private Sub CommandButton1_Click()
    
    
    Call Fractions
      
        TotalMattingOpeningHeight = Dec2Frac(PH + TR + BR)
        TotalMattingOpeningWidth = Dec2Frac(PW + LR + RR)
        TotalInsideFrameHeight = Dec2Frac(PH + TR + BR + (MB * 2) + (ES * 2))
        TotalInsideFrameWidth = Dec2Frac(PW + LR + RR + (MB * 2) + (ES * 2))
        TotalCuttingHeight = Dec2Frac(PH + TR + BR + (MB * 2) + (ES * 2) + ((TWOM * 2 - RD) * 2))
        TotalCuttingWidth = Dec2Frac(PW + LR + RR + (MB * 2) + (ES * 2) + ((TWOM * 2 - RD) * 2))
        
    End Sub
    
    
    
    
    Public Function Dec2Frac(ByVal f As Double) As String
    
    
       Dim df As Double
       Dim lUpperPart As Long
       Dim lLowerPart As Long
       Dim Num As Long
       Dim Nom As Long
       
       lUpperPart = 1
       lLowerPart = 1
       
       df = lUpperPart / lLowerPart
       While (df <> f)
          If (df < f) Then
             lUpperPart = lUpperPart + 1
          Else
             lLowerPart = lLowerPart + 1
             lUpperPart = f * lLowerPart
          End If
          df = lUpperPart / lLowerPart
       Wend
       
       Num = Int(lUpperPart / lLowerPart)
       Nom = lUpperPart Mod lLowerPart
       
       
    Dec2Frac = Num & " " & Nom & "/" & CStr(lLowerPart)
    End Function
    Attached Files Attached Files
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  9. #9
    Thank you so much, it works great. I actually had found the Dec2Frac function online and attempted to make it work but forgot to include the Dec2Frac as part of the Call Fractions procedure. I made some minor changes, such as, adding some additional Total Fields and some colour. I am attaching the Final product. Thanks again for all your help. Greatly appreciated.
    Attached Files Attached Files

  10. #10
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    I'm a little confused. Your final changes include removing the Dec2Frac utility. You could, of course have two buttons, one to return fractions, one for decimals. If you're content, please mark this solved.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  11. #11
    Hi mdmackillop,
    Definitely content. Yes, you're right I could have two buttons, one for fractions and one for decimal but for my use, I only needed fractions. I want to thank you again for all your help on this. I'm not sure how to mark as resolved, how can I do that on this Forum?

  12. #12
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,106
    Location
    Use the Thread Tools at the top of the thread. Since you asked so nicely, I did it for you this time.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  13. #13
    Great, thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •