excel: how to convert .bas file to vbscript/exe or running from command line?

Question

How to convert .bas file to vbscript/exe or running from command line ? I did script in Excel by MS Visual Basic for Aplications, but i can run this scrip only under Excel. How i can make this script as .vbs or .exe ? ?

Paracelsus

it seems you have to rewrite the code! – Fullbodied

Answer 1

I think the simplest option is to save the .bas file with a .vbs extension and modify your code to VBScript; then run it under Windows Script Host (WSH). Keep in mind that in VBA under Excel you have access to a number of built-in objects; in VBScript under WSH you'll have to create or access those objects yourself (see this answer) with the CreateObject or GetObject functions. (WSH has its own set of built-in objects.) In the case of Excel, you'd need to start with:

Dim xlApp
Set xlApp = CreateObject("Excel.Application")

Keep in mind that in VBScript, variables do not have a type, so all statements such as:

Dim i As Integer
Dim wks As Excel.Worksheet

need to have the As clause removed:

Dim i
Dim wks

---

For exact details on the differences between the two, see INFO: Visual Basic for Applications Features Not in VBScript and INFO: VBScript Features Not in Visual Basic for Applications.

---

VBA has a built-in IDE and debugger which you don't have when running code under WSH, but you can use Visual Sudio to debug the script file. (In the event that you can't install VS 2015 Community Edition, the Visual Studio integrated shells also work -- 2013, 2012, 2010.

Debug your scripts by calling them from the commandline as follows:

cscript yourscript.vbs //D //X

or:

wscript yourscript.vbs //D //X

If you have Office 2007 or earlier installed, you can use the Microsoft Script Editor for debugging; there's no need to download and install VS. Nevertheless, VS is far more powerful than both Microsoft Script Editor and the VBA debugger.

Dipole

Answer 2

I'm afraid that the short answer is that you can't, well at least not directly. VBA and VBS are slight variant's and while you could save the code into a .vbs file I very much doubt that it would do anything without changing it to VB Script.

To make a .exe you will need something like Visual Studio. Even then you would probably need to rework your code to make it do it's job.

I guess it depends on what your code does.

Postdoctoral

Answer 3

I think everyone has made good points here, but one thing I did not see that would be a big issue is that with VBA and Visual Basic you can use the On Error GoTo command and set an Error line using something such as MainErrorHandler: at the bottom of the function. VB Script does not allow this and you need to use On Error GoTo 0 or On Error Resume next which are both allowed in VBA and Visual Basic. the error handling in VB Script however is allowed in all VB languages.

Dobb