Why do my functions have to be "fixed" every time I open a spreadsheet?

[mrjjacobs]

Hi everyone,

I use

VLOOKUP([cell reference from this spreadsheet tab]; [range in another workbook]; [column offset]; [sort?])

and I have learned that it is unwise to refer directly to “formula” cells in a formula if the source cell’s formula refers to an external spreadsheet, because when a spreadsheet opens and updates links, one cannot be certain that the links will be processed before the local formulae.

The problem I am having with Functions seems to be similar…

I have been having minor problems with my spreadsheet structures when I move them between drives, so if I have to move my documents from the C: drive to the D: drive, I have search for things like this:

’file:///C:/MySubfolder/MySpreadsheetDataSources/MySpreadsheetReport.ods’#

and fix them manually.

So I am trying to improve things by hard-coding the paths in Basic with Functions that build the paths:

PRIVATE FUNCTION BUILDPATH() : BUILDPATH = CHAR(39) & CONST_PREFIX & CONST_DRIVE & CONST_PATH & CONST_FOLDER & CONST_ODSFILENAME & CHAR(39)

And then I do it, and it all works beautifully, and I sleep so nicely! And then I wake up in the morning, turn everything on, open the spreadsheet and I’ve got #NAME? all over the place, and all my

VLOOKUP([cell reference];INDIRECT(BUILDPATH()&"#$TabName.[range] have changed to

VLOOKUP([cell reference];INDIRECT(buildpath()&"#$TabName.[range]

…and then I do a case-sensitive search and replace on the catatonic functions and search for lowercase and replace with upper case, and sometimes it works and sometimes it doesn’t work straight away, and then I have to do the search and replace again and again and again and eventually it works, drives me blinking bananas.

Please will someone help and thank you.

P.S. I have a small breakthrough with my dilemma. The case-sensitive search/replace mentioned above for the “catatonic functions” under discussion (and this is as much a note to self for future reference), only (?) works if you check the “diacritic sensitive” box. Doesn’t solve my “catatonic functions” problem though. Rubbing my hands together waiting for your genii to absolutely blossom for me. Thanks.

[mariosv]

Please what file format are you using?

[mrjjacobs]

I’m using ODS files in LibreOffice Calc 6.4.2.2, with LibreOffice Basic functions.

I suspect I didn’t understand the question.

Still having immense struggles trying to MAKE the spreadsheet SEE that the function has BEEN WRITTEN and it IS THERE in the local code.

[mrjjacobs]

Riiiiight … I have an idea this is because I created my own nice Library and my own nice Module in which I placed my code, and that that isn’t such a great idea because the “Standard” library and the default “Module1” are automatically invoked OnLoad() and all others are not.