Runtime Error 49, Bad DLL calling convention
For info, I also experienced "Runtime Error 49, Bad DLL calling convention" in Excel VBA code that had been running fine.
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.
Or, the best option ever:
- Rewrite the name of the routine.
- Then recompile !
You're good to go now!
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 Enum
type to the top of any module in the addin
Private Enum Something
member = 1
End Enum
2 -> Compile the addin
3 -> Restart excel
4 -> Remove the code change made. It is no longer necessary.
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.
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.
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.*
Broken library references: check whether the library references for your module code are valid. In the VBA IDE, select Tools=>References to see the list of referenced libraries and make sure none of the checked items are marked "Missing". If so, fix those.