Creating a macro for multiple investment sheets

[matpalmeiras]

The program must verify for each fund the amount of time necessary to reach the desired capital. Using the time calculated and the investor's profile, the program must choose the best fund for investment.

There are several text documents with data from the worksheets.

The worksheet "Rentability" must have on Cell A1 the number of worksheets and in the lines below the complete name of a rentability archive. The rentability archives are in the same way as below:

The fund names and profile risks are numerical, varying from 1(low) to 4(very high).

In each line from the archive, we have the following information, separated by a blank space: fund identifier, risk, administration rate, initial minimum and monthly rentability.

In the first line of worksheets "Results", the user must put the entry data: on Cell A1 the risk profile, on B1 the initial capital, and on C1 the final capital desired.

On Cell D1 must appear the name of the best fund's archive, the best fund on Cell E1, and the number of months necessary on Cell F1.

To read the rentability data from the archives the following function must be used:
"Function LeMatrizDeRentabilidade(NomeArquivo As String, Rentabilidade() As Double) As IntegerDim Delimiter As String
Dim TextFile As Integer
Dim FileContent As String
Dim LineArray() As String
Dim DataArray() As Double
Dim TempArray() As String

Dim rw As Integer
Dim col As Integer

Dim i As Integer
Dim j As Integer


'Inputs

Delimiter = " "
rw = 0

'Open the text file in a Read State

TextFile = FreeFile
Open NomeArquivo For Input As TextFile

'Store file content inside a variable

FileContent = Input(LOF(TextFile), TextFile)

'Close Text File

Close TextFile

'Separate Out lines of data

LineArray() = Split(FileContent, vbCrLf)

'Read Data into an Array Variable

For x = LBound(LineArray) To UBound(LineArray)
If Len(Trim(LineArray(x))) <> 0 Then
'Split up line of text by delimiter
TempArray = Split(LineArray(x), Delimiter)
'Determine how many columns are needed
col = UBound(TempArray)
'Re-Adjust Array boundaries
ReDim Preserve DataArray(col, rw)
'Load line of data into Array variable
For y = LBound(TempArray) To UBound(TempArray)
DataArray(y, rw) = CDbl(TempArray)
Next y
End If
'Next line
rw = rw + 1
Next x

For i = 0 To rw - 3
For j = 0 To col
Rentabilidade(i, j) = DataArray(j, i)
Next
Next

LeMatrizDeRentabilidade = rw - 2

End Function"

And also, if you could, ordenate the lines from the worksheet "Results" (beggining from line 3) in crescent order of number of months necessary.

The main macro, who calls the other ones, must be named: Sub Main()