I am working on writing a discrete random variable generator function in excel vba. I've pulled this off a number of ways but here is the one I like the most.
Imagine in excel we have the following in cells A1:B3
red 5 blue 7 yellow 3
The following code is modeled as follows. Imagine drawing a line going from 0 to 15 (where 15 is 5 + 7 + 3). The line is red from [0,5) and blue from [5,12) and yellow from [12,15). And you pick a uniformly distributed random number, x, from 0 to 15. And wherever on the line x falls, that is the color that wins.
Here is the function:
Function discrete(r1 As Range, r2 As Range) 'shrink column ranges to contiguous cells Set r1 = Range(r1(1), r1(1).End(xlDown)) Set r2 = Range(r2(1), r2(1).End(xlDown)) 'convert ranges to arrays Dim arr1, arr2 As Variant arr1 = r1.Value arr2 = r2.Value Dim n As Long n = Application.Sum(arr2) 'sum total of 2nd array Dim x As Double x = n * Rnd() 'generate a random number 0 <= x < n Dim min, max As Double min = 0 max = 0 Dim i As Long For i = LBound(arr1, 1) To UBound(arr1, 1) 'where on the line x falls max = max + arr2(i, 1) If x >= min And x < max Then discrete = arr1(i, 1) End If min = min + arr2(i, 1) Next i End Function
I'm looking for some critique of my code and approach.
Assuming I'm not picking from 1000 values (column a), is it reasonable that I convert the ranges to arrays? Is this a time saving step? Or should I just work with the ranges? What I mean is, does this step pay for itself computationally given that I may always be working with only a few rows of data? Or might it be worth it solely on that basis that I may indeed supply this function with large ranges?
Even if you supply a full column, ie.
discrete(range("a:a"), range("b:b")), the range inside the function is reset to only those cells that are contiguous starting from the first cells of the supplied ranges. Is that a reasonable approach?
The algorithm itself relies on a for loop and 2 simple variables (min, max). I this a lightweight enough approach? Or are there some generally accepted practices I'm not aware of?