In a earlier blog I spoke the need for transitionality in development tools. One area of greatest need is in Microsoft Office; Outlook, Word, Excel, et. al.
Why Office? Recent versions of Office have provided almost full programmability, a nice object model, a macro recorder, and so on which helps power users automate processes and allows programmers to create applications using components of Office. However there are a woefully small number of people who actually program Office apps given the number of Office users. I believe that although Microsoft gave us great power by making Office programmable, they did not make programming Office accessible. They did not provide transitionality.
Case in point: I was just using Word 2003 and I needed to do a search & replace but search and replace didn’t work for what I needed so I decided to use a macro.
By the way, I was creating a mail merge document and I could not get Word to replace my placeholder text (i.e. “
I recorded the following macro that did a “find” on my placeholder and then inserted the appropriate merge field:
Sub Macro1()
Selection.Find.ClearFormatting
With Selection.Find
.Text = ""
.Forward = True
.Wrap = wdFindAsk
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
ActiveDocument.Fields.Add _
Range:=Selection.Range, _
Type:=wdFieldMergeField, _
Text:="""CompanyName"""
End Sub
I tied this macro to Alt-Z and if worked fine. Except I wanted to have it pop-up and ask me for the name of the merge field. And if it didn’t find the placeholder, I did not want it to insert the merge field.
Now I view myself as a pretty good programmer; not the best, but decent. I taught object-oriented programming before it was fashionable (over 10 years ago.) I studied programming as a sideline in college, I’ve programmed in over 10 languages, I wrote a 1000 page book on a programming language product called Clipper, and taught corporate and government developers how to program for seven years, and written a good portion of the VBxtras and Xtras.Net websites over the years, include both the front-end ASP and back-end SQL. So I’m not the typical occupational/hobbyist developer, but my role and position only allow me to program occassionally.
It took me 30 minutes reading help files to learn how to add my desired features to my macro. It shouldn’t have taken me that long. What about the power Word user who doesn’t have the same experience I have? They would have given up (I certainly should have because I didn’t get 1/2 hour of value out of my macro.) Of course a programmer who has worked with the Word object model might say “Duh?!? That is soooo easy! How could you not figure that out in 2 seconds?” But that is the point.
My point is the people most likely to make best use of programming Office are the ones for whom programming Office is generally over their heads: power users. And programmers generally only use Office as a component within other applications; they don’t know what really needs to be automated when using Office; power end users do. If power users were empowered to easily program Office, thousands of freeware, shareware, and commerical add-ons would be developed some of which would form the foundation of new 3rd party add-ons companies. For the most part this hasn’t happened, and all Office users miss the opportunity to use add-ons that were never developed. Microsoft went to tremendous expense to make Office programmable, and are getting a pitiful return on that investment.
What to do? Though I don’t have the answer, I do have some suggestions:
- When recording macros, include commented-out code that would show how to do something a user might obviously want to do if they edit the macro (i.e. after a “Find”, put a comment in showing how to test to see if the item was found.)
- Implement toolbar options using short macros and make it brain-dead easy for people to view the source code such by providing a “View Source” option on right click.
- If using short macros to implement most toolbar options is not viable, do customer research to find out the most common small tasks for which people could use macros and add special macro toolbars for those, with brain-dead easy access to view source.
- Do research to find out what the most common needs are when coding and create drill-down wizards that insert snippets of code for those common needs (i.e. “Need to ask user for some information and store for use in macro?” for which the wizard would insert compelete code for using InputBox)
- Do research to find out what higher level needs are and let power users run wizards to write complete macros for those higher level needs.
My hope is someone at Microsoft involved in programmability for Office will see these things and realize that if they could make programming Office more accessible, a lot more people would start programming Office. In future blog posts I’ll give new example of when I find difficulty doing something quick and dirty with Office macros as I run into the inspiration for them (I know I will.)
By the way, here is the updated version:
Sub Macro1()
Dim strFieldName As String
strFieldName = InputBox("Field Name:")
Do
Selection.Find.ClearFormatting
With Selection.Find
.Text = "<" & strFieldName & ">"
.Forward = True
.Wrap = wdFindAsk
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
If Selection.Find.Found Then
ActiveDocument.Fields.Add _
Range:=Selection.Range, _
Type:=wdFieldMergeField, _
Text:="""" & strFieldName & """"
End If
Loop While Selection.Find.Found
End Sub