VBA to VBS converter - possible?

**mc84excel** · 01-06-2015, 07:00 PM

Call me crazy but would it be impossible to write a VBA function that converts a VBA module into VBScript?

To make it much easier, assume that any module I input is completely 'stand-alone' (i.e. it won't contain references to any global variables/constants nor will it reference any procedures outside of the module)

Also the converter function doesn't have to output a VBS file, I'd be satisfied with it returning a string (i.e. the string contains the VBA code in VBS format)

Thinking out loud - for starters, we'd need to deal with:

VBS would have to create an Object to reference the Excel application.Then use this reference to refer to any Excel workbooks, built-in functions etc.
VBA can have named arguments. VBS does not.
Create constants in VBS for any Application Named Constants in the VBA code (e.g. xlDown)

**Kyle123** · 01-09-2015, 03:09 AM

I don't know if VBA really supports any kind of reflection so my inclination is that this would be much harder than you think it is. Obvious things that spring to mind are resolving references, so instantiating objects where the New keyword has been used rather than createobject, resolving constants, working out which functions are Excel global as opposed to language calls.

There's a lot to go at, I'm not sure it's all possible, I suspect that you could get 70% of the way there, but the remaining 30% may not be possible. In all it's probably not worth it and considering that you'd still have to check and hand edit the resulting code, probably not worth it - it would be easier and faster to transfer by hand.

**mc84excel** · 01-11-2015, 06:16 PM

Thanks Kyle

Pity. I've been playing around with swarm code (aka Multithreaded VBA) by Daniel Ferry so I'd've loved to convert certain VBA modules into dynamically created VBS files...