I have the basics of this working and my only problem is when I try to enter a value into a particular cell, say 'A4', I cannot appear to do so without changing the formula that is set up for that cell.
Is it actually possible to write code, for example:
Sheet1.range(A4).value = "blah"
which will leave the formula for the cell unchanged?
Another approach might be to make a copy of the formula into a temporary variable, write in the new cell value and then set the cell's formula to be the contents of the temporary variable however this seems to result in the formula being evaluated again immediately which is no good.
I would appreciate any advice..
If you have a formula in A4 like A4 = D4+1
You can not make A4 = 8 unless you change D4 to 7 it seems Like you are asking something elso though.
Perhaps I am not completly understanding the question or am I?
This process might take 10 seconds to complete normally so if it was to execute simply within the function call then excel will 'hang' whilst it is running. The function call would however be able to return the result populating the cell but leaving the formula unchanged.
By placing the code in an OLE server, the user can continue to work in excel and when the result has been calculated, a callback to excel is issued which can be trapped. At this point I want to place the result into the cell that contains the function call whilst leaving the function call there should the user change the contents of cell A1, for example, whereby the process will be performed again.
Instead of putting the function in to cell a4 do a worksheet change event and if the cell is a4 call your function.
If I have got this right all you need to do is this:
Range("B4") = Range("B4").Formula & "&""blah"""
Am I close ?
OzGrid Business Applications
Not sure exactly what you mean here. If I summarise my situation you might be able to say whether the technique you suggest still sounds suitable:
I can paste a function call into any cell, say A4, this function call looks like
The function when it executes simply returns a value such as "*CALCULATING*" which is displayed in the cell. Control is returned back to Excel and we are now outside the recalculation process for the cell. However, the function also sets going an OLE server process to perform a task which communicates back to Excel when it is finished by means of a callback. When the callback is trapped I want to take the result that was generated by the OLE server process and get the cell, A4, to display it whilst ensuring that the formula in the cell is not overwritten. This would mean that the next time the value in one of the referenced cells, eg A1, is changed then the process is repeated and a new value is calculated and placed in the cell.
I could sit here and make all sort of assumptions on whether my suggestion is suited to you exact need. There is only really one way to find out though, and that is to run it.
OzGrid Business Applications