In VBA is there an easy way to turn the decimal digits into an integer?

How would I store a number like 0.005 as 5 in an integer variable? I want to be able to take any decimal number, like 3.7, and store it as 7. Or 19.09 and store it as 9. What's the best way to do that in VBA?
shampouyaAsked:
Who is Participating?
 
zorvek (Kevin Jones)ConsultantCommented:
Correction, my first solution will NOT return additional digits.

This one will:

Public Sub Test()
    Debug.Print GetFirstFractionDigit(4.082)
End Sub

Public Function GetFirstFractionDigit(ByVal Value As Double) As Long
    GetFirstFractionDigit = CLng(Mid(CStr(Value), InStr(CStr(Value), ".") + 1))
End Function

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Try this:

Fix((Value-Fix(Value))*(10^-(Fix(Application.LOG10(Value-Fix(Value)))-1)))

Kevin
0
 
Rodney EndrigaData AnalystCommented:
If you want just the last digit in the number, you can use:

{variable name} = Right(cell.Value, 1)

For instance, if Cell A1=3.7, it would result in 7.
For instance, if Cell A1=19.09 , it would result in 9.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
zorvek (Kevin Jones)ConsultantCommented:
Alternatively, the first non-zero digit can be retrieved using this technique:

Public Sub Test()
    Debug.Print GetFirstFractionDigit(2.000042)
End Sub

Public Function GetFirstFractionDigit(ByVal Value As Double) As Long
    GetFirstFractionDigit = Left(CLng(Mid(CStr(Value), InStr(CStr(Value), ".") + 1)), 1)
End Function

Kevin
0
 
shampouyaAuthor Commented:
Would the first solution turn 4.082 into 82?
0
 
zorvek (Kevin Jones)ConsultantCommented:
Yes.
0
 
zorvek (Kevin Jones)ConsultantCommented:
But! You may see some anomalous digits appearing due to Excel's handling of reals. For example, 2.00004 might result in a 3 instead of a 4.

Kevin
0
 
Rodney EndrigaData AnalystCommented:
You can use something like this in your code:

result=Int(Right(cellValue, Len(cellValue) - InStr(cellValue, ".")))

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.