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