[Solved][Calc] calling function from cell

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
kenshis
Posts: 5
Joined: Wed Feb 06, 2008 10:06 pm

[Solved][Calc] calling function from cell

Post by kenshis »

I just started learning Basic. I'm trying to write data to cell:

Code: Select all

function WorkCell()
  Dim oDoc,oSheet,oCell		 As Object	
  oDoc = thisComponent
  oSheet = thisComponent.getSheets.getByName( "Sheet1" ) 
  oCell = oSheet.getCellByPosition( 0, 1 )
  oCell.String = "This is A1 cell"
End function 
i must call this function when, lets say cell A1 =1

Code: Select all

=IF(A1=1;WorkCell();"too small") 

but, nothing happens,
when i run WorkCell() from macros menu, it works.
What i'm missing?

can i call ,(dont know how to name it)

Code: Select all

sub tst
something()
end sub
from cell, like a function?
Thanks in advance.
Last edited by kenshis on Fri Feb 08, 2008 12:46 am, edited 1 time in total.
User avatar
Villeroy
Volunteer
Posts: 31265
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] calling function from cell

Post by Villeroy »

Userdefined functions are supposed to return a value to the calling formula. Your function returns nothing. Instead it tries to write something to cell B1 on a sheet named "Sheet1", which may not even exist.

Code: Select all

Function Shout_Bingo()
   Shout_Bingo = "Bingo!"
End Function
This function returns a string "Bingo!" to be used by the calling formula. One of the weird idiosyncrasies in Basic is the fact that a function returns the value that has been assigned to an equally named symbol.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
kenshis
Posts: 5
Joined: Wed Feb 06, 2008 10:06 pm

Re: [Calc] calling function from cell

Post by kenshis »

So is there any way, to write some value (automaticly), lets say, when cell A1=1, to another cell B1?
the idea is to create new sheet, and then copy data there.
i can create new sheet, whit function call from cell, but how to write new data to cells?
User avatar
Villeroy
Volunteer
Posts: 31265
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] calling function from cell

Post by Villeroy »

Cell functions can not work like this. Write a macro which "knows" about the relevant source range and target range. The most easy way is to create a new sheet and copy the data manually unless you do this all the time which however would indicate that you are using the wrong tool.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
kenshis
Posts: 5
Joined: Wed Feb 06, 2008 10:06 pm

Re: [Calc] calling function from cell

Post by kenshis »

The idea of my work: in each row there is some data. when row is completly filled, i must create new sheet, and copy data from row to it. Rows can be filled in any order.

Yes, the easest way is obvious :), ...
but i do nead make many sheets (~200), and copy data to them.
Can i call my macro automatically? or the only way is to make macro buton, and then press it each time when i must create new sheet, and copy data to it.
Else, what tool should i use ?
User avatar
Villeroy
Volunteer
Posts: 31265
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] calling function from cell

Post by Villeroy »

I guess that you want to print single records (orders or sales or something). You should use the Base component with the report builder. It can create pretty print reports from spreadsheet data.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
kenshis
Posts: 5
Joined: Wed Feb 06, 2008 10:06 pm

Re: [Calc] calling function from cell

Post by kenshis »

Thanks, you realy helped.
Post Reply