How to tell if Sub is called from immediate window

Question

Some subs I only ever want to run from the immediate window, like this slightly risky one:

Public Sub clear()

Application.SendKeys "^a", True
Application.SendKeys "{delete}", True

End Sub

In case you can't work it out; it sends ctrl+a and Del to the application, and has the effect of clearing whatever is around the cursor at the time.

I use it in the immediate window by typing clear and everything in the immediate window is removed.

As you can probably tell, this Sub is very dangerous and relatively simple to accidentally call. Therefore I would like to have it run only when called from the immediate window

• Not from another Sub
• Not from a Form Control
• Not from ActiveX (although that's effectively just a sub)
• Not from a user call (through the Developer/Macros tab)

And not from any other source either (I've listed all those, because I think even if you can't directly tell that it was called by the immediate window, you might be able to tell indirectly by ruling out the other options)

Is this possible to do programatically? Ideally, I just want to have a simple Boolean check at the start of my clear() sub, telling it to do nothing unless called by the immediate window.

---

N.B.

One route might be through the use of the Call Stack (ctrl+L to open the dialogue box), as I notice immediate calls leave no trace in the stack whereas subs calling clear() are listed and so can be ruled out. But I'm not sure how to access this through VBA

Answer 1

I've found one approach that combines some ideas:

Public Sub clear(Optional s As Variant)

If TypeName(Application.Caller) = "Error" And IsMissing(s) = False Then
'Do code
End If

End Sub

What's going on is that if a sub is called from a range (functions only) or a button then this is caught by the TypeName test, as these will return Range and String respectively. Error options are immediate window, a Sub or a user call. The extra argument can't be given by a User, so that's ruled out. Finally, we hope a sub call won't contain the argument.

s is Variant so that you can type clear whatever in the immediate window, rather than having to put watever in " (to denote text).

Obviously can be made safer with a proper argument. Obviously it's not ideal as the word hope was meant to show! And of course it doesn't really answer the question either.

Answer 2

A simple solution would be to add a parameter

Public Sub clear(sCaller As String)

If sCaller = "Immediate" Then
    Application.SendKeys "^a", True
    Application.SendKeys "{delete}", True
End If

End Sub

This is possibly also not what you want but would clearly minimize the risk

Answer 3

I know that this is not the answer to your question, but if you want to "clear" your immediate window, this is what I use:

Sub clear()
For i = 0 To 100
    Debug.Print ""
Next i
End Sub