vba 运行时错误 49，错误的 DLL 调用约定

Runtime Error 49, Bad DLL calling convention

提问by Anirudh Ramanathan

Q. Excel keeps throwing the following error, whenever my addin is loaded (Runtime Error 49, Bad DLL calling convention)

问：每当加载我的插件时，Excel 不断抛出以下错误（运行时错误 49，错误的 DLL 调用约定）

The dialog starts to pop up everytime with no indication of where the error is, despite having absolutely no external DLL references.

尽管绝对没有外部 DLL 引用，但每次都开始弹出对话框，但没有指出错误在哪里。

OR

或者

Q. Excel crashes every time I save a particular line of code.

问：每次我保存特定的代码行时，Excel 都会崩溃。

How can this be fixed?

如何解决这个问题？

回答by Anirudh Ramanathan

This error is probably occurring because of a compiler-bug. The easiest solution to this, would be to make a small code-change and recompile. What I usually do is,

由于编译器错误，可能会发生此错误。对此最简单的解决方案是进行小的代码更改并重新编译。我通常做的是，

1 -> Add a Private Enumtype to the top of any module in the addin

1 ->Private Enum在插件的任何模块的顶部添加一个类型

Private Enum Something
    member = 1
End Enum

2 -> Compilethe addin

2 ->编译插件

3 -> Restart excel

3 -> 重启excel

4 -> Remove the code change made. It is no longer necessary.

4 -> 删除所做的代码更改。不再需要了。

回答by pstraton

1. Even though this error refers to an external (DLL) function call, it can be triggered by a parameter or return-value type mismatch for a VBA-defined function or subroutine. Furthermore, when it is triggered by these causes, the debugger sometimes displays the error point to be a different function call, often higher in the call-stack, including calls that have been working and stable until the problem-situation was created. Often, the problem is triggered by a miss-match between a fixed-type parameter-argument or return value and a Variant or vice versa.

   Example: A Variant-valued function returns a Long value at run time that is assigned to an Integer variable.

   Resolution:

   • Carefully check all parameter-argument and return value types and assignment statements, especially for routines that you have been recently working on. If any are Variant-valued functions, explicitly type-cast to the correct type for the assignment.
   • If the above situation is unavoidable due to using the Application.Run method to call a routine in a different workbook (for which you have no control over the parameter definitions), as a result of the Application.Run method passing all arguments ByVal, then, if the containing routine is a Sub, try converting it to a Function with no specified return type. This seems to force a clean-up of the stack and suppresses the error condition being thrown at a higher level in the call-stack.

2. An object method (such as AutoFit) applied to an erroneous object variation for which that method isn't available (such as AutoFit being applied to a range that is neither an entire row or entire column range). Similarly to the above scenario, the error may be thrown at the return point of the routine in which the problem statement exists, not at the statement itself.

   Resolution: Start with fixing the syntax problem. Unfortunately fixes that should work sometimes continue to throw the error until the VBE editor is reset. I haven't deduced the minimal set of steps that resolve that issue but something like this often works:

   • Explicit recompile the project.
   • Save the file and close it.
   • Re-open the file and re-run the code.

3. If a call to an external library function is identified as the culprit, refer to Microsoft's documentation on the error:

   Bad DLL calling convention

   *Arguments passed to a dynamic-link library (DLL) must exactly match those expected by the routine. Calling conventions deal with number, type, and order of arguments. Your program may be calling a routine in a DLL that is being passed the wrong type or number of arguments.

   To correct this error make sure all argument types agree with those specified in the declaration of the routine that you are calling.

   Make sure you are passing the same number of arguments indicated in the declaration of the routine that you are calling.

   If the DLL routine expects arguments by value, make sure ByVal is specified for those arguments in the declaration for the routine.

   Return argument: One thing that can be easily overlooked when talking about procedure arguments is the return argument. Make sure its of the correct type, or that its not missing. Excel/VBA users are used to the fact that if you leave out a return type for a function, the system implicitly sets the return type to Variant, and it works with any returned data. Not so with externally declared functions!! The return type has to be declared in the DECLARE statement.*

4. Broken library references: check whether the library references for your module code are valid. In the VBA IDE, select Tools=>Referencesto see the list of referenced libraries and make sure none of the checked items are marked "Missing". If so, fix those.

