LinuxQuestions.org - Libre Calc and macro's / functions

- Linux - Software (https://www.linuxquestions.org/questions/linux-software-2/)

- - Libre Calc and macro's / functions (https://www.linuxquestions.org/questions/linux-software-2/libre-calc-and-macros-functions-4175581015/)

Libre Calc and macro's / functions

I've written a function is star basic to take some cell values and do a number of calculations on them. The output length varies. I had a hell of a game finding any documentation that showed how to do it with out using the more recent OO type extensions. There is masses of documentation on those but I wanted to retain the spirit of Basic and also hopefully retain compatibility with MicroSoft. There is such a lot of doc on the recent stuff several hours reading may not cover it that well as it does masses of things I don't want to do. To be honest I think devs have lost track of what Basic is meant to be. Very quick to pick up is one aspect. A for All-Purpose which apart from screen handling is very true. It's for crunching various things and vdu / printer output. Calc looks after the screen. Rant over.

The function could easily be changed to a macro. As it seems functions can only return one result I have got round that by turning them into a coma separated list. Either Calc or a word processor could tabulate this but it's messy. I have to copy and then use special paste to avoid copying the function as well.

This leaves me feeling that originally there must have been a method of writing to cells in what might be called basic basic and there probably still is but I can't find any info. Does anyone have any details? A noddy example would be ideal.

John
-

I've put together quite a few calc macros but it's hard to offer meaningful help without knowing more about what you're tring to achieve.
I'm sure one of us could point you in the right direction if you outlined what the function/macro is actually doing.

Explaining what my function does may not be easy. The first two paragraphs here explain the principle

https://en.wikipedia.org/wiki/Indexing_head

As it mentions one rotation of a handle turns work through some angle. What isn't clear is how the index plates work.They have rings of holes of various counts and a peg locates in them. That way fractional turns can be made to provide various divisions per complete rotation of the work.

The function takes in the hole counts in the index plate and works out all of the division that can be done over a range of them. Essentially the hole counts on the plates are based on prime numbers and those set what the plate can do. There are some spread sheets about and applications that will do this but as it's prime number based it isn't unusual for rounding errors to miss divisions. I've got round that by doing it in pure integer using mod and quotient. This way it also means I only have to check 3 trial values to be sure all value it gives will be ok. It also only generates divisions that can be done.

Some people do it by having a column of divisions just numerically ascending, 2,3,4 ..... and so on up to a few hundred and then use spread sheet functions to see if they can be done so some rows can't. The plate I am using to test the code with can provide divisions up to 3,660 and the gaps it can't do gets bigger bigger as the divisions go up so the usual approach people use isn't very convenient.

As it appears that a function can only return one value I have concatenated them into one long string. It can be pretty long. I haven't counted characters but the results generated into B23 run up to BU. Then comes the messy aspect I mentioned in paragraph 2 of my 1st post. Converting that into a table. Even being able to make the function put the result into a cell other than the one it's in would help with that.

While I can read up on an use things like sheet. etc etc I feel that there aught to be a more Basic like way of doing the same thing and that there probably is or was but can't find any documentation on it at all. It was difficult to find any on reading the hole counts on the index plate being done. I did find 2 examples eventually. In both cases the data is passed as a range of cells and either for Lbound to Ubound or a sort of for all in where used to get the actual data. Surely there is something similar for writing results back to the spread sheet. However the results are digits in groups of 4 and the number of them will vary. It should be possible if needed to set a results space that is larger than ever is likely to be needed.

Out of interest I couldn't find a concise programmers star basic syntax reference guide either. Or even a wordy one. It's a language that I haven't used for a very long time so that did cause some problems. I have never used any of the other languages offered and don't really have the time to pick them up.

John
-

Another way of putting the question is that in Visual Basic on Excel I could use either of these

Code:

Cell (1,1) = whatever I like and it will be handled

Range (A1) = what ever

However taking just one of the methods that seem to be available in starbasic I may stuck with using something like these

Code:

Dim Doc As Object

Dim Sheet As Object

Dim Cell As Object  

 

Doc = ThisComponent

Sheet = Doc.Sheets(0)

Cell = Sheet.getCellByPosition(0, 0)

Cell.String = "Test"



'Or



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"

That is more rigorous but even though I am a long term dedicated Linux users I would much prefer something as simple as the VB way.

:) I suppose that is because I feel that the VB way should be available in StarBasic for casual users which I am and they may well be in there but I can't find any info at all on them. Finding the above Star' ones put nice and tersely didn't happen that quickly. To be honest I don't think that these sort of things should be in Basic at all. Just simple ones like the VB's. They too have some . type notation but it looks to be less wordy than Star's and they have left what some might call the noddy or even BAD stuff in. Basic is not an OO language.

