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.
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