Excel addin中未使用的函数仅在第二次运行和通过VBScript运行时导致崩溃

Sorry for the long title.

对不起,长标题。

I have several .xlsm files which share a lot of code, so I moved the repeated parts to an addin .xlam file. I have been using a .vbs script to open all the files one after another and run a macro in each.

我有几个共享大量代码的.xlsm文件,因此我将重复的部分移动到addin .xlam文件中。我一直在使用.vbs脚本一个接一个地打开所有文件,并在每个文件中运行一个宏。

Problem

The problem I'm facing is that on the second run of the .vbs script, excel crashes and gives what seems to be a very generic error, said here to be an "Automation Error":

我面临的问题是,在.vbs脚本的第二次运行中,excel崩溃并给出了一个非常普通的错误,这里称为“自动化错误”:

Script: C:\Users\~\Desktop\test\test.vbs
Line: 5
Char: 1
Error: The server threw an exception.
Code: 80010105
Source: (null)

To my surprise, I was able to reproduce this crash even after removing 99% of the content of my files.

令我惊讶的是,即使删除了99%的文件内容,我也能够重现此崩溃。

test.vbs:

Dim xlApp
Dim xlBook
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Users\~\Desktop\test\test.xlsm")
xlApp.Run "Auto.Run"  '<~~ error on this line
xlBook.Save
xlBook.Close (True)
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing

test.xlsm:

test.xlam has a module Module1, test.xlsm has a Module Auto and a Reference to test.xlam

test.xlam有一个模块Module1,test.xlsm有一个Module Auto和一个test.xlam的参考

test.xlsm, Auto:

Sub Run()
    MsgBox "hello"
    Test.Load
    MsgBox "goodbye"
End Sub

test.xlam, Module1

Sub Load()
    MsgBox "Load"
End Sub

Function Other()
End Function

With the function Other() commented out, the code works fine (saying hello, load and goodbye). It also works fine if the macro is run from within excel. Only when Other() is present, and Run() is run through the .vbs file is there an error (right after hello).

使用函数Other()注释掉,代码工作正常(说你好,加载和再见)。如果从excel中运行宏,它也可以正常工作。只有当Other()存在且Run()通过.vbs文件运行时才会出现错误(在hello之后)。

Workaround

If I open test.xlsm, save it, and close it again in between each run of test.vbs, there are no problems. I believe this has something to do with the addin, rather than the spreadsheet, because in my original script, which opened multiple excel files, only one file needs to be opened and saved.

如果我打开test.xlsm,保存它,并在每次test.vbs运行之间再次关闭它,没有问题。我认为这与插件而不是电子表格有关,因为在我打开多个excel文件的原始脚本中,只需要打开和保存一个文件。

