Search code examples
vbaapiexcelsendkeys

Excel VBA: Why is API better than SendKeys?

I am currently working on a VBA project in Excel where I need to unlock a VBProject and also lock another VBProject. So far, I have been doing this with SendKeys, but I keep reading that it is not a good method, and that API is better? (For example in this thread: Unprotect VBProject from VB code)

However, I could not find any detailed information as to why during my research.

Could someone please tell me why exactly SendKeys is bad? What are the things that could go wrong? (Please note that my SendKeys sequence is only 1.5 seconds long at most.) Also, why is API the better approach?

Thanks! :)

Solution

  • WinAPI uses things like window handles (you may have seen hWnd in code before?) to target a specific window. Once you have this you can send and receive messages to that window regardless of it's window state (active/inactive) etc.

    You are working directly with the object, which is the way programming should be.

    The SendKeys() method just emulates a user hitting keys on a keyboard, irrespective of what window is open and where - so it naturally sends the output to whatever object is active and able to receive it.

    Another way to think about it

    If you're coding to place a value in a cell on a certain sheet in VBA you can do the following:

    Range("A1").Value = "Foo"

    This is all well and good, but it assumes that the sheet we want is the active sheet at that moment in time. If it isn't, the wrong cell on the wrong sheet will be populated instead. This is effectively what you are doing with SendKeys()

    This on the other hand:

    Workbooks("Target Workbook.xlsx").Sheets("Target Sheet").Range("A1").Value = "Foo"

    Specifies the exact cell, in the exact sheet, in the exact workbook that we want to target - so if that sheet isn't active at that point in time then no worries! It will still go to the right place (this is kind of what you're doing with API)

    A WORD OF CAUTION

    Playing with WinAPI in VBA can be risky if you don't know what you're doing - the code for these methods is pre-compiled in an external library which means your VBE error handler isn't going to be of any use. If you make a mistake with API you run the risk of corrupting your workbook (or worse depending on what you're actually doing).

    You also need to look at conditional compilation in VBA, because you have to declare functions and parameters differently depending on whether you're using a 32-bit or 64-bit version.