After hours of struggle last night on an automation project I am doing for work, I had a breakthrough today. I feel good about the project for the first tome in months.

In my experience, robotic process automation (RPA) cannot be coded in the same way as, say, VBA to manipulate data. I am very good at coding in VBA, even though it is my least favorite programming language. VBA was built for traditional programmers like me. I am used to using code to manipulate data, which is how VBA works. I am not used to using programming (not even code, but commands) to manipulate the user interface around the data, which is the most important part of how RPA works.

Automating applications and websites to work on data is tricky for numerous reasons. First, unpredictable things happen at runtime, like error messages for the application or the operating system that pop up and interrupt the program flow. Websites change unpredictably, and what you expect to be there suddenly is gone. In addition, if one step in an automation does not work right, and that is not handled appropriately, the automation will just barrel on and do goodness knows what in the wrong place or in the wrong window or application. Lastly, every action takes an unpredictable amount of time to finish, for a myriad of different reasons. It is hard to predict all of the weird things that might happen at runtime, but you have to deal with them as best you can.

The one thing that has been especially bedeviling to me is that, when an automation (or, colloquially, a bot) runs, the amount of time any task within it takes is completely unpredictable. If the bot does not wait long enough after something finishes, what it needs for the next task (typically a window, menu, or other control) may not even exist yet. The bot I am building would run one day, and fail to finish the next day, solely because my computer ran more slowly sometimes.

I found that my bot started working consistently only after I stopped trying to wait a fixed number of seconds between tasks, but instead told it, every step of the way, to wait until the control it needs to interact with next exists on screen. This leads to a lot more steps that I have to add to the automation, but also to a lot less guesswork about how long a pause is necessary between actions.

I also figured out a better way to wait for long-running data tasks and macros in Excel to finish. It’s simple, but I did not know it was possible until I looked for it today: wait for the mouse cursor to change from the hourglass cursor back to a normal one. It’s just what you would do as a user, but my programmer mindset, always focused on the data rather than on the user interface around the data, made me blind to it for a long time.

With these few changes to my coding approach, my confidence, which admittedly had been shaken by the project, has grown. I now feel that a lot more is possible to build with RPA technology. It just will be more tedious to build it than I would like it to be.