Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Mod, Excel Floor, etc.
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? -- Thanks for your help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Mod, Excel Floor, etc.
perhaps...
N-Round(N, 0) -- HTH... Jim Thomlinson "LesHurley" wrote: 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? -- Thanks for your help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Mod, Excel Floor, etc.
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. -- Gary''s Student - gsnu200766 "LesHurley" wrote: 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? -- Thanks for your help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Mod, Excel Floor, etc.
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" wrote in message ... 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? -- Thanks for your help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Mod, Excel Floor, etc.
This works fine for my application but , for future reference, it always
returns a positive fraction. -- Thanks for your help "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200766 "LesHurley" wrote: 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? -- Thanks for your help |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Mod, Excel Floor, etc.
-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. -- Thanks for your help "Jim Thomlinson" wrote: perhaps... N-Round(N, 0) -- HTH... Jim Thomlinson "LesHurley" wrote: 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? -- Thanks for your help |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Mod, Excel Floor, etc.
Same comment as for Gary's suggestion.
-- Thanks for your help "Bob Phillips" wrote: 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" wrote in message ... 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? -- Thanks for your help |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Mod, Excel Floor, etc.
On Tue, 29 Jan 2008 09:23:07 -0800, LesHurley
wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Mod, Excel Floor, etc.
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) "LesHurley" wrote in message ... Same comment as for Gary's suggestion. -- Thanks for your help "Bob Phillips" wrote: 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" wrote in message ... 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? -- Thanks for your help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I draw Floor Plans with Excel or Power Point ? | Excel Discussion (Misc queries) | |||
draw a floor plan to scale in excel | Excel Worksheet Functions | |||
How do I use a block diagram to draw a floor plan in Excel? | Excel Discussion (Misc queries) | |||
How do I link an Excel Data Base to a Visio Floor Plan? | Excel Discussion (Misc queries) | |||
EXCEL: FLOOR/SPACE RATIO CALCULATION | Excel Worksheet Functions |