Generate Test Paper from Question Bank Using Excel VBA

Josh wants to know how to Generate Test Paper from Question Bank Using Excel VBA. Here is his email:
‘How can I create a question paper automatically in MS Excel?

I have a question bank of 110 questions in Excel and i would like to create a test paper automatically. Every week I want to select 10 questions randomly from my collection of the 110 questions to create a test paper. No question should repeat in the test paper.


In our VBA code we first generate a random number unsing the random function. Next using the countif function we ensure that the number generated is unique. If it’s a duplicate we generate the number again till we have 10 unique numbers because we wish to get 10 questions from a question bank of 110 questions. Of course you can use our VBA code with as many questions as you have in your question bank and as many unique questions you wish to generate for your weekly or monthly or term test paper.
We assign these unique randomly generated numbers to get data from workshee2 into worksheet1 using the RowNum variable. So that the process of generating the test paper id fully automated we use a looping process with a ‘for… next’ loop.

You can use the code as is to create your own test paper. Just ensure that your question bank has the data in the same columns as in our example. Also take care that your sheets are named like our worksheets or change them accordingly.

Private Sub CommandButton1_Click()
‘First we define two variables
Dim i, RowNum
‘We clear all the content in column A
‘We use the ‘for next’ loop to get our questions for the test paper. If you want 30 questions use i= 1 to 30
For i = 1 To 10

‘ we generate random integers. If you 2000 questions in your question bank use 2000 in the random function
RowNum = Application.RoundUp(Rnd() * 110, 0)
‘This gets the number generated in column C
Cells(i, 3).Value = RowNum
‘ We want no duplicates so we check with countif
If Application.CountIf(Sheets(“Sheet1”).[A:A], Sheets(“Sheet2”).Cells(RowNum, “A”)) = 0 Then
‘if not duplicate we get the question from sheet2 to sheet1
Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Offset(1).Value = Sheets(“Sheet2”).Cells(RowNum, “A”).Value
‘if number generated is duplicate we generate another random number
GoTo generate
End If
Next i
‘ we do some formatting of the data in sheet1
Range(“A1”).Value = “Define the following terms in MS Excel”
Range(“A1”).Font.Bold = True

End Sub

Watch the video:

Further reading:

2 thoughts on “Generate Test Paper from Question Bank Using Excel VBA

  1. Jathin Dev

    Hi Sir,

    I am a great fan of yours. I learnt most of the basic VBA techniques from your videos. Those videos are awesome.
    I have a task which needs to be done through macros. I have n number of data contains Username in one column and Case file ids in other column. I want a macro which randomly select casefile ids on following manner and paste in another sheet.
    User1 – 3 Samples
    User 2- 4 samples
    user 3- 2 samples
    User 4- 1 sample
    (the above samples varies)
    Can you please help me on this?

  2. Chris

    First I’d like to say that this is an awesome video and your website is great too. I can learn by reading things but being able to see something is by far the easiest way for me to learn.

    I have a question though. You’re video is half of what I’m trying to do. I want a random question test generator like you have here, but I want to incorporate 4 multiple choice answers into each question and have the answer populate off to the side.

    I currently have 144 questions and they are all multiple choice questions. I want it to be a random 50 question test and I want it to include all of the multiple choice questions preferably below it.

    Is there a way to make this work or no?

    Thanks for any assistance/guidance.



Leave a Reply

Your email address will not be published. Required fields are marked *