Wednesday 24 January 2018

COM - Windows Scripting Components - wrap JScript and VBscript into components callable from VBA

Surprisingly, it is possible to write JScript or VBcript programs and package them into Windows Scripting Components which can be registered as COM components. Here are two examples, one JScript and one VBScript.

Example JScript Component

<?xml version="1.0"?> 
<package>
<component id="ExcelDevelopmentPlatform.JScriptComponent1">
 
<registration 
   description="Example JScript component" 
   progid="ExcelDevelopmentPlatform.JScriptComponent1" 
   version="1.00" 
   classid="{63A7A78A-933C-4BD2-844A-5D78CFC5FB92}"
   remotable="True"> 

</registration> 

<public> 
   <method name="Greet"> 
      <PARAMETER name="s"/> 
   </method> 
   <method name="Sum"> 
      <PARAMETER name="a"/> 
      <PARAMETER name="b"/> 
   </method> 
</public> 
<script language="JScript"> 
<![CDATA[ 

function Greet(s) {
   return ("From JScript Component, hello " + s); 
}

function Sum(a,b) {
   return (a+b);
}

]]> 
</script> 
</component>
</package>

Example VBScript Component

<?xml version="1.0"?> 
<package>
<component id="ExcelDevelopmentPlatform.VBScriptComponent2">

<registration 
   description="Example vbScript component" 
   progid="ExcelDevelopmentPlatform.VBScriptComponent2" 
   version="1.00" 
   classid="{63A7A78A-933C-4BD2-844A-5D78CFC5FB93}"
   remotable="True"> 

  <script language="VBScript">
  <![CDATA[
    '* not strictly necessary but nice to see one can customize the messages
 '* received when registering with regsvr32.exe 
  
    sMyBlog = "See my blog http:\exceldevelopmentplatform.blogspot.com"

'******************************************************************************

    Function Register
        Msgbox "Windows Script Component registered." & vbNewLine & sMyBlog
    End Function

'******************************************************************************

    Function Unregister
        Msgbox  "Windows Script Component unregistered." & vbNewLine & sMyBlog
    End Function

'******************************************************************************

  ]]>
  </script>   
   
   
</registration> 

<public> 
   <method name="Greet"> 
      <PARAMETER name="s"/> 
   </method> 
   <method name="Sum"> 
      <PARAMETER name="a"/> 
      <PARAMETER name="b"/> 
   </method> 
</public> 
<script language="VBScript"> 
<![CDATA[ 

function Greet(s) 
   Greet = ("From VBScript Component, hello " + s)
end function

function Sum(a,b) 
   Sum = (a+b)
end function

]]> 
</script> 
</component>
</package>

Registering the components on the command line

N:JScriptDev>regsvr32 jScriptComponent1.wsc

N:JScriptDev>regsvr32 vbScriptComponent2.wsc

VBA code to create and call the components

Sub TestJScriptComponent1()

    Dim obj As Object
    Set obj = VBA.CreateObject("ExcelDevelopmentPlatform.JScriptComponent1")
    
    Debug.Assert TypeName(obj) = "ExcelDevelopmentPlatform.JScriptComponent1"
    Debug.Print obj.Greet("Simon")
    Debug.Print obj.Sum(3, 4)
End Sub


Sub TestVBScriptComponent2()

    Dim obj As Object
    Set obj = VBA.CreateObject("ExcelDevelopmentPlatform.VBScriptComponent2")
    
    Debug.Assert TypeName(obj) = "ExcelDevelopmentPlatform.VBScriptComponent2"
    Debug.Print obj.Greet("Simon")
    Debug.Print obj.Sum(3, 4)
End Sub

Links

Some useful links given that a lot of the documentation has fallen into disuse.

No comments:

Post a Comment