John
-

It seems too technical for me. Are you trying to work out how many holes you would need to turn to get the exact degree of indexing for your work?
I would have thought a formula would be more appropriate. Where you put your end result (the no. of turns needed?) in A1 and a formula in A2 that calculates based on the ratio of the holes in the particular indexing head (there's mention of a ratio of 40:1).
I hope I'm not being stupid here because I've got to say I don't understand the technicalities but there does appear to be some mathematical logic in there somewhere.

There is some logic in it Pete but the problem is that there is a need to iterate hole circle counts to find if any can do a particular division. I don't think it can be done in a way that some hole count on a index plate can do these divisions. It's more a case of can any of the hole counts available do a particular division. So division say from 10 to 200 might be checked against 6 different hole counts in a plate. It will do some and it wont do others.

Anyway I found some comments that putcell has been replaced with the sort of code I posted snippets of. Maybe putcell did write to cells the same as MS's cell (x,x) = what ever so I have written a small function as follows

Code:

'No noddy cell writing in star basic so did this to replace cell (n,n)=data



Function WrCell ( Row as integer, Colm as integer, Data as Variant )

Dim Doc as Object

Dim Sheet as Object

Dim Cell as Object



Doc = ThisComponent

Sheet = Doc.Sheets (1)

Cell = Sheet.getCellByPosition ( Colm, Row )



Cell.String = Data



End Function

Then I can stick my tongue out at the devs and say I can still do cell (x,x) = what ever. Having looked at the doc's on the various things that can be done this way I am not surprised that there are few people about who bother to write openoffice macro's casually. There is a huge amount of data to read and all they are likely to want to do is write to cells and maybe format them.

I should add that ideally I would have preferred to write to sheet 0, the one the basic data is on. It wont let me and only seems to be able to write to sheet 1, I can see some sense in that after a fashion but it seems to be enforced. Maybe there is a way round that. When I run the functions sheet 0 has to be open.

John
-

I'm not sure I understand. Your function appears to input data which you've given in the function into a cell address which you've also given. But I can't see how it would work unless it's part of a larger macro that is perhaps taking this info from other cells.

Is this permanent info that they store or do they just input their best guess, see if it works and then retry if it doesn't?

The function I wrote calculates what divisions an index plate can do. That works without any problems at all. It just calls WrCell to write the output from it back to the spread sheet.

The only problem now is that I would like the output to be on Sheet 0 not 1. If I set it up with docs.sheet(0) both run without any complaints but the output doesn't appear. The input data that the main function uses is on sheet 0. It makes more sense to have the output there as well.

John
-

I don't think I can help. It still seems to me that the function is being fed with info that is already on sheet 0 and then inputting it to the same cell address on sheet 1. If it needs to appear on sheet 0 in a different cell then the function needs to also be given a separate address on sheet 0 in which to place the result.
I may be totally misreading this as I don't have the full picture and probably wouldn't be able to cope with it if I did.

It turns out that the WrCell function doesn't need to return anything to the other function that calls it. It can just do what the code in it does and return nothing. I haven't tried that when the main one is activated from the spread sheet via the input data being changed but suspect it will be the same.

It might be worth adding more for any one else trying to do this sort of thing. The data is passed to the main function in a spread sheet array the usual way eg C1:C15. They have provided mechanisms for reading it what ever it's length is. The details were hard to find. The one I used is this

Code:

  for each CellValue in PlatesHoleRowRange          'Read each rings hole count and save in an array

    Counter = Counter+1                        'PreCount how many hole rings. Avoids 0 for Excel

    HoleCounts (Counter) = CellValue                'May not be needed but getting them is unusual

  next                                                ' so read into and array and not use direct

It's also possible to use for lbound to Ubound. In my case currently PlateHoleRange is passed as G11:L11 in the call from the spread sheet. It looks like there was no need to read the values into a local array. They could have been fetched and processed one at a time without that.

As to compatibility with Excell - looks to be a pipe dream so may as well have used array (0). MS don't. They start from 1.

John
-