There is nothing to stop you writing VBA in any text editor you want. However, AFAIK you will need to copy and paste it into a proper Excel module before it will work.
Are you trying to write some VB-style code that will run stand-alone (ie. without requiring Excel to be open)? If so then you might want to look up VBScript. There's a useful reference at http://www.devguru.com
Following is the script from AVAYA CMS CenterVu Supervisor that opens CMS, runs a report and creates an excel file in the path specified. I think this code is in VBScript. What I want is, when this code is executed It should start executing a Excel VBA Macro without any manual intervention. I don't want to stop here and then go to Excel and run the VBA separately.
The other option would be, have the below code converted into Excel VBA and then I will continue running the other VBA macro. I can't do the converting part and if I paste the below code into Excel module, I get an error Argument not optional in the line "Set Log = CreateObject("AVSERR.cvsLog")"
Public Sub Main()
On Error Resume Next
cvsSrv.Reports.ACD = 1
Set Info = cvsSrv.Reports.Reports("Historical\CMS custom\Custom Multi/Split")
If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The report Historical\CMS custom\Custom Multi/Split was not found on ACD 1.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
Set Log = CreateObject("AVSERR.cvsLog")
Log.AutoLogWrite "The report Historical\CMS custom\Custom Multi/Split was not found on ACD 1."
Set Log = Nothing
b = cvsSrv.Reports.CreateReport(Info,Rep)
If b Then
b = Rep.ExportData("M:\Maxi\abc.xls", 9, 0, True, True, True)
If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove Rep.TaskID
Set Rep = Nothing
Set Info = Nothing
CreateObject is normally a VBScript command, but VBA gives you a different way to do a simiar thing. In Excel I think you need to add a reference to whatever this "AVSERR" thing is (presumably a DLL file or something?). Then you might be able to change the code to something like this:
Dim Log as cvsLog
Set Log = New cvsLog
Set Log = Nothing