I am new to VBA and new to message boards. Please excuse any mistakes.
I have a spreadsheet of client information (approx. 10,000 clients), including client id, cashflows and dates in different columns. The dates are all sorted by each client. I would like to run the built in XIRR function once for each client using the cashflows and date information associated with their specific client id. I created a separate column with a "1" in each row that is the last row for a client. I was planning to run "=if(X2=1, abc123(...), "") in a new column so that it only runs the script when it sees that 1.
The script I created is pieced together from things I found online.
My function looks like this: =abc123(A2,A:A,S:S,M:M)
I am receiving a #ref error when I run this script:
Sub abc123(client_num, cin_client_id_col, cashflows_col, dates_col)
'runs internal rate of return at the client level
'--------------------------------------------
' create variant objects of length n to store values from range inputs\
'--------------------------------------------
n = WorksheetFunction.CountA(cin_client_id_col) 'number of elements in input ranges
ReDim client_array(1 To n, 1) As Variant
ReDim value_array(1 To n, 1) As Variant
ReDim date_array(1 To n, 1) As Variant
client_array = cin_client_id_col
value_array = cashflows_col
date_array = dates_col
'--------------------------------------------
' check if client array values are equal to the desired client key
'--------------------------------------------
ReDim client_key(1 To n, 1) As Variant
For i = 1 To n
If client_num = client_array(i, 1) Then
client_key(i, 1) = 1
Else
client_key(i, 1) = 0
End If
Next
'--------------------------------------------
' throw out clients that aren't used
'--------------------------------------------
Dim count As Integer
count = WorksheetFunction.Sum(client_key)
ReDim data_mat(1 To count, 2) As Variant
k = 1
For j = 1 To n
If client_key(i, 1) = 1 Then
data_mat(k, 1) = value_array(j, 1)
data_mat(k, 2) = date_array(j, 1)
End If
k = k + 1
Next
'--------------------------------------------
n = count
ReDim values_client(1 To n, 1)
ReDim dates_client(1 To n, 1)
For i = 1 To n
values_client(i, 1) = data_mat(i, 1)
dates_client(i, 1) = data_mat(i, 2)
Next
abc123 = WorksheetFunction.Xirr(values_client, dates_client)
'=client_irr(A2,A:A,S:S,M:M)
End Sub
Any guidance would be greatly appreciated.
Bookmarks