BBC BASIC Programmers' Reference

by Malcolm Marten, May 2008

The Component Object Model, or COM for short (not to be confused with COMmunication ports!), is a Microsoft protocol allowing programs to cooperate in sharing data.

The key word in COM is Object which is the crux of the issue: Object Oriented Programming. Today most applications are programmed using objects. These are reusable bits of code with a well-defined input and output for communicating with the world around them. We use an object by getting a pointer to a copy of this code made available for our exclusive use. We can then use the methods and properties of the object in our program.

OK, so let’s explain this object model in some physical terms so that we get a good understanding of what’s what. An object is a “thing”. For example a car is a “thing”, an iPod is a “thing”. You can do stuff with “things”. You will have no trouble understanding that these are objects, because you can see, feel or touch them.

An object might also contain other objects. Your Car object has objects called wheels, and hopefully an object called an engine. In the case of the car there is a whole collection of objects called “Wheels” and if we want to talk about a particular wheel we would say Wheel(1), Wheel(2) or Wheel(3) so that we know which one of the collection we are describing. I know, one of the wheels is missing. Perhaps you have a collection of bricks instead.

In the same way, software that uses COM consists of Objects that you can do things with. The top of the list is the all-encompassing Application Object. This Application Object may have a collection of other objects such as Documents or Workbooks. In fact, the word “Collection” is an important one and collections of objects are found very frequently; when they exist you will need to further specify which member of the collection you are talking about. Confusion between an object and a collection is easy, and the names that software publishers give to the objects are not always helpful. Look for plural names like Rows or Pages; these probably relate to collections of Objects. If you don’t specify just one then you get the lot, however many there are. You can actually find out because collections always have a Count property that you can look up.

We have the actions that we can take with objects, which we call methods, and ways that we can change the way objects look or behave called properties. The distinction between these two gets pretty blurred in the middle and is somewhat up the software writer’s discretion.

The Car object has a Wheels collection. Wheels(1) has methods: it can do things. It can drive forwards, it can brake and it can drive in reverse. It also has properties such as being black, being alloy, being low profile, etc.

An instruction to a particular Car object of “Range_Rover.wheels.drive_forward” describes telling my particular instance of “Car” class, which is a “Range_Rover” to put the whole of the “wheels” collection in to “drive_forward” mode.

The difficulty comes with things like doors. The “Doors” is a collection of objects but is “Opening” a door a method or is “Opened” a door property? You simply have to look at the documentation to find out. It could be either, depending on how the software writer thinks.

Recapping, objects are things, methods are ways of doing stuff with the things and properties are the attributes of the thing that you can change. Now let’s get to grips with how to use COM objects in BBC BASIC for Windows.

Most likely you will want to manipulate some other piece of software so you want its Application Object. Before you can do that you need to know what name the application has registered itself as. The office applications by Microsoft go under names like Excel, Word, Outlook etc. However, not all applications are that easy to figure out and you may have to search the web and literature.

When we use an object all we need is to know where the object is in memory, that is, an address or a pointer to it. So we first fire up the COMLIB library by installing it and initializing it. Then we create the Parent Object:

        INSTALL @lib$+"COMLIB"
        PROC_cominit
        Xlapp% = FN_createobject("Excel.Application")

Here Xlapp% will be the pointer to the object you are going to use exclusively. You have created an “Instance” of the Excel.Application object.

However, you can’t yet see anything, although it does exist, because its visible property is turned off! We can remedy that by adjusting the property by writing the value we want to it:

        PROC_putvalue(Xlapp%, "Visible(BTRUE)")

Now when you get that Excel program on the screen you are probably still disappointed because it is empty, just a shell. We need to add some pages before it is of any use. A look at the Excel object model shows that the Application object has a collection of Workbooks that contain a collection of Worksheets, that contain a collection of Rows and Columns and lots of other things. Now you can’t just start adding Rows, you have to start building things up.

First we create a Workbook, for which we can use the “Add” method, and give it a name. When we use this method only the application knows where the workbook resides in memory:

        PROC_callmethod(Xlapp%, "Workbooks.Add(-4167)")
        PROC_putvalue(Xlapp%,"Workbooks(1).WorkSheets(1).Name(""My First Sheet"")")

Here the (-4167) parameter to the Add method is defining a ‘type’ of workbook. In this case it gives you a workbook with a single worksheet object already in it. You could also have your workbook populated with charts, forms or macrosheets, or if you leave off this modifier you get the default behavior, which, out of the box, is Excel’s normal 3-worksheet workbook.

