Placing value into a cell without affecting its formula
Posted by Steve Wardle on April 23, 2001 4:56 AM
I am currently writing some VBA code in Excel97 that, when a user calls a function, sets up a request against an OLE Server which processes the request and issues a callback to Excel with the result. The result can then be placed in the appropriate cell.
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..
Cheers, Steve.
Posted by David on April 23, 2001 5:48 AM
What is the formula in the cell.
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?
Posted by Steve Wardle on April 23, 2001 7:14 AM
The formula in the cell is a function call which
sets a process going in an OLE server Eg:
GetSomeData(A1,A2)
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.
Posted by David on April 23, 2001 7:49 AM
why don't you do something like this.
Instead of putting the function in to cell a4 do a worksheet change event and if the cell is a4 call your function.
Posted by Steve Wardle on April 23, 2001 8:00 AM
Unfortunately, we need the user to be able to set up any cell they want with the function. Therefore there is not a definitive list of cells that the function will apply to otherwise your solution would work. why don't you do something like this. Instead of putting the function in to cell a4 do a worksheet change event and if the cell is a4 call your function.
Posted by Dave Hawley on April 24, 2001 2:12 AM
Hi Guys
If I have got this right all you need to do is this:
Range("B4") = Range("B4").Formula & "&""blah"""
Am I close ?
Dave
OzGrid Business Applications
Posted by Steve Wardle on April 24, 2001 3:18 AM
Hi Dave,
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
=GetAValue(A1, A2)
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.
Thanks,
Steve.
Posted by Dave Hawley on April 24, 2001 5:24 PM
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 =GetAValue(A1, A2) 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. Thanks,
Hi Steve
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.
Dave
OzGrid Business Applications
