SendKeys runs twice

[DavidB]

Hello there

I have written a wee program to extract the contents of a cell one character at a time and write them to the Immediate window along with the character position and the ASCII value. The purpose of this code is to determine what any invisible characters in the cell are.

At the start of the code I clear the contents of the immediate window using Application.SendKeys.

The code works perfectly if I step through the code using F8. If I run the code using F5 the table displays briefly in the immediate window but then disappears.

It appears that the SendKeys buffer needs to be flushed to stop it running a second time as the subroutine terminates but I cannot find a way to do this.

I tried forcing an ABEND by putting a deliberate mathematical error (x = x/0) into the code before the End Sub but this resulted in the code window being cleared, however, it did leave the contents of the Immediate window intact.

Here is my code:
Sub ReadCellByCharacter()

Dim NumChars As Single

' Clear the immediate window
' https://social.msdn.microsoft.com/F...-to-clear-the-immediate-window?forum=exceldev
Application.SendKeys "^g ^a {DEL} {backspace}", False ' *** This line appears to execute a second time before end sub. ***

' Determine the number of characters in a cell
NumChars = Len(ActiveCell.Value)

' Print header
Debug.Print Now
Debug.Print "Character#", "Character", "ASCII Value"

' Read Cell a character at a time then write to debug window.
For x = 1 To NumChars
Debug.Print x, Mid(ActiveCell.Value, x, 1), Asc(Mid(ActiveCell.Value, x, 1))
Next x

End Sub


Any ideas how to overcome this problem?

Thanks DavidB

 

[Yongle]

I would never use send keys to delete anything in the VBA window
- SendKeys is unpredictable and therefore risky
- one false move and your code is deleted instead of the immediate window

If you are simply after some clear space after previous Debug.Print

Try replacing

Application.SendKeys "^g ^a {DEL} {backspace}", False

with

Application.SendKeys "^g^{END}", True
DoEvents
Debug.Print String(200, vbCrLf)

 

[DavidB]

Thanks Yongle

Yes that the revised code seems to work thank you.

I note your comments concerning SendKeys and thoroughly agree. SendKeys must be used with extreme caution. I would go so far as to say one must clear the SendKeys Buffer on not use at all.

I found that my simple code closed Excel if the ActiveCell was vacant. Most undesireable.

I have not used SendKeys before today and am surprised how unpredictable it is.

Cheers DavidB