CODEWITHSUNDEEP
vbaexcel
Tom Ruiz
Tom Ruiz

Reputation: 307

How can I transform this UDF to a Macro

I have a question I have this User Defined Formula in Excel

    Function RANDNUMNOREP(Bottom As Integer, Top As Integer, Amount As Integer) As String
'This UDF will generate a non-repeating set of random numbers in excel and display those in the same cell as the function

Dim iArr As Variant
Dim i As Integer
Dim r As Integer
Dim temp As Integer

Application.Volatile

ReDim iArr(Bottom To Top)

For i = Bottom To Top

iArr(i) = i

Next i

For i = Top To Bottom + 1 Step -1

r = Int(Rnd() * (i - Bottom + 1)) + Bottom

temp = iArr(r)

iArr(r) = iArr(i)

iArr(i) = temp

Next i

For i = Bottom To Bottom + Amount - 1

RANDNUMNOREP = RANDNUMNOREP & " " & iArr(i)

Next i

RANDNUMNOREP = Trim(RANDNUMNOREP)

End Function

basically what this does is generate a set of random numbers based on a criteria which is first number (of the range) last number (of the range) and how many numbers I want this numbers will not repeat so for example I would write in a cell =RANDNUMNOREP(25,50,10) That would result in 10 random numbers between 25 and 50 and those numbers will not repeat.

The problem with this is that since is a formula all the numbers generated are left on the same cell to my understanding there is no way in excel for a formula to generate numbers or text or any value in other cells but the one the formula was written on.

So my question is how can I translate this into a Macro that will display or generate the random numbers in separate cells?

Upvotes: 0

Views: 121

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

you can make your function return an array and thus fill a set of cells rather than create a string of values:

Function RANDNUMNOREP(Bottom As Long, Top As Long, Amount As Long)
'This UDF will generate a non-repeating set of random numbers in excel and display them in a row of cells
Dim iArr As Variant
Dim i As Long
Dim r As Long
Dim temp As Long

Application.Volatile

ReDim iArr(Bottom To Top)

For i = Bottom To Top
    iArr(i) = i
Next i

For i = Top To Bottom + 1 Step -1
    r = Int(Rnd() * (i - Bottom + 1)) + Bottom
    temp = iArr(r)
    iArr(r) = iArr(i)
    iArr(i) = temp
Next i

RANDNUMNOREP = iArr

End Function

For example, hi-light cells A1 thru E1 and in the formula bar enter the array formula:

=RANDNUMNOREP(10,50,5)

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

This is what it would look like:

demo

Upvotes: 1

hnk
hnk

Reputation: 2214

The trivial solution would be

  1. to run the Function in a Macro,
  2. take the output into a Variant array
  3. Set RngVariable = Range("A1:A10") or something like that
  4. RngVariable = OutputVariantArrayVariableName

Much better solutions, in case you need to maintain these as separate functions called from different cells dragged down are shown below (if interested)

The solution is not as obvious as it looks and can be handled in two ways.

  1. Create a function with static variables. So your function...

    • calls your Subroutine
    • Stores the result in a static variable array
    • Dishes out the correct value based on its position in the spreadsheet.

    This may work, but can still cause problems since you'll have to very aggressively manage the variables, overwriting them when not needed, etc.

    It can be done, but not advisable.

    You can use the same approach with Global variables but with no greater gain

  2. Now for a simple and elegant solution:

    • Use a pseudo-random number generator activated by a seed in your program, rather than using the standard random number generator.
    • Your calling function shall take in the serial number of the series to be generated, i.e. 3 = generate the 3rd number which the sub would have generated, etc.

    AND

    It takes in a seed, which you can randomly generate on the spreadsheet.

That way your function on the spreadsheet would be

= CALLNONREPEATINGNUMBER(RandomSeed, SerialNumber, Optional Min, Optional Max)

Maintain the same Seed for each series and use the seed to internally calculate what the 3rd of 4th of 10th number in the original series would have been.

There are plenty of simple uniform random number generation algorithms available which will deterministically generate a number using a seed, such that these numbers are statistically indistinguishable from a true uniform random number.

P.S. Anyways, that is how even the Rnd function internally works

Upvotes: 0

Related Questions