Help with Case statement

[kiyiya]

Hello all,

I am wanting to condense a Case statement or see if there is another way to accomplish my goals.

My statement is:

Select Case DoseDec
Case Is = 0.125
DoseDec = " 1/8"
Case Is = 1.125
DoseDec = " 1 1/8"
Case Is = 2.125
DoseDec = " 2 1/8"
Case Is = 3.125
DoseDec = " 3 1/8"
Case Is = 4.125
DoseDec = " 4 1/8"
Case Is = 5.125
DoseDec = " 5 1/8"
Case Is = 6.125
DoseDec = " 6 1/8"
Case Is = 7.125
DoseDec = " 7 1/8"
Case Is = 8.125
DoseDec = " 8 1/8"
Case Is = 9.125
DoseDec = " 9 1/8"
Case Is = 10.125
DoseDec = " 10 1/8"

I know I can include all values in a single statement like:

Case 0.25, 1.25, 2.25, 3.25, 4.25, 5.25, 6.25, 7.25, 8.25, 9.25, 10.25
DoseDec = " 1/4"
but how do I make my variable = the whole number and the decimal?

I have to do this for whole numbers 1-10 and decimal .125, .250, .50 and .750.

I hope that came out clearly.

[Cosmo]

If you are familiar with regular expressions, that would be the best way. You could separate the string into 3 sets of numbers and then do the math on those characters.

(\d*)\s?(\d+)/(\d+)$

[kiyiya]

Not familiar with that. The numbers are entered into a textbox on a userform.

Care to elaborate on your method?

[Bob Phillips]

Format the cell as # #/8

[kiyiya]

There are no cells involved with this.

[Bob Phillips]

Just pretend there is


DoseDec = Application.Text(DoseDec, "# #/8")

[mdmackillop]

to handle 1/2, 3/4 etc

Application.Text(DoseDec, "# ?/?")

[Norie]

How about just this?


DoseDec = Int(DoseDec) & " 1/8

[kiyiya]

Hey Norrie that is what I was trying to accomplish!

Looks like XLD and mdmackillop's solution gets rid of the CASE statement completely though.

Learned 2 things with this one.

Thanks to all.

[Bob Phillips]

Hey Norrie that is what I was trying to accomplish!

No it isn't, you said that 0.125 returns " 1/8", whereas this give "0 1/8". Both MD's and mine give as stated.

[kiyiya]

No it isn't, you said that 0.125 returns " 1/8", whereas this give "0 1/8". Both MD's and mine give as stated.

Norrie's code works as I wanted. I just plugged in his code

DoseDec = Int(DoseDec) & " 1/8 (and added " at the end) and it seems to work as expected.


Select Case dosedec
Case 0.125, 1.125, 2.125, 3.125, 4.125, 5.125, 6.125, 7.125, 8.125, 9.125, 10.125
dosedec = Int(dosedec) & " 1/8"
Case 0.25, 1.25, 2.25, 3.25, 4.25, 5.25, 6.25, 7.25, 8.25, 9.25, 10.25
dosedec = Int(dosedec) & " 1/4"
Case 0.5, 1.5, 2.5, 3.5, 4.5, 5.5, 6.5, 7.5, 8.5, 9.5, 10.5
dosedec = Int(dosedec) &" 1/2"
Case 0.75, 1.75, 2.75, 3.75, 4.75, 5.75, 6.75, 7.75, 8.75, 9.75, 10.75
dosedec = Int(dosedec) &" 3/4"
Case Else
dosedec = txtDosage.Value
End Select

[kiyiya]

I guess I should say it works "close" to what I wanted. I forgot to try just ".125" in my textbox. How would you drop the leading 0 and still make that block of code work?

Sorry Norrie!

[mdmackillop]

Hi kiyiya,
The point of my code (refining XLD's) was to lose the whole select case structure. Just do it in one line

Sub Fraction()
MsgBox Application.Text(InputBox("Enter decimal", "Show fraction", 3.75), "# ?/?")
End Sub

[kiyiya]

The point of my code (refining XLD's) was to lose the whole select case structure. Just do it in one line.]

Yes, I agree, and that is how I am implementing it into my project. Thank you (and XLD) very much for the help.

Norrie's code helped to clarify that I can use a CASE statement the way I wanted (or intended) and that is useful information to me. I am sure to put to it to use in other circumstances, so thank you as well Norie.

[mdmackillop]

For the latter; (add 3/8 and 5/8 if required)


Select Case dosedec - Int(dosedec)
Case 0.125
dosedec = Int(dosedec) & " 1/8"
Case 0.25
dosedec = Int(dosedec) & " 1/4"
Case 0.5
dosedec = Int(dosedec) & " 1/2"
Case 0.75
dosedec = Int(dosedec) & " 3/4"
Case 0.875
dosedec = Int(dosedec) & " 7/8"
Case Else
dosedec = txtDosage.Value
End Select

[kiyiya]

Even better! Thanks again.

[Cosmo]

FWIW, Here's the function using regular expressions.

Private Function parseNumber(ByVal inputValue As String) As Double
Dim objRegExp As RegExp
Dim colMatches As MatchCollection
Dim myArray
Dim firstNumber As String
Dim decimalNumber As String
On Error GoTo errorcode
'Create a regular expression object.
Set objRegExp = New RegExp
With objRegExp
.IgnoreCase = True 'Set Case Insensitivity.
.Global = True 'Set global applicability.
.Pattern = "\d+"
If .test(inputValue) Then
Set myArray = .Execute(inputValue)
firstNumber = (myArray(0))
Else
firstNumber = 0
End If
End With
If myArray.Count > 2 Then
decimalNumber = myArray(1) / myArray(2)
parseNumber = CDbl(firstNumber) + CDbl(decimalNumber)
Else
parseNumber = CDbl(firstNumber)
End If
Exit Function
errorcode:
Debug.Print "Error occurred parsing number - " & Err.Description
'Resume Next
End Function