OOF: Getting a better Grip on OLE Objects
Vector 17:1, page 121
Transcribed from the printed issue; not yet reviewed. Read it in the PDF of the issue, page 121
This paper was first published in FinnAPL News.
Introduction¶
During the last few years, I have made a few short experimental trips into Object Land, primarily from Dyalog APL, and each time I ended up feeling intensely frustrated. I seemed to have to go through endless steps in order to access functionality which, judging from code examples in magazines, seemed to be much more accessible to users of "inferior" tools like Visual Basic. All the code examples I found in magazines, on the web, or using the Excel Macro Recorder, required a real translation effort, and turned into somewhere between three and ten times as many lines of code as the original. This article describes the results of my attempts to improve this situation; a tool which is freely available to anyone who is interested (see under Acknowledgements below).
For several years, I successfully evaded the issue by assuming the guise of a manager (apologies to those of you who are not familiar with this term; try looking it up on the web). Finding myself unable to keep this up indefinitely, I have now accepted that it is inevitable that I must try to understand objects a bit better. Being an APL’er, the next step is obviously to build a tool which maps the world into my preferred co-ordinate system. I named the tool "OOF" because, in addition to being a TLA (Three Letter Acronym) for OLE Object Functions, "OOF" is "FOO" backwards, and symbolises the struggle of an ageing developer who wants to get rid of all those statements which simply manipulate objects, and get back to the function calls which he understands.
The OLE Server that I needed to use is one of the most common: Microsoft Excel. As an example, if the task is to retrieve the values in the range [A1;D3] from the first sheet in a particular book, the "raw" Dyalog APL code required to do this is:
'XL' ⎕WC 'OLEClient' 'Excel.Application'
'WBS' ⎕WC 'XL' ⎕WG 'WorkBooks'
'BOOK' ⎕WC 3 ⎕NQ 'WBS' 'Open' 'MyBook.xls'
'SHEETS' ⎕WC 'BOOK' ⎕WG 'WorkSheets'
'SHEET1' ⎕WC 'SHEETS' ⎕WG 'Item[1]'
'RANGE' ⎕WC 'SHEET1' ⎕WG 'Range[A1;D3]'
⍴'RANGE' ⎕WG 'Value'
3 4
With OOF, the corresponding code is:
#.OOF.Add '#.XL' 'Excel.Application'
#.OOF.Add '#.BOOK' '#.XL.Workbooks.Open' 'MyBook.xls'
⍴#.OOF.Prop '#.BOOK.Sheets[Sheet1].Range[A1;D3].Value'
3 4
Acknowledgements¶
Many thanks to Carlo Alberto Spinicci of APL Italiana, who paid for the work described in this article, and graciously agreed not only to allow me to publish this article, but also to make the tool available to anyone who is interested. If you would like a copy, write to oof@insight.dk. I can heartily recommend this approach: save printing costs and get an APL publication to print your internal documentation for you!
Thanks also to Peter Donnelly and John Daintree of Dyadic Systems Ltd. for patiently answering stupid questions about whether "Item" is a property or a method.
A Map of The Battle Field¶
Any OLE Server exposes an Object Model, which you must understand in order to use it. Microsoft Excel has a huge and complicated (sorry, "provides a rich") hierarchy of objects. The following picture illustrates my understanding of the very tiny fragment of the Excel Object model which we "navigated" in the introductory example:
At the top of the hierarchy is the Excel.Application, the object to which we connect when we create our OLE Client object. Each object has properties and methods. A property can be simple, like the Name property which contains the string "Microsoft Excel". Alternatively, the property can be another object, with its own properties and methods. Methods can have no result, simple results, or return objects. For example, the "Range" method of a worksheet returns an object which contains the cells in the range.
In the case of Excel, the operations that we would like to perform on the objects are things like:
- Open our favourite book containing spreadsheets:
WorkBooks.Open "MyBook" - Query or set the value of a range
WorkBooks[MyBook].WorkSheets[Sheet1].Range[A1;D3].Value - Execute methods, like
WorkBooks[MyBook].Save, or
WorkBooks[MyBook].PrintOut
Introduction to the Dyalog APL OLEClient¶
Before talking more about OOF, let’s take a look at the native mechanisms provided by Dyalog APL for manipulation of object models:
Connecting to an OLE Server¶
Or, seen from the APL side, creating an OLE Client:
This creates a namespace which represents the object in the APL workspace.
Accessing Properties and Methods¶
Dyalog APL provides no less than 3 mechnisms(!) for accessing properties and methods, once you have created a namespace representing an object:
-
By setting the
'AutoBrowse'property of a new object to1, you can get APL to scan libraries and immediately define all properties as APL variables and all methods as APL functions within the new namespace:'XL' ⎕WC 'OLEClient' 'Excel.Application' ('AutoBrowse' 1) XL.Name Microsoft Excel XL.InchesToPoints 1 72Unfortunately, this is too slow for runtime applications, our original example could easily take 30 seconds to execute using this technique.
-
2 ⎕NQ 'SetPropertyInfo'and'SetMethodInfo'allow you to control the creation of individual properties or methods:'XL' ⎕WC 'OLEClient' 'Excel.Application' 2 ⎕NQ 'XL' 'SetPropertyInfo' 'Name' 2 ⎕NQ 'XL' 'SetMethodInfo' 'InchesToPoints' XL.Name (XL.InchesToPoints 1) Microsoft Excel 72This runs quite fast, but adds one statement for each property or method you wish to use.
-
Finally,
⎕WGand3 ⎕NQgive direct access to properties and methods without ‘creating’ them first:
Building the Hierarchy in your Workspace¶
If a property or method returns an object, ⎕WC is used to create namespace for next level:
'XL' ⎕WC 'OLEClient' 'Excel.Application'
'WBS' ⎕WC 'XL' ⎕WG 'WorkBooks'
'BOOK' ⎕WC 3 ⎕NQ 'WBS' 'Open' 'MyBook.xls'
This allows you to reach your way through the hierarchy, creating a new namespace for each object you need to pass through. You can choose whether to create all these namespaces at the same level, as in the above example, or reflect the hierarchy by creating the objects inside their parent (this makes it easier to tidy up: simply delete the object at the top):
'XL' ⎕WC 'OLEClient' 'Excel.Application'
'XL.WBS' ⎕WC 'XL' ⎕WG 'WorkBooks'
'XL.WBS.BOOK' ⎕WC 3 ⎕NQ 'XL.WBS' 'Open' 'MyBook.xls'
Just be careful that you do not use names which conflict with any properties or methods at each level.
Collections¶
In object oriented terminology, a collection is an object which has a Count property and an Item property or method:
'#.XL' ⎕WC 'OLEClient' 'Excel.Application'
'#.WBS' ⎕WC '#.XL' ⎕WG 'WorkBooks'
'#.WBS' ⎕WG 'Count'
0
z←3 ⎕NQ '#.WBS' 'Open' 'MyBook.xls'
'#.WBS' ⎕WG 'Count'
1
'#.BOOK' ⎕WC '#.WBS' ⎕WG 'Item[1]'
Note that we used ⎕WG to read the Item as a property. In environments like Visual Basic, the distinction between a "parameterised property" and a method is blurred (at least with my eyesight). This can cause quite a bit of grief, since you are never sure which it is. OOF contains nasty trap statements to try first one then the other, to hide this – just how well this works in practice remains to be seen.
If I understood it all correctly, a collection will also have Item defined as its DEFAULT method, so that a Visual Basic expression like Workbooks[1] is equivalent to calling Workbooks.Item with an argument of 1. Many Item functions also support string arguments, allowing indexing by name.
Doing Something¶
We now know enough to understand what the example code in the introduction does. It creates 6 objects, corresponding to successively deeper levels of hierarchy (Excel-WorkBooks-Book1-Worksheets-Sheet1-Range). Finally, we can access the Value property of the range, which the Visual Basic developer might refer to as WorkBooks[MyBook].WorkSheets[Sheet1].Range[A1;D3].Value:
'XL' ⎕WC 'OLEClient' 'Excel.Application'
'WBS' ⎕WC 'XL' ⎕WG 'WorkBooks'
'BOOK' ⎕WC 3 ⎕NQ 'WBS' 'Open' 'MyBook.xls'
'SHEETS' ⎕WC 'BOOK' ⎕WG 'WorkSheets'
'SHEET1' ⎕WC 'SHEETS' ⎕WG 'Item[1]'
'RANGE' ⎕WC 'SHEET1' ⎕WG 'Range[A1;D3]'
⍴'RANGE'⎕WG'Value'
3 4
'RANGE' ⎕WS 'Value' (3 4⍴⍳12)
In my opinion, it is a bit hard to see the forest for the trees.
Introducing OOF¶
With OOF, APL expressions correspond much more closely to the expressions which a Visual Basic developer would be able to use. The above example could be written as:
#.OOF.Add '#.XL' 'Excel.Application'
#.OOF.Add '#.BOOK' '#.XL.Workbooks.Open' 'MyBook.xls'
⍴#.OOF.Prop '#.BOOK.Sheets[Sheet1].Range[A1;D3].Value'
3 4
(3 4⍴⍳12) #.OOF.Prop
'#.BOOK.Sheets[Sheet1].Range[A1;D3].Value'
The big difference is that you do not need to explicitly create all the intermediate objects required to reach into the hierarchy. OOF does this for you, exactly as Visual Basic does for its users. The function OOF.Objects shows the objects which OOF is managing for you:
#.OOF.Objects
#.XL #.XL
#.XL.Workbooks X0001
#.BOOK #.BOOK
#.BOOK.Sheets X0002
#.BOOK.Sheets[Sheet1] X0003
#.BOOK.Sheets[Sheet1].Range[A1;D3] X0004
In other words, OOF has created Dyalog APL namespaces X0001-X0004 because these intermediate namespaces were required in order to reach into the hierarchy as requested. The two objects #.XL and #.BOOK were named by us, using the function #OOF.Add.
Internally Visual Basic does the same thing; but it immediately disposes of the temporary objects. Apparently, many VB developers write quite ineffective code because they do not realise that they are causing an enormous amount of creation and garbage collection of objects, and that they would be better off explicitly assigning frequently-used objects to named variables. OOF keeps objects until you explicitly dispose of them using OOF.Close (you only have to close objects you created yourself, when you do, OOF will close any related objects it created on your behalf).
OOF consists of about half a dozen functions:
#.OOF.Add¶
Adds a new object in one of three ways:
-
Attach to new OLE Server (second element or argument does not start with #, and contains a single ".")
-
Create an object from property of existing object (second element does start with #)
-
Create an object from the result of Method (argument has more than 2 elements)
#.OOF.Prop¶
Queries or sets a property of an existing object, or an object which can be reached from an existing object by extending the hierarchy:
-
Monadic calls query a property:
-
Dyadic calls set a property. The previous setting is always returned:
Note that Prop also works with array properties like the Value of a Range.
#.OOF.Invoke and InvokeNil¶
Invoke a method in an existing object, or an object which can be reached from an existing object. If the method does not return a result, you must use the function InvokeNil. Examples:
-
Get Excel to multiply by 72 for you:
Close the workbook without saving it (set Saved property to avoid Excel asking the user whether to save):
If you use Invoke instead of InvokeNil, you will get a value error. Note that, if a method takes several arguments, the third element of the argument must be a list of all of the arguments. Do NOT use elements following the third element for additional arguments.
#.OOF.CollProp¶
Being APL developers, we must have a way to manipulate the properties of multiple items of a collection in a single call! OOF.CollProp is a user-defined operator which allows us to:
-
Read value of one or more properties (right operator argument) from one or more items (left operator argument):
-
Set the value(s) – returns old value(s):
-
Query or set property or properties for all items:
Note that the left operator argument can have any value accepted by the Item function of the collection, we could have used 'Sheet1' as an index in the second example. '*' is special-cased, using the function OOF.GetIndexSet (see below).
#.OOF.GetIndexSet¶
The "index set" of a collection is not always a dense set of integers from 1 to the value of the Count property of the collection. The function GetIndexSet returns the full index set of a collection:
Is the collection is indexed by an enumerated type, GetIndexSet returns a vector of enclosed two-element vectors, containing the names and values of the constants defining the set:
↑#.OOF.GetIndexSet '#.RANGE.Borders'
xlInsideHorizontal 12
xlInsideVertical 11
xlDiagonalDown 5
xlDiagonalUp 6
xlEdgeBottom 9
xlEdgeLeft 7
xlEdgeRight 10
xlEdgeTop 8
If you don’t want to check the depth of the result, use a left argument of 1 to state that you want numeric indices only:
#.OOF.Close¶
Close an object, plus all associated objects which OOF has created on your behalf.
#.OOF.Objects
#.XL #.XL
#.BOOK #.BOOK
#.SHEET #.SHEET
#.BOOK.Sheets X0001
#.BOOK.Sheets[1] X0002
#.OOF.Close '#.BOOK'
#.OOF.Objects
#.XL #.XL
#.SHEET #.SHEET
You can close associated objects at any time, if you know that you will not need them again. For example, we could have made the call:
If we knew that we did not want to work on sheets any more. This would have expunged X0001 and X0002, since X0002 was a child of X0001, but left #.BOOK intact. Future versions of OOF may incorporate some kind of automatic garbage-collection mechanism (suggestions welcome)!
#.OOF.Investigate (experimental)¶
This is an experimental function which, if applied to an object which has ‘AutoBrowse’ set to 1, "tastes" all the properties and attempts to let you know something about them.
#.OOF.AutoBrowse←1 ⍝ Applies to all OOF-created objects
#.OOF.Add '#.XL' 'Excel.Application'
#.OOF.Investigate '#.XL'
_Default Microsoft Excel 0 Normal
ActiveCell 2 Object
ActiveChart 0 0 Normal
ActiveDialog 0 0 Normal
ActiveMenuBar 2 Object
ActivePrinter \\SRVLAN\HP4050 PCL 6 on Ne00: 0 Normal
ActiveSheet 2 Object
... etc ...
Conclusion¶
Dyalog APL provides most of what you need to use OLE Servers from APL, but in my opinion, they could be significantly easier to use. The "OOF" tool allows APL developers to use expressions which are closer to that which they can find in documentation and other literature, which is usually aimed at Visual Basic or Visual C developers.
However, I can’t help feeling that I should not have had to build this tool – it should have been part of my APL system! It will soon be time to re-launch APL to a new generation of developers. The people with an "attitude" to APL (good or bad) are moving on, and I think we will soon have an opportunity to try to sell APL again. This will not be possible if the new audience feel that the mechanisms we provide for inter-application communication are significantly inferior to the environments they are used to. On the other hand – if we have it, there is no reason why we should not be able to go out and sell APL to people who need to compute something, all the other languages are still far behind APL in this respect.
If the vendors are not already working on it, this seems to be the next big challenge which we need to address as a community. I think I would go as far as to say that the long term survival of APL depends on support for objects at the "language" level.