1

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

Community
  • 1
  • 1
Greedo
  • 4,967
  • 2
  • 30
  • 78
  • You say "*this Sub is very dangerous and relatively simple to accidentally call.*" so I would rename the function into e.g. `cleardebug` so it differs from anything like `listbox.clear()` and there is no more accidentally calling of it. Someone should not accidentally call a function called `cleardebug`, can't imagine a scenario where this would ever happen accidentally. – Pᴇʜ May 22 '17 at 09:12
  • @Peh I agree that it might help with vba calls, but I'm not fully convinced for 2 reasons; First, that main form of accidental calling that I can think of is just clicking on the wrong macro in the run macros window - changing the name wouldn't solve this. Secondly, typing `cleardebug` feels like more effort than just doing ctrl+a+del manually (obviously you could pick a different name, but finding something obvious enough to be memorable but obscure enough not to have another meaning in VBA, and concise enough to type is hard)! – Greedo May 22 '17 at 09:46
  • 1
    Ok let me say that there are about 1000 discussions, how-tos and tutorials out in the web on how to clear out the immediate window and about 10 different solutions to that issue. If there was a solution like detecting the immediate window as function caller (or like you suggested the other way round) I think someone would have found that already. I think there is no better solution to that beside these which the discussions already pointed out. – Pᴇʜ May 22 '17 at 09:54
  • @Peh Valid point, *but* it's possible that a new question wording will attract some different ideas, I found no mention of this approach in the research I did (almost certainly because it is a fruitless approach, but possibly because it was not being considered) – Greedo May 22 '17 at 10:21
  • 1
    I've always used the button provided on MZ Tools for clearing the Immediate Window - unfortunately not free any more. https://www.mztools.com/ – Darren Bartrup-Cook May 22 '17 at 10:32

3 Answers3

1

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

Storax
  • 11,158
  • 3
  • 16
  • 33
  • In fact it could even be an optional parameter, because user/button clicks can't pass the string, and it would be hard to accidentally type in a sub. So having it `Optional` just avoids error messages but doesn't make it any less safe – Greedo May 22 '17 at 10:28
  • Or you change your original code to Application.SendKeys "^g ^a {DEL}" – Storax May 22 '17 at 10:33
0

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
  • 1
    I have seen this elsewhere, in the great [clear the immediate window](http://stackoverflow.com/q/10203349/6609896) debate, I don't like how it leaves the cursor at the bottom of the window though. Also, as you say, it answers my specific case but not the actual question (although workarounds are in the SO spirit!). – Greedo May 22 '17 at 09:52
0

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.

Greedo
  • 4,967
  • 2
  • 30
  • 78
  • Small point: your argument is optional so doesn't have to be supplied **at all** when calling the routine, whether by the user (possible though unlikely) or by another sub. – Rory May 22 '17 at 12:14
  • @Rory, but I check if it `IsMissing` making it necessary for the code within the sub to run, just not for the sub as a whole to run (to avoid errors when it is called accidentally) – Greedo May 22 '17 at 12:56
  • Sorry - I overlooked that part. Technically the extra argument *can* be given by the user, it's just not going to happen accidentally! ;) – Rory May 22 '17 at 13:05