Returning Existing Cell Value From A Function Call

[mavest]

I have a series of cells that contain calls to a VBA function. This function in turn calls a dll. There are times when I do not want this function to call the dll, but to just want the function to return the present value in the cell. Using the debugger, I've noticed that by the time the cursor arrives at the function call or the worksheet_change event, the value in the cell has already been blanked out by Excel. Hence if I read and return this value it is a zero and not the original value in the cell.

Is there some event that occurs before the value in the cell has been blanked out?

Or is there an easy and robust way to do what I want?

Thanks for your help!


-Mike Vest

 

[ck1999]

What do you mean by blanked out by excel?

What is the code for your function?

ck1999

 

[mavest]

In VBA my function looks as below. The Tower_1_Data and Tower_2_Data are ranges of data in the worksheet. When data in these ranges change, it causes Excel to call the function. The function then calls the dll, which I don't always want to happen because it takes a long time. So, I'd just like to return the current cell contents. I'd hoped to control this by setting the value of aBool to true or false. However, as I mentioned in my initial post, Excel has already updated the cell contents to "blank". Hence, cellValue = 0, and instead of the original cell value being returned a 0 is returned.

Public Function GetBlendProps(aBool As Boolean, Tower_1_Data As Variant, Tower_2_Data As Variant) as variant

Dim aCell As String, sht As String, wrkbk As String, aLoc As Long, aRow As Long, cellValue as variant

sht = Application.Caller.Parent.Name
wrkbk = Application.Caller.Parent.Parent.Name
aCell = Application.Caller.Address
cellValue = workbooks(wrkbk).sheets(sht).range(aCell).cells(1,1).value

'this is a call to a function that calls the dll
If aBool = TRUE Then
getBlendProps = doTowerCalcs(Tower_1_Data, Tower_2_Data)
Else
getBlendProps = cellValue
End If

End Function

 

[xlbo]

If you are using the CHANGE event to run the function then the cell has already been changed. Ergo, you cannot find out what WAS in there

To do that, you would need to use the SELECTION CHANGE event to capture cell values prior to them being changed

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question