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
– Tim Williams, Commented Apr 9, 2013 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
– Izomiac, Commented Apr 11, 2013 at 5:18

Community · Accepted Answer · 2017-05-23 10:33:57Z

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, 2017 at 10:33

CommunityBot

11 silver badge

answered Apr 18, 2013 at 5:53

Laurence Moroney

1,2638 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Izomiac Over a year ago

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.

Collectives™ on Stack Overflow

Using Javascript in Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related