Convert C# to VBA

[rong3]

Hi all, Can anyone help me to convert this C# to VBA, this function returns a random list number that sum all equal to the given number.
Ex: Given number:100 and input: 6 then generate a list contains 6 numbers that sum to given number.

        public static List<int> RandomListByIncrementing(int digitMin, double digitMax, int targetSum, int numDigits)
        {
            if (targetSum < digitMin * numDigits || targetSum > digitMax * numDigits)
                throw new ArgumentException("Impossible!", "targetSum");
 
            List<int> ret = new List<int>(Enumerable.Repeat(digitMin, numDigits));
            List<int> indexList = new List<int>(Enumerable.Range(0, numDigits + 1));
 
            Random random = new Random();
            int index;
 
            for (int currentSum = numDigits * digitMin; currentSum < targetSum; currentSum++)
            {
                index = random.Next(0, indexList.Count - 1);
 
                if (++ret[indexList[index]] == digitMax)
                {
                    indexList.RemoveAt(index);
                }
            }
            return ret;
        }

[snb]

If you are interested in VBA you should take the challenge yourself to create a macro that performs just that.
In this case 'translating' isn't the issue.

[Kenneth Hobs]

Similar to snb's thoughts, what is your real goal? Is it translation or making a VBA routine to accomplish the same goal?

If the latter, I would guess that a solution already exists on this forum or another. If the former, can you give example input and output? I am not clear on what the numdigits means. I was thinking it might be a number of decimal places thing.

I can't remember if I was able to get the .Net Random.Next to work in VBA or not. We have used some .Net routines. e.g.

'http://www.vbaexpress.com/forum/showthread.php?48491Function ArrayListSort(a As Variant, Optional bAscending As Boolean = True)
    With CreateObject("System.Collections.ArrayList")
        Dim cl As Variant
        For Each cl In sn
            .Add cl
        Next
         
        .Sort 'Sort ascendending
        If bAscending = False Then .Reverse 'Sort and then Reverse to sort descending
        ArrayListSort = .Toarray()
    End With
End Function

For the ++ increment, we use i=i+1 sort of thing in VBA.

Of course you could always make that into a DLL and use it in Excel. I have shown how to do that for a short vb.net example. http://www.vbaexpress.com/forum/show...-via-vba-macro

[rong3]

Similar to snb's thoughts, what is your real goal? Is it translation or making a VBA routine to accomplish the same goal?

If the latter, I would guess that a solution already exists on this forum or another. If the former, can you give example input and output? I am not clear on what the numdigits means. I was thinking it might be a number of decimal places thing.

I can't remember if I was able to get the .Net Random.Next to work in VBA or not. We have used some .Net routines. e.g.

'http://www.vbaexpress.com/forum/showthread.php?48491Function ArrayListSort(a As Variant, Optional bAscending As Boolean = True)
    With CreateObject("System.Collections.ArrayList")
        Dim cl As Variant
        For Each cl In sn
            .Add cl
        Next
         
        .Sort 'Sort ascendending
        If bAscending = False Then .Reverse 'Sort and then Reverse to sort descending
        ArrayListSort = .Toarray()
    End With
End Function

For the ++ increment, we use i=i+1 sort of thing in VBA.

Of course you could always make that into a DLL and use it in Excel. I have shown how to do that for a short vb.net example. http://www.vbaexpress.com/forum/show...-via-vba-macro

I can explain shortly by this example which function means:
* GIven number: 15
*Quantity Number random: 3
Then the result will return as List: 6,4,5 (6+4+5=given number).
Cause i am not good at code too much, so I really need your help.

[mdmackillop]

Ex: Given number:100 and input: 6 then generate a list contains 6 numbers that sum to given number.

Is this an accurate example or are your actual numbers radically different? e.g. 100,000 and 60
I assume you want 6 different numbers?

[Kenneth Hobs]

I added an option to allow 0's or not. It does allow repeat integers.

Sub Test_kRandom()  Dim a
  a = kRandom(100, 20)
  With Range("A1:A20")
    .ClearContents
    .Value = WorksheetFunction.Transpose(a)
  End With
End Sub


