Refreshing formulas after a command has been completed

[msl1]

I am working on a project that involves an access DB, a DLL and Excel. The excel spreadsheet calls functions from the dll which queries the db, I could've just done it in vba, but I started the project in VB. It is an order book for a commodity trading firm, and they want to know positions by system, commodity, etc.

So here is the issue, there are functions that I concatenated in cells for versatility, since the firm wants to be able to do whatever they want to the spreadsheet, so if I call a function in a cell, such as "getallsystems(commname)" , it will return the total position for the commodity, only once tho. I need to be able to refresh the entire sheets formuals after a new order has been placed in the order book, or excel will just keep the old value that was returned orinally by the function. Anyone have any idea on how to do this?

Thanks

 

[Zathras]

In VBA there is Application.Volatile that can be used in a UDF, but I don't think it will help you with the DLL.

What I would do is require a second parameter that will have a different value so that Excel will think it has to call your code to execute the function again. If nothing else, you could use RAND(), but you could also use NOW(), or anything else that will have been changed since the last update - maybe order number.

 

[msl1]

how would application.volatile help? I will redo it in vba if that works. And I was thinking about using a second parameter, such as price action of the commodity, but I think that would call the formulas so often , it would bog it down beyond belief.

 

[Zathras]

Application.Volatile would cause the function to be recalculated everytime the worksheet is recalculated. Otherwise the function is recalculated only when one or more of its arguments are changed.

Perhaps you can use the Worksheet_Change event to run a macro to update your data when the order number is changed. That way you can control when the functions are called.

For example, if the commname is in column G and the functions are currently in column H, you could remove the functions from column H and do something like this in the worksheet code:
[blue]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  If Target.Address = Range("ordernumber").Address Then
    With Range("CommNumbers")
      .FormulaR1C1 = "=getallsystems(RC[-1])"
      .Copy
      .PasteSpecial (xlPasteValues)
    End With
    Application.CutCopyMode = False
    Target.Select
  End If
End Sub

[/color]
This assumes you have a one-cell range named "ordernumber"
and that your CommNumber parameters are all in a neat stack
and that the function return value is wanted in each cell immediately to the right of the commnumber ID and that stack is named "CommNumbers"

 

[msl1]

I figured it out myself, all I needed to do was convert to vba and add in
application.calculatefull.

thanks tho.