Return fractional part of values

[Guest]

In an internal VBA calculation, I need to return the fractional part of a
value in a cell.

For example,

5.35 returns 0.35

7.00 returns 0

I thought: dValue = ActiveCell.Value Mod 1

would do it, but this doesn't work...don't know why...

Thanks much in advance for your assistance.

 

[Niek Otten]

<doesn't work> What does that mean?

From Excel Help:

The modulus, or remainder, operator divides number1 by number2 (rounding floating-point numbers to integers)

Try:

Function test(a As Double) As Double
test = a - Int(a)
End Function

Use "Fix" instead of "Int" depending on what you require; see VBA Help

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| In an internal VBA calculation, I need to return the fractional part of a
| value in a cell.
|
| For example,
|
| 5.35 returns 0.35
|
| 7.00 returns 0
|
| I thought: dValue = ActiveCell.Value Mod 1
|
| would do it, but this doesn't work...don't know why...
|
| Thanks much in advance for your assistance.

 

[Guest]

Fraction = Activecell.Value - INT(Activecell.Value)

 

[JE McGimpsey]

don't know why...

The operative sentence in XL/VBA Help ("Mod"):

The modulus, or remainder, operator divides number1 by number2
***(rounding floating-point numbers to integers)***

Instead use

With ActiveCell
dValue = .Value - Fix(.Value)
End With