Number system conversion
Self portrait
Here's a more ambitious program to convert a number from any base system to decimal. Calc has one but it only converts integers and there's a size limit. This one will also convert fractions. So, here's the whole program:

Function Basen2Dec (arr1 as string, arr2 as integer)
Dim ConvArray(5,len(arr1)), I as integer, J as Integer
Dim Hl as Integer, Frac as Integer, Dec as double

'REM First, fill he first column of ConvArray with the digits of the base-n number...
For I = 1 to len(arr1)
    ConvArray(1,I)= Mid(arr1,I,1)
next I
'...then find the decimal point.
For I = 1 to len(arr1)
    If ConvArray(1,I)="." then
        Exit For
    end if
Next I
'HL is the number of rows (digits) for the whole part off arr1.
'Frac is the number or rows (digits) for the fractional part of arr1.
'Now, find the values of the digits.

for I=1 to len(arr1)
    If IsNumeric(ConvArray(1,I))=True then
    Elseif ConvArray(1,I)="." then
    End If
next I
'Fill column 3 with the place values.

for I=1 to Hl

Next I
For I=Hl+2 to Hl+1+Frac
    ConvArray(3,I)= -J
Next I
'Fill column 4 with exponential place values
For I=1 to len(arr1)

next I
'Fill column 5 with place vale x digit
For I=1 to Len(arr1)
    'If ConvArray(2,1)<>" " then
    'end if
next I

'Calculate output as sum of 5th column entries.
Dec = 0
For I = 1 to Len(arr1)
next I
Basen2Dec= Dec
End Function

I did document this one so, if you're into  math, you will understand how it works. Unlike the last one, this one is a function. For the sub routine, there were things it expected from the spreadsheet document: there had to be a sheet called "DANSYS Functions" because the sub would be looking for it. This one works exactly like a built-in spreadsheet function except it won't show up in the Calc Function Wizard and you won't get function tips as you type it in.

A note on porting OpenOffice macros to Excel. The logic of the programming languages work pretty much the same but the object languages are very, very different, so you can't use Calc macros directly onto Excel; you have to  make some changes. If you know both languages, it's not too difficult. It may be tedious but after you run the Calc macro on Excel several times and Excel shows you all the "errors", you should be able to make it work. The real work comes with porting a macro that handles a lot of spreadsheet objects. I'm currently working on a graphing utility that uses drawing objects to build graphs - it handles sheets, cells, ranges, labels, drawing objects, documents - just about everything - and I would hate having to port that thing over to Excel!

Okay - interesting points about the Basen2Dec function:

Function Basen2Dec (arr1 as string, arr2 as integer)

You saw how to begin a subroutine the last time. This is how to start writing a function. Type "FUNCTION" and follow it with the parameters (argument) in parentheses. These are the variables you will pass to the function. If the variables must be a particular type (arr1 has to be a string because numbers in bases other than base 10 have to be represented as text strings, and arr2 is the base (radix) of the input number, so it has to be an integer.) you can designate them "as" the type. Functions must have the parentheses, even if you don't need to pass a variable. I have a few macros that return a specific value (Like Enum, which returns the value e) and the first line looks like "FUNCTION Enum()".

You can  name your subs, functions, and parameters anything you want as long as you don't use a reserved keyword. For instance, you can't name a function "SQR" because that's the name OpenOffice Basic uses for it's square root function. It's best to use names that fit the code you're writing. For instance, Basen2Dec is a function that takes a number in the Base n number system and converts it 2 a decimal number.

If you're use to programming in any language, the If....Then....Else...End If and For...Next loops are familiar. They're the same Basic language that's been around since I was a pup.  One thing here - Else If doesn't work. This Basic expects Elseif. On the other hand, you can end a If block with either Endif or End if.

Basen2Dec= Dec

this is how you get the information out of the function and onto the spreadsheet. You type the name of the function, and follow it by an equals sign and the variable that you want to ship out to the spreadsheet. That variable can be a number, string, or array. You can even place drawing objects and functions on a spreadsheet but you have to use a sub and it's much more complicated but I've done a good bit of that so we'll get to it eventually.

On the spreadsheet, the function looks like "=Basen2Dec("012110";3)" (without the outer quotes), which will convert the base 3 number 012110 into a decimal number. If you're not used to Calc, notice that, unlike Excel which uses a comma, Calc separates it's parameters with a semicolon. In the function statement in the code, you have to use the comma to separate the parameters.

