[Solved] How can I get a Dialog to interact?

[ptoye]

I'm trying to write a macro for a Calc spreadsheet which (after a load of processing of no interest here) asks me if I want to make any changes to the underlying spreadsheet. What I want it to do is to allow me to select to a cell, click a "Modify" button on the Dialog box, which then calls a macro to change the sheet.

The code I've written brings up the Dialog box OK, but I have two problems:

1) I can't then either select the cell (clicking doesn't change anything - presumably because the Dialog box has control)

2) When I click the "Modify" button (which I don't want to exit the Dialog as I may want to do it more than once) the macro doesn't get called even though it's set up in the "Execute Action" event for the button. Presumably the macro system won't multi-thread.

Obviously, this isn't the way to do it, but, please, what is the correct way to get this functionality? I can get round (2) by having several Dialogs and changing the program structure a bit, but I can't see how to get round (1).

[Villeroy]

A cell picker is very difficult to implement. I would always prefer to edit the spreadsheet directly. In the end we all use spreadsheets because we don't like to write programs for trivial tasks.
http://wiki.openoffice.org/wiki/Documen ... _Selection
http://www.openoffice.org/api/docs/comm ... ction.html
http://forum.openoffice.org/en/forum/vi ... ion#p52957

[ptoye]

Thanks. It looks like a "If I was trying to get there, I wouldn't start from here" answer. The links look useful and I'll investigate them when I have time.

Editing the sheet directly is difficult - what I need to do depends on the results of the original processing. I'd have to completely rework my ideas which is annoying!

Now - how to implement a listener in OOBasic? Or should I start a separate thread?

[Villeroy]

Ignore the white rabbit.
Follow the third link.

[ptoye]

Time is pressing at the moment. But Thanks, I will.

[ptoye]

Ignore the white rabbit.

White Rabbit? Surely not. Aren't you thinking of the Cheshire Cat, who was pointing out that if you don't know where you want to go, it doesn't much matter which way you go. I have exactly the opposite problem.

[ptoye]

A cell picker is very difficult to implement. I would always prefer to edit the spreadsheet directly. In the end we all use spreadsheets because we don't like to write programs for trivial tasks.
http://wiki.openoffice.org/wiki/Documen ... _Selection
http://www.openoffice.org/api/docs/comm ... ction.html
http://forum.openoffice.org/en/forum/vi ... ion#p52957

I've looked at these and they go along way towards answering the question. But there's still a hole in my understanding: in the macro as I've currently written it, there are two parts: deciding which parts of the spreadsheet to operate on, and then doing the operation. Currently they're all part of one monolithic Sub. What I'd intended was that after part 1, the macro would put up a Dialog box asking if I wanted to select any more, and if I said Yes, to allow me to do a selection, then click a "finish" button on another Dialog box. But I can't see a way of programming this, as there's no way I can see of suspending the macro while I'm selecting. Do you know if this is the case, or am I missing something?

I agree that I can always split the macro into two Subs with loads of private variables to hold all of the information which needs to be passed between the two, but this will be very cumbersome.

[Villeroy]

I agree that I can always split the macro into two Subs with loads of private variables to hold all of the information which needs to be passed between the two, but this will be very cumbersome.

This is what "real programmers (tm)" do all the time in order to get things done. And it is what you are doing when you call anything that has parameters behind the function name: call_function( a, b, c )
However "real programmers" avoid the overly complicated Basic lingo.

If you don't want to learn programming (and by "learning" I don't mean the casual "learning by doing") then you should really work with spreadsheets cells instead of Basic macros.

[Villeroy]

I had to add another long comment to my example macro http://forum.openoffice.org/en/forum/vi ... ion#p52957 which had been written for OOo2.x which could not copy a filtered range.
The whole macro is no longer needed for filtered ranges.

The part you are asking for still works without modification which is the reason why splitting up code into separate chunks is the way to go if each chunk of code does one thing right.

Code: Select all