Alternatively we can get the address of a new Workbook object by using FN_getvalueint (or FN_getobject) rather than PROC_callmethod . The value returned is an integer address where the program finds the details of this particular instance of the workbook. Once we have that it can be the starting point or root for more objects, methods or properties:

        Xlbook% = FN_getobject(Xlapp%, "Workbooks.Add(-4167)")
        PROC_putvalue(Xlbook%,"WorkSheets(1).Name(""My First Sheet"")")

This does exactly the same thing as before. Which style you choose to use depends on how often you call up an object, whether you want to often switch between objects or like to be explicit.

If we have an existing workbook file that we wish to load instead of generating a new blank workbook as we have just done then we can use:

      Xlbook%=FN_getobject(XLapp%,"Workbooks.Open(""Path\MyWorkbook.xls"")")

If you know the exact structure of the pre-existing workbook then you can use sheet names, but it is probably safer to use indexed references rather than named references, which will at least return something.

COMLIB V3.3 addendum.
It has been discovered that there are some applications that “put” Objects. To cope with this a new Procedure has been added which is of similar form to 'putting' of values i.e.:

     PROC_putobject(tts%, "Voice(O vobj%)")

See Outputting speech to a file for an example of this construction.

Now we have a real Excel application on our Windows desktop. Time for some change to a property: let’s make the cells show numbers with commas separating thousands. We can use:

        PROC_putvalue(Xlapp%,"Workbooks(1).WorkSheets(1).Cells.Style(""Comma"")")

or

        PROC_putvalue(Xlbook%,"WorkSheets(1).Cells.Style(""Comma"")")

We can refer to the Workbook Object Xlbook% or the Application Object Xlapp% as the root, but still have to specify exactly which sheet in the collection of Worksheets the property should be applied to (you know there is only one sheet but Excel wants it spelled out, all the same, or it won’t play). Since we have already named the first sheet we could even refer to it by its name: WorkSheets(““My First Sheet””) rather than WorkSheets(1). Indeed, this may be better because if I add a sheet then what was Worksheets(1) becomes Worksheets(2) and the new sheet becomes Worksheets(1). Using the name stops any confusion.

Now for some more methods. The most common method in Excel is selecting a range of Cells to work with. Select is the method and the Range is an object that it returns:

        PROC_callmethod(Xlbook%,"Activesheet.Range(""D16:E20"").Select")
        PROC_putvalue(Xlapp%,"Selection.Font.Bold(BTRUE)")

A quick talk about Cells in Excel. They don’t appear as objects, as you might expect; they are a property. I am sure there must be a good reason for it, but it certainly is confusing. Cells is a property that returns a range object. So cells(2,4) is generating a range object, range(““D4””). The feature of cells that makes it so useful is the way of addressing as Cells(row%,col%). This is much more convenient than trying to cobble up the equivalent string range$ that you can use with range(“”“+range$+”“”).

There are few shortcuts with the object hierarchy here as well. Once we have opened a workbook, there will be an “Active” worksheet, and an “Active” Cell on start up. If that is the sheet or cell that we want then we can refer to it by the shorter name, Activesheet or Activecell. While Range is an object, we don’t keep a handle or pointer to it because after the Select method it becomes the “Selection” object of which there can only be one at a time, so Excel remembers what it is and how to get to it, we don’t have to.

We can now change the properties of all the cells in the range (the collection of cells) in one go. However, there is one snag and that is that the Select method is global, so the Selection object does not become a sub-object of Xlbook% but of the parent Xlapp% instead, just to confuse you:

        PROC_putvalue(Xlapp%, "Selection.value(""Hello"")")

Here we are putting a value (which is a property) into every cell in the selection. The data is a literal “Hello” but we have to add a couple of “’s to make sure we can understand that, and that we are not at the end of the command.

