Some good “bad” examples and a good “good” one



WRT the problem of the abstraction bar being set wrong in the Office object model, here are two good “bad” examples, and a good “good” example of where the abstraction bar could be moved to.


Currently, to create a table in Word you have to write this code:


Dim myApp As New Word.Application
myApp.Visible = True
Dim myDoc As Word.Document
Set myDoc = myApp.Documents.Add()
Dim myRange As Word.Range
Set myRange = myDoc.Range(Start:=0, End:=0)
myDoc.Tables.AddRange:=myRange, NumRows:=3, NumColumns:=4


Then you have to write a bunch of tedious code to populate the table by walking a dataset.  Ironically, the way we tell customers to create tables from data is not to create a table but to create an empty range and then get a dataset and convert it to a string and add some heading text with tabs embedded and, well you get the picture–far from the programmer’s conceptual model of creating a table.  For more see http://support.microsoft.com/default.aspx?scid=kb;EN-US;q261999


Excel isn’t much better when it comes to the abstraction level.  For example, imagine a programmer wants to write code that runs against an Excel Workbook containing an expense report that detects when a cell containing the employee’s e-mail address is edited.  When the e-mail address is edited, the programmer wants to automatically lookup the employee’s employee ID and put it into another cell in the worksheet.  The code a developer would write using the generic API of Excel might look like this.


Public Sub Workbook_OnChange(changedRange As Range)
  If (changedRange.Address = “$A$4”) Then
    Document.Range(“$B$1”).Value = GetIDFromEMailAlias(changedRange.Value) 
  End If
End Sub


What you’d really like to do is code in terms of the domain of the problem.  For example, the code above written in the language of the domain of the problem being solved rather than the domain of Excel object model trivia—would look like this:


Public Sub EmployeeEmail_OnChange()
  Employee.ID = GetIDFromEMailAlias(Employee.Email)
End Sub

Comments (4)

  1. Peter Torr says:

    Shouldn’t that be:

    Document.Range("$B$1").Value = GetIDFromEMailAlias(changedRange.Value)

    :-)

  2. Well, there actually IS a better way than you describe for Word, and it’s fairly close to what you wish for your developers

    "Then you have to write a bunch of tedious code to populate the table by walking a dataset. Ironically, the way we tell customers to create tables from data is not to create a table but to create an empty range and then get a dataset and convert it to a string and add some heading text with tabs embedded and"

    Put the data into a delimited string, pop it into a range, then convert it to a table. All that’s left is formatting the table… Roughly

    Dim rng as Word.Range

    Set rng = ActiveDocument.Range

    rng.Collapse wdCollapseStart

    rng.Text = strMyData

    rng.convertToTable

  3. Eh, correction to the last line of my previous post:

    dim tbl as Word.Table

    Set tbl = rng.ConvertToTable

    With tbl

    ‘Do the table stuff here

    End With

  4. Peter Jamieson says:

    In addition to Cindy Meister’s comment there’s also

    Selection.Range.InsertDatabase

    but of course the sort of thing that is lacking in the existing model is

    a. connectivity with XML data sources, disconnected recordsets etc.. In fact, connectivity using existing methods such as OLEDB is not all that good either

    b. the ability to build and/or specify a table template (other than the predefined table styles) that allows much more control over per-cell, per-column, per-page formatting

    c. genuinely new stuff such as the ability to define hierarchical layout structures (invoice headers/invice lines) and flow hierarchichal recordsets into them. That’s the sort of thing where XML-style data representations should really begin to help.