I also noticed that the excel file is a little bigger in its "problem" state, and that once I open and save it, it returns to its slightly smaller original size. (EDIT: This is at least partly caused by new cache streams __SRP_4 and __SRP_5 inside the vbaProject.bin file, which I extracted using this answer (oh, and this). After manually deleting all SRP entries, I was able to run the .vbs script again without problems, although just like the open-save-close strategy, it's only temporary, and will then crash on the third run rather than the second.)

我还注意到excel文件在“问题”状态下有点大,而且一旦我打开并保存它,它就会恢复到略小的原始大小。 (编辑:这至少部分是由新的缓存流引起的和__SRP_4的__SRP_5文件vbaProject.bin,我用这个答案(哦提取里面,这一点)后手动删除所有SRP项,我能够运行。 vbs脚本没有问题,虽然就像open-save-close策略一样,它只是临时的,然后会在第三次运行而不是第二次运行时崩溃。)

Question

Are addins not appropriate for shared code? May they not contain functions? Is there any way to work around this crash besides what I'm doing right now?

addin不适合共享代码吗?他们可能不包含功能吗?除了我现在正在做的事情之外,还有办法解决这次崩溃吗?

Any thoughts are appreciated.

任何想法都表示赞赏。

4 个解决方案

#1

It sounds to me like the first instance isn't being unloaded/released before the second instance is being called. Perhaps using the Application.Wait Method to wait a few seconds before each subsequent run in performed might help?

听起来像第一个实例在第二个实例被调用之前没有被卸载/释放。也许使用Application.Wait方法在每次后续运行之前等待几秒钟可能会有所帮助?

'Open file1
'Run macro from file1
'Close file1
Application.Wait(Now + TimeValue("0:00:10")) 'wait 10 seconds
'Open file1
'Run macro from file1
...
...
So on

To install your add-in to excel via vbscript you can use the following code

要通过vbscript将加载项安装到excel,您可以使用以下代码

'Launch Excel
set objExcel = createobject("Excel.Application")
strAddIn = "ESP Assistant.xlam"
'~~> Path where the XLAM resides
SourcePath = "Your source path\" & strAddIn

'Add the AddIn
On Error Resume Next
With objExcel
    'Add Workbook
    .Workbooks.Add
    'Show Excel
    objExcel.Visible = True
    .AddIns.Add(SourcePath, False).Installed = True
End With

If this fails you might have to clear your registry values first, then rerun the above script

如果失败,您可能必须先清除注册表值,然后重新运行上面的脚本

'File to use just in case Add-In installation fails
'Refreshes Excel Registry Entries to allow for clean install of Add-In
Dim objFSO, objShell
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = WScript.CreateObject ("WScript.shell")
objShell.Run "cmd /c ""C:\Program Files (x86)\Microsoft Office\Office14\excel.exe"" /unregserver && timeout /t 3 && tskill excel && ""C:\Program Files (x86)\Microsoft Office\Office14\excel.exe"" /regserver",1,True
Set objFSO = Nothing
Set objShell = Nothing
x=msgbox("Excel registry refreshed." ,0, "Registry Update")
wscript.quit

#2

Unfortunately, I still don't know why this is happening, but I found an automated solution that I'm going to stick with.

不幸的是,我仍然不知道为什么会这样,但我找到了一个我将坚持使用的自动化解决方案。

As I mentioned in my question, the test.xlsm file was a little bigger in its "problem" state, due at least partially to some kind of cache, of which I could only find one offical mention here:

正如我在我的问题中提到的,test.xlsm文件在其“问题”状态下有点大,至少部分归因于某种缓存,我在这里只能找到一个官方提及:

2.2.6 SRP Streams

2.2.6 SRP流

Streams that specify an implementation-specific and version-dependent performance cache. MUST be ignored on read. MUST NOT be present on write. The name of each of these streams is specified by the following ABNF grammar:

指定特定于实现和版本的性能缓存的流。读取时必须忽略。写作时不得出现。每个流的名称由以下ABNF语法指定:

SRPStreamName = "__SRP_" 1*25DIGIT

SRPStreamName =“__SRP_”1 * 25DIGIT

My solution was to remove the cache, which I did manually at first with this tool. When that seemed to work, I wrote a Java program to do it automatically (gist here). It's glue between java.util.zip and Apache POIFS.

我的解决方案是删除缓存,我首先使用此工具手动完成。当这似乎有效时,我写了一个Java程序来自动完成它(这里的要点)。它是java.util.zip和Apache POIFS之间的粘合剂。

I also added a line to call the Java at the end of the .vbs script:

我还在.vbs脚本的末尾添加了一行来调用Java:

CreateObject("WScript.Shell").Run "java -jar clear-excel-cache.jar C:\Users\~\Desktop\test\test.xlsm", 1, false

In my actual .vbs file, which calls multiple excel files in a loop, this line is just inside the loop. There is a little cmd window that opens after each file is run but it no longer crashes on the second run, so I'm calling that a success.

在我的实际.vbs文件中,它在循环中调用多个excel文件,这一行就在循环内部。每个文件运行后都会打开一个小cmd窗口,但在第二次运行时它不再崩溃,所以我称之为成功。

#3

Your issue could be the same issue which I am trying to resolve - Random 64-bit Excel 2013 VBA crashes (VBE7.dll errors). You can check the Application Event logs for a VBE7.dll crash to confirm this.

您的问题可能与我尝试解决的问题相同 - 随机64位Excel 2013 VBA崩溃(VBE7.dll错误)。您可以检查应用程序事件日志中的VBE7.dll崩溃以确认这一点。

In my case various XLSM files become intermittently corrupted through manual use.

在我的情况下,各种XLSM文件通过手动使用间歇性地损坏。

My fix as an alternative to yours is the following VBS (anything to trigger a VBA "recompile").

我的修复作为你的替代品是以下VBS(任何触发VBA“重新编译”的东西)。

Resave "myfile.xlsm"

Sub Resave(filename)
    Set objExcel = CreateObject("Excel.Application")

    currentDirectory = left(WScript.ScriptFullName,(Len(WScript.ScriptFullName))-(len(WScript.ScriptName)))

    objExcel.Application.AutomationSecurity = 3 ' Disable to avoid crash
    objExcel.Application.enableevents = False
    objExcel.Application.Workbooks.open(currentDirectory + "\" + filename)
    objExcel.Application.Visible = True

    objExcel.Application.DisplayAlerts = False
    Set objSheet = objExcel.ActiveWorkbook.Sheets.Add
    objSheet.Delete
    objExcel.Application.DisplayAlerts = True

    objExcel.Application.enableevents = True
    objExcel.ActiveWorkbook.Save
    objExcel.ActiveWorkbook.Close

    objExcel.Application.Quit 

    Set objExcel = Nothing
End Sub

#4

FYI - Microsoft released a patch which fixes the issue in Excel 2013 on 3rd May 2016.

仅供参考 - Microsoft于2016年5月3日发布了一个补丁,用于修复Excel 2013中的问题。

https://support.microsoft.com/en-us/kb/3085486