Outputing a dynamic array into a spreadsheet

[james87]

Hi,

In simple, i've designed a project to have a dynamic array. This is composed of 5 stocks, each one has a name, ID, expected return and standard deviation. This has been incorporated in the code, and then the user is asked if he would like to add another stock to the array, in which a series of input boxes ask for the relevant details. All the details entered are entered into the array for each stock.

My question is, how can I now export the entire dynamic array into an excel spreadsheet?

My code is as follows:

Option Explicit
Dim objstocks() As clsStock
Dim n As Long
Public Sub AddTheFirstFive()
Dim i As Long
 
n = n + 1
ReDim Preserve objstocks(1 To n)
Set objstocks(n) = New clsStock
objstocks(n).Initialize "Microsoft", "MSFT", "10", "20"
 
n = n + 1
ReDim Preserve objstocks(1 To n)
Set objstocks(n) = New clsStock
objstocks(n).Initialize "Google", "GOOG", "12", "25"
 
n = n + 1
ReDim Preserve objstocks(1 To n)
Set objstocks(n) = New clsStock
objstocks(n).Initialize "Goldman Sachs", "GS", "5", "10"
 
n = n + 1
ReDim Preserve objstocks(1 To n)
Set objstocks(n) = New clsStock
objstocks(n).Initialize "Morgan Stanley", "MS", "4", "5"
 
n = n + 1
ReDim Preserve objstocks(1 To n)
Set objstocks(n) = New clsStock
objstocks(n).Initialize "Citigroup", "C", "2", "3"
 
For i = 1 To n
objstocks(i).PrintOut
Next i
End Sub
Public Sub addmorestocks()
Dim strStockName As String
Dim strStockID As String
Dim dblExpRet As Double
Dim dblStDev As Double
Dim i As Long
 
strStockName = InputBox("Please enter the name of your stock")
strStockID = InputBox("Please enter the ID of your stock")
dblExpRet = InputBox("Please enter the Exp Ret of your stock")
dblStDev = InputBox("Please enter the St Dev of your stock")
 
n = n + 1
ReDim Preserve objstocks(1 To n)
Set objstocks(n) = New clsStock
objstocks(n).Initialize strStockName, strStockID, dblExpRet, dblStDev
 
For i = 1 To n
objstocks(i).PrintOut
Next i
 
End Sub
 
Private Sub Writetocells()
Dim i As Long
Dim rngmyrange As Range
Dim mstrStockName As String
 
 
Range("B3").Select
ActiveCell.FormulaR1C1 = "Stock Name"
 
Range("C3").Select
ActiveCell.FormulaR1C1 = "Stock ID"
 
Range("D3").Select
ActiveCell.FormulaR1C1 = "Expected Return"
 
Range("E3").Select
ActiveCell.FormulaR1C1 = "Standard Deviation"
 
Range("F3").Select
ActiveCell.FormulaR1C1 = "Sharpe Ratio"
 
 
 
Columns("B:B").ColumnWidth = 16.14 'MACRO!!!
Columns("C:C").ColumnWidth = 12.43
Columns("D:D").ColumnWidth = 15.14
Columns("E:E").ColumnWidth = 17.43
Columns("F:F").ColumnWidth = 11.57
Range("B3:F3").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
 
End Sub

###AND MY Class module is as follows :

Option Explicit
Private mstrStockName As String
Private mStrStockID As String
Private mdblExpret As Double
Private mdblstdev As Double
Private mdblSharpeRatio As Double
 
Public Sub Initialize(ByVal strStockName As String, ByVal strStockID As String, _
ByVal dblExpReturn As Double, ByVal dblStDev As Double)
 
mstrStockName = strStockName
mStrStockID = strStockID
mdblExpret = dblExpReturn
mdblstdev = dblStDev
End Sub
Public Sub PrintOut() ' display the results!
 
MsgBox ("This stock's name is " & mstrStockName & vbNewLine & "The stock ID is " & mStrStockID & vbNewLine & _
"The return is: " & CStr(mdblExpret) & vbNewLine & "The St Dev is " & CStr(mdblstdev) & _
vbNewLine & "The Sharpe Ratio is " & CStr(CalculateSharpeRatio))
 
End Sub
Public Function CalculateSharpeRatio() As Double
 
CalculateSharpeRatio = mdblExpret / mdblstdev
 
End Function

So now I need some method to export the data into a spreadsheet. I am a beginner at vba, and any help would be greatly appreciated!

Many Thanks

James

 

[james87]

ps. I am not sure if my array has even been define properly?

 

[GTO]

Hi James,

I have barely used classes, so hopefully someone brighter will come along. In the meantime, if you change the variables in the class to public...

Public mstrStockName As String
Public mStrStockID As String
Public mdblExpret As Double
Public mdblstdev As Double
Public mdblSharpeRatio As Double

Then in the Standard Module:

Sub exa()
Dim myArray
    
    myArray = RetVals
    
    Range("A2").Resize(UBound(myArray, 1), UBound(myArray, 2)).Value = myArray
End Sub
    
Function RetVals()
Dim aryVals
Dim lRows As Long
Dim i As Long
    If Not UBound(objstocks, 1) = -1 Then
    
        lRows = UBound(objstocks, 1) - LBound(objstocks, 1) + 1
        ReDim aryVals(1 To lRows, 1 To 5)
        
        For i = LBound(objstocks, 1) To UBound(objstocks, 1)
            aryVals(i, 1) = objstocks(i).mstrStockName
            aryVals(i, 2) = objstocks(i).mStrStockID
            aryVals(i, 3) = CStr(objstocks(i).mdblExpret)
            aryVals(i, 4) = CStr(objstocks(i).mdblstdev)
            aryVals(i, 5) = objstocks(i).mdblExpret / objstocks(i).mdblstdev
        Next
    End If
    
    RetVals = aryVals
End Function

This is of course just a rudimentary example of how you could get all the objects and their properties into an array.

Hope it helps a little.

I'm signing out, but will hopefully get to return later and see much better.

Mark