@CoolBlue: And what is the mechanism of the crash: what is happening exactly to make Excel crash?
I can can give you an expansion of Siddarth Rout’s answer, but not a complete explanation.
API calls are not VBA: they exist outside VBA’s error-handlers and when things go wrong they will either do nothing, or call on a resource in memory that doesn’t exist, or attempt to read (or write!) to memory that’s outside the designated memory space for Excel.exe
When that happens, the Operating System will step in and shut your application down. We used to call this a ‘General Protection Fault’ and that’s still a useful description of the process.
Now for some details.
When you call a function in VBA, you just write the name – let’s call it ‘CheckMyFile()’ – and that’s all you need to know within VBA. If there’s nothing called ‘CheckMyFile’ to call, or it’s declared where your call can’t see it, the compiler or the runtime engine will raise an error in the form of a breakpoint, or a warning before it compiles and runs.
Behind the scenes, there’s a numeric address associated with the string ‘CheckMyFile’: I’m simplifying a bit, but we refer to that address as a Function Pointer – follow that address, and we get to a structured block of memory that stores definitions of the function parameters, space for their stored values and, behind that, addresses directing those parameters into the functional structures created to execute your VBA and return values to the address for the function’s output.
Things can go wrong, and VBA does a lot of work to ensure that all this folds up gracefully when they do go wrong.
If you give that function pointer to something that isn’t VBA – an external application or (say) an API Timer Call – your function can still be called, it can still run, and everything will work.
We refer to this as a ‘Callback’ when you hand the function pointer to the API, because you call its timer function, and it calls you back.
But there had better be a valid function behind that pointer.
If there isn’t, the external application will call its own error-handlers, and they won’t be as forgiving as VBA.
It might just drop the call and do nothing if Excel and VBA are in a ‘busy’ state or otherwise unavailable when it tries to use that function pointer: you might be lucky, just that once. But it might call down the wrath of the operating system on the Excel.exe process.
If the callback results in an error, and that error isn’t handled by your code, VBA will raise the error to the caller – and, as the caller isn’t VBA, it’ll probably have no way of handling that: and it’ll call for ‘help’ from the operation system.
If it’s an API call, it was written for developers who are assumed to have put the error-handling and contingency management in place in the calling code.
Those assumptions are:
- There will definitely be a valid function behind that pointer;
- It definitely be available when it is called;
- …And it will raise no errors to the caller.
With an API callback, caller is the operating system, and its response to detecting an error will be to shut you down.
So that’s a very simple outline of the process – a ‘why’ rather than a ‘what’ explanation of it.
The full explanation, without the oversimplifications, is for C++ developers. If you really want the answer in depth, you must learn to program with pointers; and you must become fluent with the concepts and practice of memory allocation, exceptions, the consequences of a bad pointer and the mechanisms used by an operating system to manage running applications and detect an invalid operation.
VBA exists to shield you from that knowledge and simplify the task of writing applications.