The data that we put or send out with PROC_putvalue can be text, short integer, long integer, floating point or unsigned long integer. We tell the program what we want by a prefix letter. Text has the inverted commas so that is clear enough, but we put a U for an unsigned integer, B for Boolean and F for Floating Point or Reals. A BB4W standard 4 byte integer is the default and needs no prefix. These would all be valid:

        PROC_putvalue(Xlapp%, "Selection.value(""data"")")
        PROC_putvalue(Xlapp%, "Selection.value("""+data$+""")")
        PROC_putvalue(Xlapp%, "Selection.value(U 2567)")
        PROC_putvalue(Xlapp%, "Selection.value(12345656)")
        PROC_putvalue(Xlapp%, "Selection.value(B FALSE)")
        PROC_putvalue(Xlapp%, "Selection.value(F 64.257)")
        PROC_putvalue(Xlapp%, "Selection.value(-4163)")
        PROC_putvalue(Xlapp%, "Selection.value(data%)") REM See Caution on direct use of variables*

It would seem that the majority of constants used to describe property values (and the index number, which defines which item in a collection that you want to address) are integers and so fit within 4 bytes and require no prefix. If you get an error, consider whether the number is of the right type and try the other possibilities, as documentation on the topic is likely to be virtually non-existent. With Excel you can write numbers as a strings and it will translate all the common formats correctly.

We can write data, so now let’s see about reading data. The same issue arises as to what data type is being read and you may have to experiment or do some error handling to find out for you. Some cases are obvious, but many will remain unknown until you do a bit searching or experimenting:

        A$ = FN_getvaluestr(Xlapp%,"Selection.text")

Clearly this expects a string value to be returned or an empty string. The alternatives are
FN_getvalueint and FN_getvaluefloat. Since BB4W is fairly forgiving we can map all the integers types into a BB4W integer, and the 8 byte floating point format used in Excel is common to BB4W’s FLOAT 64 mode (but can be mapped to a FLOAT40 variable if that is the current mode).

In Excel there are a couple of other data types that are not supported in BB4W such as date and currency, which have internationalized variations. The safest way to deal with these is to convert them to text with a code sequence something like this:

        PROC_callmethod(Xlbook%,"WorkSheets(""My First Sheet"").Cells(22,9).Select")
        B$=FN_getvaluestr(Xlapp%,"Selection.Numberformat")
        PROC_putvalue(Xlapp%,"Selection.Numberformat(""@"")")
        A$=FN_getvaluestr(Xlapp%,"Selection.text")
        PROC_putvalue(Xlapp%,"Selection.Numberformat("""+B$+""")")

We select a range, get the format that already exists, which we preserve and store in B$. We change the property of the range to text and then read the text value into A$. If it is important, we can reinstate the original formatting using the style stored in B$. This method should always work and give a reasonable answer, that you then have to convert from a string to the appropriate numerical type.

There is no reason why the data from one application should not be copied and pasted into yet another. Application objects will, more likely than not, support Copy, Cut and Paste Methods. The data from a copy is held in multiple formats in the clipboard and the accepting application selects which type suits it best, or ignores it!

I’ll leave it to the reader to figure out how to open the applications and make the selection. Then use Selection.Copy in one application, decide where to paste it in the other and do an equivalent Selection.Paste, or Selection.PasteSpecial(param1, param2, etc.).

Finally, once you have finished using an Application you can close it using the Quit method, which again, is universal. However, you can leave the applications open so that you can work on them manually. What you MUST do is to let Windows know when you no longer need the instance of the objects that you have been using. There will be areas of memory assigned to these objects, and we must “destroy” the objects to release this memory back to Windows:

        DEF PROCcleanup
        Xlapp% += 0  : IF Xlapp%  PROC_releaseobject(Xlapp%)  : Xlapp% = 0
        Xlbook% += 0 : IF Xlbook% PROC_releaseobject(Xlbook%) : Xlbook% = 0
        wdapp% += 0  : IF wdapp%  PROC_releaseobject(wdapp%)  : wdapp% = 0
        wddoc% += 0  : IF wddoc%  PROC_releaseobject(wddoc%)  : wddoc% = 0
        PROC_comexit
        ENDPROC

Releasing the objects frees the memory and then we need to exit COMLIB gracefully by calling PROC_comexit. A procedure like PROCcleanup should, ideally, be called from an ON ERROR and ON CLOSE event so that the COM processes are released before the program finally shuts no matter what the cause.

So how does this all work at a technical level? Read about that here on MSDN. It’s not an easy read.

If we use a construction such as these code examples below, then when we compile the program with the default “abbreviate names” option set , errors will be generated.

        PROC_putvalue(XlNewSheet%, "cells(row%,col%).value("""+data$+""")")
        PROC_putvalue(Xlapp%, "Selection.value(data%)")
        PROC_putobject(tts%, "Voice(O vobj%)")

What is the problem? The issue is that the variables in the parameters are not exposed to the “crunching” engine because they are within a string, but the variables when defined are exposed and will be crunched. The variable name in the parameter string will no longer be the same as the crunched version. It is the same issue that occurs with DATA and EVAL statements and so the same method can be employed to avoid it. That solution is to use the Complier Directive REM!Keep available from version 5.50a. This stops only those variables mentioned in the directive from being crunched.

        REM!Keep row%,col%
        PROC_putvalue(XlNewSheet%, "cells(row%,col%).value("""+data$+""")")
        REM!Keep data%
        PROC_putvalue(Xlapp%, "Selection.value(data%)")
        REM!Keep speed%
        PROC_putvalue(tts%, "Rate(U speed%)")
        REM!Keep vobj%
        PROC_putobject(tts%, "Voice(O vobj%)")

A similar problem can arise if a static integer variable (A% to Z%) is passed in a parameter string. In this case the issue is that the specified variable may be used internally to the COMLIB library (for example as a LOCAL or PRIVATE variable, or as a formal parameter) in which case it will be the value of that internal variable that is used rather than the value intended. Both problems may be eliminated by replacing the variable references in the supplied parameter string with their numeric values. For example:

        PROC_putvalue(XlNewSheet%, "cells("+STR$row%+","+STR$col%+").value("""+data$+""")")
        PROC_putvalue(Xlapp%, "Selection.value("+STR$data%+")")
        PROC_putvalue(tts%, "Rate(U"+STR$speed%+")")
        PROC_putobject(tts%, "Voice(O&"+STR$~vobj%+")")

In this last example an added complication arises in that the variable is an Object which is a 32 bit address and forcing it to hexadecimal ensures that the STR$ function will not corrupt it.

The choice of solution is yours but one of them must be employed or the COMLIB function will return an error because it is being presented with a variable that has not been defined. The defined name having been crunched and most likely shortened.

How do you know what methods and properties you need to tweak for the program to do what you want?

There are two methods. First, all the Microsoft office applications use VBA (Visual Basic for Applications) as a macro language. They also have means of editing VBA with an Editor; this is accessed by Alt+F11. In the editor there is an Object Browser; it has an Icon of geometric shapes spewing out of a box! In the top drop down box select the application whose information you want to retrieve, such as “Word”. In the Classes window find “Application” and in the Members of Application window to the right you will find the list of Methods (looks like a moving brick) and Properties (a hand holding a card). There are also constants, which are useful, and lightning strokes, which are events that you can’t use from BB4W. If you then click on the question mark icon you will open up Microsoft Visual Basic Help and it will show the hierarchy of the Objects and methods in the COM. If you did not load this help file when you installed the program get the CDs out now because you are going to need it. Then there are code examples there as well for you to study.

The second method is perhaps easier but does not give you much understanding of what is going on. That is to use Tools, Macro, Record new Macro. Then do what ever it is you are trying to achieve, stop the macro recorder and go back to Edit the macro. This brings up the VBA editor, which will now have code on view that describes what you did. It may not be very understandable but it should work. All you have to do is translate that to COMLIB format. This still takes some time to learn. Here is an example:

Compare VBA:

        Set wrd = GetObject(, "Word.Application")
        wrd.Visible = True
        wrd.Documents.Open "C:\My Documents\Temp.doc"
        Set wrd = Nothing

with BB4W / COMLIB:

        wrd% = FN_createobject("Word.Application")
        PROC_putvalue(wrd%, "Visible(BTRUE)")
        PROC_callmethod (wrd%, "Documents.Open(""C:\My Documents\temp.doc"")")
        PROC_releaseobject(wrd%)

One issue is that you will still need to learn about how to actually record meaningful macros and their limitations. Such as, when you record a macro in Word 2000, you can use the mouse to click commands and options, but the macro recorder doesn't record mouse movements in a document window. Selection has to be done via the keyboard. That’s certainly going to slow you down quite a bit.

Excel is much more friendly and ranges and cells can be selected by the mouse. Excel has lots of examples on the web of automation and is much easier to automate in my opinion. However, if I were more familiar with Word macros I would probably have a change of heart. What is certain is that COMLIB and BB4W can give you very nearly the same degree of control over applications as Visual Basic itself, and with all the advantages of small size and easily distributable executables that we love in BB4W.