VBA help

[Soccerboy83]

My question is this, i have a forumula in VBA that reads as follows,


Public Function RandomNumbers(Lowest As Long, Highest As Long)
If IsMissing(Decimals) Or Decimals = 0 Then
Randomize
RandomNumbers = Int((Highest + 1 - Lowest) * Rnd + Lowest)
Else
Randomize
RandomNumbers = Round((Highest - Lowest) * Rnd + Lowest, Decimals)
End If
End Function

However when i try and run the program it is giving me problems. It keeps
telling Highlighting (Decimals) and gives me an compilation error, saying it
cannot find it in the project or Library. Can someone please help me. or
give me another way of writing the code above to give me a random number,
that does not recalculate every time something changes. Thanks,

 

[Rick Rothstein]

What is Decimals supposed to be (it is not declared anywhere that I can
see)?

 

[Mike H]

Hi,

Your missing an argument in your function called decimals which must be
optional. Try this

call with =randomnumbers(1,9,3)

for a random num to 3 decimal places or the decimals argument can be omitted
for an integer
=randomnumbers(1,9)



Public Function RandomNumbers(Lowest As Long, Highest As Long, _
Optional Decimals As Integer)
If IsMissing(Decimals) Or Decimals = 0 Then
Randomize
RandomNumbers = Int((Highest + 1 - Lowest) * Rnd + Lowest)
Else
Randomize
RandomNumbers = Round((Highest - Lowest) * Rnd + Lowest, Decimals)
End If
End Function

Mike

 

[Chip Pearson]

You need to declare a variable named Decimals, presumably in the
function declaration. E.g,

Public Function RandomNumbers(Lowest As Long, Highest As Long, _
Optional Decimals As Long = 0)
If Decimals <= 0 Then
Randomize
RandomNumbers = Int((Highest + 1 - Lowest) * Rnd + Lowest)
Else
Randomize
RandomNumbers = Round((Highest - Lowest) * Rnd + Lowest, Decimals)
End If
End Function


Here, if Decimals was not supplied by the caller, it gets the default
value of 0. Your code should treat that as omitted. In order to use
the IsMissing function, the variable would need to be declared As
Variant, since IsMissing can work only with Variants:

Public Function RandomNumbers(Lowest As Long, Highest As Long, _
Optional Decimals As Variant)
If IsMissing(Decimals) Then
Randomize
RandomNumbers = Int((Highest + 1 - Lowest) * Rnd + Lowest)
Else
Randomize
RandomNumbers = Round((Highest - Lowest) * Rnd + Lowest, Decimals)
End If
End Function

The problem with using an Optional Variant is that you code needs to
do type checking to ensure that the content of the Variant is valid.
Since a Varaint can contain any sort of data, you'd need to test to
ensure it is not a string or other incompatible data type (or an array
of data or an object). It is, in my opinion, better to declare an
Optional variable with a specific type, such as Long, supply a default
value, and use that value to test if the variable was passed.

As your code stands now, there is no such variable named Decimals, so
the compiler throws an error.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Charlie]

It actually quite funny. The reason the compiler is giving an error on
IsMissing(Decimals) is that Decimals IsMissing!

(Sorry)

 

[Bernd P]

Hello,

All these RandomNumbers functions are somewhat flawed, I am afraid.

With lowest number 1 and highest number 2 and decimals = 1 they
generate 1.0 and 2.0 with half the likelihood as the other ones 1.1 -
1.9.
With lowest number 1 and highest number 1.9 and decimals = 1 they
sometimes generate even 2!
The problem lies in the usage of Round and because the boundaries are
of type long (they should be of type double).

I suggest to use my UDF rnddigit instead:
http://sulprobil.com/html/rnddigit.html

Regards,
Bernd