[vba] mod function not working for variables bigger than 'long'

[AB91]

Hi all,

This is my first post here - so apologies if I don't write things in the best way.

Basically I'm writing a short VBA program to determine all of the prime factors of a given number (let's call this x). I'm pretty happy with the general logic but one thing I'm struggling on is a step where I determine whether or not a number (from 1 to sqrt(X)) is a factor.

Originally, I did:

If x/i = round(x/i,0) Then [...]

Which worked until x was > 2.1b (the maximum Long number), however x was defined as Single or Double.

Next, I then looked to:

If x mod i = 0 Then [...]

And the mod function seemed a lot quicker, but again broken when x exceeded the maximum Long value, despite being defined as Single or Double.

I might be missing something obvious here, but would love to know of any solutions to get around this, as I think the logic is sound.

Thanks!
Alex

[shg]

Welcome to the board.

In VBA, Mod is an operator that applies only to Longs. For Doubles, you can use

Please Login or Register  to view this content.

[AB91]

Hi shg,

Thanks for the reply. I did also try that but still the error persists. I tried it looking at 10,000,000,000 using:

If Int(x / i) = x / i Then

MsgBox x / i

I put in the msgbox so I could check the value and x/i was being calculated as an integer, so int(x/i) did always equal x/i. Am I doing something stupid?

[shg]

What numbers did you use that you believe were in error?

Post all of your code.

[AB91]

Please Login or Register  to view this content.

This was so in K it noted down the factors of the number. I know I said prime factors but I'll incorporate that once I fix what's currently there.

If i use x = 10b and just loop through from y-5 to 5 (for speed) then the output is 99995
99996
99997
99998
99999
100000

And only the last one is actually a factor.

Thanks again, I really appreciate this!

[shg]

What's in B2?

[AB91]

Ah sorry, B2 was 10,000,000,000 - everything there works as expected when B2 is less than the maximum Long value

[shg]

Number on worksheets are Double, not Singles, and a Single only has a 23-bit (IIRC) mantissa, which is not long enough to store 10,000,000,000 as an integer.

Try this:

Please Login or Register  to view this content.

E.g.,

	A	B	C	D	E
1	10,000,000,000	2		10,000,000,000	D1: =PRODUCT(B1:B23)
2		2
3		2
4		2
5		2
6		2
7		2
8		2
9		2
10		2
11		5
12		5
13		5
14		5
15		5
16		5
17		5
18		5
19		5
20		5

[xladept]

I did this:

Please Login or Register  to view this content.

and got:

Data Range

	J	K	L	M
2	10000000	1		10000000
3	10000000	2		5000000
4	10000000	4		2500000
5	10000000	5		2000000
6	10000000	8		1250000
7	10000000	10		1000000
8	10000000	16		625000
9	10000000	20		500000
10	10000000	25		400000
11	10000000	32		312500
12	10000000	40		250000
13	10000000	50		200000
14	10000000	64		156250
15	10000000	80		125000
16	10000000	100		100000
17	10000000	125		80000
18	10000000	128		78125
19	10000000	160		62500
20	10000000	200		50000
21	10000000	250		40000
22	10000000	320		31250
23	10000000	400		25000
24	10000000	500		20000
25	10000000	625		16000
26	10000000	640		15625
27	10000000	800		12500
28	10000000	1000		10000
29	10000000	1250		8000
30	10000000	1600		6250
31	10000000	2000		5000
32	10000000	2500		4000
33	10000000	3125		3200