Change Excel Cell Values using a Function

[harius]

I'm looking to load data in an excel sheet from a database. I would like to do this via a function, so that when I recalc the sheet, the data is loaded. The function is something like:

=loaddata(product, startdate as date, enddate as date) in cell A1. Data for the product between startdate and enda date to be loaded in cells A2 and following.
I do not want to call the data from an array which could directly read the return array from loaddata() as I want this function to execute only if the date changes.

I have seen this done. Bloomberg, a financial news and data provider has functions that return data to cells other than the one that calls the function.

Can you help?

[MikeTheBike]

@harius
Hi

This is the way I would tackle it

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("StartDate").Address Or Target.Address = Range("EndDate").Address Then
        If IsDate(Target) Then LoadData Sheets("Sheet2").Range("A2")
    End If
End Sub
 
Sub LoadData(ByVal DataRange As Range)
    Dim cn  As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sql As String
 
    cn.Open "file name=H:\My Data Sources\CES Information DB DEV.udl"
 
    sql = "SELECT * FROM tblEmployees WHERE StartDate " & _
            "BETWEEN #" & Format(Range("StartDate"), "mm/dd/yy") & "# " & _
            "AND #" & Format(Range("EndDate"), "mm/dd/yy") & "#"
 
    rs.Open sql, cn, adOpenStatic, adLockReadOnly
 
    DataRange.CopyFromRecordset rs
 
    rs.Close
    cn.Close
 
    Set rs = Nothing
    Set cn = Nothing
 
End Sub

As you can see this uses the worksheet change event. I have also defined the startdate and enddate cells with a range name. The LoadData sub only fires if one of the date cells is changed (and changed to a date!).

You can then copy the data to any sheet in the wokbook if you want (I used Sheet 2 as it would have overwritten my dates otherwise).

Hope this is of some help.

If it is and you have any queries please ask.


MTB

ps. I have used the for function because I am in the UK but US date formats are required for sql strings!!

[harius]

Thanks for your post.

I understand it addresses the problem I posted. However, it is only part of my problem.
Here is the full problem:

I have a series of products I need to load data for. I have the product Ids for these in a row, say B1, C1, etc... K1.
A1 has today date (end date of the data series) and A2 the start date, say 1/1/2009.
When today() changes, I would like all the data series to recompute on recalc.

I am currently using a loaddata() function taking B2:B10000 as input, with the drawbacks I listed in my first post.

I could of course play with your solution and make it work. However a function based solution would be more nimble. I am specifically looking for such a solution as I have seen it done. Bloomberg has a function for historical data series of securities that is entered on a single cell in the form =BDP(ticker, fields, startdate, enddate) and that returns an array of nbfields columns and nbdates rows. No idea how it's done though...


Regards,

Hari