Mixing the Native Excel Functions with VBScript

Macro Scheduler comes complete with some native functions for controlling Excel, such as XLOpen, XLGetCell, XLSetCell and others. Obviously, although we intend to add more functions over time, not every possible Excel function has been duplicated. So sometimes you may want to utilise COM via VBScript which allows you to access the entire Excel API. There are plenty of examples of this here in the blog and on the forums.

But what if you want to use a combination of both? You might already have a script which uses the native XL functions to open a sheet and get or set some data. Let’s say you now want to augment this with an Excel method which is not exposed by the native functions. Rather than re-writing your entire script to use VBScript, is there a way we can let VBScript take over?

While it’s not possible to share native XL references with VBScript object references, what we can do is have VBScript attach to an open instance of Excel using the GetObject function. So sometime after running XLOpen we could then run a VBScript function which does a GetObject to get an object reference to Excel and then after that we are able to utlise any Excel function we like via VBScript.

The following script demonstrates:

VBSTART
  Dim xlApp
  Dim xlBook
  Sub GetXL
    Set xlApp = GetObject(,"Excel.Application")
    Set xlBook = xlApp.ActiveWorkbook
  End Sub

  Function FindCell(Sheet,Data)
    Dim theCell
    Dim xlValues
    xlValues = -4163

    Dim xlSheet
    Set xlSheet = xlBook.Worksheets(Sheet)
    xlSheet.Range("A1").Select
    Set theCell = xlSheet.Cells.Find(Data, xlApp.ActiveCell, xlValues)
    FindCell = CStr(theCell.Row) & ":" & CStr(theCell.Column)
  End Function
VBEND

//Open an XLS file natively
XLOpen>%SCRIPT_DIR%\example.xls,1,xlH

//Call GetXL to give VBScript a reference to the XL instance
VBRun>GetXL

//now we can access any XL function via VBScript
VBEval>FindCell("Sheet1","Price"),res

The only thing to be careful of is that there are no existing copies of Excel open before the one opened by XLOpen because according to the Microsoft docs GetObject will attach to the first opened instance. You could of course make the script check for this.