Apache OpenOffice Community Forum

I have converted some excel MACRO's to work in CALC but running in to some issues:

Dim oSheet
oSheet = ThisComponent.Sheets.getByName("somesheetname")

1) while this code works when stepping through the code with F8 ... if I save and open my ods file I get an error for each cell that calls the function containing this object??? OpenOffice 4.1.1 BASIC runtime error ... property or method not found: sheets

2) This error occurs for each cell calling the function and provides no way to ignore all

3) There doesn't appear to be a way to turn off auto calc when calc opens ... excel has a shift F9 function to force a recalc of a sheet

Am I missing something?

The menu item Tools -> Cell Contents -> AutoCalculate can be used to turn off automatic calculation. Also, I wrote a simple function that call ThisComponent.Sheets.getByName() and I don't see a problem when opening the sheet. Can you post an example document?

Hi,

In the title of its thread, [color=#105289][b]bxdobs[/b][/color] wrote:MACRO ISSUES

What a descriptive title... and it shouts (uses uppercase)

In fact you only have one problem: using ThisComponent in your function for Calc.

Your Basic function is stored in "My Macros".
Store your macro in the Standard library of your document, instead of using the container "My Macros", it will work.
For some reason, ThisComponent is not yet initialized at the time of cell evaluation when loading the document.

Some people want to use any API code within a function created for Calc cells. Usually they have problems. A function for Calc should restrict to computing a value using its arguments. Computing is the primary use of a spreadsheet.

FJCC: Ok thanks, I totally missed the Cell setting ... this however appears to only be a file specific setting, I guess I was expecting there would be a global setting that I could change before opening the offending file.

Bernard: the example I provided wasn't my standard naming convention ... my sheet names are descriptive and include spaces so something like "Some Sheet Name" ... within the call I would use the same text as in "Some Sheet Name" ... are you suggesting all characters need to be upper case or perhaps you are just suggesting I should be using "SomeSheetName" instead of "somesheetname" ?

The converted Excel macros are stored in My Macros Standard Module1 ... My thought was they would be globally available from this location ie available any time I start a new sheet which appears to be the case. Excel provides global Macros using a hidden personal worksheet

Your comment that the ThisComponent object hasn't been initialized can be demonstrated by turning off autocalc, once the file is completely loaded, F9 properly calculates all functions.

I use Macro's in EXCEL to provide all spreadsheet access to functions that use special data in their calculations... this data is usually contained in the hidden personal excel file. Without this data the functions would have to be manually added with their data any time a new file is started