'Similar to, https://codereview.stackexchange.com/questions/132075/create-a-list-of-random-numbers-with-sum-n/132078
Function kRandom(mySum As Long, kItems As Long, _
  Optional tfZeros As Boolean = False)
  Dim myNumbers() As Long
  Dim i As Long, j As Long, k As Long
  ReDim myNumbers(mySum)
  
Again:
  On Error GoTo Again
  i = 0
  k = 0
  Do
jAgain:
    j = Int(10 * Rnd())
    If Not tfZeros And j = 0 Then GoTo jAgain
    If k + j > mySum Then j = mySum - k
    k = k + j
    myNumbers(i) = j
    i = i + 1
    If i > kItems - 1 Then GoTo Again
  Loop Until WorksheetFunction.Sum(myNumbers) = mySum
  ReDim Preserve myNumbers(0 To kItems - 1)
  'Debug.Print i, j, k, LBound(myNumbers), UBound(myNumbers)
  kRandom = myNumbers
End Function

[snb]

Only the first number is at random.

[Kenneth Hobs]

I don't know what you mean snb. I did +16k tests. Randomize can be added but some will argue about that too.

For the routine in #6, it still needs some work to skip 0 entries.

Here is another routine. It too can have multiple duplicate numbers and 0(s).

Sub Test_geRandom()  
  Dim a, i As Integer
  
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  
  ActiveSheet.UsedRange.ClearContents
  For i = 1 To 5  'Columns.Count
    a = kRandom(100, 20, False)
    With Range(Cells(1, i), Cells(20, i))
      .Value = WorksheetFunction.Transpose(a)
    End With
  Next i
  
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
End Sub


'Similar to, Gary Evans, https://stackoverflow.com/questions/37835877/generate-n-random-numbers-summed-upto-100-vba
Function geRandom(aSum As Long, xItems As Long)
  Dim AryNumbers()    As Long
  Dim LngCounter      As Long
  'ReDim AryNumbers(0)
  ReDim AryNumbers(0 To xItems - 1)


  Randomize


  Do Until LngCounter = aSum
    AryNumbers(UBound(AryNumbers, 1)) = Int(xItems * Rnd + 1)
    If (LngCounter + AryNumbers(UBound(AryNumbers, 1))) > aSum Then
      AryNumbers(UBound(AryNumbers, 1)) = aSum - LngCounter
      Else
        LngCounter = LngCounter + AryNumbers(UBound(AryNumbers, 1))
        'ReDim Preserve AryNumbers(UBound(AryNumbers, 1) + 1)
    End If
  Loop
  geRandom = AryNumbers
End Function

[rong3]

Is this an accurate example or are your actual numbers radically different? e.g. 100,000 and 60
I assume you want 6 different numbers?

If your input is 100,000 and 60, it will be generate 60 random numbers that sums to 100,000.

[rong3]

I don't know what you mean snb. I did +16k tests. Randomize can be added but some will argue about that too.

For the routine in #6, it still needs some work to skip 0 entries.

Thank you so much, i tried your k_Random with values as 300 and 6, and it loop constanly and freeze, I must restart the Excel too many times for exit.

[Kenneth Hobs]

Please do not quote a whole post unless there is a reason. A direct response is usually for the previous post. If unclear who you are responding to: e.g. Code in posts #6 and #8, takes to long to run.

This code will zero in faster. There are cases where no solution will be possible and an infinite loop will really occur.

Sub Test_RandomSumTo()  Dim a, i As Integer
  
  Application.EnableCancelKey = xlErrorHandler
  On Error GoTo TheEnd
  
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  
  ActiveSheet.UsedRange.ClearContents
  For i = 1 To 5  'Columns.Count
    a = RandomSumTo(300, 6)
    If Not IsArray(a) Then GoTo TheEnd
    With Range(Cells(1, i), Cells(UBound(a), i))
      .Value = WorksheetFunction.Transpose(a)
    End With
  Next i
  
TheEnd:
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
End Sub


'Similar to, Mike Erickson, http://www.ozgrid.com/forum/showthread.php?t=200724&p=775408
Function RandomSumTo(targetValue As Long, CountOfTerms As Long, _
  Optional tfZeros As Boolean = True) As Variant
  Dim Result() As Double
  Dim tempSum As Double
  Dim i As Long, j As Long
  ReDim Result(1 To CountOfTerms)
  
  Rem make random array
  For i = 1 To CountOfTerms
