[Solved] Calling Calc Functions From Macro

[bladeoflight16]

Hello.

I have a spreadsheet that will soon have about 20 columns of data, with 99 rows of data each. I'm trying to find a formula to predict the data, so each column of recorded data will have a corresponding column that gives a prediction based on my formula and a couple of parameters (which are located elsewhere in the spreadsheet and I can modify there if necessary) as well as another corresponding column calculating the difference between my formula's prediction and the actual data.

The problem is that I will be modifying the formula itself frequently in an attempt to find the right one. So changing it and copying it to roughly 2000 cells would be a huge pain. It would be far easier to define the function in one place and change it there. I have found out how to use a macro in Basic to define a function with parameters that can be called in the cells, but my formula is at present using some spreadsheet functions. Is there a way to call spreadsheet functions from inside a macro?

If not, is one of the macro languages better than the others for mathematical operations? Also, where can I find something about the mathematical API for whichever language is best?

I do have programming experience (primarily in Java and a tiny bit in C), but I've never worked with any of the languages listed for macro creation. Obviously, if there's a better way to define a spreadsheet function than using a macro, I'm open to that.

Also on a side note, I wasn't sure if this belonged here or in the Calc section, so sorry if it's in the wrong place.

[squenson]

Here is an example that uses the MAX calc function:

Code: Select all

Function calc_MAX(vRange)

							' The first argument to callFunction() is the Calc Function Name
							' The 2nd argument is the parameters of that function as an array
	FuncAcc = createunoservice("com.sun.star.sheet.FunctionAccess")

	calc_MAX = FuncAcc.callFunction("MAX", array(vRange))
	
End Function

Put some numbers in A1:A5 and in B1 type =calc_MAX(A1:A5)

[bladeoflight16]

Thank you very much. After I fixed my security settings to allow documents in this file's location to execute their macros and discovered that I had to put my function in the document's Standard library to get it to load when I opened the file, that worked perfectly.

Now I have a related problem.

If I edit the function and recompile the macro, the function definition actually changes. I tested this by putting the function into a new cell, and it calculated according to the changed version. However, if the function is already in a cell, the spreadsheet does not realize the function has changed and does not recalculate. It also seems that using Recalculate (F9 or Tools --> Cell Contents --> Recalculate) does not force recalculation either, since the cell contents have not changed. I can close and reopen the document to force full recalculation, but I'd prefer to avoid that. Is there a way to force all cells to recalculate?

[squenson]

May be I am wrong but does not Shift-F9 or Ctrl-F9 force a full recalculation? Try and let us know!

[bladeoflight16]

Ah, thank you. It was both, actually: Ctrl+Shift+F9. Much appreciated!

Edit: It's really surprising that I wasn't able to find that by a simple Google search and a search on this forum.

[TheGurkha]

The best way to search here is to use the advanced search, and sort the results by topic, not by post.

[Villeroy]

The calculation results of the FunctionAccess service depend on several additional properties. Just like many results in a spreadsheet depend on the options in Tools>Options>Calc>Calculation.
http://api.openoffice.org/docs/common/r ... ccess.html
http://api.openoffice.org/docs/common/r ... tings.html [deprecated but without alternative]