View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.programming
[email protected] baobob@my-deja.com is offline
external usenet poster
 
Posts: 100
Default Is Application.Caller valid in Functions but not Subs?

Thanks to ALL who answered.

I may not've stated my Q clearly enough. I forebore from providing any
context in an attempt to be simple. Which was prob. a bad idea.

The user launches my Sub by macro keystroke (Shift-Ctrl-<x).

After killing ScreenUpdate, the Sub walks thru various sheets 'n'
cells in the workbook and does stuff. My Q is:

On exit, how do I return to the user's cell, so when I reenable the
screen, he has no clue I was traveling all around his WB?

I do have a working solution: On Sub launch (in Groton?), I push
ActiveCell into a var, and before exit I pop it with
OrigCell.Sheet.Activate followed by OrigCell.Activate.

But to me that smells like a kluge. I don't have to do that in a
Function, do I? Application.Caller retains its value thruout a
Function's life, doesn't it? Ergo, what's a Sub's equivalent?

It seems to me Excel oughta know the user's home cell thruout a
routine's life. This pervert, at any rate, doesn't care whether it's a
Function, Sub, or event handler (in which case you're handed Target on
a silver platter anyway, right?).

But on further thought, well, I guess I can surmise why the answer
might be no. Because Subs can do things Functions can't, right? Like
do interactive things, Goto places, etc. So yeah, I comprehend and
readily accept such differences.

So it's a yes-no question. I apologize if I caused folks to answer as
if otherwise.

And again, we are all indebted to you Knights of the Net.

***