Anyway, you can copy and paste all my functions into your OpenOffice Basic editor and they should work. If not, drop me a line and I'll try to figure out what went wrong.

A short program
Self portrait
I'll start with a short routine I wrote to call up a spreadsheet page.

One of the big advantages Microsoft Office has over OpenOffice is that the object language in Microsoft Visual Basic is much more fully integrated into the programming language. In OpenOffice, the Uno system which handles the office is practically a different programming environment with a myriad of services that must be called up to do fairly simple tasks. Unfortunately, Microsoft Offfice doesn't support past programming language. I learned that the hard way in my practice when I programmed a test scoring and reporting utility for Microsoft Excel only to have them discontinue that version of Visual Basic. I couldn't upgrade my computer because I would lose my main program and all that effort.

There's a lot of European influence in OpenOffice so the Basic language maintains the Base 0 convention, meaning that counting, matrix positions and such all start at zero. I'm used to Base 1 so I change that in all my modules.

Here's the first few lines in the BASIC module:

REM  *****  BASIC  *****
Option Base 1
Sub FunctionList
Dim aSheet as object
    aSheet=thisComponent.getSheets.getByName("DANSYS functions")
Controller = thisComponent.getcurrentController

End Sub

Now, line by line:

REM  *****  BASIC  *****

When I set up the Basic module using the Macro Organizer, it automatically placed a header in the module. Now, when I go into the Tools>Macros>Organize macros dialog, the Basic module is listed in the Standard folder.

I'm pretty sloppy at documenting programs but, since I'm not explaining here how my routines work but what the statements mean and how to use them, I'll be annotating each line. But you can (and I would certainly recommend that you be a more responsible documenter than I am) place comments anywhere you want in a program. In OpenOffice REM will do it but, more common, a apostrophe (') beginning the line will do the trick.

It's pretty obvious that actual users have had a lot to do with designing OpenOffice. The most commonly used symbols are ones that do not require the shift key.

Something that's sorta hard to find in the help files is how to continue or string out statements in a line. That's useful information, so, here it is.

If a line of code is longer than you want, you can continueit to the next line by ending the first line with a space and an underscore      ( _). If you have several very short statements that you want to place on the same line, you can separate them by colons. Therefore:

x=5:y=1:If x>y then z=0

is the same as:

if x>y then z=0.

Option Base 1

This is the magical statement that changes the base (starting point for counting, matrix positions, etc.) from 0 to 1. If you use it, it goes at the very first of the module before the first program.

The first program in this module is called FunctionList.

Sub FunctionList

The first line in a program introduces it by name and indicates whether it is a sub (subroutine) or a function. A function can be called from a spreadsheet just like a built-in spreadsheet program but, otherwise, it can't change the spreadsheet document. I wanted something tat would move me to a sheet I had that listed all the functions I had written for my statistics spreadsheet (which I call DANSYS). A sub name may or may not be followed by stuff in parentheses (that's how you pass values to a subroutine or a function), but a function name is always followed by parentheses, even though there may not be anything in the parentheses. We'll get into that later. This sub only needs a name since I'm not going to send it any information. When it's called, it's just going to send me to another sheet.

Dim aSheet as object

It's a good idea to declare each variable you use by type. You don't always have to but not declaring can lead to unexpected consequences. In this case, I'm dealing with a spreadsheet object - a sheet - and objects have to be declared. aSheet is an object; it's the sheet that I want to be redirected to when I call this subroutine. Sheets, cells, cell ranges, drawing objects, documents, services - they're all objects, and there are many other objects beside.

    aSheet=thisComponent.getSheets.getByName("DANSYS functions")

