Code to get ActiveCell to start a Subroutine

[Bob Barnes]

I had 2 other threads here yesterday and found answers, but we still need.
Need to code for the ActiveCell in the 1st line of
Private Sub SeeDiff() .. below.

TIA - Bob

Snippets from the 2 other threads from yesterday...
I'm going to run this code in Workbook_Open instead of Worksheet_Change (it
does work in Worksheet_Change). We've decided the Excel file will be
essentially only a "snapshot" as all data will be maintained in the Access
Database.

So..Workbook_open will include code for each of the 72 Cells, IE...
Range("DNine").Select
Call SeeDiff
Range("ENine").Select
Call SeeDiff
.....

Private Sub SeeDiff()
Set t = Application.Caller <---No "Target" here...how do I set the
"ActiveCell"?
.....I tried Application.Caller & Application.ThisCell suggested in this
thread by Chip...
....but that didn't work............................

 

[JLGWhiz]

Dim t As Range
Set t = ActiveCell

 

[Bob Barnes]

It works !! Thank you.

Yesterday, I read this...

I would strongly recommend that you not use ActiveCell in any
calculation, because you cannot predict where the active cell will be,
let alone what worksheet and workbook might be active when Excel
decides that it is time to calculate. If you need to get a reference
to the cell in which the function was called, use either
Application.Caller or Application.ThisCell. These will return a Range
object to points to the cell in which the function was called.

Your thoughts on that? I don't know, but

Dim t As Range
Set t = ActiveCell

....is working. Thanks again, Bob

 

[Peter T]

That's very good advice. If you recall, I had also told you I doubt you want
the ActiveCell. I wasn't sure though as I didn't understand what you were
trying to do. At the risk of repeating myself, you may get a more useful
answer if you explain your objective in words, not your code which is highly
ambiguous.

Regards,
Peter T

 

[Bob Barnes]

Peter T - Sorry you don't understand what I said. Some others here did. I
thank you for your Input.

I turned it over to Mgmt today, and they loved it.

Thank you again, Bob

 

[JLGWhiz]

Bob, just to clear the air a little. In the case where you know where the
active cell is, and you intend to use that as a reference point, you can set
it to an object variable which will represent that specific cell until you
re-set it or end the macro. What Peter was referring to was continuing to
use ActiveCell, where it may be a different value or different cell as the
macro progresses. That is not a good practice because it can not only
confuse someone who is trying to understand what the macro is supposed to
do, it can also confuse the creator of the macro and produce undesired
results along with unnecessary errors. For what you were trying to do,
setting the active cell to a variable is OK.

 

[Bob Barnes]

"JLGWhiz" - thank you.

In Workbook_Open, the code is like...

Range("AOne").Select
Call SeeDiff
Range("BOne").Select
Call SeeDiff
Range("COne").Select
Call SeeDiff

...so the ActiveCell has to be, for example, Range("AOne").Select ...
directly above the ...Call SeeDiff.....can that cause an error? I can't see
any confusion to another Developer interpreting the flow of the
Procedure...or am I missing something?

Private Function SeeDiff()
Set t = ActiveCell
If (t.Value = "" Or IsNull(t.Value)) Then
t.Offset(2, 0).Value = "": Exit Function
End If
If ((t - t.Offset(0, -1).Value < 0) _
And Abs(t - t.Offset(0, -1).Value) > 9000) Then
If Len(t.Offset(0, -1)) = 4 Then
I = (10000 - t.Offset(0, -1).Value)
ElseIf Len(t.Offset(0, -1)) = 5 Then
I = (100000 - t.Offset(0, -1).Value)
ElseIf Len(t.Offset(0, -1)) = 6 Then
I = (1000000 - t.Offset(0, -1).Value)
ElseIf Len(t.Offset(0, -1)) = 7 Then
I = (10000000 - t.Offset(0, -1).Value)
End If
SeeDiff = t + 1
t.Offset(2, 0).Value = SeeDiff
Else
SeeDiff = (t - t.Offset(0, -1).Value)
t.Offset(2, 0).Value = SeeDiff
End If
End Function