DushiPunda
Well-known Member
- Joined
- Nov 14, 2015
- Messages
- 509
Hello,
I currently have a spreadsheet which contains 2 worksheets:
1. Question Bank
2. Random Selection
On Question Bank, I am using Columns A, B, and C, in the following format:
<tbody>
</tbody>
On Random Selection, I am also using Columns A, B, and C, in the following format:
<tbody>
</tbody>
Starting on Row 3:
Column A:
Column B is just hard coded 1-X.
Column C:
My UDF, RandomSelection:
So, this was used at a previous job for the same reason I want to use it at my current job: to generate a random test. However, I'd like to implement one thing and I'm not too sure how to accomplish it:
I will be using this to generate a monthly test and I'd like to prevent questions from showing up in sequential months. As an example, if question 136 is used on May's monthly test, I don't want it to populate on another test until, say, August. So, when I'm generating the test for June & July, if the result of the UDF is 136, it should recalculate.
Just brainstorming ideas, I figured I could just add a helper column (say, Column D) on my Question Bank sheet where the value of the cell would be the numerical value of the month that question was last selected. And then, in subsequent months - if the result of the UDF refers to a question who's value in Column D is within the 3 of the numerical value of the current month, discard and try again (if Month(Date) - D.Value <= 2 then discard/try again).
But the problem is, I didn't write the code for this function and so I'm having a little trouble figuring out how exactly to implement this function.
Any help is greatly appreciated. Thanks!
I currently have a spreadsheet which contains 2 worksheets:
1. Question Bank
2. Random Selection
On Question Bank, I am using Columns A, B, and C, in the following format:
# | Question | Answer |
1 | Question 1 | Answer 1 |
.. | .. | .. |
150 | Question 150 | Answer 150 |
<tbody>
</tbody>
On Random Selection, I am also using Columns A, B, and C, in the following format:
Rnd | # | Selected Question |
<tbody>
</tbody>
Starting on Row 3:
Column A:
Code:
{=RandomSelection('Question Bank'!A2:A149)}
Column B is just hard coded 1-X.
Column C:
Code:
=VLOOKUP(A3,'Question Bank'!$A$2:$B$149,2,FALSE)
My UDF, RandomSelection:
Code:
Option Explicit
Function RandomSelection(aRng As Range)
Dim myTarg As Range, _
SrcList, Rslt(), _
i As Long, j As Long, k As Long
Application.Volatile 'Force recalculation (new selection of random data) even if data doesn't change
SrcList = aRng.Value
Set myTarg = Application.Caller
Randomize
With myTarg
If .Areas.count > 1 Then
RandomSelection = _
"Function can be used only in a single contiguous range"
Exit Function '<<<<<
End If
If .Rows.count > 1 And .Columns.count > 1 Then
RandomSelection = _
"Selected cells must be in a single row or column"
Exit Function '<<<<<
End If
If .Cells.count > aRng.Cells.count Then
RandomSelection = _
"Range specified as argument must contain more cells than output selection"
Exit Function '<<<<<
End If
ReDim Rslt(1 To IIf(.Rows.count > 1, .Rows.count, .Columns.count))
End With
j = UBound(SrcList, 1)
For i = LBound(Rslt) To UBound(Rslt)
k = Int(Rnd() * (j - LBound(SrcList, 1) + 1)) + LBound(SrcList, 1)
Rslt(i) = SrcList(k, 1)
SrcList(k, 1) = SrcList(j, 1)
j = j - 1
Next i
If myTarg.Rows.count > 1 Then
RandomSelection = Application.WorksheetFunction.Transpose(Rslt)
Else
RandomSelection = Rslt
End If
End Function
So, this was used at a previous job for the same reason I want to use it at my current job: to generate a random test. However, I'd like to implement one thing and I'm not too sure how to accomplish it:
I will be using this to generate a monthly test and I'd like to prevent questions from showing up in sequential months. As an example, if question 136 is used on May's monthly test, I don't want it to populate on another test until, say, August. So, when I'm generating the test for June & July, if the result of the UDF is 136, it should recalculate.
Just brainstorming ideas, I figured I could just add a helper column (say, Column D) on my Question Bank sheet where the value of the cell would be the numerical value of the month that question was last selected. And then, in subsequent months - if the result of the UDF refers to a question who's value in Column D is within the 3 of the numerical value of the current month, discard and try again (if Month(Date) - D.Value <= 2 then discard/try again).
But the problem is, I didn't write the code for this function and so I'm having a little trouble figuring out how exactly to implement this function.
Any help is greatly appreciated. Thanks!