Using Javascript in Excel
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 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?