1. 即使此错误涉及外部 (DLL) 函数调用，它也可能由 VBA 定义的函数或子例程的参数或返回值类型不匹配触发。此外，当它由这些原因触发时，调试器有时会将错误点显示为不同的函数调用，通常位于调用堆栈的更高位置，包括在创建问题情况之前一直工作且稳定的调用。通常，问题是由固定类型参数参数或返回值与 Variant 之间的不匹配触发的，反之亦然。

   示例： Variant-valued 函数在运行时返回一个 Long 值，该值分配给一个 Integer 变量。

   分辨率：

   • 仔细检查所有参数参数和返回值类型以及赋值语句，尤其是您最近处理的例程。如果有任何 Variant-valued 函数，则明确地将类型转换为正确的赋值类型。
   • 如果由于使用 Application.Run 方法调用不同工作簿中的例程（您无法控制其参数定义）而导致上述情况不可避免，则作为 Application.Run 方法传递所有参数 ByVal 的结果，则，如果包含例程是一个 Sub，尝试将它转换为一个没有指定返回类型的函数。这似乎强制清理堆栈并抑制在调用堆栈中更高级别抛出的错误条件。

2. 一种对象方法（例如 AutoFit）应用于该方法不可用的错误对象变体（例如 AutoFit 应用于既不是整个行也不是整个列范围的范围）。与上述情况类似，错误可能会在存在问题语句的例程的返回点抛出，而不是语句本身。

   解决方案：从修复语法问题开始。不幸的是，有时应该起作用的修复程序会继续引发错误，直到 VBE 编辑器被重置。我还没有推导出解决该问题的最少步骤，但这样的事情通常有效：

   • 显式重新编译项目。
   • 保存文件并关闭它。
   • 重新打开文件并重新运行代码。

3. 如果对外部库函数的调用被确定为罪魁祸首，请参阅 Microsoft 的有关错误的文档：

   错误的 DLL 调用约定

   *传递给动态链接库 (DLL) 的参数必须与例程预期的参数完全匹配。调用约定处理参数的数量、类型和顺序。您的程序可能正在调用传递了错误类型或数量的参数的 DLL 中的例程。

   要更正此错误，请确保所有参数类型与您正在调用的例程声明中指定的参数类型一致。

   确保您传递的参数数量与您正在调用的例程声明中指示的参数数量相同。

   如果 DLL 例程需要按值传递参数，请确保在例程声明中为这些参数指定了 ByVal。

   返回参数：在谈论过程参数时很容易忽略的一件事是返回参数。确保它的类型正确，或者它没有丢失。Excel/VBA 用户习惯于这样一个事实：如果您省略函数的返回类型，系统会隐式地将返回类型设置为 Variant，并且它可以处理任何返回的数据。外部声明的函数不是这样！！返回类型必须在 DECLARE 语句中声明。*

4. 损坏的库引用：检查模块代码的库引用是否有效。在 VBA IDE 中，选择 Tools=>References以查看引用库的列表，并确保所有选中的项目都没有标记为“ Missing”。如果是这样，请修复这些。

回答by Fernando Fernandes

Or, the best option ever:

或者，有史以来最好的选择：

- Rewrite the name of the routine.

- 重写例程的名称。

- Then recompile !

- 然后重新编译！

You're good to go now!

你现在可以走了！

回答by sandbrain

For info, I also experienced "Runtime Error 49, Bad DLL calling convention"in Excel VBA code that had been running fine.

有关信息，我还在运行良好的 Excel VBA 代码中遇到了“运行时错误 49，错误的 DLL 调用约定”。

The error was pointing to an internal function call and the fix for me was to change an argument from ByVal to ByRef. There existed a call to another function where that value was already passed ByRef, so this may have been a factor.

错误指向内部函数调用，而我的解决方法是将参数从 ByVal 更改为 ByRef。存在对该值已通过 ByRef 传递的另一个函数的调用，因此这可能是一个因素。

回答by Stejin

Another way instead of "change/add something and recompile" would be to /decompile your project. Esp. in large access/excel projects that can get ride of a lot of problems.

另一种代替“更改/添加内容并重新编译”的方法是/反编译您的项目。特别是 在可以解决很多问题的大型 access/excel 项目中。

回答by Ross

