Textboxes Not Calculating Properly

[RyanH]

I have 4 Textboxes that I use to calculate a length of an outdoor sign.
Height in Feet, Height in Inches, Width in Feet, and Width in Inches. I want
to click a command button and the code will automatically round the height
and width to the nearest 1/16th of an inch. But for some reason when I enter
0.125 or 0.0625 in both of the inch textboxes the numbers returned are not
equal, why?

Private Sub cmbEstimate_Click()

Dim Lheight As Double
Dim Lwidth As Double
Dim Hft As Double
Dim Hins As Double
Dim Wft As Double
Dim Wins As Double

Lheight = Val(tbxHeightFt) + Val(tbxHeightIns) / 12 'face height in ft
MsgBox Lheight
Lwidth = Val(tbxWidthFt) + Val(tbxWidthIns) / 12 'face width in ft
MsgBox Lwidth

'dimensions of cabinet in feet & inches
Hft = Int(Lheight)
MsgBox Hft
Hins = WorksheetFunction.Ceiling((Lheight - Hft) * 12, 0.0625)
MsgBox Hins
Wft = Int(Lwidth)
MsgBox Wft
Wins = WorksheetFunction.Ceiling((Lwidth - Wft) * 12, 0.0625)
MsgBox (Wins)

End Sub

Thanks in Advance,
Ryan

 

[Bernie Deitrick]

Ryan,

You are running into the limitation of representing a decimal number. Try storing the inches as the
number of 16ths:

Private Sub CommandButton1_Click()

Dim Lheight As Double
Dim Lwidth As Double
Dim Hins As Double
Dim Wins As Double
Dim WFt As Double
Dim WIn As Double
Dim HFt As Double
Dim HIn As Double

HFt = Val(tbxHeightFt)
HIn = Application.RoundUp(Val(tbxHeightIns) * 16, 0)
WFt = Val(tbxWidthFt)
WIn = Application.RoundUp(Val(tbxWidthIns) * 16, 0)

MsgBox HFt & " Ft & " & HIn / 16 & " in by " & WFt & " ft & " & WIn / 16 & " in"

Lheight = HFt + HIn / 16 / 12 'face height in ft
MsgBox Lheight & " length in decimal feet"
Lwidth = WFt + WIn / 16 / 12 'face width in ft
MsgBox Lwidth & " width in decimal feet"

'dimensions of cabinet in feet & inches
HFt = Int(Lheight)
MsgBox HFt & " length in decimal feet"
Hins = WorksheetFunction.Ceiling((Lheight - HFt) * 12, 0.0625)
MsgBox "You had: " & Hins & " inches, now it is: " & HIn / 16
WFt = Int(Lwidth)
MsgBox WFt & " width in decimal feet"
Wins = WorksheetFunction.Ceiling((Lwidth - WFt) * 12, 0.0625)
MsgBox "You had: " & Wins & " inches, now it is: " & WIn / 16

End Sub

HTH,
Bernie
MS Excel MVP

 

[RyanH]

I am still getting different numbers. If I enter 4 = tbxHeightFt, 2 =
tbxWidthFt, .125 = tbxHeightIns, .125 = tbxWidthIns, then Hins = .1875 and
Wins = .125. Here is the code that I pasted into my module:

Private Sub cmbEstimate_Click()

'result of which face was selected on userform
If optSingleFace = True Then
Face = optSingleFace.Caption
Else
Face = optDoubleFace.Caption
End If

Dim Lheight As Double
Dim Lwidth As Double
Dim Hins As Double
Dim Wins As Double
Dim WFt As Double
Dim WIn As Double
Dim HFt As Double
Dim HIn As Double

HFt = Val(tbxHeightFt)
HIn = Application.RoundUp(Val(tbxHeightIns) * 16, 0)
WFt = Val(tbxWidthFt)
WIn = Application.RoundUp(Val(tbxWidthIns) * 16, 0)

MsgBox HFt & " Ft & " & HIn / 16 & " in by " & WFt & " ft & " & WIn / 16
& " in"

Lheight = HFt + HIn / 16 / 12 'face height in ft
MsgBox Lheight & " length in decimal feet"
Lwidth = WFt + WIn / 16 / 12 'face width in ft
MsgBox Lwidth & " width in decimal feet"

'dimensions of cabinet in feet & inches
HFt = Int(Lheight)
MsgBox HFt & " length in decimal feet"
Hins = WorksheetFunction.Ceiling((Lheight - HFt) * 12, 0.0625)
MsgBox "You had: " & Hins & " inches, now it is: " & HIn / 16
WFt = Int(Lwidth)
MsgBox WFt & " width in decimal feet"
Wins = WorksheetFunction.Ceiling((Lwidth - WFt) * 12, 0.0625)
MsgBox "You had: " & Wins & " inches, now it is: " & WIn / 16

'Preview Line 1: Single/Double Face, Extruded Cabinet: Dimensions
lblPreview1.Caption = Face & ", Extruded Cabinet: " & HFt & "'-" & Hins
& "'' H x " & WFt & "'-" & Wins & "'' W x " & Val(tbxDepthIns) & "'' D"


Thanks,Ryan

 

[RyanH]

I got it to work now. This is what I did.

Public Sub Preview()

Dim height As Single
Dim width As Single
Dim Hft As Byte
Dim Hins As Single
Dim Wft As Byte
Dim Wins As Single
Dim Face As String

'result of which face was selected on userform
If optSingleFace = True Then
Face = optSingleFace.Caption
Else
Face = optDoubleFace.Caption
End If

'round height & width to the nearest 1/16th of an inch
height = Round(Val(tbxHeightFt) * 12 * 16 + Val(tbxHeightIns) * 16)
'height in 1/16th of inches
width = Round(Val(tbxWidthFt) * 12 * 16 + Val(tbxWidthIns) * 16)
'width in 1/16th of inches

'dimensions of cabinet in feet & inches
Hft = Int(height / 16 / 12)
Hins = (Round((height / 16 / 12 - Hft) * 12 * 16)) / 16
Wft = Int(width / 16 / 12)
Wins = (Round((width / 16 / 12 - Wft) * 12 * 16)) / 16

'Preview Line 1: Single/Double Face, Extruded Cabinet: Dimensions
lblPreview1.Caption = Face & ", Extruded Cabinet: " & Hft & "'-" & Hins
& "'' H x " & Wft & "'-" & Wins & "'' W x " & Val(tbxDepthIns) & "'' D"

Thanks,
Ryan