REM  *****  BASIC  *****
Private sRangeSelection$,bRangeSelecting As Boolean
'calls: module-vars bRangeSelecting, sRangeSelection
'return a valid com.sun.star.table.CellRangeAddress from a user's range-selection (or Empty)
Function getRangeSelectionAddress(oController,sInitial$,sTitle$,bAutoClose as Boolean,bSingle as Boolean)
On error goto returnEmpty
On error goto returnEmpty
Dim oListener,aProps(3) As New com.sun.star.beans.PropertyValue
	oListener = createUnoListener("RangeSelection_","com.sun.star.sheet.XRangeSelectionListener")
	oController.addRangeSelectionListener(oListener)
	aProps(0).Name = "InitialValue"
	aProps(0).Value = sInitial
	aProps(1).Name = "Title"
	aProps(1).Value = sTitle
	aProps(2).Name = "CloseOnMouseRelease"
	aProps(2).Value = bAutoClose
	aProps(3).Name = "SingleCellMode"
	aProps(3).Value = bSingle
	With oController.getFrame
	'this is required when calling from IDE or other frame in order to avoid endless loop
		.activate
		.getContainerWindow.toFront
	End With
	bRangeSelecting = True
	oController.startRangeSelection(aProps())
	while bRangeSelecting
		wait 500
	Wend
	oController.removeRangeSelectionListener(oListener)
	if len(sRangeSelection)>0 then _
		getRangeSelectionAddress = oController.getActiveSheet.getCellRangeByName(sRangeSelection).getRangeAddress()
returnEmpty:
End Function
'calls: module-vars bRangeSelecting, sRangeSelection
Sub RangeSelection_done(oEv)
	sRangeSelection = oEv.RangeDescriptor
	bRangeSelecting = false
End Sub
'calls: module-vars bRangeSelecting, sRangeSelection
Sub RangeSelection_aborted(oEv)
	sRangeSelection = ""
	bRangeSelecting = false
End Sub
Sub RangeSelection_disposing(oEv)
End Sub

Let's say you stored my function getRangeSelection with the 2 module variables in a global library named "Calc" and a module named RangeSelection. The following routine stored on an arbitrary Basic module lets you play with it:

Code: Select all

Sub test_RangeSelection
REM load the library of helper routines where getRangeSelection can be found:
GlobalScope.BasicLibraries.loadLibrary("Calc")

REM could be any other document's controller as well
oController = ThisComponent.CurrentController

REM play with the named arguments
addr =Calc.RangeSelection.getRangeSelectionAddress(oController,sInitial:="",sTitle:="Click Target Cell",bAutoClose:=True,bSingle:=True)

REM indicate the resulting range
print addr.Sheet, addr.StartRow, addr.EndRow, addr.StartColumn, addr.EndColumn
End Sub

This is getRangeByAddress function which is refered to as Calc.scalc.getRageAddress because I stored this in library Calc, module scalc. It takes some object (document, sheet or range) together with a RangeAddress struct and returns the respective subrange of the given object.

Code: Select all

'pass a spreadsheet-document, sheet or range  together with a c.s.s.table.CellRangeAddress
'return empty if oAddr out of bounds or wrong obj
Function getRangeByAddress(obj, oAddr as com.sun.star.table.CellRangeAddress)
on error goto nullErr:
Dim oSheet
	If obj.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
		REM use the sheet specified by given address
		oSheet = obj.getSheets.getByIndex(oAddr.Sheet)
	else
		REM use given object (range/sheet) as parent range
		oSheet = obj
	endif
	getRangeByAddress = oSheet.getCellRangeByPosition(oAddr.StartColumn,oAddr.StartRow,oAddr.EndColumn,oAddr.EndRow)
exit function
nullErr:
	getRangeByAddress = Null
End Function

[ptoye]

This is what "real programmers (tm)" do all the time in order to get things done. And it is what you are doing when you call anything that has parameters behind the function name: call_function( a, b, c )
However "real programmers" avoid the overly complicated Basic lingo.

If you don't want to learn programming (and by "learning" I don't mean the casual "learning by doing") then you should really work with spreadsheets cells instead of Basic macros.

Thanks - I wrote my first program in the 1960s and worked in the IT industry as a systems programmer for many years, before becoming a data communications consultant. I don't think I need to learn programming, even if I'm a bit out of date in the latest must-have language (is it Python this week?).

