problem with udf modulus function

pete6982

New Member
Joined
Aug 7, 2006
Messages
19
I am using excel 2000 and needed a mod function to return the mod number rather than 0. ex 3 mod 3 is normally 0, but I want it to return 3. So I wrote this function

Code:
Function Modulus(a, b) As Integer 'for x,y positive
    Do While a > b
        a = a - b
    Loop
    Modulus = a
End Function

it seems to work, but when I call it with this stmt

Code:
If Ceiling(i / 3) = Ceiling(x / 3) And Ceiling(j / 3) = Ceiling(y / 3) Then
    If Cells(x, y) <> 0 Then
        Possible((Modulus(x, 3) - 1) * 3+ Modulus(y, 3)) = 0
    End If
End If

with x=1,y=3; the y somehow becomes 0 when I try to set that member of possible to 0. I am stumped. any suggestions?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
544
Office Version
  1. 2013
Platform
  1. Windows
Have you tried stepping through the code in break mode to see the calculation, and where it goes wrong?
 

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
544
Office Version
  1. 2013
Platform
  1. Windows
Another thought - do you have the same problem if you replace your "Modulus" function with an equivalent, like:
Code:
Function Modulus(x As Long, y As Long) As Long

Modulus = x Mod y - ((x Mod y) = 0) * y

End Function

Are the values supplied to the function declared as integers (or longs), or reals? If the "y" value supplied to the second Modulus function is a real, like say 3.005, then it will coerce 0.005 to an integer and return 0, instead of 3.
 

Forum statistics

Threads
1,141,720
Messages
5,708,090
Members
421,546
Latest member
delatollas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top