[Solved][Calc] calling function from cell

Creating a macro - Writing a Script - Using the API

[Solved][Calc] calling function from cell

Postby kenshis » Wed Feb 06, 2008 10:28 pm

I just started learning Basic. I'm trying to write data to cell:
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
=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   Expand viewCollapse view
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.
kenshis
 
Posts: 5
Joined: Wed Feb 06, 2008 10:06 pm

Re: [Calc] calling function from cell

Postby Villeroy » Thu Feb 07, 2008 12:19 am

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   Expand viewCollapse view
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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26632
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] calling function from cell

Postby kenshis » Thu Feb 07, 2008 11:30 am

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?
kenshis
 
Posts: 5
Joined: Wed Feb 06, 2008 10:06 pm

Re: [Calc] calling function from cell

Postby Villeroy » Thu Feb 07, 2008 11:44 am

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26632
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] calling function from cell

Postby kenshis » Thu Feb 07, 2008 6:51 pm

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 ?
kenshis
 
Posts: 5
Joined: Wed Feb 06, 2008 10:06 pm

Re: [Calc] calling function from cell

Postby Villeroy » Thu Feb 07, 2008 7:15 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26632
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Calc] calling function from cell

Postby kenshis » Fri Feb 08, 2008 12:42 am

Thanks, you realy helped.
kenshis
 
Posts: 5
Joined: Wed Feb 06, 2008 10:06 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 8 guests