When I wrinte this worksheet function in a spreadsheet:

= MOD ( -11993.4 , 29.53058867 )

....I get 25.55 - which is what I want...

....but if I do the same in VBA:

MsgBox -11993.4 Mod 29.53058867

....I get -23 !!!


How come MOD provides different answers, depending on if you're using it
in a spreadsheet or in a VBA-code???

How to make the VBA-code provide the result, that I want?


TIA,

CE

On Mon, 18 Feb 2013 12:01:41 +0100, "Charlotte E." wrote:

How come MOD provides different answers, depending on if you're using it
in a spreadsheet or in a VBA-code???

The "defintions" are diferent

Worksheet: Returns the remainder after number is divided by divisor.
VBA: Used to divide two numbers and return only the remainder BUT firts rounding floating-point numbers to integers.



How to make the VBA-code provide the result, that I want?



Use this equivalent: n - d*INT(n/d)

Thanks :-)

Both for the explanation and the solution...

....got it working :-)


CE


Den 18.02.2013 13:24, Ron Rosenfeld skrev:
On Mon, 18 Feb 2013 12:01:41 +0100, "Charlotte E." wrote:

How come MOD provides different answers, depending on if you're using it
in a spreadsheet or in a VBA-code???

The "defintions" are diferent

Worksheet: Returns the remainder after number is divided by divisor.
VBA: Used to divide two numbers and return only the remainder BUT firts rounding floating-point numbers to integers.



How to make the VBA-code provide the result, that I want?



Use this equivalent: n - d*INT(n/d)

On Mon, 18 Feb 2013 13:58:13 +0100, "Charlotte E." wrote:

Thanks :-)

Both for the explanation and the solution...

...got it working :-)


CE

Glad to help. Thanks for the feedback.

Charlotte E. wrote:
When I wrinte this worksheet function in a spreadsheet:

= MOD ( -11993.4 , 29.53058867 )

...I get 25.55 - which is what I want...

...but if I do the same in VBA:

MsgBox -11993.4 Mod 29.53058867

...I get -23 !!!


How come MOD provides different answers, depending on if you're using it
in a spreadsheet or in a VBA-code???

How to make the VBA-code provide the result, that I want?


because these are two different functions just sharing the same name :)


another example

try trim in excel and trim in vba.

and use " aaa bbb " as example