Selection of range for the 'slope' function

[avk47]

Hello everyone,
Here is what I'm trying to do:

1) I have column labels, and row labels on one worksheet which I input into
a function.

2) These inputs should be matched with column and row labels, and then input
into the index function to get a certain cell - let's call it the event cell.

I'm ok with this part. Then:

3) I would like to collect a one column array. The number of cells in this
array should depend on an input from a cell in the worksheet, and the final
cell in the array should be the column label of the column where I am
inputing the formula.

4) The array from 3) should go into the slope function.

5) Then I want to get the second array for the slope function from a fixed
column, but the same rows as in the variable first array, and this should
input into the function.

Below is what I have been trying to do, but it's not working. I would
appreciate any help.



Option Base 1

Function beta(company_name, event_date, event_window)
'Calculates beta based on pre-event window data
Dim row_num, col_num, col_mar, event_cell, market_cell 'Event Cell Location
Dim L As Integer 'Estimation window
Dim i As Integer
Dim start_equity, start_market 'start of array
Dim y(), x() As Variant 'Equity Array, Market Array for regression

beta = Application.WorksheetFunction.Slope(y(company_name, event_date,
event_window), x(company_name, event_date, event_window))
End Function

Function event_cell(company_name, event_date, event_window)
'Location of Event Cell
row_num = Application.WorksheetFunction.Match(event_date, Sheets("Equity
Returns").Range("ER_Dates"), 0)
col_num = Application.WorksheetFunction.Match(company_name, Sheets("Equity
Returns").Range("Companies"), 0)
event_cell = Application.WorksheetFunction.Index(Sheets("Equity
Returns").Range("Equity_Returns"), row_num, col_num)

End Function

Function market_cell(company_name, event_date, event_window)
'Location of Market Cell
row_num = Application.WorksheetFunction.Match(event_date, Sheets("Equity
Returns").Range("ER_Dates"), 0)
col_mar = Application.WorksheetFunction.Match("FTSE All Share",
Sheets("Equity Returns").Range("Companies"), 0)
market_cell = Application.WorksheetFunction.Index(Sheets("Equity
Returns").Range("Equity_Returns"), row_num, col_mar)
End Function

Function y(company_name, event_date, event_window)
'Definition of Estimation Window for Equity
Dim equity_array() As Variant
L = Sheets("Abnormal Returns").Range("B4")
ReDim equity_array(L)

start_equity = event_cell(company_name, event_date, event_window).Offset(-L
+ event_window, 0)

'Array Generation
For i = 0 To L
equity_array = start_equity.Offset(i, 0)
Next i
y = equity_array
End Function

Function x(company_name, event_date, event_window)
'Definition of Estimation Window for Equity
Dim market_array() As Variant
L = Sheets("Abnormal Returns").Range("B4")
ReDim market_array(L)

start_market = market_cell(company_name, event_date, event_window).Offset(-L
+ event_window, 0)

'Array Generation
For i = 0 To L
market_array = start_equity.Offset(i, 0)
Next i
x = equity_array
End Function

 

[Joel]

Par t of you r problem may be the referencing of ranges on worksheets without
passing the parameter to the function

from
Function x(company_name, event_date, event_window)
'Definition of Estimation Window for Equity
Dim market_array() As Variant
L = Sheets("Abnormal Returns").Range("B4")
ReDim market_array(L)

to
Function x(company_name, event_date, event_window,L)
'Definition of Estimation Window for Equity
Dim market_array() As Variant
ReDim market_array(L)


Excel won't call the function is you change the value in Sheets("Abnormal
Returns").Range("B4") without referencing the the cell in the call paramters.


For debugging the code you should add break points in the functions by
clicking on the line in the code and then pressing F9. Then go back to
worksheet and force a change in the worksheet to call the functions. I do
this by clicking on the cell in the worksheet where the call to the function
is located (ie =Function x()). then going to the Fx box at top of worksheet
and clicking at end of formula with mouse and pressing the ENTER on the
keyboard.

You can step through the code by typing F8. You can view values by adding
watch to VBA. Hightlight variable market_array with mouse then right click
and select ADD WATCH.