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

brettdj · Accepted Answer · 2014-01-01 09:23:51Z

4

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

edited Jan 1, 2014 at 9:23

answered Dec 31, 2013 at 1:06

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

brettdj Over a year ago

@Tronald did you ever try this?

Tronald Over a year ago

I don’t know why I never noticed this was left unchecked. Sorry about that!

Gary's Student · Accepted Answer · 2013-12-31 01:02:56Z

3

Try syntax like:

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

answered Dec 31, 2013 at 1:02

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

5 Comments

Tronald Over a year ago

I appreciate your answer as well. I must have worded my question bad as you and Brett gave the same answer. I know I can run the script from VBA but what I would like to do is call a specific function within the script and set my string object within VBA equal to the result of the function contained in the script. Is there a way to do this?

Gary's Student Over a year ago

@Tronald: I have never done this, but both VBA and VBScript can read/write Registry keys. You could design and implement an interface using them.

brettdj Over a year ago

@tronald can't you run all of this - including automating VBA - from the VbS directly, rather than looking to switch between the two

Tronald Over a year ago

@brettdj, that would be the 'makes sense' route if i hadn't already built a giant beast withing VBA. I have a few programs that that worked great for years. Last week an update to office was pushed over our network and now excel closes when I log into my database at work through excel. I can use the same code within word or any other office product and it works fine, but excel just closes when it runs it's so strange. The people who designed the database no longer offer SDK support and my chances of getting the network admins to remove the patch or getting Microsoft to fix what they broke is 0

Tronald Over a year ago

I ran out of characters so continuing on.... So as a temporary work around i sent the database login sub to word. I open a word application from excel, run the code in word as a function then set my login object in Excel = to the function that ran in Word. It works fine, but opening word takes up far more memory that running a simple script. If I can figure out how to do a simple hello world like described above, I can figure out this workaround I need.

Collectives™ on Stack Overflow

Running code from VBA to VBScript back to VBA

2 Answers 2

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related