Jumping into the deep end (when you don’t know how to swim)
This week I got an email from a person working at an organization that I had previously worked with; I'll call him Jamad. For the previous project I helped to automate the creating and updating of several unique Excel files, and based on that work, Jamad thought of me for a few more Excel projects.
One of them was really interesting to me: Jamad had a spreadsheet that he had been using for a while but would not work on any new versions of Excel. He had been remotely logging into an old computer that had the older version of Excel on it, and he wanted to stop doing that and be able to get rid of that old machine that was also a security risk to the organization's network. We set a day and time to meet so he could show me more, then we had to move the call, then we had to move it again. To get things started asynchronously, Jamad sent me a screenshot of the error he was hitting.
An aside of appreciation and gratitude for the screenshotters and Loom-makers: THANK YOU for sending this visual and auditory information to help us understand what is going on with your technology. It can be challenging to imagine what is going on from the other side of the screen. But YOU go the extra mile to make an asynchronous conversation so much clearer and as close to being with you in person as we can get in the moment. When I get screenshots and Looms, my day feels a little brighter, I gain more confidence in my next steps, I can ask more helpful questions, and we are both saved from probably an extra 2 or 3 emails back and forth trying to understand each other. Thank you!
On the old machine, when Jamad would hit a button on the spreadsheet called "Refresh," a login screen would appear asking for a username and password. The login is to the organization's bank account, and the rest of the process should update information from the bank in the Excel spreadsheet. In the screenshot, I saw an error message in the VBA code of the Excel file.
Let me stop here and say a little bit about the knowledge, skills, and experience I came to this project with, which let me feel like I should try to figure out what was going on and how to get things back on track for Jamad.
I've been really into learning about APIs - the code that makes it possible for computers and cloud applications to connect with and talk to each other - the thing that would allow for an Excel file to "talk" to a bank website and ask for some data from the bank to be shared into the Excel spreadsheet.
I have had extensive experience using programs like Zapier and Google's API Connector to manage these API connections between systems, and I've dabbled with API-specific platforms like Postman.
I have worked with VBA code, aka Macros in Excel, for a lot of different projects and learned the basics of computer programming in VBA and other programming languages.
I have also used a lot of code written by other people and learned over the years that I can often make changes to someone else's code if I need to; even if I wouldn't have been able to write the code myself.
Jamad had forgotten to attach the Excel file to the email with the screenshot but sent it over as soon as I asked for it again. I downloaded it, opened it, and hit my first issue: a warning message saying that macros had been disabled because it was a file from an unknown source. I Googled "how to enable macros," then went into the file's Properties to check a checkbox.
I had to Google “how to enable macros” and go into the Properties of the file and check a checkbox. Did that, and then hit the "refresh" button, where I received the same error from Jamad's screenshot. I looked through the code in the Excel file, but it was really advanced for me - I needed help understanding what the code meant and what it was supposed to make Excel do. So my next step was to Google the error message.
Google the error message. That is something that I do so often that I am tempted to shorten the phrase into a catchy mnemonic. GTEM - pronounced "get 'em" - is one of the most powerful actions we can take to find solutions to our problems. And all thanks to the incredible number of people who have asked their questions on websites like Reddit and StackOverflow and countless other forums for tech conversations, and all of the people who have answered those questions and contributed to the conversation.
I don't always get it right. In this case, the first few sources I checked out led me down a path about different "libraries" that you can reference in the VBA code. I clicked a few links, read a few threads, and became more interested in this idea of the libraries…but I couldn't find how to add these "libraries" to an Excel file. I spent about 15 minutes (my "I-hit-a-wall" limit”) trying to figure out if this was the right path, but I pivoted after that point and started looking for other clues.
I got lucky by clicking around in the VBA code and saw that there was also a place where a line was highlighted in yellow. I don't have a screenshot of this, and I don't remember exactly how I got to find this highlighted line. It seemed worth looking into. I Googled a portion of the highlighted text - "MSXML2.DOMDocument" - and found a common thread across a few of the first sites I clicked on. Apparently "MSXML2.DOMDocument" was also used as "MSXML2.DOMDocument30" and sometimes even "MSXML2.DOMDocument60" and that this was some version control naming convention from Microsoft. It seemed worthwhile to give it a shot and change this in the VBA code, then try to hit that refresh button again.
This time, I got the same error message, but I noticed that it was referencing a different line in the code. It was still landing on a line with "MSXML2.DOMDocument" in it, but it was lower in the code. This told me that the code ran fine through the section where I had updated the term to "MSXML2.DOMDocument60" and hit the error again in the second place in the code where I had not changed "MSXML2.DOMDocument". I changed the second instance of that phrase and hit refresh again.
Again, I hit the same error message, but this time it was for a line that had a similar setup to the first, but instead had the phrase “MSXML2.XMLHTTP” in it. It seemed reasonable that I could find a way to update this phrase like I had in the other phrase, and so I Googled “MSXML2.XMLHTTP”. I found one forum thread that suggested it could be updated to “MSXML2.XMLHTTP.6.0” so I tried that out. Bad news - I hit the same error at the same place.
I took a chance on another idea - just to update this phrase the same way I had done the other - by adding “60” to the end of it: “MSXML2.XMLHTTP60”.
SUCCESS! The error didn’t happen where I had made the change - only in places in the code where the same phrase existed and I had not changed it yet. Once I made the change and hit the refresh button, a beautiful login screen appeared for me in the Excel file, and I felt good about sending this spreadsheet (saved in the most recent version of Excel) to Jamad.
I heard back from Jamad within an hour - NOT success on his side. He hit the same error and sent me another screenshot (THANK YOU, JAMAD). The screenshot showed that the location of the error in the code was for yet another phrase similar to the first two. What a roller coaster, because I still had the adrenaline of having "figured it out" and feeling like a rock star for having "solved" the problem in a place I didn’t understand and could easily break by messing around with. When I got the email from Jamad about it not working for him, it was a big letdown. And since I didn’t hit the same error, I couldn’t reproduce Jamad’s experience and would have no way of testing any solution.
Since I had success adjusting similar phrases in the code by adding the number 60 to the end, it was a fair bet that this would be the solution to the error Jamad was still getting. I made the adjustment and sent it back to Jamad, saying, "I can't test this out, but I think this will have solved your error message. Can you try it out, please and let me know what happens?" I gave it a 50% chance of working and a 50% chance of ruining the entire process with the same error message, a new error message, or some other weird reaction.
One more note here on the experience I've had that allowed me to take that risk of it not working: I have lived with so many errors and failures in our technology and meditated on this topic for a long time, and have found peace knowing that this is all really part of the journey. We can expect and even welcome error messages and failures as part of the process. I may have at times (ok, let's be honest, I still do this sometimes) wanted and tried to hide some of this process from the people who would be using the technology. But I've become much more comfortable with sharing those moments with others and asking them to view them as similarly productive.
Fortune favored me this time - Jamad shot back an email saying that now his file finally worked, and he saw the login screen he needed to use. It was a win for both of us - Jamad got his file working and can shut down the old, risky machine, and I got a little thrill out of figuring out a tiny little puzzle in a game that is way over my head.
Whatever, it worked, I’m happy.
My friend and business partner said to me recently that she doesn't want to read any more blog posts that don't have clear steps for helping people approach and/or solve a problem, do something new, or work something out. In one sense, this is such a unique case that I don't think a step-by-step will help anyone else out there. Not to mention that I honestly hardly know why making the changes in the code that I did make the thing actually work. It has something to do with those libraries and version control stuff from earlier. The code may be referencing something in a code library somewhere that had been updated to a slightly new name, so we needed to also update our code. Or maybe that isn’t it at all - I need help understanding it. It worked so I'm happy.
But I can call out the steps in the non-technical process I took to find a technical solution:
Step One: Think over the things that I know and the things that I don't know, and let that guide me in my next steps.
Step Two: (Embrace and) Google the error messages. Skim around the forums to see what I can find.
Step Three: Use my "I-Hit-A-Wall" Limit so I don't go crazy down a path that might not be right.
Step Four: Take a risk and try something out, even if I need help understanding everything I am doing.
Step Five: Roll with the punches when things don't work out as you expected or thought they would.
Step Six: Take an even bigger risk without being able to test the solution myself, but make sure to communicate this to others.
Step Seven: Celebrate! Talib Kweli said, "If I don't celebrate, I got nothing to fight for."
Jamad reached out thinking that we would need to have a contract to do this work, but it really only took me 1-2 hours from start to finish, and I learned some really interesting things along the way that I’m sure will pop up again for me in the future.
Don’t doubt your capabilities, even in the face of adversity. You are not alone - so many people have shared their experiences online that can be referenced and used to impact your experience and reach your solutions.
