2

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! :)

Community
  • 1
  • 1
Sun
  • 762
  • 4
  • 10
  • 2
    SendKeys is unreliable because it literally sends the command to the active window, and it is not always possible to guarantee that that is the intended window. –  May 10 '16 at 09:02
  • So for API, it does not matter which window is active? – Sun May 10 '16 at 09:04
  • 1
    Correct. It is just [sending key strokes](https://msdn.microsoft.com/en-us/library/office/ff821075.aspx) (as explained by @ojf). And now imagine your Excel code is running and someone is trying to get some browsing done while the code is running... pressing keys and changing the active window to a browser. – Ralph May 10 '16 at 09:11
  • Ok, I see, thanks for the quick replies! I just tried it, and changing to another window indeed produced an error message... Guess I need to learn about APIs then – Sun May 10 '16 at 09:17
  • 1
    The API has the great advantage that you can specify which object is being worked on. You can specify an Excel instance if you like, but more common is to specify the [workbook](https://msdn.microsoft.com/en-us/library/office/ff835568.aspx) and/or [worksheet](https://msdn.microsoft.com/en-us/library/office/ff194464.aspx), as that will find the right instance automatically. – Vegard May 10 '16 at 10:43
  • 1
    Try and see. I can't remember a single application of send keys which wasn't fragile and unreliable. Trying to code with it is almost always an exercise in aggravation. An hour spent trying to get it to (sort of) work is enough to convince any programmer that `SendKeys` is a tool of last resort. – John Coleman May 10 '16 at 11:11
  • 1
    Think of the classic Simpsons episode where fat-Homer used a bobbing-bird toy to continually tap OK while he went to an afternoon movie matinee. That's SendKeys and often results in a similar outcome. –  May 10 '16 at 18:41

1 Answers1

3

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.

SierraOscar
  • 17,507
  • 6
  • 40
  • 68