shampouya
asked on
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?
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.
{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.
Alternatively, the first non-zero digit can be retrieved using this technique:
Public Sub Test()
Debug.Print GetFirstFractionDigit(2.00 0042)
End Sub
Public Function GetFirstFractionDigit(ByVa l Value As Double) As Long
GetFirstFractionDigit = Left(CLng(Mid(CStr(Value), InStr(CStr(Value), ".") + 1)), 1)
End Function
Kevin
Public Sub Test()
Debug.Print GetFirstFractionDigit(2.00
End Sub
Public Function GetFirstFractionDigit(ByVa
GetFirstFractionDigit = Left(CLng(Mid(CStr(Value),
End Function
Kevin
ASKER
Would the first solution turn 4.082 into 82?
Yes.
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
Kevin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can use something like this in your code:
result=Int(Right(cellValue, Len(cellValue) - InStr(cellValue, ".")))
Fix((Value-Fix(Value))*(10
Kevin