[Last Call] Learn how to a build a cloud-first strategyRegister Now


Syntax to import Excel function into access VBA. It contains an sql statement as well

Posted on 2007-10-03
Medium Priority
Last Modified: 2013-11-27
I am trying to use XIRR function from excel in Access vba.
I have this code
Option Compare Database

Private Sub Command6_Click()

Dim obj As Excel.Application
Dim sql As String
Dim SQL2 As String
Set obj = CreateObject("Excel.application")
sql = "select Indices.Tdate " & _
           "from indices " & _
           "where [Tdate] Between #" & Forms!form77!tstart & "# And #" & Forms!form77!tstart & "# " & _
           "and [Index]= '" & Forms!form77!index1 & "'; "

obj.Workbooks.Open (obj.Application.LibraryPath & "\Analysis\atpvbaen.xla")
obj.Workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen)
MsgBox obj.Application.Run("atpvbaen.xla!XIRR", sql, 2)
Set obj = Nothing
End Sub

This code is apparently wrong. But the thing I want to do is on
MsgBox obj.Application.Run("atpvbaen.xla!XIRR", sql, 2)
line. I want to put the Index Close values which is into the arg 1 space. the structure of  XIRR(Values,Dates,guess) is like this. so the user will enter into the form 3 values. which are forms!form77!index1(this is to determine which index in the table is going to be used), forms!form77!tstart(this is the desired starting date), forms!form77!tend(this is the desired end date. So the XIRR function should be able to retrieve the desired daha from the "Indices" table.Then get the values from the "Close" field in the "Indices" table. This information is put into the first argument space in XIRR. The second space is all the values between the desiren start and end date. This information is put into the second argument space. and the final argument space is for the guess. this can  just be a number lets say 0.1.
So briefly speaking, I need to put these necessary sql statement into the XIRR function
Question by:awesomejohn19
  • 2
LVL 10

Accepted Solution

slamhound earned 1200 total points
ID: 20011038
I'm not sure how to use a SQL statement but in the past, i've found it easier to create a two dimentional arrya (arWorksheet(x,y) ) who's structure matches the worksheet. Then you just have to loop through the entire worksheet and grab all the data. Then you are free to reference anything you need using the x,y coordinates in code.

This is also a quicker method than re-quering the worksheet over and over again

Author Comment

ID: 20014944
I see your point but who can you code it?
LVL 10

Expert Comment

ID: 20018838

Expert Comment

ID: 20943201
Forced accept.

Community Support Moderator

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question