将代码从VBA运行到VBScript返回到VBA [英] Running code from VBA to VBScript back to VBA

问题描述

我试图找出一种使用 VBScript 函数的方法在 Excel 中的title =显示问题标记'vba' =tag> vba ，然后将值传回 excel-vba 。见下文

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

Excel中的VBA

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

我知道这可能看起来很奇怪，因为我可以在VBA中写这个函数，但是有一个办公室补丁推出，完全杀死了我的一个宏的功能由于某些原因。奇怪的是，我可以在任何其他办公室程序中运行完全相同的代码，只是没有excel。作为一个工作，我移动了崩溃excel到word的功能，我拉它使用application.run，但我更喜欢不必这样做，因为打开一个应用程序来运行我的宏减慢我的过程方式下降。

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

推荐答案

感觉有点脏：）

此代码有两个关键部分：

This code has two key parts:

• vbs 使用 GetObject 和完整的主机工作簿路径重新附加到包含 VBA 调用 VBS


• VBS 向主机 VBA 文件中的特定工作表添加一个值，以触发 Worksheet_Change 事件触发，运行 VBA 与从 VBS 中传递的字符串。

• 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.

步骤1：常规Excel代码模块

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

步骤2：myvbs

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

步骤3：Excel文件中 vbs表的表格代码

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

这篇关于将代码从VBA运行到VBScript返回到VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！