Macros Will Save You Time Automate Even The Most Menial Tasks by Les Smith

Have you learned the trick of creating a macro in a couple of minutes to do some repetitive task, even if you only use the macro once?  This article will hopefully cause you to remember the power of macros so that you will get in the habit of thinking of them as a way to consistently save time in making code modifications.

This week, I had the following code already written.  

With RequestData.edInfo1       xtw.WriteStartElement("EDUCATION_INFO")       If .Institution.Length > 0 Then          xtw.WriteAttributeString("INSTITUTION", .Institution)       End If       xtw.WriteAttributeString("INSTUTITION_TYPE", .Institution_Type)       xtw.WriteAttributeString("CITY", .City)       xtw.WriteAttributeString("STATE", .State)       xtw.WriteAttributeString("COUNTRY", .Country)       xtw.WriteAttributeString("PHONE_NBR", .Phone_Nbr)       xtw.WriteAttributeString("FROM_DATE", .From_Date)       xtw.WriteAttributeString("TO_DATE", .To_Date)       xtw.WriteAttributeString("GRADUATED", .Graduated)       xtw.WriteAttributeString("DEGREE", .Degree)       xtw.WriteAttributeString("MAJOR", .Major)       xtw.WriteAttributeString("MINOR", .Minor)       xtw.WriteAttributeString("NAME_ATTENDED_UNDER", .Name_Attended_Under)       xtw.WriteAttributeString("REQUESTER_REFERENCE", .Requester_Reference)       xtw.WriteEndElement()    End With

What it does is use a StreamWritter object to create an XML Element with a number of attributes.  Having written the code and several other sequences just like it, I found that I did not want to create an attribute when the data object property contained an empty string.  So, I started to surround each line that writes an attribute with an If/End If to skip the code if the property value is an empty string.

I hadn't wrapped but a couple of them when the thought hit me...I have a bunch of these to do and I know there is a way to automate this.  So I quickly opened the Macro IDE and coded the following macro.

Public Sub AQuikWrapIF()          Dim ts As TextSelection = DTE.ActiveDocument.Selection          Dim s As String = ts.Text          ts.StartOfLine()          ts.LineDown(True)          Dim s2 As String = "   If " & s & ".Length > 0 Then" & vbCrLf          s2 &= ts.Text          s2 &= "   End If" & vbCrLf          ts.Delete()          ts.StartOfLine()          ts.Insert(s2)       End Sub

I have dozens of macros that resemble the one shown above, so I copied one and changed the five lines in the middle that I have bolded.   The main body of automation code, in the macro, is involved with retrieving selected code from the active code window, manipulating it in some way and then putting it back in the code window.  That's what the first two lines do.  In using the macro, I double-click on the object property in the code window ("Institution" is the first one) in order to select it.  Next, I double-click the macro name in the Macro Explorer Tool Window.  In this case, the macro picks up the selection and saves it in the variable named "s".  I then move to the start of the selected line, move the TextSelection Object down with Extend = True, which highlights the whole line, including the end-of-line characters.  Next, I construct the "If" statement with the selected object property.  This is what makes the macro pick up and create the new "If" regardless of the property selected.  I then wrap the selected line with the constructed If/End If.  Finally I delete the currently selected line of code and replace it with the wrapped line of code.

Now with an alternating set of double-clicks, I can transform the code into the code shown below, along with several other sequences of the same type code.  And, I will be guaranteed that I do not make any spelling mistakes as I most certainly would if I hand wrapped each line manually.

   With RequestData.edInfo1
      xtw.WriteStartElement("EDUCATION_INFO")
      If .Institution.Length > 0 Then
         xtw.WriteAttributeString("INSTITUTION", .Institution)
      End If
      If .Institution_Type.Length > 0 Then
         xtw.WriteAttributeString("INSTUTITION_TYPE", .Institution_Type)
      End If
      If .City.Length > 0 Then
         xtw.WriteAttributeString("CITY", .City)
      End If
      If .State.Length > 0 Then
         xtw.WriteAttributeString("STATE", .State)
      End If
      If .Country.Length > 0 Then
         xtw.WriteAttributeString("COUNTRY", .Country)
      End If
      If .Phone_Nbr.Length > 0 Then
         xtw.WriteAttributeString("PHONE_NBR", .Phone_Nbr)
      End If
      If .From_Date.Length > 0 Then
         xtw.WriteAttributeString("FROM_DATE", .From_Date)
      End If
      If .To_Date.Length > 0 Then
         xtw.WriteAttributeString("TO_DATE", .To_Date)
      End If
      If .Graduated.Length > 0 Then
         xtw.WriteAttributeString("GRADUATED", .Graduated)
      End If
      If .Degree.Length > 0 Then
         xtw.WriteAttributeString("DEGREE", .Degree)
      End If
      If .Major.Length > 0 Then
         xtw.WriteAttributeString("MAJOR", .Major)
      End If
      If .Minor.Length > 0 Then
         xtw.WriteAttributeString("MINOR", .Minor)
      End If
      If .Name_Attended_Under.Length > 0 Then
         xtw.WriteAttributeString("NAME_ATTENDED_UNDER", .Name_Attended_Under)
      End If
      If .Requester_Reference.Length > 0 Then
         xtw.WriteAttributeString("REQUESTER_REFERENCE", .Requester_Reference)
      End If
      xtw.WriteEndElement()

Please, before you write me and tell me that I should have created a method to call to test each property and let that method determine whether to add the attribute to the element or not; I thought of that, but I had already written the macro and was in a hurry to get the project modified.  Besides, if I had done that I would not have had a chance to write another article on automation (after work) and automation/extensibility fascinates me and saves me tons of time.  And time is money, so I'm into saving time.

Ask a Question, or give your feedback on my articles or products by going to the KnowDotNet Forum or by clicking on My Blog.