Create a text file (JSEvaluate.wsc) with the following contents somewhere on your hard drive:
<component><public><method name="Evaluate"><PARAMETER name="code"/></method><property name="Application"/><property name="Global"/></public><script language='JScript'>
Global = this;
function Evaluate(code) {var result;try {result=Global.eval(code);
} catch(e) {
result=e.message;
}
return result;}
</script></component>
You now have an embeddable JavaScript Engine that you can call from any Window’s Application.
To test it, open Excel/Word/Outlook/PowerPoint, etc.
In our example, we are going to use Excel. Once Excel is loaded, press ALT+F11 to open the VBA Development Tools:
As illustrated in the above screenshot, accessing your new JavaScript Engine is as simple as instantiating a new Object with a call to the COM GetObject function. This capability exists in pretty much all Window’s programming languages in one form or another.
Now that we have the ability to evaluate JavaScript expressions on the fly, we might want tighter integration to the host application so that we can extend it’s features via script. The following code snippet illustrates just how simple this is:
Taking this one step further, we can create a new Excel Function
=Eval()
that provides the ability to execute JavaScript code in native Excel Macros. This can be accomplished by including the following code in a new VBA module, either as part of the existing WorkBook or globally as part of PERSONAL.XSLB. Insert a new module in your VBA code and add the following code:
Public JScript As Object
Public Function Eval(code As String)If JScript is Nothing ThenSet JScript = GetObject("script:c:\temp\jsevaluate.wsc")Set JScript.Application = Excel.ApplicationEnd IfEval = JScript.Evaluate(code)End Function
It should look like this in the development environment:
You now have a new Excel Function (Eval) that can be used in any Excel Formula:
Also, since the JavaScript Engine has access to the Excel application in it’s Global Context, you can do some pretty sophisticated stuff that is hard to do in just native Excel formulas. Indeed, you can write whole programs in JavaScript that are accessible via Excel Formulas. In the following example, we write our JavaScript code snippets in column A and have them evaluated using our Eval formula in Column B:
Finally, extremely powerful is the ability, within the scope of our JavaScript Engine, to call out to any native functions available via COM objects. This is illustrated in the above example by
=Eval(A9)
which calls out to an external website to retrieve some information.
I look forward to your comments.
Cheers.