How to Write a Basic OO Calc Macro
I
am not a whiz or object oriented programmer, but I just went through
the process of trying to get a macro up and running in open office calc
and my experiences may help you. Check out the wiki for more
detail:
http://en.wikipedia.org/wiki/Object-oriented_programming
Basically,
object oriented programming has its own vocabulary, a few terms you
need to know. An object has characteristics and is a way of
accessing and modifying data. There is a hierarchical naming
convention that is “dot” notation. The
basic item is the class, followed by object. A Calc document has
sheets, sheets have cells, and cells have characteristics. A cell
is accessed and modified by methods. Every language has it's
nomenclature of access, and since I know and used basic to write my
macros, that's what I'm going to refer to. And maybe an example will
clarify this babble.
In basic format code:
Dim Doc As Object ' Name the class (document) – top level
Dim Sheet As Object ' Name the sheet
Dim Cell As Object ' pointer to a specific cell
Doc = ThisComponent ' ThisComponent is a reserved word and this loads the address in the variable doc
Sheet = Doc.Sheets(0) ' Sheets is a reserved word
' This loads sheet with the address of a specifc sheet in doc.
Basic
addressability has now been established and the basic routines can now
be written. It seems that the above code needs to be supplied in
the routine that uses it and did not, for me, work in the global area.
Here is code that works for me:
Some
comments: Variable definitions above the first sub are global to
all subroutines. This can be bad code, so use it
cautiously. If option explicit is not specified, dynamic
assignment of variables is allowed. This is NOT a good idea and
can lead to code confusion that can be almost impossible to
debug.
'Zero Expense and adjust Start Date
dim permo as object
dim target as object
dim today as object
dim tgtday
dim row as integer
dim col as integer
Sub Main
Dim Doc As Object:Dim Sheet As Object:Dim Cell As Object
Doc = ThisComponent:Sheet = Doc.Sheets(0)
for row=1 to 26
if Sheet.getCellByPosition(7,row).value<>0 then ' Expense
modify
endif
next
End Sub
Sub Modify
Dim Doc As Object:Dim Sheet As Object:Dim Cell As Object
Doc = ThisComponent:Sheet = Doc.Sheets(0)
PerMo=Sheet.getCellByPosition(2,row) 'col D
Target=Sheet.getCellByPosition(6,row) ' col G (formula)
today=Sheet.getCellByPosition(8,1) ' I2 (calc date)
perday=permo.value/30
Days=Target.value/PerDay
TgtDay=Today.value-Days
' cell=Sheet.getCellByPosition(4,row) 'col E
' cell.value=TgtDay '(date format)
Sheet.getCellByPosition(4,row).value=TgtDay ' date format
Sheet.getCellByPosition(7,row).string="" ' Clear Expense
End Sub
~~~~~~~~~~~~~~~~~~~~~
Thats the code. Here are some useful examples.
Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object
Doc = ThisComponent
Sheet = Doc.Sheets(0)
Cell = Sheet.getCellByPosition(0, 0)
Cell.Value = 100
Cell = Sheet.getCellByPosition(0, 1)
Cell.String = "Test"
Cell = Sheet.getCellByPosition(0, 2)
Cell.Formula = "=A1"
MsgBox Cell.Value