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

Improve this question
7
  • 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. Commented May 10, 2016 at 9:02
  • 1
    Correct. It is just sending key strokes (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. Commented May 10, 2016 at 9:11
  • 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 and/or worksheet, as that will find the right instance automatically. Commented May 10, 2016 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. Commented May 10, 2016 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. Commented May 10, 2016 at 18:41

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.