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:

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

  1. 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?
    [email protected]

  2. 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.


  3. Hello,

    I am trying to use your code but, unfortunately, I am getting duplicate values. I’ve determined the cause to be the use of additional lines in the cells I am attempting to randomly pull (additional lines meaning “alt+enter” in the cell). Is there any way to get this code to work if I am using these additional line formats in the cells?


  4. Hello,

    Sorry for another response, but I figured out a way around my previous question. With that said, my test bank has a header row that I do not want included when the program randomly pulls the test question. How can I prevent the random generator from grabbing from the first row of the test bank?


  5. first i want to thank you about the amazing help that you made for me
    second i want to ask you one question and i need your answer as soon as possible please
    i wrote all the codes and i finished everything but i can not make save to complete after another time
    so can you please help me to make an auto save to keep not losing any data

  6. Dear Dinesh,

    God afternoon from Italy..

    i very appreciate that somebody may help people as this. I am just a novice and with your video i’m learning more..
    I have change a bit the VBA, just for have the total of question into bank and decide with a cells value the amount of maximun question for test.
    I have tried to improve your VBA sample because i need to have more option and i will try to explain you, maybe you can help me.
    I added a cells columns for define wich kind of question is as “Topic1, topic2, topic3 ect.ect.
    So, maybe i must decide the amount maximum of question for each topics.. can be mixed between topics of course.
    Sample as: 3 for the topics#1, 4 for topics#2, 0 for [email protected]
    and when a group of student is bigger to have opportunity to change position of correct answer..just like random position as example below.
    Bank with 100 question divided into 10 different topics.
    – test #1
    Q.1 xxxxxxx
    A. ..(wrong)
    B. ..(Correct)
    C. .. (wrong)

    – Test #2 (same question for example)
    Q. 1 xxxxxxx
    A. (correct)
    B. (wrong)
    C. (wrong)

    i have tried to change VBA but of course my low, very low experience generate error..

    So, you have any idea how i may improve it?

    Thanks for all of your support.


  7. Sorry, forgot to say you that i have this columns:
    – A – Question #
    – B – Question text
    – C – Group (topics1, Topics2..ect.ect
    – D – Answer A
    – E – Answer B
    – F – Answer C
    there are always 3 answer possible for each question…

  8. Dear Sir,

    This video is very informative. I am trying to construct a chart audit for our electronic health record utilized at our hospital.
    1. I want the first 12 questions to always be the same as this is basic audit information.

    2. I want rows 13 to 53 to be the randomly, non repeating questions to appear.

    3. I want the whole row “A:O” to paste into the “AUDIT SHEET.” The whole row is essential as it contains categories and information to point the staff members to the correct area of the chart.

    4. I also want to create a button at the end of the audit to submit the whole row, with answer to a collective excel spreadsheet to develop and examine any trends.

    I have created the workbook and have run into a dead end attempting to copy the whole row into the audit.

    Your help has been amazing to my projects in the past and I am ever hopeful to get some guidance to finish my project.

    Thank you in advance.

    Very Respectfully,

    Roxanne Truth Sickles

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.