July 21st, 2004, 10:24 am
amkey04: If you are working hard on the performance then I would recommend that you don't have any explicit calls to time based volatile functions. Better to use the VB function, and populate the cell as text.You cannot use a function to change a cell on a spreadsheet I am afraid - at least I have never managed to find a solution to this problem.True, you can't do it directly or synchronously, but you can arrange for it to happen later.I define a simple queue. Every second, the DeQue sub is called, and puts values where you tell them to go.You can fiddle with the value for this to suit your own puposes.There are a lot of things that can't be done during function calls, and you can easily extend this approach to call routines or system functions.Note you must start the timer outside the function.Type qItem what As Variant where As StringEnd TypeConst MaxQ = 200Dim QCount As LongDim Queue(MaxQ) As qItemSub DeQue()Dim i As Long Application.OnTime Now + TimeValue("00:00:01"), "deque"For i = 1 To QCount With Queue(i) Range(.where).Value2 = .what End WithNext iEnd SubSub EnQue(what As Variant, where As String) QCount = QCount + 1 Queue(QCount).what = what Queue(QCount).where = whereEnd SubFunction Asynch(value As Double) As DoubleAsynch = 42EnQue value, "a1"End FunctionSub StartAsynch() Application.OnTime Now + TimeValue("00:00:10"), "DeQue"End Sub
Last edited by
DominicConnor on July 20th, 2004, 10:00 pm, edited 1 time in total.