VBA Mod, Excel Floor, etc.

[LesHurley]

In VBA, I want to find the fractional part of a number, say N=123.456. The
VBA version of Mod rounds N to an integer before it functions. The
WorksheetFunction Mod() isn't available in VBA. WorksheetFunction.Floor() is
available but it wont work with a negative N. Can anyone suggest how to do
this for any real N?

 

[Jim Thomlinson]

perhaps...

N-Round(N, 0)

 

[Gary''s Student]

Sub fracpart()
x = Application.InputBox(prompt:="", Type:=1)
x = Abs(x) - Int(Abs(x))
MsgBox (x)
End Sub

will give the fractional part of both positives and negatives as a positive.

 

[Bob Phillips]

This works as the Excel Mod does

Dim N As Double
Dim remain As Double
Dim sign As Boolean

N = 123.456
sign = N < 0
remain = N - Int(N)
MsgBox remain


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[LesHurley]

This works fine for my application but , for future reference, it always
returns a positive fraction.

 

[LesHurley]

-5.4-Round(-5.4,0) = -.4: thats OK but -5.6-Round(-5.6,0) = +.4: Not OK in
general but it would work OK for my particular application since I am testing
if the fractional part is zero.

 

[LesHurley]

Same comment as for Gary's suggestion.

 

[Ron Rosenfeld]

In VBA, I want to find the fractional part of a number, say N=123.456. The
VBA version of Mod rounds N to an integer before it functions. The
WorksheetFunction Mod() isn't available in VBA. WorksheetFunction.Floor() is
available but it wont work with a negative N. Can anyone suggest how to do
this for any real N?

This should work, including with negative N:

===================
Function vbMod1(N)
vbMod1 = N - Fix(N)
End Function
===================

Due to rounding issues, you probably will want to Round to a specified number
of decimals.
--ron

 

[Bob Phillips]

I transcribed it incorrectly, I really meant

Dim N As Double
Dim remain As Double
Dim sign As Boolean

N = -123.456
sign = N < 0
remain = N - Int(N)
MsgBox IIf(sign, -remain, remain)



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)