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 https://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

• Anonymous
  April 05, 2004
  Shouldn't that be:
  
  Document.Range("$B$1").Value = GetIDFromEMailAlias(changedRange.Value)
  
  :-)
  
• Anonymous
  April 13, 2004
  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
• Anonymous
  April 13, 2004
  The comment has been removed
• Anonymous
  April 15, 2004
  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.