Back in the old times when I programmed in VBA/ VB sometimes there was the necessity to use … workarounds. A very popular especially in Excel development was DoEvents. Have a look onto this official sample by Microsoft:
' Create a variable to hold number of Visual Basic forms loaded
' and visible.
Dim I, OpenForms
For I = 1 To 150000 ' Start loop.
If I Mod 1000 = 0 Then ' If loop has repeated 1000 times.
OpenForms = DoEvents ' Yield to operating system.
End If
Next I ' Increment loop counter.
To be honest, I did see very few reasons for the DoEvents function to return the count of opened forms, but anyway it did what MessageFilters for OLE Servers did when using C# or any other language controlling Office applications – it let Excel the time to finish operations.
There was another method in Excel that allows for special handling. To say it nicely, user interactions could be performed on the user interface. Other people would claim Excel needed a lot of workarounds and hard to control methods to make it possible to implement concrete functionalities. I am talking about SendKeys.
I started ’96 my professional career as a programmer, basically with Access 2.0 and VBA. I am pretty happy that I didn’t start before to not being scared by the German programming language that has been baked into the older Word versions. 😀
When I moved on to a different company in ’99, I started to do a lot of stuff in Excel. Excel 97 was a pain and a lot of times, DoEvents was used to overcome synchronization issues when updating ranges, worksheets, shapes or the like. But even back in the old times, I refused to use SendKeys, as it has pretty nerving consequences.
- The code has to wait for execution on the user interface. If there is any chance to do it differently, great idea
- The are a lot of situations when it doesn’t work.
- When the user interrupts the sendkeys because he just types
- When the application is not the active window
- There are actually not that many possibilities to capture if SendKeys worked. And trying it again is mostly as bad as the first try. 🙂
So here we go, it hit me again:
public void LeaveCellEditMode()
{
_log.Debug();
if (IsExcelInteractive())
{
return;
}
using (var range = Application.ActiveCell)
{
SendKeys.SendWait("{ENTER}");
range.Select();
}
}
Actually, the C# implementation looks better but does in fact has the same issues. And it is still necessary in Excel interop as the discussion on StackOverflow How can I force to stop cell editing in Excel interop shows, there is still no straight way for doing so.
Fast forward for 20 years.
Still the same issues.
Unbelievable.
Good to move. To Cloud. To Office 365. To anything. Only standing still means being dead.