0

The latest MS Office update has disabled vbscript which I am using in Excel VBA to evaluate expressions. I have found this to be substantially faster than the VBA "Evaluate" function in the past so want to avoid doing that.

Javascript still works so I am trying to migrate to that. However some of our more complex expressions, which use powers or square roots, do not work without re-writing the expressions to use Math.Pow() etc.

So my question is if I can run a javascript which evaluates a vbscript expression.

Example of what I am doing currently as follows:

Sub TestVBScript()
Dim objscript As New ScriptControl
Dim var As Variant
Dim str As String
objscript.Language = "vbscript"
str = "2+2"
var = objscript.Eval(str)
MsgBox str & " = " & var
End Sub

What I want is something which evaluates the vbscript expression but using javascript, thereby bypassing the MS Office bug.

Here is information about the MS Office update issue in case anyone interested. I think likely to be a big problem for a lot of people: https://social.msdn.microsoft.com/Forums/en-US/45c14333-3685-4b2d-9a3a-6a109a5a2a86/access-2016-microsoft-script-control-stopped-working-error-380?forum=accessdev

Thank you for your help!

bgarrood
  • 419
  • 8
  • 17
  • 1
    I thought use of Eval /script control in this way was discouraged. Not performant and dangerous. See if I can find something on it...... – QHarr Jul 10 '18 at 12:03
  • 1
    Section called [Do not ever use eval!](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/eval#Do_not_ever_use_eval!) in that link. And this [answer](https://stackoverflow.com/a/32429348/6241235) I am not an expert. This is just what I have read. – QHarr Jul 10 '18 at 12:05
  • +1 with what QHarr said ^ You should avoid `eval` like a plague. There is however an also inbuilt `Application.Evaluate()` method , but I kind of feel bad for even giving you this suggestion. One of the first rule of javascript goes, whatever you want to do with eval, can be achieved without it, with fewer negative side effects – Samuel Hulla Jul 10 '18 at 12:09
  • While I don't want to take anything away from these comments, using a `New ScriptControl` and then `Eval` is way less dangerous/evil then just using `VBA.Eval` (or that `Eval` in JS). `ScriptControl.Eval` has its own scope. I might still be able to do evil things if I get a text box and everything in it gets passed to that function, but it'll be harder. – Erik A Jul 10 '18 at 12:26
  • Thank you . Eval(script) is 10+ times faster than application.evaluate, which is why I am trying to do it this way. The "script" is always a simple expression such as (1+2+3^4) so really I am not running complex scripts, just using it as a means to evaluate an expression. Any other ideas? – bgarrood Jul 10 '18 at 12:26
  • VBA Evaluate uses Excel's formula parser and interpreter. (BTW Worksheet.Evaluate is about 2x faster than Application.Evaluate). There is no VBA equivalent of VBScript's Eval: Eval evaluates VBscript expressions rather than Excel formulas. – Charles Williams Jul 10 '18 at 14:59
  • Thanks Charles. Question is whether I can use javascript to run vbscript. i.e. can I do x=eval(javascript) where javascript = eval(vbscript) or similar? As shown above, eval(vbscript) generally works fine apart from the fact that latest MS office update killed it. Thank you! – bgarrood Jul 12 '18 at 13:40

1 Answers1

0

Try this solution:

Sub Test()

    MsgBox Eval("1+2+3^4")

End Sub

Function Eval(s As String)

    Static oDoc As Object

    If oDoc Is Nothing Then
        Set oDoc = CreateObject("htmlfile")
        oDoc.parentWindow.execScript "Function EvalExpr(s): EvalExpr = Eval(s): End Function", "vbscript"
    End If
    Eval = oDoc.parentWindow.EvalExpr(s)

End Function
omegastripes
  • 12,351
  • 4
  • 45
  • 96