Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help
What is Decimals supposed to be (it is not declared anywhere that I can
see)? -- Rick (MVP - Excel) "Soccerboy83" wrote in message ... 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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help
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 "Soccerboy83" wrote: 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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help
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) On Tue, 6 Oct 2009 08:43:02 -0700, Soccerboy83 wrote: 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, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help
It actually quite funny. The reason the compiler is giving an error on
IsMissing(Decimals) is that Decimals IsMissing! :) (Sorry) "Soccerboy83" wrote: 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, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|