I want to create a list of random integers in the range [0, 9], that sums up to 100 in Excel, using VBA. The list should be printed in a single column.
The routine I've written is as follows:
Sub RandomList() Dim arr(100) As Integer Dim i As Integer i = 0 Do arr(i) = Int(10 * Rnd()) i = i + 1 Loop Until WorksheetFunction.Sum(arr) >= 100 arr(i - 1) = 100 - WorksheetFunction.Sum(arr) + arr(i - 1) Range("A1").Resize(i, 1) = Application.Transpose(arr) End Sub
This gives the correct result, but I'm sure there are ways to improve it:
- Initializing the size to 100, seems odd. I want to use a dynamic sized array, but I can't get it to work. I've tried different approaches, but all result in a list containing only zeros (or an error).
- I tried to sum all elements except the last one, but I didn't manage to make it work. Therefore I had to sum all elements, and subtract the last one. I can't believe that's optimal.
- Should I delete the
(100-i)last elements of the list somehow, before writing it to Excel? How and why?
- Is the last line a good way of writing to Excel, or is there a better way?
- I'm looking for a nice and clean way of doing this task, not necessarily the fastest way
I can do the same thing using
Function RandomList() too, but as far as I can tell, that's used when I want to call the function from the worksheet.
PS! I'm not interested in some builtin function that can do the task directly, as I'm doing this to learn VBA, not to do that specific task.
This is inspired by this (bad) question on SO. There are other approaches there that can be used, but they are substantially different. I'm wondering if the way I'm doing this is a "good way" of doing it, or if it's filled with "Bad practice" code.