• Post Reply Bookmark Topic Watch Topic
  • New Topic

How to generate the same random values in VBA and Java ?  RSS feed

 
jacques dusieur
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello
I have large VBA macros that use Rnd() method.
I have to port this code in Java, I use here java.util.Random
Now I would like to check it does the exact same thing and of course random numbers seem to prevent that check.
I was wondering how I could manage to generate the same random values in VBA and in Java ?
Can I put a seed? Point to a common Random function (via a third-party library ? via the common operating system ? ...) ? Any other way ?
Thanks for your help
Regards,
 
Piet Souris
Master Rancher
Posts: 2044
75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi jacques,

easiest I can think of:
generate a sufficient number of random values in VBA,
write these values to a file, and use this file to check
your VBA and Java routines.
 
Tim Cooke
Marshal
Posts: 4051
239
Clojure IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Does it matter? If the purpose of the function is to generate a random number, why is it important that the VBA version and the Java version generate the same number?
 
Dave Tolls
Ranch Foreman
Posts: 3068
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim Cooke wrote:Does it matter? If the purpose of the function is to generate a random number, why is it important that the VBA version and the Java version generate the same number?


I think he wants to check that the code that uses the random number(s) does produces the same results between VBA and Java, which leads to Piet's suggestion as that's the usual way for checking these things.
 
jacques dusieur
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Indeed, it is not that the numbers are important in the verification process. I need to see that the code that uses those numbers is the same between VBA and Java.
I thought about using a list of numbers at first as well. The problem with that is that I would have to change significantly the original code to adapt with a pre-list of numbers, so I will not be 100% sure it still acts the same way as expected, whereas if I could just replace the Rnd() calls by one that generates all the time the same numbers in the same order (and given the fact that I could reproduce this scenario in Java), I could be sure that the code is the same for sure if it produces exactly the same final results.
You said there is no easy way, but I am ready to use a more complex/kludge solution as it will be temporary.
Thanks
 
Campbell Ritchie
Marshal
Posts: 56596
172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The Random class tells you which algorithm is used (try the next() method). You can also find the source code in your Java® installation directory; look for a file called src.zip and unzip it. I would hope similar information would be available for the corresponding VB class, so you can verify the algorithms used.
 
J. Kevin Robbins
Bartender
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'll admit I've only had one cup of coffee, so maybe I'm missing something here. Why not generate the list of random numbers from VB into a file as Piet suggested, then create your own Random class that reads that file and returns a number from the list?

Yes it would require creating a new class for testing purposes, but the changes to your production code would be minimal.
 
Dave Tolls
Ranch Foreman
Posts: 3068
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
J. Kevin Robbins wrote:I'll admit I've only had one cup of coffee, so maybe I'm missing something here. Why not generate the list of random numbers from VB into a file as Piet suggested, then create your own Random class that reads that file and returns a number from the list?

Yes it would require creating a new class for testing purposes, but the changes to your production code would be minimal.


The problem there is how do you run those values through the VBA code so you can compare the results?
I'm guessing the VBA stuff has not been written with this in mind.
 
J. Kevin Robbins
Bartender
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:
The problem there is how do you run those values through the VBA code so you can compare the results?
I'm guessing the VBA stuff has not been written with this in mind.

Good point. You would need to generate the same list of numbers twice; once to create the file and once to test the VB code. I know just enough VB to be really dangerous. I don't know if the VB random() process can be seeded to produce the same results twice.

Okay, I'm officially out of ideas. I'll step back and let the smart people figure this one out.
 
Stephan van Hulst
Saloon Keeper
Posts: 7993
143
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you call Rnd() with a negative argument before calling Randomize(), it will generate the same sequence. That's what MSDN says at least.
 
Winston Gutkowski
Bartender
Posts: 10575
66
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
jacques dusieur wrote:I have large VBA macros that use Rnd() method.
I have to port this code in Java, I use here java.util.Random
Now I would like to check it does the exact same thing and of course random numbers seem to prevent that check.
I was wondering how I could manage to generate the same random values in VBA and in Java ?
Can I put a seed? Point to a common Random function (via a third-party library ?

It strikes me that the RNG is a bit of a red herring here.

You say that this is a "porting" exercise (a migration to Java perhaps?), so isn't it more important to test that what your macro does with a value is identical in both cases?

Now that might mean breaking out the "doing" part of your VBA code, so that you can test it with an equivalent Java class (or program), but then all you need to do is supply both versions with the same set of test values.

Furthermore, doing that will allow you to tailor your test sets for for things like corner and 'edge' cases. All an RNG can do is supply you with values for a "smoke test".

Winston
 
jacques dusieur
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have found a way to generate the same VBA Random sequence. From this page : https://support.microsoft.com/en-us/kb/231847 I could write the following java method :

Thanks for your help anyway!
 
Campbell Ritchie
Marshal
Posts: 56596
172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well done

Why are you using big integers? Can't you use ints or longs? Why are you using a float at all? Do you require a 32‑bit result? If so don't write
(float)16777216.0
Write
16777216f
or
16777216.0f
Let the compiler do the work of comverting to a float.
 
jacques dusieur
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It seems to be possible without using BigInteger as someone offered a parallel implementation that proved to work (for at least the first 100 Random numbers that I have tested) : other implementation
I understand that Long numbers can store less values than Unsigned Long ones, so I guessed this would have an impact when generating random numbers so I wanted to stick as much as possible to the solution given by Microsoft...
I found that the equivalent of Unsigned Long in java is BigInteger (in Java 1.8 it can be handled differently but I have to use Java 1.7)
What is actually the "issue" with using BigInteger?
 
Campbell Ritchie
Marshal
Posts: 56596
172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Performance. It is faster to do arithmetic on primitives.
 
Winston Gutkowski
Bartender
Posts: 10575
66
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
jacques dusieur wrote:I have found a way to generate the same VBA Random sequence.

As Campbell said: well done.

However, I'm not sure it solves your problem. Did you read my post? And if so, how do you plan to test "corner" cases?
As I said, all an RNG can do is provide values for a smoke test; and I'd be very worried about relying on that alone.

Winston
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!