PDA

View Full Version : [SOLVED] Convert C# to VBA



rong3
10-14-2017, 02:23 AM
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
10-14-2017, 03:14 AM
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
10-14-2017, 08:47 AM
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/showthread.php?45316-How-to-call-use-DLL-via-vba-macro

rong3
10-15-2017, 11:44 PM
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/showthread.php?45316-How-to-call-use-DLL-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
10-16-2017, 06:03 AM
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
10-16-2017, 10:55 AM
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
10-16-2017, 11:40 AM
Only the first number is at random.

Kenneth Hobs
10-16-2017, 02:17 PM
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
10-16-2017, 06:33 PM
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
10-16-2017, 06:35 PM
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
10-16-2017, 08:32 PM
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
10-16-2017, 08:39 PM
@Kenneth Hobs (http://www.vbaexpress.com/forum/member.php?3661-Kenneth-Hobs) Thank so much , it works like a dream, and exactly what I want.

Kenneth Hobs
10-16-2017, 09:02 PM
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
10-16-2017, 11:50 PM
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
10-17-2017, 07:34 AM
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
10-18-2017, 08:45 AM
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