I want to specify which sheet I will be redirected to. In this case, it will be a sheet named "DANSYS functions" (that's a sheet in my spreadsheet document and I gave it the name when I added it into the document as a new sheet). Objects are assigned values in OpenOffice just like mathematical variables. I specified that the sheet was part of the current document using the thisComponent object. Things that follow and object separated by periods are methods and properties. thisComponet has a method called getSheets which returns a specific sheet or collection of sheets. I wanted to call up a sheet by name so I used the getByName method to call the sheet named Dansys functions. Notice that the name is placed in quotes. You almost always have to place text strings in quotes when using OpenOffice Basic.

Controller = thisComponent.getcurrentController

the current controller is one of those services I mentioned above. This statement accesses the controller for the current document and it is going to switch me over to the sheet I want. getCurrentController is another method available to thisComponent.

To tell the controller to move my, I use:


This tells the controller to make aSheet the active sheet so that I'm suddenly looking at "DANSYS functions".

All programs end with either "End sub" or "End function". This is a subroutine so I use:

End Sub

This is an older function so I had not yet begun capitalizing code.

I keep all my code in a document of a program called KeyNote, which is like an electronic notebook (you can find it on SourceForge) in case something wipes DANSYS. Windows and complex OpenOffice documents don't seem to play well together and my OpenOffice often crashes when I try to save. If I do lose the program, I can use the copies of the code in KeyNote to rebuild it. I also save chunks of code that I use often there so I don't have to remember complex Uno statements. It saves me a lot of trouble and headaches.

Long walk 2015
Self portrait
( You are about to view content that may only be appropriate for adults. )

Spreadsheet as other
Self portrait
I heard often on the OpenOffice help forum that a spreadsheet is a business application and you shouldn't  be doing other things with it. If I have a spreadsheet, I feel downright betrayed if it doesn't do exactly what I want it to do. My spreadsheet is my layout engine. I'm so used to using the spreadsheet that I'm much more comfortable with it than a desktop publisher program. I know just how mant rows and columns of cells are in a printable page and I can merge cells into blocks and move them around as I wish.

I also use a spreadsheet document for my reference hub. I call it my utilities hub and I access my reference library, often used web links, and common software through it. Pages are different topics and there are several pages up front for specific tasks such as composing notes, and developing OpenOffice Basic code. The hyperlinks I use to open weblinks and documents from my reference library are straight forward and well covered in the OpenOffice help files. The program launcher is another thing. It's the only programming I've done for the Utilities Hub, but it's actually simple.

Let's say you want a launcher for the Windows calculator on a spreadsheet. Open the programming environment by going to the Tools menu and clicking Tools>Macros>Organize Macros>OpenOffice Basic. If you want to put it in it's own folder, you may want to use the Organizer... to do that but once you've provided a home for the sub routine, you can move it in.

It only requires three lines (and this is one of many places where OpenOffice seems to me to work better than Office). The first line begins the subroutine and it looks like:
    SUB name()

OpenOffice Basic in most places is case insensitive and you can capiytalize or not. I've found it much easier to distinguish between, say, i, 1, and l if I just capitalize everything. "name" is the name you want to give the subroutine. I would call it something unimaginative like "calculator".  "Calc" would confuse it since that's the OpenOffice spreadsheet's name. Notice that the sub doesn't take an argument in it's parentheses since nothing needs to be passed to it.

The second line is a shell function:
    SHELL("program path")
"program path" is how you get to the program staring from the drive letter all the way to the executable file. The easy way to get this is to just type SHELL(" and then open the properties (right click a shortcut to the program and Properties is at the bottom of the menu that opens up) and then copy the program path from the dialog box that appears to the code you've  been typing. When I do that on my computer, the finished second line looks like:
note: The program path is usually highlighted when you open the properties dialog and all you have to do is press Ctrl+C and go to your code and press Ctrl+V.

The last line is simple. It's:
      END SUB

Use the draw menu in Calc to place a small rectangle where you want it, open the contex menu by right clicking on the rectangle, and select Assign Macro. Find the shell sub in the list of macros that opens up, select it, and you have a link to the Windows Calculator on your spreadsheet.

Here's one of the subroutines from my utilities hub:

Sub LibreCAD
Shell("C:\Program Files (x86)\LibreCAD\LibreCAD.exe")
End Sub

It is the link to my LibreCAD program. Simple, huh?

Self portrait
This will be rather short but an introduction of sorts. I have just had my eyes slashed (ala Un Chein Andalou), that is, I've just had cataracts removed from both eyes. And before Salvador Dali's movie images become too fixed in your brains, I should say that the process was prettymuch painless and Madison Street Surgery Center, Omni Eye Care, and 3D Vision in the Denver area have done an excellent job. Short form - my eyes do not take too well to prolonged work.....

So, I have found new inspiration for posting. One of my hobbies is statistics and statistical programming. I am programming a statistical spreadsheet document for my own use in OpenOffice Calc. It will be available with my statisics commentary on The Therian Timeline once I have finished the basic statistics procedures, but I would like to comment on the actual programming here along with comments about other open source programs, literature, and educational materials.

Like most programming documentation, programming help for OpenOffice Basic is given more on the basis of, you already know and just need a reminder. For new knowledge, you might geet help, and you might not. I was on the help forums for awhile but I busted some chops about people on the forum saying, "you shouldn't use the Calc spreadsheet for programming - it's a spreadsheet. Use the spreadsheet functions." They have a perfectly good programming language. Why disuade people from using it?! Pretty quickly, I could no longer post on the forum and my posts about how to do certain things in the programming language disappeared (by the way, I didn't call anyone a doody-head or anything like that - I just criticized the nay-sayers.)

But there are huge gaps in the help that's available for OpenOffice Basic. For instance, if you want to rotate a drawing object in Calc, there is supposedly a way but it is very occult and what little there is on the Internet about it is opaque and steel. Can you use paramarray in OpenOffice Calc. There are articles on the Internet that you can but I defy anyone to make it work and the official statement is, "No, you cannot."


Honetsly, OpenOffice is aa great product and they deserve better than that, so I want to help. I will be posting chunks of my code on this forum (especially after I get my eyes working again) with explanations. Hopefully that will hep folks with some of the more difficult, but useful corners of the language. Also, my email is available in case someone wants to approach me directly with some programming problem.

Well, my left eye is screaming "STOP!!!" now so I will end this post.

More reads
Self portrait
I've finished Polyglot, by Kato Loeb. It is a fascinating and informative (and well written) autobiography of an interpreter and one of the first simultaneous interpreters. As she unfolds her life, she also tells her theories of foreign language acquisition. Her ideas are different enough that she has inspired me to, again, try to learn another language - this time, Spanish. So now I've pulled out my Schaum's Spanish books and 100 Years of Solitude (in Spanish) and away I go.

I am also reading Henry Adams' History of the United States and am finding it a very good read - so after Lord Action's difficult treatise, it's nice to know that there are historians who have been able to write with the intension of being read.

I've just begun The Man Wolf and Other Tales by Emile Erckmann and Alexandre Chatrian and immediately I see where Ann Rice got her Nideck Point.

Continuing my reviews for my tutoring job, I'm reading CK12's high school texbook for Chemistry and continue to be impressed by the readability and completeness of CK12's products.

Annual Longwalk
Self portrait
Hm. Got over it in three days. Even the deep blisters on my feet erupted and stopped bothering me. But I guess that's the point - like a physical without the doctor. If I survive it, I must be in pretty good shape, hm?

I started out from home before 7:00 am and started walking up toward Coyote Ridge. Broomfield is a pretty place even in town. I stopped at the Community Park Lake at 6:44 as the sun was coming over the mountains in teh west. It was a good start, a chilly morning - enough to make my hands hurt. The geese are coming through. I'm noticing ducks in the ponds. My last bloodwork indicated that I'm having problems retaining potassium and I haven't adapted to the altitude yet, so I knew I had to pace myself.and drink a lot of fluids with electrolytes. There's a string of parks all the way to Highway 287 which make for a pleasant urban trail.

8:39 - I followed 287 to the King Sooper Foods and got some supplies - trail mix and a sports drink, then I caught the Lake Link Trail across the street. It follows a ridge around the broad valley that shelters Flatirons Crossing and Boulder. People are out jogging and walking their dogs. There was a couple of hot air balloons in the distance. There's a nice view of the Rockies from there. I ate a Reese's Cup and drank my sports drink, and headed across 287 for Flatirons Crossing.

It took about an hour and a half to get down to the mege-shopping center that is Flatirons Crossing. At the end of the trail, I met a long switchback around a railroad before I could pass under the highway and enter town. Irritating  but I was treated to a long train heading into the mountains.

My intension was to find the in-town end of the Coalton Trail, which the map said was behind the shopping center, but I never found it. I ate at a man-made waterfall and walked around the sprawling parking lots and finally gave up and headed to the trailhead just outside of town.

I reached the trailhead at 12:25 and it was quite hot by then (here, a week later, it's 38 degrees. Colorado doesn't mess around with changes). I took a long rest. I hadn't even needed to pee the whole day up to that time and I think my kidneys shut down twice over the length of the hike. After more trail mix and water, I started up the trail.

Up. The start of the trail was rather flat but the little mini-mesa was well in sight at the trail head and I'd been there before, so I knew what was coming. Soon the trail headed up and it kept going up for some time. There's a cosmic law that Wolf's hikes have to be vertical.

At the top, the trail leveled out again and I stopped acouple of times to keep my trail mix down and to try to avoid passing out.

Very nice view of the Flatirons - the edge of the Rockies - from close up.Far below, highway 94 traces the curve of the hill. At 2:04 I reached the end of Coalton Trail. There was a windmill research station and some little pines that looked like Christmas trees lining the highway before it disappeared between the shoulders of two mountains.. I stopped for awhile and then started back. It had been a long time since I had walked that far. My usual midsummer death hike had been on the order of 10 or 15 miles. I was already around 16 miles and I still had to walk home. That's part of the ordeal - don't give yourself options. If you make it back, you have to walk back.

At 3:27 I was back at the start of the Coalton Trail and, after a long rest (15 minutes or so), I start home. My intension was to head a straight as I could home. There's a strange geography to the roads around here. I should have known that you can't trust any road called "Interlocken". I've driven that way from Flatirons Crossing back to Broomfield twice with no problem. In the past, Interlocken has made a beeline back to Broomfield. This time, it made a beeline to the Rocky Mountain International Airport. How rude.

So I end up in Arvada, asking a nice strolling family how I might get across highway 36 back to Broomfield. Turns out, it wasn't that big a deal. In about 20 minutes I found myself on Main Street very near home and I arrived, very tired but alive at 7:03.

Schaum's Outlines
Self portrait
I've finished the Microeconomics Outlines by Dominick Salvatore (4th Edition) Honestly, I've never been disappointed by Schaum's. The books are breif but they're readable and they have plenty of interesting examples and worked exercises. They are updated enough so that they're not obsolete by the time you pick them up, and they're relevant. THere's a lot in here about what the givernment is doing right and what they're doing wrong to help me understand what the heck's going on behind the scenes.

I'm now reading a cool book by Kato Lomb called Polyglot: How I learn Languages. It is provocative. I may have to try again to pick up another language. Her method is different enough, it may actually work for me.

CK12 Calculus
Self portrait
Okay, not your usual reading fare but I'm trying to keep up with my tutoring job and they're teaching kids things now that they didn't even know when I was in high school, believe you me. But I've had oodles of calculus in college and some of my continuing educatioon excursions have carried me back into that territory, so I'm a calculus tutor. So to stay on top of it, I'm reading hiigh school text books.

CK12 ( has never disappointed my. They are an organization which provides free teacher and student resources on the web. The free textbooks are keyed to standards and they're written by teachers who care about their students and their subjects so they're well illustrated, well informed, and well written. I enjoyed this calculus textbook. Even the heavy sections on series were as well presented as any other textbook I've ever seen on the subject. The only problem I have with the CK12 materials is that there are generally no answers to the exercises (in the back of the book) so you can check your work. Some of the texts do have teacher's guides. They have a teacher's guide and a Texas Instrument student's suppliment along with lots of other support material in calculus. If you can't learn calculus here,.....well, get a tutor, there's still hope, but hang onto the text materials for reference.

Now, on to microeconomics - I'm reading Schaum's Outline, so it ought to be good.

A long walk through history and nature
Self portrait
I walked several hundred million years Labor Day. It was very strange - I began at home, had a nice stroll through several city parks and found myself wandering through the Solar System, Then I was back on Earth but found myself walking backward through time to the dawn of life (thank a bunch of eighth graders for that). Then things settled back down and I had a leisurely stroll back home.

Okay, okay. Honestly, there were no drugs involved. The Denver area including Broomfield and Boulder is repleat with great walking and hiking trails. As a pedestrian, I find myself, in my retirement, in the pedestrians paradise. Labor Day afternoon, I filled my water bottle and started out, down to the Library and the 9-11 memorial and the other statues there around the lake in Community Park up to Coyote Ridge, then to a little park near Miramoonte where they have built a scale model of the Solar System. Although I couldn't find Pluto (it was probably back at teh library (remember - "scale model"), Neptune was waaaaaaay over there.

Not too far from there was Broomfield Heights Middle School's  "Walk Through Time", a well reviewed scale model of geologic time built by te students. The walk (with rock samples and a human sun dial (the walker is part of it)) is scaled to the part of earth's history occupied by living things. The period that has seen humanity stretches about the width of an adult human thumb. It represents both geologic history and local human history to the present.

This is my kind of education.


Log in