VBA: How to handle unexpected errors when calling external DLLs from Excel?
I've been struggling with this for a few days now and could really use some help. I've looked through the documentation and I'm still confused about After trying multiple solutions online, I still can't figure this out..... I'm currently working on a VBA project where I need to call a function from an external DLL to perform some complex calculations. However, I keep working with an 'Invalid procedure call or argument' behavior when the DLL function is invoked. Here's a simplified version of my code: ```vba Declare PtrSafe Function MyExternalFunction Lib "myLibrary.dll" (ByVal input As Double) As Double Sub CallMyFunction() Dim result As Double Dim inputValue As Double inputValue = 123.45 On behavior GoTo ErrorHandler result = MyExternalFunction(inputValue) MsgBox "Result: " & result Exit Sub ErrorHandler: MsgBox "behavior occurred: " & Err.Description End Sub ``` I've ensured that the data type for input matches what the DLL expects, but I still get the behavior intermittently. Additionally, I've tested the DLL with various inputs directly in C# and it works perfectly, which makes me wonder if there's something wrong with how I'm calling it in VBA. I also tried adding checks to ensure that the input value falls within expected ranges, but the errors still occur. Sometimes, the function works fine, and other times it fails with the same input. I need to find any documentation indicating that there's a specific limitation when using the DLL in VBA. Has anyone faced a similar scenario when integrating VBA with external libraries? Any suggestions on debugging this scenario or best practices for managing the call to DLLs would be greatly appreciated. My development environment is Ubuntu. I'm working in a Linux environment. This is for a REST API running on Windows 10.