Funny Little Thing.

I’ve been blundering around around for years flicking back and forth to the VBAIDE immediate window to run simple lines of VBA. Its always niggled a little, but never really registered what a PITA it was.

That was until I found Ross’s bit of code to rip the immediate window out of VBA and put it in the Excel desktop instead. What a great idea, I wish I’d thought of it. I didn’t, but it did inspire this little form based tool. Its as tatty as, just a mega rough and ready thing that I probably wont bother to develop further. No idea if its any use, of if I’ve missed something obvious, and there is a more simple way to do this. If so, be sure to let us know via comments.

You are welcome to have it and do what you want (apart from sue me of course). Open it with macros disabled to see what it is doing (not much). If you don’t know much VBA this may have limited value, if you do know VBA this may have limited value. There is no real error checking so it just falls apart at the first sign of trouble. Some people might say all my code is like that!

I wont bother with a screen shot because its short and wide, and therefore will wreck all the wordpress formatting (must get a wider theme).

Its pretty much just a text box and a label on a form with a button you type any one liner you would put in the immediate window and it executes it and puts the answer in the label. Stuff like ‘?activecell.numberformat’, or ‘?activeworkbook.path’. You can also do stuff like ‘activecell.formula = xlapp.activecell.value’ to remove formulas, or even selection… for ranges. (the xlapp is a reference to the Excel application and represents an opportunity for improvement!).

I did a couple of For Eaches so if you type in ‘FEworksheet.unhide’ (with FE in uppper case) it will unhide all worksheets. There is also an ‘FEworksheet.zoom’ in which case you put the zoom value (10-400 I think?) in the little text box, it then zooms all sheets in the active wb to that level.

It relies on the Microsoft script control (msscript.ocx) which is on the form somewhere. No idea where that control came from, or what it came with, but if you get a compile error check your refs. It fires some activex component security warning on first activation, which I assume is the control itself. You can use javascript with this, which is pretty neat, I didn’t bother.

Hmm looks like many people might not have that control (http://support.microsoft.com/kb/184739).

The next developments would have been putting it in a toolbar, and maybe using a combo box and keeping previous commands, maybe a better way to handle parameters, but I don’t have the time.

If you decide to develop it further and would like to share those improvements then let us know. If you know a better way then definitely let us know that.

Heres the zip before I forget, when I say tatty I’m not being modest.

Let us know what you think



6 Responses to “FLT”

  1. Rob Bruce Says:

    “Heres the zip before I forget”: 404 on left click, corrupt file on Save As…

    I wrote exactly the same utility about 5 years ago and ended up not using it much. I just tried to look for it on my old backup disks, but couldn’t find it. It was an offshoot of this: http://www.asta51.dsl.pipex.com/zip/ – a simple macro editor for the VBAIDE.

  2. Simon Says:

    Thanks for the heads up, I moffed up the link, should be fixed now.
    Did you use the scripting thingy or another approach?

  3. Rob Bruce Says:

    Yeah, I used the script control. I’d used it before a couple of times to add macro functionality to some (non-spreadsheet) apps I’d written and I was really excited by its power/simplicity ratio. At the time Microsoft was demanding half your kingdom and the hand of your eldest daughter in marriage if you wanted to integrate VBA, otherwise we might have considered that.
    Note that the script control is unfinished. The original documentation mentioned all sorts of features such as a debugging window that never appeared. I suspect it was abandoned when MS made the corporate decision that it had painted itself into a corner with COM.

  4. Ross Says:

    Nice Simon, it work fine on my PC. I might have a little play when i get some time.

  5. Simon Says:

    Thanks Ross, glad to hear it worked for someone.

  6. Resolver « Smurf on Spreadsheets Says:

    […] Here is my more limited scripting thingie. […]

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: