Difficulty with MOD operator when using Mod 1

[Terry Holmes]

Could someone please help me with a silly little problem that is really
bugging me. I have just started learning Visual Basic and am having
difficulty with the MOD operator.

If I set up a formula using the MOD function in an Excel workbook:

=MOD(2.08, 1) * 100

I get the expected result 8 in the cell.

If I then try to use the MOD operator to get the same result in a VBA
procedure:

Var1 = 2.08
Var2 = (Var1 Mod 1) * 100
MsgBox Var2

I get the result of 0 in Msgbox instead of the expected 8.

I have just done some more searching and found a reply to a recent post on
this newsgroup which mentioned briefly that floating point numbers are
rounded to integers and this causes mod 1 to return 0.

Is there any way of using the Mod operator to get the result I am after or
is it a lost cause?

Terry Holmes

PS. A second question – is it possible to change the names of modules in the
Project Explorer?
(I find that lists of names like module 1, module 2, module 3 etc. in the
tree under each workbook are not very informative to help you remember where
you wrote a piece of code and would like to change them to something more
meaningful. What looks like the obvious solution - right-clicking on the
module name in the tree - takes you to a dialogue box that only lets you
change the project name).

 

[Bob Phillips]

You should read the help. It says

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

You could try

Var1 = 2.08
Var2 = (Var1 * 1000 Mod 1000) / 1000 * 100
MsgBox Var2

 

[keepITcool]

vba's MOD operator works ENTIRELY different from Excel MOD function.

vba mod is intended for longs or integers and vba MOD ROUNDS the
arguments first...

8.1 mod 3.4 both are rounded 8 mod 3 gives 2
8.1 mod 3.5 both are rounded 8 mod 4 gives 0

you'll need to use
var1 * 100 mod 100

2.08 * 100 = 208 mod 100 gives 8




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Terry Holmes wrote :

 

[keepITcool]

also:
to get the "fractional part" use
frac1= var1 - var1 \ 1

(the backslash is the operator for integer division)

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


keepITcool wrote :

 

[Tushar Mehta]

As keepitcool pointed out, you have to use x - x \ 1 to get the
fraction part.

For your second question, you have to use the 'properties window.'
Activate it with F4 (or View | Properties Window). Now, when you
select a module, the prop window will let you change the module name.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Terry Holmes]

The solution’s always so obvious once you are shown it. Ain’t it easy when
you know how!

I read the help, didn’t digest the implications fully and then went chasing
my tail looking in all the wrong places until I came to this group.

Your examples have now made everything crystal clear. It’ll also be nice to
change those module names.

Thanks to you all for taking the trouble to answer so promptly.

Regards