Home / Excel / To solve a problem – ask the right question

To solve a problem – ask the right question

Macros - asking the right questionEarlier this past week, I was trying to write up a macro program to delete a specific picture that had been copied over from a download. With the intent of keeping the process simple for the users of this file,  part of the process involved copying the entire download, including a picture of a graph. This is fine except that over a period of time, I feared that the file would contain multiple pictures of this graph, thus loading down the file.

I can’t ask my users to delete the picture themselves because some of them may not very familiar with Excel and already, it will be a burden for them to do this report.

So I embedded in the file a program to delete the prior week’s download when the users want to do a new report (as opposed to looking at the prior report). I was able to create a macro to delete the data but I got stumped on deleting the pictures. There were two: one I wanted to delete and the other I wanted to keep.

I went to the Excel help through the Developer screen, which took me out to the Internet, and typed in my questions, or rather key words, into the search box and started researching from there. I hit upon Shape and the various ways of adding and deleting them. I found out that there are different kinds of shapes such as charts, pictures, texts, embedded objects, etc. It took quite a while to figure out how to use the Shapes command (?) because the little snippet of codes I had copied over did not work due to leaving out some requisite modifier or qualifier or object or something.

After two hours (?), I finally succeeded except…the macro deleted both pictures, not just the one I wanted deleted. I sort of suspected that would happen so I was not surprised.

So next, I researched on how to write a macro to add back in the other picture. The research took many more hours and my intuition told me that the answers I was finding were not leading me in the right direction.

By the way, I had started off my research by using the macro recorder and performing the specific actions that I wanted the macro to perform. This method is usually a great way to start writing macros and gives you a clue on how to write the code. But not this time. The macro recorder was not giving me anything usable.

After a while I was starting to doubt that I would figure this out. It felt like I was close to finishing (until the next round of user testing tells me more changes to make) but this picture was the showstopper. Finally, I realized I was asking the wrong question: instead of a question about picture, I should think “camera tool” because that was how the second picture was created. And finally, I got my answer.

They oftentimes say you have to ask the right question or frame the right question before you can generate solutions. The thing is, how do you go about asking the right question?

Leave a Reply

Your email address will not be published. Required fields are marked *