coding of Module in VBA
Answer
Step 1 :Create a “main” sub, and assign the sub to the “Start” button on the worksheet as follows :
Sub a() Dim btn As Button Application.ScreenUpdating = False ActiveSheet.Buttons.Delete Dim t As Range For i = 2 To 6 Step 2 Set t = ActiveSheet.Range(Cells(i, 3), Cells(i, 3)) Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height) With btn .OnAction = "btnS" .Caption = "Btn " & i .Name = "Btn" & i End With Next i Application.ScreenUpdating = True End Sub Sub btnS() MsgBox Application.Caller End Sub
Step 2: Write the “clearOutput” sub. Also, assign this sub to the “Reset” button. In this sub, you will clear the content of range (B3:D5), (B9:B11), and (D14:D15) on the worksheet “RP” as a folows:
Sub Clearcells_Output()
Range("B3", "B5").Clear
Range("B9", "B11").Clear
Range("D14", "D15").Clear
End Sub
Step 3 : Write the “loadData” sub. The data is stored in the worksheet “HR”. Name the data range on the worksheet for T-bills, bond, and stock as “tbillHistory”, “bondHistory” and “stockHistory”. Then, you should use the historical data to calculate the average annual returns of each investment option (hint: use excel function Average). You also need to calculate the betas of each investment option. Note that Beta is a relative risk, so here we assume beta of stock is 1, therefore you will calculate the betas of T-bills and bond in relative to stock. The formula to calculate Beta (shown in the photo) Where r_a is return of T-bill or Bond, and r_b is return of stock. Covar() stands for covariance function, and Var() stands for variance function. These two functions are readily available in Excel. (Hint: using the range names you created in step 2 will simplify the code). After you calculate the betas and returns, write them to range (B3:C5) on worksheet “RP”.
sub btnCalculateVarCovar_Click()
Function VarCovar(Rng As range) As Variant Dim r_a As Integer Dim r_b As Integer Dim numcols As Integer numcols = Rng.Columns.Count Dim matrix() As Double ReDim matrix(numcols - 1, numcols - 1) For r_a = 1 To numcols For r_b = 1 To numcols matrix(r_a - 1, r_b - 1) = Application.WorksheetFunction.Covar(Rng.Columns(r_a), Rng.Columns(r_b)) Next r_b Next r_a VarCovar = matrix End Function
Open up a clean Module and complete the coding of Module in VBA. Do the following tasks 1. Create a “main” sub, and assign the sub to the “Start” button on the worksheet. 2. Write the “clearOutput”...