In my case this was "caused" by an excessive use of the continue character _ in a single if conditional

在我的情况下，这是由于在单个 if 条件下过度使用继续字符 _ 造成的

I had already recompiled, checked all return codes, moved modules around ,restarted excel , restarted my computer, copied the code to a brand new Excel spread-sheet, I read this article and the bit about return codes made me think about how many returns can be in an if statement

我已经重新编译，检查所有返回代码，移动模块，重新启动 excel，重新启动我的计算机，将代码复制到一个全新的 Excel 电子表格，我读了这篇文章，关于返回代码的一点让我想到了多少返回可以在 if 语句中

I had this

我有这个

    If CompressIntoOneLineON(rg1, rgstart, rgend) or _
       CompressIntoOneLineOS(rg1, rgstart, rgend) or _
       CompressIntoOneLineOGN(rg1, rgstart, rgend) or _
       CompressIntoOneLineOGS(rg1, rgstart, rgend) or _
       CompressIntoOneLineGO(rg1, rgstart, rgend) Then
       <code>
    End If

I was getting the error when the subroutine containing this code exited So I changed to this

当包含此代码的子例程退出时，我收到错误所以我改为

matched = True
If CompressIntoOneLineON(rg1, rgstart, rgend) Then
ElseIf CompressIntoOneLineOS(rg1, rgstart, rgend) Then
ElseIf CompressIntoOneLineOGN(rg1, rgstart, rgend) Then
ElseIf CompressIntoOneLineOGS(rg1, rgstart, rgend) Then
ElseIf CompressIntoOneLineGO(rg1, rgstart, rgend) Then
Else
  matched = False
End If
if matched then
  <code>

and the error went away

错误消失了

回答by wallyeye

I had a similar issue, on my development PC it was working just fine. Funny thing was I had two separate calls to the same routine, one worked and the other didn't. On a production PC, kept getting the error. Tried renaming the routine, changing the parameters, parameter types to no avail.

我有一个类似的问题，在我的开发 PC 上它工作得很好。有趣的是，我对同一个例程进行了两次单独的调用，一个有效，另一个无效。在生产 PC 上，不断收到错误消息。尝试重命名例程，更改参数，参数类型无济于事。

What worked for me was to move the failing calling routine into the same module as the called subroutine.

对我有用的是将失败的调用例程移动到与被调用子例程相同的模块中。

The routine that was working previously was already in the same module.

之前工作的例程已经在同一个模块中。

回答by Anthony Moloney

Just to add another possible cause, I was using the Application.OnTimemethod to call a public sub with a parameter. The parameter is meant to be a long (the current row), but I'm guessing it's actually passed as a string value.

只是为了添加另一个可能的原因，我使用Application.OnTime方法来调用带有参数的公共子程序。该参数意味着很长（当前行），但我猜它实际上是作为字符串值传递的。

Here is an example of the OnTime call:

以下是 OnTime 调用的示例：

Application.OnTime Now + TimeValue("00:00:01"), "'UpdateEditedPref " & curRow & "'"

I tried performing an arbitrary update to the code and recompiling, but this didn't fix the issue. What fixed it was changing the parameter type from long to string in the called sub:

我尝试对代码执行任意更新并重新编译，但这并没有解决问题。修复它的是将被调用子中的参数类型从 long 更改为 string：

Public Sub UpdateEditedPref(ByVal inRowStr As String)

Then you just need to convert the string to a value within the sub. Thankfully, no more error.

然后您只需要将字符串转换为子中的值。谢天谢地，没有更多的错误。

Update:Passing a parameter using Application.OnTimeseems to have caused another error, "Cannot run the macro". I was getting this error when the worksheet was locked. I'm still using Application.OnTime, but instead of passing a parameter, I'm saving the value in a global variable and using that value in the called sub. This now appears to be working correctly.

更新：使用Application.OnTime传递参数似乎导致了另一个错误，“无法运行宏”。当工作表被锁定时，我收到此错误。我仍在使用Application.OnTime，但不是传递参数，而是将值保存在全局变量中并在被调用的子中使用该值。现在这似乎可以正常工作。

The OnTime call now looks like this:

OnTime 调用现在看起来像这样：

' Set global variable
gCurRow = curRow

Application.OnTime Now + TimeValue("00:00:01"), "UpdateEditedPref"