Resolved: Pulling VBA Func Result into Cell (Excel)

[techgnome]

Ok, hopefully this will be clear.
I have an Excel worbook that has a number of wroksheets, each of which do a number of rather complex calculations. A lot of these calculations are based upon rates. Fortunatly these rates are found in a database, and I've added a macro sub that, when run will log into the database and run a stored procedure that will return the rate values based on a date range found on sheet 1. With me so far?

Ok, so now, I have all these rates on a new sheet in the workbook. Because these rates can change over time, there's some calculations that go on in the worksheet to prorate the rates and calc a new "blanded" rate.

Still with me?

So what I'd like to do now, is to pull the rates into the appropriate places on the individual sheets. If I could, I would simply goto the destination cell and punch in =RateSheet!A23. But that won't work, since if a new rate is added, the rate will no longer be in cell A23, but could be in A24, or A30 even. So that's no good.

I haven't lost you yet have I?

So, I worte a VBA function, that given a name, looks through the list on that sheet, find the named rate and returns the value. Sounds simple. Except that I cannot for the life of me figure out how to call my VBA Func from the cell so that the value shows up. Know what I mean? Does any one know if it's possible? I tried as VLookupTable but that didn't work out too well.



Tg