Using Javascript in Excel

Question

I'm currently creating an Excel workbook that runs a Monte Carlo type simulation. The simulator code is currently in Javascript though, and porting entirely to VBA appears to be non-trivial given the team's inexperience with this language. So, I've been able to incorporate the javascript components into a WSC file, which works well. (Simplified example below.)

    Sub Simulate()
        Set ATPSim = GetObject("script:http://www.example.com/ATPSim.wsc")
        'Set ATPSim = GetObject("script:C:\ATPSim.wsc")
        Dim ParamOne As Integer
        ParamOne = Range("B2").Value
        Dim ParamTwo As Double
        ParamTwo = Range("B3").Value
        Dim ParamThree As Double
        ParamThree = Range("B4").Value
        Range("B1").Value = ATPSim.simulate(ParamOne, ParamTwo, ParamThree)
    End Sub

Unfortunately, this requires me to host the javascript code or rely on unsophisticated users to update the absolute path. Is there a self-contained solution? I'd prefer to keep everything in one .xlsm file which can be e-mailed to the users.

You could store the js in a cell (if there's not too much code) and then use that to write out a file to the user's temp folder. — Tim Williams, Apr 09 '13 at 20:23
Like this http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/ ? — deusxmach1na, Apr 09 '13 at 20:23
The temp folder idea is an interesting one that should work. I'll have to look into it. The final simulation code will likely be rather complex, but I doubt it'll exceed a few hundred KB. The Chandoo tutorial uses pure excel rather than VBA or JS. Unfortunately, this simulation has far too many branch points to model with a spreadsheet (it'd require something like 100 million cells). — Izomiac, Apr 11 '13 at 05:18

score 1 · Answer 1 · edited May 23 '17 at 10:33

1

You should be able to host the JS in a COM Component and use WSH to access it.

See the example here: How can I use JavaScript within an Excel macro?

edited May 23 '17 at 10:33

Community

1
1

answered Apr 18 '13 at 05:53

Laurence Moroney

1,263
8
20

That's what I did, AFAIK (perhaps my terminology isn't correct?). As I understand it though, this approach requires registering the WSC or keeping it in a predictable location. I do not anticipate the end users being savvy enough to do either. – Izomiac Apr 18 '13 at 23:42

Using Javascript in Excel

1 Answers1