Spreadsheet Problem

[Black Hole]

Anybody got any clues on this?:

I have an extremely complex analysis function which takes an integer input value and returns a floating point output value, but as the function involves analysing a large number of data points using the input value as a parameter it occupies two spreadsheet columns (with 20,000 rows) to compute.

My aim is to create a plot of output value against input value for (ideally) 10,000 values, but if done within the spreadsheet that would mean 20,000 columns as well as 20,000 rows - obviously too much to ask (and when I tried multiplying up to a couple of dozen columns just to try it, the spreadsheet overloaded and crashed.

What I need is an external process which can update the value in one cell of the spreadsheet and then retrieve the output value and create a table of input v output for the 10,000 values (and leave it running for a day or three). Maybe there's a spreadsheet facility that I'm not familiar with which can already do that, or some kind of DDE link.

I'm using OpenOffice 3.3 if that matters, I could install Excel if it would solve the problem.

 

[Ezra Pound]

The spec. for Microsoft Excel 2010 is HERE, it allows 1,048,576 rows by 16,384 columns, with each cell containing a maximum of 32767 characters. Not sure what the limits are in Open Office, but I think it has a 1024 column limit

 

[Black Hole]

I now have AutoIt running a macro which collects the data, after a bit of head scratching. Bring back Windows Macro Recorder! (AutoIt is very powerful, but not easy to get into)

 

[Black Hole]

What the heck is this?:

 

[Brian]

What the heck is what?

 

[Black Hole]

The pair of glasses... but I've figured it out now - it's because I set a watch on this topic.

 

[Brian]

Yes, that is correct.

 

[MikeSh]

I now have AutoIt running a macro which collects the data, after a bit of head scratching. Bring back Windows Macro Recorder! (AutoIt is very powerful, but not easy to get into)

I don't understand exactly what you are trying to do, but I've used the macro system in OpenOffice on occasions to do more tricky stuff.

 

[Black Hole]

I find the OpenOffice macro system impenetrable. I could just about cope with MSOffice macro programming, but the OO system... Well, it records a series of user actions, but doesn't do what you expect when played back and then looking at the resulting script half the actions are not there because the data model doesn't support them.

At least with AutoIt (or another key click recorder) I can just automate a series of user inputs, which allowed me to increment the input parameter and capture the resulting output, compile them into a table and plot a graph of it.

 

[Trev]

At least with AutoIt (or another key click recorder) I can just automate a series of user inputs, which allowed me to increment the input parameter and capture the resulting output, compile them into a table and plot a graph of it.

That's a piece of cake in Excel VBA

 

[MikeSh]

I find the OpenOffice macro system impenetrable. I could just about cope with MSOffice macro programming, but the OO system... Well, it records a series of user actions, but doesn't do what you expect when played back and then looking at the resulting script half the actions are not there because the data model doesn't support them.

That's a piece of cake in Excel VBA

I agree the OO system (or perhaps I should say the Help) is not terribly user friendly.

But in fact I wasn't recording complete macros but writing them in whatever Basic-like language it is they use in OO much as Trev has alluded for Excel.
Sometimes I do record a short macro so I can gut it for the code for some action(s) and then write them into my mega-macro in (eg) a for ... next loop or whatever where I can control the values with maths.

 

[Black Hole]

That's pretty much how I work too, but to illustrate how limited the OO macro programming system is: in Writer, I can't find a way to select the contents of a whole table - which in "manual" mode is simply Alt+A, S, T.

 

[Trev]

Can you not record a macro for the above commands in OO and then look at the code to see how it does it? (Which is what I do in Excel)Or does OO not have a record macro function?

 

[Black Hole]

That's the point. Set it recording, and then look at the resulting code... sequences like Alt+A, S, T (Table Select Table) produce no code entry. Crap.

I had a whinge on the OO forum about it, but all I got was "the macro language does what it does" and no recognition that it is inadequate.