jAgain:
    j = Rnd()
    If Not tfZeros And _
      WorksheetFunction.Sum(Result) <> 0 Then
      If WorksheetFunction.Round( _
        j * targetValue / WorksheetFunction.Sum(Result), 0) = 0 _
        Then GoTo jAgain
      End If
    Result(i) = Rnd()
  Next i
  
  Rem make result sum to targetvalue
  tempSum = WorksheetFunction.Sum(Result)
  For i = 1 To CountOfTerms
    Result(i) = Result(i) * targetValue / tempSum
    Result(i) = WorksheetFunction.Round(Result(i), 0)
  Next i
  
  Rem adjust last term for round-off error
  Result(CountOfTerms) = targetValue - (WorksheetFunction.Sum(Result) - Result(CountOfTerms))
  ReDim Preserve Result(1 To CountOfTerms)
  RandomSumTo = Result
End Function

[rong3]

@Kenneth Hobs Thank so much , it works like a dream, and exactly what I want.

[Kenneth Hobs]

Good deal. Mike does good work. He used concepts similar to the manual method.

The other methods are fine but don't converge as fast as his does. As you saw, the fewer the picks that work, the longer it took. For 20 of 100, the others were very fast. I often see similar things in other code. For a small dataset, one can use many methods. For speed working with large datasets, more optimal methods are needed. So, one needs to know their data well to really know what is "best".

[snb]

Sub M_snb()
    y = 100
    ReDim sn(5)
    
    For j = 0 To UBound(sn) - 1
       sn(j) = Int((y - Z) * Rnd)
       Z = Z + sn(j)
    Next
    sn(j) = y - Z
    
    MsgBox Join(sn, vbLf)
End Sub

[Kenneth Hobs]

Great job snb!

As usual, his routine is concise and runs very fast. He does not use Dim so all his variables are type Variant. I like Dim, myself. To test his routine, I added it as my usual practice. When you test these, be sure to test in a backup copy and the ActiveSheet is empty.

One thing that I noticed was that snb's, as a general observation for the (300, 6) test, tends to pick a 3 digit number early and towards the end, single digits and two or three 1's and 0's. Of course this is fine given the constraints.

For Mike's routine, using the same setup as above, I see that most of his numbers are 2 digits. In the scheme of random numbers to a set sum, that is fine too.

As an Engineer, I used a stratified random number scheme in my practice. I generated the numbers before I left the office. Of course like any good Civil Engineer, I made adjustments in the field if the computation was not practical.

I may workup a .Net project and see how post #1's routine compares. It would be a good 2nd DLL tutorial.

FWIW:

Sub Test_snbRandom()  Dim a, i As Integer
  
  Application.EnableCancelKey = xlErrorHandler
  On Error GoTo TheEnd
  
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  
  ActiveSheet.UsedRange.ClearContents
  For i = 1 To Columns.Count
    a = snbRandom(300, 6)
    If Not IsArray(a) Then GoTo TheEnd
    With Range(Cells(1, i), Cells(UBound(a) + 1, i))
      .Value = WorksheetFunction.Transpose(a)
    End With
  Next i
  
TheEnd:
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
End Sub


'y=Sum of return array, xItems number of elements in return array.
Function snbRandom(y, xItems)
  Dim j, z
    xItems = xItems - 1 'Base 0 array
    ReDim sn(xItems)
    
    'Randomize
     
    For j = 0 To UBound(sn) - 1
        sn(j) = Int((y - z) * Rnd)
        z = z + sn(j)
    Next
    sn(j) = y - z
     
    snbRandom = sn
End Function

[snb]

I tested this way, but fundamentally the order in which the numbers are being generated does not matter; you might want to shuffle them afterwards if necessary:

Sub M_snb()
    y = 300
    ReDim sn(60, 5)
    
    For j = 0 To UBound(sn)
      Z = 0
      For jj = 0 To UBound(sn, 2) - 1
            sn(j, jj) = Int((y - Z) * Rnd)
            Z = Z + sn(j, jj)
        Next
        sn(j, jj) = y - Z
     Next
    
    Cells(1).Resize(UBound(sn) + 1, UBound(sn, 2) + 1) = sn
End Sub