Question

Microsoft Excel VBA (Visual Basic for Applications) Programming Language

Objectives: Create an array and redim it to a size equal to an assigned value of a variable, populate the array with a series of random numbers, output the array to a message box and to a worksheet.

Instructions:

- Review the variables already declared. You won't need others.

- See comments in the code that will act as your guide. Add new code directly after each comment.

- Assign a value of intRandom. (start with 20, change it later, if desired)

- ReDim the array to be of a size equal to the previous variable.

- Fill the array with random numbers using RND function.

- Loop through the array and create a message string.

- Also, create a title that pulls the value of the sixth elements.

- Display the title and message in a Msgbox.

- Output the array to the output sheet with index value in column A and the random number in column B.

Lines of code needed: about 16 including 3 for each of 3 loops

The message box should like like the following:

$0?ui=2&ik=186360e1dd&attid=0.2&permmsgid$

________________________________________________________

Option Explicit

Option Base 1

' ******** DSS 620 TEMPLATE ---- Created by John A. Michl jmichl@sju.edu ********

' **

' **    Module 4        Exercise 2

' **    Template Revision: 01/12/2018 v1.1 by John A. Michl

' **    Note:   Professor's comments preceeded by '[

' **            Instructions / hints preceeded by '>>

' **    Student comments should use apostrophe only

' **

' **    Assignment modified by STUDENT NAME on DATE

' **

' ******************************************************************************

Sub RandomList()

    ' all variables needed for Part A have been declared

    Dim i As Integer        ' number of random numbers to make

     Dim intRandom As Integer ' number of random numbers to make

     Dim arrRandom() As Single    ' array in which to store numbers

    Dim strMsg As String

     Dim strTitle As String

   

     Randomize   ' this initializes the random number generator

   

     Debug.Print Rnd   ' just to show how easy it is to create a

   

     Worksheets("Output").Range("A:B").Clear         ' remove old results

   

     ']vvvvv Student code below vvvvvv

   

     '] assign a value to intRandom

   

    

     '] redim the array to be the appropriate size

   

    

     '] fill the array with random numbers using a loop

       

    

    

     '] create the list of numbers for the message string using a loop

     '] be sure to include both the index value i and the contents of the array

  

    

     '] create the message title to show the random number in position 6 of the array

     '] see the screen shot but note the random numbers will be different

   

    

    

     '] display the message box

   

    

     '] output the array to the output worksheet showing the index number in

     '] column A and the random number in column B

   

    

    

    '^^^^ Student code above

End Sub

Answer 1

Sub RandomList()

' all variables needed for Part A have been declared

Dim i As Integer ' number of random numbers to make
Dim intRandom As Integer ' number of random numbers to make
Dim arrRandom() As Single ' array in which to store numbers
Dim strMsg As String
Dim strTitle As String

Randomize ' this initializes the random number generator

Debug.Print Rnd ' just to show how easy it is to create a

Worksheets("Output").Range("A:B").Clear ' remove old results

']vvvvv Student code below vvvvvv

'] assign a value to intRandom
intRandom = 20

'] redim the array to be the appropriate size
ReDim arrRandom(intRandom)

'] fill the array with random numbers using a loop
For i = 1 To intRandom
arrRandom(i - 1) = Int((25 - 10 + 1) * Rnd + 10)
Next i


'] create the list of numbers for the message string using a loop
'] be sure to include both the index value i and the contents of the array
For i = 1 To intRandom
strMsg = strMg + Str(arrRandom(i - 1)) + " "
Next i


'] create the message title to show the random number in position 6 of the array
'] see the screen shot but note the random numbers will be different
strTitle = Str(arrRandom(6))

'] display the message box
MsgBox strMsg, vbInformation, strTitle


'] output the array to the output worksheet showing the index number in
'] column A and the random number in column B
For i = 1 To intRandom
Sheets("Output").Cells(i, 1) = i
Sheets("Output").Cells(i, 2) = arrRandom(i - 1)
Next i

'^^^^ Student code above

End Sub

________________________________________________________________________________________________

image screenshot

Book1 Microsoft Excel Home Insert Page LayoutFomulas Data Review View Developer 齒E-aaRecord Mae References 昂 KE Properties E Map Properties mport Expansion Packs port Refresh Data Use Relative Visual Macros Macro Security Add-Ins COM Insert Design Mode Document Add-Ins | Run Dialog Add-In5 Control Modify 114 25 17 17 Button 1 13 10 10 13 15 16 17 24 Sheet2 Sheet3 Microsoft Visual Basic for Applications - Bookl Modulel (Codel Eile Edit Yiew Insert Format Debug Bun Iools Add-Ins Window Help Type a question for help Project VBAProject XGeneral) RandomList Sub RandcnList VBAProject (Book1) all variables needed for Part A have been declared Microsoft Excel Objects ชา Sheet1 (Output) 삐 Sheet2°Sheet2) Dim 1 A3 Integer Dim intRandom As Integer 'number of random numbers to make Dim arRandom) Single Dim stHsg As String Dim strTitle As String number of random numbera to make Sheet3 (Sheet3) ThisWorkbook array in which to store numbers Module 1 Module2 Randomize " τη13 1n1t1a11zeg the random number generator Debug.Print Rnd ust to ahow how easy it is to create a Norksheets ( "Output") . Range ("A:B") .Clear Properties Madulel 'remove old results Modulei Module wv student code below vvwwww Alphabetc Cateporized Modic1 assign a value to intRandom intRandom10 ] redim the array to be the appropriate siz el fii1 the array with random numbers using a loop For i-1 To intRande arrRandom(i - 1)Int ((25 - 10 + 1Rnd 10) ext i "1 create the 11at of nubera or the measage string uaing a loop be sure to include both the index value i and the contents of thearray

thank you !