I agree with your later comment about correct program design - unfortunately OOBasic (like all of the other Basic offshoots) doesn't lend itself to this too well (better than it was in the 1970s, though!). The "Basic lingo" that you quote wasn't too bad then, but decent structuring was out of the question. The main problem is the mismatch between the capabilities of the Basic language and the requirements of an interactive program (which can include multi-threading), especially when the concepts behind the OOBasic dialect are spread about between many documents, which doesn't make it the easiest to learn! Maybe I should bite the bullet and write it all in Java.

I've found by experiment that if there's a Dialog box open, the selector doesn't work - presumably a focus issue. So I'll restructure.

Your comment about doing it all in a spreadsheet is not viable. If you can write a spreadsheet-only mechanism which scans a sheet for suitable (TBD - hence the need for interaction) rows and constructs a Writer document containing an invoice to the supplier, you're welcome to try.

[Villeroy]

Your comment about doing it all in a spreadsheet is not viable. If you can write a spreadsheet-only mechanism which scans a sheet for suitable (TBD - hence the need for interaction) rows and constructs a Writer document containing an invoice to the supplier, you're welcome to try.

Of course I can produce database reports.

[ptoye]

I'm sorry Villeroy, but you don't understand my problem. If I knew in advance exactly which items my major client wanted me to put on which of his invoices it would be easy. But I don't always find this out until way after the event, which is why I need to make manual adjustments. I don't even know how he wants me to bill for today's job. And had I realised that his accounting system was so complex I might not even have started - I now find I may (but I don't know) have to bill for hourly paid and fixed price jobs on the same invoice, which I hadn't catered for either!

My spreadsheet, and the billing macro, sort of "grew like Topsy", as do many small jobs. So sermons on how easy it is don't go down well - I realise you're trying to help, but "You've done it all wrong", when I knew I had, isn't exactly helpful.

I'll try to find time to restructure the macro (which I well know that I should have done) - Andrew Pitonyak's document helps a lot on how to write listeners.

[Villeroy]

You do this as a paid job for a client? This job should be done by a professional. And a professional would never choose a spreadsheet as invoicing tool.
What you are trying to do is so unprofessional.

[ptoye]

Again, you misunderstand me. I'm not producing the spreadsheet for a client. I'm using it to invoice my client for my real work, which has NOTHING to do with computing. If you want a flame war, go ahead, but my profession is no longer IT. And in no way would I have produced this code when it was. You are, I would suggest, being "so" unprofessional in not understanding my messages before replying. Or maybe I'm not being clear enough.

But I understood this was a technical forum, not a business seminar.

[Villeroy]

Cell_Range_Pickers.ods

Small application to pick a cell, select a range and copy the range to the cell.

(21.07 KiB) Downloaded 278 times

[ptoye]

Thanks Villeroy. I'm away for a few days and won't have time to look until I get back. I've started working it out for myself, thanks to your links. It doesn't seem too complicated once I've restructured the program.

[RoryOF]

You are, I would suggest, being "so" unprofessional in not understanding my messages before replying. Or maybe I'm not being clear enough.

You may not be aware that Villeroy, as many of the Volunteers on this forum, is replying not in his native language to a very technical subject.

[ptoye]

You may not be aware that Villeroy, as many of the Volunteers on this forum, is replying not in his native language to a very technical subject.

I appreciate that. But I do not appreciate being accused in a public forum of being unprofessional. His comment was not a technical one, after all, but about his incorrect assumption about my business practices.

Villeroy's technical help has been invaluable and is much appreciated by myself any, I imagine, many here.

[karolus]

Hallo

Thanks - I wrote my first program in the 1960s and worked in the IT industry as a systems programmer for many years, before becoming a data communications consultant. I don't think I need to learn programming, even if I'm a bit out of date in the latest must-have language (is it Python this week?).

This Statement is ignorant, especially for a Guy who call himself a Programmer -
Python still exist ~twenty years - its not a "latest-must-have language"
http://en.wikipedia.org/wiki/History_of_Python

Karolus

[ptoye]

Thanks for putting me right on the history of Python. My comment was meant to be a bit humorous - languages go in and out of fashion, and have their fans - and detractors. I don't know Python at all - too late for me I'm afraid. It will probably die like most others. Or metamorphose out of all recognition (which seemed to happen in V.3 according to the Wikipedia article).

