Entering Fractions in Dimensions Calculator

[mleblanc001]

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

[mdmackillop]

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

[mleblanc001]

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.

[mdmackillop]

Can you post appropriate numbers in a workbook that I can import into the form

[mleblanc001]

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

[mdmackillop]

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

[mleblanc001]

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.

[mdmackillop]

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

[mleblanc001]

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.

[mdmackillop]

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.

[mleblanc001]

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?

[SamT]

Use the Thread Tools at the top of the thread. Since you asked so nicely, I did it for you this time.

[mleblanc001]

Great, thank you.