Scrambling data with OpenOffice calc macro, hand on approach

This blog provides an hand-on approach and a simple tutorial for writing OpenOffice Calc macros. There are quite a few documents around about this and I try not to overlap with those. Basically you can find here a guide to jumpstart the first learning curve in OpenOffice Macros and a few comments/evaluation about it

If you want further information you can look at the following docs, note that I am working with OpenOffice 2.1
C'è anche la versione in Italiano di Openoffice

Porting Excel/VBA to Calc/StarBasic, pdf
OpenOffice.org 2.0 Documentation Web Site
Macro Documentation Web Site

Where are the macros ?

The first thing one needs to know is where the macros are stored. It is a bit deep for my taste but here it is an image that shows you where the macro handling stuff is. NOTE: that I will be talking about Star Basic Macros !

How to reach the Macros in OpenOffice 2.1

When you select the above menu you come to this window that basically shows all macros that are "available" and also allows you to create, edit and delete macros. Of course you can create Macros only in places where there are no macros, this is the reason why you see no "new" button here, but if you select a place where there are no macros the "delete" button will be replaced by the "new" button.

Another interesting thing to notice is that there are three sections in the following window

Macro personali, personal Macros: Are Macros that belog to the user and are always available, no matter what document is loaded.
Openoffice.org Macro: Are Macros that are distributed with OpenOffice Org
TEST_correzioni: Are macros that are loaded with the given document and change whan you change document

If you want macros bounded to a document you put them into the last of the three, othervise you put them in the first one.

What I am interested is the Macro Editor Window that allows you to edit and modify the macros. The most interesting button is the "RUN" button that allows you to run the first macro that is in the editor.

What follows is the small macro that I used to scramble a set of correct and wrong answers for a test. If you have little knowledge of programming you see immediatly what the program does. There is plenty of documentation on syntax, methods, just google for the keyword you do not understand and you will find the answer (or look for it in the documentation that I mentioned at the beginning).


Rem a comment is either the word Rem followed by the comment
' or a 'followed by a comment

' The following is quite important to avoid using uninitialized variables by mistake
Option Explicit

' this is the macro definition, this macro is the first one and will be run when you press the run
Sub CommandButton1_Click()

Dim Document As Object
Document = ThisComponent

Dim AllSheets As Object
AllSheets = Document.getSheets()

' this is used to have access to CALC embedded functions
Dim svc As Object
svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )

'Dim IniBase As Object
'Dim IniTest As Object
'Dim IniEsito As Object
'Set IniBase = AllSheets.getByName("Base").getCellRangeByName("IniBase")
'Set IniTest = AllSheets.getByNam("Test").getCellRangeByName("IniTest")
'Set IniEsito = AllSheets.getByNam("Esito").getCellRangeByName("IniEsito")


Dim nomeSource As String
nomeSource ="Base"

Dim sourceSheet as Object
sourceSheet = AllSheets.getByName(nomeSource)

Dim destSheet As Object
destSheet = AllSheets.getByName("Test")

'ThisComponent.CurrentController.setActiveSheet(testSheet)
' ranges = testSheet.getRanges()

Dim srcCell as Object
Dim destCell as Object

'cell numbering starts at 0, meaning tat A1 is 0,0
'	srcCell = sourceSheet.getCellRangeByPosition(2,rowIndex)

' ok, inserisco la colonna corretta nel prima colonna (0)
' poi la domanda nella seconda (1)
' risposta (2), (3), (4)

' This command, if used hides the whole row
'	destCell.Rows.isVisible = false


Dim rowIndex as long

Dim arg As Object
arg = array( )

Dim randPos as double
Dim distPos As integer

For rowIndex = 3 to 50

	' I need to know where to put the correct answer
	destCell = destSheet.getCellByPosition(0,rowIndex)
	randPos = svc.callFunction( "RAND", arg )
	randPos = randPos * 30

'	msgBox("rando1 "+randPos)

    ' I decide in which column to put the answer based on a random number
	if randPos < 13 then
		 randPos = 3
	else
		if randPos < 23 then
			randPos = 4
		else
			randPos = 5
		end if
	end if

'	msgBox("rando "+randPos)
	destCell.setValue(randPos)

	' Question comes always from second column and goes always to second column
	srcCell = sourceSheet.getCellByPosition(2,rowIndex)
	destCell = destSheet.getCellByPosition(2,rowIndex)
	destCell.setString(srcCell.getString())

	' Now I need to get the result and put into the randPos
	srcCell = sourceSheet.getCellByPosition(3,rowIndex)
	destCell = destSheet.getCellByPosition(randPos,rowIndex)
	destCell.setString(srcCell.getString())

	' Now the fun part is with the distractors...
	Dim tmpPos as integer

	srcCell = sourceSheet.getCellByPosition(4,rowIndex)
	' the pos of the distractor is the third one unless there is the answer there...
	tmpPos = nextDistractor(randPos,3)

	msgBox("tmpPos(a)="+tmpPos)

	destCell = destSheet.getCellByPosition(tmpPos,rowIndex)
	destCell.setString(srcCell.getString())

	'' second distractor
	srcCell = sourceSheet.getCellByPosition(5,rowIndex)
	' the pos of the distractor is one more of the previous one unless there is the answer there
	tmpPos = nextDistractor(randPos,tmpPos+1)

	msgBox("tmpPos(b)="+tmpPos)

	destCell = destSheet.getCellByPosition(tmpPos,rowIndex)
	destCell.setString(srcCell.getString())

Next


srcCell = destSheet.getCellRangeByName("B2")
ThisComponent.CurrentController.select(srcCell)

'msgBox ( "prova"+svc.callFunction( "RAND", arg ) )

' Look for Xray and OpenOffice to find out what it does, basically it is a powerful
' tool to understand properties and methods of objects.
'Xray destCell

End Sub

Function nextDistractor ( resPos, curPos ) As Integer

	Dim risul as integer

	if curPos <> resPos then
		risul = curPos
	else
		risul = curPos + 1
	end if

	nextDistractor() = risul
	end Function

OpenOffice Macros are very powerful and remembar, platform independent (you can run them on Linux) and also are multi language ,meaning that you can chose your preferred programming language (Pyton, Beanshell, javascript, starBasic) and this is very good.

With power and freedom comes a bit of complexity, especially for a beginner (then things get simpler). Microsoft products have a start learning curve low and then, once you are hooked it becomes "difficult" and of course you are bound to the Microsoft platform (and also a specific version since not all MS Office are equal)

So, if you are just a bit quick learner you will have absolutely no problem with OpenOffice macros, this will land you on a free and powerful platform.

Last update 07/02/2007