Question

The user (me) will input between 3 and 20 data points for two variables on Sheet1 starting in cells A2 and B2 (your program should work if I input 3, 4, 5, … , or 20 data points). Column A will contain the values for variable x and column B will contain the values for variable y.

Graphical User Interface

Create a UserForm on Sheet1 that contains the following controls:

A Run button will be used to execute a macro that obtains data from the worksheet. The data will be used to calculate a₀, a₁, r, and the percentage probability. These four values will be output to four TextBoxes on the UserForm.

A fifth TextBox will tell the user if the relationship between the x and y data is "significant" or "not significant".

A Clear button will clear all five TextBoxes.

A Quit button will close the UserForm.

Also create a button on Sheet1 that will load the UserForm.

Executing the Program

When the user clicks the Run button on the UserForm, your program will perform the following tasks:

1) Call a Sub procedure named GetInput that obtains the x and y values from Sheet1 and stores the data in a 2D array named xyvalues.

2) Call a Sub procedure named CalcLine that calculates the coefficients for the best-fit line (a₀ and a₁) using the data in xyvalues.

3) Call a Function procedure named CalcR that calculates the correlation coefficient (r) using the data in xyvalues.

4) Call a Function procedure named CalcPerProb that determines the percentage probability using the values of r and N, and the values of r_O in Table C.

Note: Store the values from Table C in Sheet3 before running the code as you will need to access those values in CalcPerProb. You likely will need to interpolate between values of r_O. See the end of the Background section or the Testing section for examples of how to do this.

5) Call a Sub procedure named GiveOutput that outputs a₀, a₁, r, the percentage probability, and whether the correlation is significant to the five TextBoxes.

Answer 1

open excel sheet and press alt+F11 to open a vb page
Here go to insert and choose user form to open an user form
take 3 buttons for RUN, CLOSE and CLEAR
take 5 text box to display 4 values and the significance relationship

create a button in spread sheet and write a below code to open user form

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

Microsoft Visual Basic - Book1.xlsx - [Book1.xlsx - UserForm1 (UserForm)] 쑤 File Edit View Insert Format Debug Run lools Add-Ins Window Help Type a question for help roject VBAProject X UserForm1 23 EVBAProject (Book1.xlsx) 白 Microsoft Excel Objects RUN ] Sheet1 (Sheet1) Sheet2 (Sheet2) Sheet3 (Sheet3) ThisWorkbook 白 Forms UserForm 1 UserForm2 close 白 Modules clear Module 1 嘰Module2 Watches Value Toolbox Context Expression Controls

Microsoft Visual Basic- Book1.xlsx-Usertorm 1 (Code)] View Insert Format Debug Run Iools Add-Ins Window Help Type a que ect (General) (Declarations) Private Sub Clear Click () ClearForm (Book1.xlsx) Excel Objects tl (Sheet1) t2 (Sheet2) t3 (Sheet3) orkbook End Sub Private Sub Close Click () cmdClose Click End Sub orm orm Private Sub Run Click) le1 le2 ReadData CalcLine CalcR CalcP Watches Expression Value

Microsoft Visual BasiC - BookT.xlsx - Modulel (Code le Edit View Insert Format Debug Run Iools Add-Ins Window Help VBAProject X (General) cmdClose Click rowcounter = 0 'to move downwards BAProject (Book1.xlsx) Microsoft Excel Objects rowcounter -rowcounter 1 Sheet1 (Sheet1) Sheet2 (Sheet2) Sheet3 (Sheet3) ThisWorkbook to check if we hit last row X = Sheets ("Sheets!") . Cells (rowcounter, Xcol). Value If X = "" Then rowcounter = 3 Forms End If UserForm1 E UserForm2 Modules to extract value X = Sheets ("Sheets!") . Cells (rowcounter, Y= Sheets ("Sheets!"). Cells (rowcounter, Xcol). Value Yc01). Value Module 1 嘰Module2 End Sub Sub ClearForm) For j 1 To 5 Next j End Sub UserForm1.controls ("TextBox" & j).Value = "" Private Sub cmdClose Click ) Close UserForm Unload Me End Sub Watches ression Value

define all these sub procedures in module

write a sub program to RUN button
1. to read x and y column

Private Sub GetInput()
   Dim X.col As Integer
   Dim Y.col As Integer
  
   X = 1
   Y = 2

   Dim X As Integer
   Dim Y As Integer
   Dim rowcounter As integer

   rowcounter = 0
' to move downwards
   rowcounter = rowcounter + 1

   'to check if we hit last row
   X = Sheets("Sheets1").Cells(rowcounter, X.col).Value
   If X = "" Then
       rowcounter = 3
   End If  

   'to extract value
   X = Sheets("Sheets1").Cells(rowcounter, X.col).Value
   Y = Sheets("Sheets1").Cells(rowcounter, Y.col).Value

End Sub

to clear form

Sub ClearForm()

For j = 1 To 5
    UserForm1.Controls("TextBox" & j).Value = ""
Next j

End Sub

to close all

Private Sub cmdClose_Click()
    'Close UserForm.
    Unload Me
End Sub

to calculate coefficient

Sub CalcR()

Dim r As Integer
r =CORREL( B3:B20, A3:A20 )
TextBox3.Value = r
End Sub

to calculate propability

Sub Calp()

Dim P As Integer
Dim Avg As Integer

Avg = "=AVERAGE(A1:A2)"

P = NORM.DIST(A3:A20,Avg,r, TRUE)
TextBox4.Value = P
End Sub