Running code from VBA to VBScript back to VBA

Question

I'm trying to figure out a way to call a VBScript function using vba in Excel, and then pass a value back to excel-vba. See below

VBA within Excel

    Sub RunTest()
    Dim objString as String

    'Begin Pseudocode
    objString = Call VBScript Function Test()
    'End Pseudocode

    MsgBox objString `from VBS
    End Sub

VBScript

    Function Test
    Test = "Hello World"
    End Function

I know this may seem strange because I could just write the function in VBA, but we had an office patch pushed out and it completely killed the functionality of one of my macros for some reason. Strange thing is, I can run the exact same code within any other office program, just not excel. As a work around, I moved the function that crashes excel to word and I pull it using application.run, but I prefer to not have to do that, as opening a the word application to run my macro slows my process way down.

Any help is appreciated, thank You

Answer 1

Try syntax like:

Sub Test()
    Shell "cscript c:\TestFolder\sample.vbs", vbNormalFocus
End Sub

Answer 2

Ok, I feel a litte dirty :)

This code has two key parts:

• the vbs Uses GetObject and the full host workbook path to re-attach to the file containing the VBA that called the VBS
• the VBS adds a value to a specific worksheet in the host VBA file to trigger the Worksheet_Change event to fire, running VBA with the string passed from the VBS.

Step 1: Regular Excel code module

Sub VBA_to_VBS_to_VBA()
Shell "wscript c:\temp\myvbs.vbs", vbNormalFocus
End Sub

Step 2: myvbs

Dim xlApp
Dim xlSht
On Error Resume Next
Set xlApp = GetObject("c:\temp\mybook.xlsx").Application
Set xlSht = xlApp.Sheets("vbs sheet")
On Error GoTo 0
If Not xlSht Is Nothing Then
xlSht.Range("A1").Value = "hello world"
Else
wscript.echo "sheet not found"
End If

Step 3: Sheet code for vbs sheet in your Excel File

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox [a1].Value, vbCritical, "VBS insertion"
End Sub