Learn How To Code JavaScript in VBA

JavaScript Functions With in VBA: The Merger

Include a reference to ‘Microsoft Script Control 1.0’ as explained below, before proceeding with further JavaScript tricks in this page.

1. Goto Menu -> Tools -> Reference
2. Search for “Microsoft Script Control 1.0’ in the listbox and enable it.

Only if this reference is included, the VBScript or JavaScript commands that you are going to code inside VBA will be executed. Note: With this article, we are not going to execute any external VBS or JS file, instead we are going to make VBA to execute the Scripts within Excel itself.

Coding Scripts inside VBA

As per Microsoft documentation, a script can be executed within a VBA in 4 different methods that is explained in detail in this page. In our examples we are only going to use ‘Run’ & ‘Eval’ methods.

Sub JavaScript_in_VBA()
    'Go to Menu -> Tool -> References -> Microsoft Script Control 1.0 & Enable by Clicking it
    Dim jsObj As New ScriptControl
    Dim InputValue1 As Integer, InputValue2 As Integer, Result As Integer

    'Get Sample Parameters
    InputValue1 = ThisWorkbook.Sheets(1).Cells(1, 1)
    InputValue2 = ThisWorkbook.Sheets(1).Cells(1, 2)

    'Define Scripting Language
    jsObj.Language = "JScript"
    With jsObj

        'Add JavaScript Code to Script Control Object
        .AddCode "function xProduct(a,b) {return (a*b);}"

        'Execute Added Script & Get result
        Result = .Run("xProduct", InputValue1, InputValue2)
        'Result = .Eval("xProduct( 1, 2)")

    End With
    ThisWorkbook.Sheets(1).Cells(1, 3) = Result
End Sub

Sub VBScript_in_VBA()
    Dim vbsObj As New ScriptControl
    vbsObj.Language = "VBScript"

    With vbsObj
        .AddCode "function xGetDate() xGetDate = Date End Function"
        MsgBox .Run("xGetDate")
        MsgBox .Eval("xGetDate()")
    End With
End Sub

Why we Need a JavaScript or VBS within VBA?

Sometimes, we will find built in functions in other programming languages. It would be tough to change the whole application from one programming language to another just because the other has few advanced functions. In these situations, these kind of inter operable techniques such as this JavaScript inside VBA would save lot of time and effort. For example, JavaScript has enhanced library routines to handle JSON data which is tough to manipulate within VBA. In such case, we can make use of this technique and refer JSON libraries within VBA.