VBA Function syntax

F

Forrest

I currently have an Access module that drops data into Excel and places the cursor below the last row of the last column. I want to programically place Excel's IRR function in this cell. Column A contains the dates and column C the amount field. Code snippet

' Selects c7; first blank cell at the bottom of a contiguous colum
xlWs.Range("c1").End(xlDown).Offset(1, 0).Selec

Thanks

Forrest
 
T

Tom Ogilvy

IRR does not accept dates; XIRR does:

Selection.FormulaR1C1 = "=XIRR(R1C:R[-1]C,R1C1:R[-1]C1,0.1)"

--
Regards,
Tom Ogilvy



Forrest said:
I currently have an Access module that drops data into Excel and places
Click to expand...
the cursor below the last row of the last column. I want to programically
place Excel's IRR function in this cell. Column A contains the dates and
column C the amount field. Code snippet:
 
F

Forrest

Tom, thanks for the syntax. I adjusted it to exclude row one which includes headers, but nothing drops in the Excel spreadsheet cell, so I hard coded the referenced cells; again nothing? What am I missing? I have included the entire code module up to your aditional line as modified per comments above. Any thoughts

Look forward to your feed-back as you recognized my need for the XIRR function

Thanks

Forres

Private Sub Command0_Click(
Dim cnt As New ADODB.Connectio
Dim rst As New ADODB.Recordse
Dim xlApp As Object ' Excel applicatio
Dim xlWb As Object ' Excel workboo
Dim xlWs As Object ' Excel workshee
Dim strDB As Strin
Dim fldCount As Intege
Dim recCount As Lon
Dim iCol As Integer 'Index variable for the current colum
Dim iRow As Integer 'Index variable for the current ro

strDB = "c:\FundSectors2002.mdb

'Open connection to the databas
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &
"Data Source=" & strDB & ";

'Open recordset based on CashFlow1 tabl
rst.Open "Select CashFlow1.Date, CashFlow1.Transaction, CashFlow1.Amount From CashFlow1", cn

'Open or create an instance of Excel and add a workboo
On Error Resume Nex
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 The
Err.Clear ' Clear Err object in case error occurred
Set xlApp = CreateObject("Excel.Application")
End I
Set xlWb = xlApp.Workbooks.Ad
Set xlWs = xlWb.Worksheets("Sheet1"

'Display Excel and give user control of Excel's lifetim
xlApp.Visible = Tru
xlApp.UserControl = Tru

'Copy field names to the first row of the workshee
fldCount = rst.Fields.Coun
For iCol = 1 To fldCoun
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Nam
Nex

'Copy the recordset to the worksheet, starting in cell A
xlWs.Cells(2, 1).CopyFromRecordset rs

'recCount = rst.RecordCount 'Attempt to capture record count and use as variable instead of End property

xlWs.Range("c1").End(xlDown).Offset(1, 0).Select 'Selects c7, blank cell at bottom of contiguous colum
xlWs.Selection.FormulaR1C1 = "=XIRR(R2C:R[-1]C,R2C1:R[-1]C1,0.1)" 'Tom's line modifie
xlWs.Selection.FormulaR1C1 = "=XIRR(C2:C7,A2:A7,0.1)" 'Hard coded cell references
 
T

Tom Ogilvy

You don't need to select anyway:

xlWs.Range("c1").End(xlDown).Offset(1, 0).FormulaR1C1 = _ 'Selects c7,
blank cell at bottom of contiguous column
"=XIRR(R2C:R[-1]C,R2C1:R[-1]C1,0.1)" 'Tom's line modified

or,
Selection is a property of the Application or the Window, but not the
worksheet

xlWs.Range("c1").End(xlDown).Offset(1, 0).Select
xlApp.FormulaR1C1 = "=XIRR(R2C:R[-1]C,R2C1:R[-1]C1,0.1)"

--
Regards,
Tom Ogilvy


Forrest said:
Tom, thanks for the syntax. I adjusted it to exclude row one which
Click to expand...
includes headers, but nothing drops in the Excel spreadsheet cell, so I hard
coded the referenced cells; again nothing? What am I missing? I have
included the entire code module up to your aditional line as modified per
comments above. Any thoughts?
Look forward to your feed-back as you recognized my need for the XIRR function.

Thanks,

Forrest

Private Sub Command0_Click()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim xlApp As Object ' Excel application
Dim xlWb As Object ' Excel workbook
Dim xlWs As Object ' Excel worksheet
Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer 'Index variable for the current column
Dim iRow As Integer 'Index variable for the current row

strDB = "c:\FundSectors2002.mdb"

'Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"

'Open recordset based on CashFlow1 table
rst.Open "Select CashFlow1.Date, CashFlow1.Transaction, CashFlow1.Amount From CashFlow1", cnt

'Open or create an instance of Excel and add a workbook
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Clear ' Clear Err object in case error occurred.
Set xlApp = CreateObject("Excel.Application")
End If
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")

'Display Excel and give user control of Excel's lifetime
xlApp.Visible = True
xlApp.UserControl = True

'Copy field names to the first row of the worksheet
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next

'Copy the recordset to the worksheet, starting in cell A2
xlWs.Cells(2, 1).CopyFromRecordset rst

'recCount = rst.RecordCount 'Attempt to capture record count and use as
Click to expand...
variable instead of End property?
xlWs.Range("c1").End(xlDown).Offset(1, 0).Select 'Selects c7, blank cell at bottom of contiguous column
xlWs.Selection.FormulaR1C1 = "=XIRR(R2C:R[-1]C,R2C1:R[-1]C1,0.1)" 'Tom's line modified
xlWs.Selection.FormulaR1C1 = "=XIRR(C2:C7,A2:A7,0.1)"
Click to expand...
'Hard coded cell references
 
T

Tom Ogilvy

glad it worked - for completeness

xlWs.Range("c1").End(xlDown).Offset(1, 0).Select
xlApp.FormulaR1C1 = "=XIRR(R2C:R[-1]C,R2C1:R[-1]C1,0.1)"


should have been

xlWs.Range("c1").End(xlDown).Offset(1, 0).Select
xlApp.Selection.FormulaR1C1 = "=XIRR(R2C:R[-1]C,R2C1:R[-1]C1,0.1)"
 
Top