return the VBscript result back to vba

[Former Member]

Hello,

I’m trying to call VBScript ( gui script I recorded ) from within VBA code. I'm aware I can do all from VBA - but in my project i need to
call the VBScript from within the VBA

Is it possible to return the VBscript result back to vba ?

For example it requires to return the success or error message
after push the save

button in the at the VBscript - back to vba which will return it to the excel
result column

Thanks,

Ziv

[stefan_schnell · Accepted solution]

Hello Ziv,

sure it is possible, try this:

Sub Test()

  '-Variables-----------------------------------------------------------
    Dim VBScript As MSScriptControl.ScriptControl
    Dim Code As String
    Dim Result As Variant

  Set VBScript = CreateObject("MSScriptControl.ScriptControl")
  If Not IsObject(VBScript) Then
    Exit Sub
  End If

  Code = "Function Hugo()" & vbCrLf
  Code = Code & "Hugo = MsgBox(""Test"", vbOkOnly)" & vbCrLf
  Code = Code & "End Function"

  VBScript.Language = "VBScript"
  VBScript.AllowUI = True
  VBScript.AddCode Code
  Result = VBScript.Run("Hugo")

  Set VBScript = Nothing

End Sub

In the variable code it is stored the VBScript function Hugo which delivers the result of the MsgBox function back. With the function Run you get the result from this function.

Let us know if it worked.

Cheers

Stefan