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.

Hl=0

For I = 1 to len(arr1)

If ConvArray(1,I)="." then

Exit For

end if

Hl=Hl+1

Next I

Frac=Len(arr1)-HL-1

'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

ConvArray(2,I)=Val(ConvArray(1,I))

Elseif ConvArray(1,I)="." then

ConvArray(2,I)=0

Else

ConvArray(2,I)=AlphaValue(ConvArray(1,I)

End If

next I

'Fill column 3 with the place values.

for I=1 to Hl

ConvArray(3,I)=Hl-I

Next I

J=1

For I=Hl+2 to Hl+1+Frac

ConvArray(3,I)= -J

J=J+1

Next I

'Fill column 4 with exponential place values

For I=1 to len(arr1)

ConvArray(4,I)=arr2^ConvArray(3,I)

next I

'Fill column 5 with place vale x digit

For I=1 to Len(arr1)

'If ConvArray(2,1)<>" " then

ConvArray(5,I)=ConvArray(2,I)*ConvA

'end if

next I

'Calculate output as sum of 5th column entries.

Dec = 0

For I = 1 to Len(arr1)

Dec=Dec+ConvArray(5,I)

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.