Remember "The Last One"? Was touted as the only programming language you'll ever have to learn about 30 years ago. Somewhat failed. See http://en.wikipedia.org/wiki/The_Last_O ... oftware%29

[Villeroy]

Some other things that changed for the better during the last 2 decades:

1) Accounting in spreadsheets is an perfect reason to get fired.
2) Basic is an extinct dinosaur. It is too complicated and platform dependent to be useful. Only MS Office and this office still offer a development platform for this (what a shame). Basic lacks a living community. It's a dead horse.
3) Object oriented programming.
4) More than 90% of the complexity is due to the API you are hacking against. The scripting language provides just a more or less capable tool set to call the respective APIs. Compared to JavaScript and Python, Basic does not deserve any preference to call an huge object oriented API like this one.
5) No comment on this one:

Currently they're all part of one monolithic Sub.

[ptoye]

Some other things that changed for the better during the last 2 decades:

1) Accounting in spreadsheets is an perfect reason to get fired.
2) Basic is an extinct dinosaur. It is too complicated and platform dependent to be useful. Only MS Office and this office still offer a development platform for this (what a shame). Basic lacks a living community. It's a dead horse.
3) Object oriented programming.
4) More than 90% of the complexity is due to the API you are hacking against. The scripting language provides just a more or less capable tool set to call the respective APIs. Compared to JavaScript and Python, Basic does not deserve any preference to call an huge object oriented API like this one.
5) No comment on this one:

Currently they're all part of one monolithic Sub.

1) Up to a point. It depends on the size of the business. For a large enterprise, I agree totally. I am self-employed and my turnover is too small for me to have to charge VAT (sorry, I don't know the German for this). Being able to supplement my pension by casual work is useful, but hardly needs complex accounting software.

2) Agreed. I decided to try it out, and found it very frustrating. I knew VBA when working (it was useful when the basic Word/Excel etc. didn't have the relevant facilities built in), and hoped that it would be easy to move to OOBasic. How wrong I was!

3) Agreed. I should have started in Java (which I also speak), had I realised that the invoice-generating application would become so complex. And that, presumably, provides a further learning curve.

4) Agreed.

5)

[Villeroy]

All you need is a structured storage of data and some query language to query and calculate relevant subsets of your stored data.
This may be doable with a spreadsheet which yields quick results, but ends up as an unmaintainable flea circus in the long run.
It would be easier with a database (steep learning curve but will work for many years and millions of records).
I use to recommend http://gnucash.org which -- of course -- relies on a database.

I know how to do such things in spreadsheets but the details depend entirely on the concrete data to be processed. Spreadsheet macros are a waste of time and energy unless you have a working solution that needs some refinement.

[ptoye]

Can we agree to differ on this - it's wasting both of our times? When I manage to do millions of jobs a year I may start to agree with you.... At my age, I'm running down, not expanding.

[Villeroy]

Can we agree to differ on this - it's wasting both of our times? When I manage to do millions of jobs a year I may start to agree with you.... At my age, I'm running down, not expanding.

The whole discussion is fruitless. I can not give any advice without knowing anything about your data. Concrete input data are the one and only possible starting point. This is a generic tool that does not do anything specific for you. Each and every feature depends on concrete input data, the types of data (numbers, dates, text, any pictures?), where data come from (keyboard, clipboard, import), how they are structured (lists with fields and records, cross-tables, vertical blocks).
Today's productive topic on this forum is: http://forum.openoffice.org/en/forum/vi ... =9&t=60765 simply because we have concrete data as a starting point. From there we can introduce even the more sophisticated spreadsheet features.

Macros become far less frustrating when a couple of simple code lines stitch together some of these featues. This requires that you are familiar with the built-in features instead of programming your own ones.
What you are trying to do could be done with a most simple database, possibly in combination with a spreadsheet and finally with some macros as glue and sugar coating. Your macro will not re-invent the database. A macro can not even force a spreadsheet to work the whay you want it to work if that way is beyond the way how it is supposed to be used.

[ptoye]

Cell_Range_Pickers.ods

Thanks Villeroy - now I've had time to look at it I see that you have to end the Dialog and pass results around using variables. A bit cumbersome, but it works fine. I would probably have worked out how to do it by myself in the end, but this is really helpful.