Stand alone VBA script files

  • Thread starter Thread starter tishoo
  • Start date Start date
T

tishoo

Question: Is it possible to have a standalone VBA script file that Excel can
open, or does the VBA have to be written into an Excel document? If so how
is it done?

Ta
T
 
You're probably confusing VBScript and VBA. Both are derivatives of Visual
Basic. VBA is always stored within a host document like an Excel workbook or
a Word document. VBScript is stored in simple text files with a 'vbs'
extension. VBScript runs outside of any application (other than the VBS
interpreter).

By the way, you need post only in one group.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
 
Note: A .bas file cannot be run as is, but must first be imported into an
appropriate host document (like an Excel workbook).

- Jon
 
Gary''s Student said:
Use .bas files

Using Excel VBA editor, create a script, then export it to a standalone
file
(.bas)
Click to expand...

Thanks for the tip. Worth noting that approach only works if the BAS file is
stored locally. It seems to be impossible to import a BAS file over the web.
 
It's hard to email them, too. A lot of email programs and antivirus programs
block them, because we all know that any code is malicious and the user
can't be trusted to open a "safe" one.

- Jon
 
This is how you import a standalone exported VBA Module. This imports a
module named WriteData.bas and runs a macro named WriteAsText in that
module :

Set objwb = ThisWorkbook
Set oVBC = objwb.VBProject.VBComponents
Set CM = oVBC.Import(GetPath() & "\WriteData.bas")
objwb.Application.Run "WriteAsText"

This clip shows how you add VBA code from a String using CM defined
above :

CM.CodeModule.AddFromString "Public Const TEXT_FILE = chr(34) &
"C:\file.txt" & r(34) & chr(10)

Yes, one can execute the code above from VBScript.

In the most general case, if you have a worksheet that uses only VBA
you can convert your Worksheet into a Worksheet created by a VBScript.

This VBScript code shows how you create your Excel, Workbook, and
access Worksheets:

Dim objXL,objwb,objws 'Put this at top of VBScript file for global
scope

Set objXL = CreateObject("Excel.Application")
Set objwb = objXL.Workbooks.Add
Set objws = objwb.Worksheets("Sheet1")

Sub ExcelSetUp()
objws.Name = "ActiveDirectory"
objws.Activate
objXL.Visible = True
data = Array ( "employeeID", "sAMAccountName")
objws.Range(objws.Cells(1,1),objws.Cells(1,2)).Value = data
End Sub
 
Back
Top