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

[RobDog888]

TG, how about invoking your macro code from within the double-click event
of a particular cell? I modified some of my code from CodeBank to look like below...

'Behind "ThisWorkbook"

Option Explicit

Public goTest As clsEventTrap

Private Sub Workbook_Open()
    Set goTest = New clsEventTrap
End Sub


'Behind a new Class file names "clsEventTrap"
Option Explicit

Public WithEvents goApp As Excel.Application
Public WithEvents goSheet As Excel.Worksheet
Public WithEvents goBook As Excel.Workbook

Private Sub Class_Initialize()
    Set goApp = Excel.Application
    Set goBook = goApp.ActiveWorkbook
    Set goSheet = goBook.ActiveSheet
End Sub

Private Sub goApp_NewWorkbook(ByVal Wb As Workbook)
    goApp.DisplayAlerts = False
    Wb.Close False
    goApp.DisplayAlerts = True
End Sub

Private Sub goApp_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    'MsgBox "No double-clicking either!", vbOKOnly + vbExclamation
    'TGs macro function call goes here!
    'You can check the range and determine what the cell is that was double-clicked in
    'Then use that cells value to pass to your macro, if thats what you need?
    'Cancel = True
End Sub

Private Sub goApp_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
'FIRES SECOND AFTER THE GOSHEET_BEFORERIGHTCLICK EVENT
    MsgBox "No right-clicking!", vbOKOnly + vbExclamation
    Cancel = True
End Sub

Private Sub goApp_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)
    goApp.DisplayAlerts = False
    Sh.Delete
    goApp.DisplayAlerts = True
    MsgBox "No adding new worksheets!" & vbNewLine & "I removed it for you already!", vbOKOnly + vbExclamation
End Sub

Private Sub goBook_Open()
'
End Sub

Private Sub goSheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'
End Sub

Private Sub goSheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
'FIRES FIRST BEFORE THE GOAPP_SHEETBEFORERIGHTCLICK EVENT
    MsgBox "File size in bytes: " & goBook.BuiltinDocumentProperties.Item("Number of Bytes")
End Sub

I have some other features that may be helpful in there too.

[techgnome]

Thanks for the help. Some one here suggested the samething, but with over 100 rates needing to be updated, that didn't sound like a fun idea. What I'm kinda hoping for is to allow the user to open the WB, enter some info on the Ws1, click over to Ws2, hit the Update Rates button, then be able to click through Ws3-Ws12 to see the results of the calculations.

I'm probably asking too much of Excel, and am thinking of it in terms of a VB devloper.... too bad this isn't in VB6, I'd whip out some code no prob... but alas.

Tg

[RobDog888]

Ok, now you kinda lost me. Isnt that easier to call your macro from the refresh
button click event? I didnt know that using a button was an option. You can
have your rates populate several sheets no problem there, but getting the
updated data, I would think, would be more difficult?

To get that solution working what do you need solved first? We can take it a
step at a time.

[techgnome]

I wondered how long it would take to lose someone.....

Mind if I PM you with the details? I'd rather not get into too much specifics here.

Tg

[RobDog888]

No Prob. I will try to help as much as I can.

[techgnome]

Milli grati to RobDog888 to helping me get this.

I ended up doing it in a round about way, but the prob is now solved. I had to create a sub section with a list of rates and added a macro that loops through each sheet, fiunds the list, then pulls the rates from the rates sheet. Then the formulas then point to the subsection.

Tg