Macro Function not working after closing document

[KonstantinosFroudarakis]

Hello everyone,

I have a macro function in a Calc workbook that takes a currency formatted number and converts it into words for the same currency value. This code initially began as the code in Microsoft’s VBA guides but has been extensively modified to:

1. Write the value in Greek

2. Follow Greek grammar which has more complicated demands

The Function works great and as expected… apart from two things, one little and one deal braking.

The little one is that the function does not autocomplete as the user types it. No biggy, I am not expecting the user to need to type it to another cell other than the one I specified it in.

The other is that every time I close the document and reopen it, the function gives a #NAME? error. No matter how many times I retype it correctly it doesn’t work.

I need to open the macro with Alt+F11, do something in the code and resave it (as simple as adding a breakpoint, not changing anything) and then I need to → erase it from the cell → retype it and it works. I can even remove the breakpoint then. It will perform exactly as expected.

However, I can not give this document to anybody else to work with it because I cannot expect other users to know how to handle this situation unless they have experience with macro code. And I don’t want to encourage poking around the macro code because they could break it and then they’d send it back for me to “fix it”.

Any ideas???

[JohnSUN]

It is difficult to help, the cause of the error may be different. If you brought at least a fragment of your code (the heading of the function and returning the value) and the way you call it (the formula in the cell and the value for which the function is called), we could probably determine the source of the error. If you can attach a file with a macro and how to call it to your original question, then, I think, you will get useful tips very quickly.

[KonstantinosFroudarakis]

I found a “workaround”. I cut the entire code for the function from the library and module I had created for it and added them to the standard lib of the actual document.

Now it works every time. But the issue is that I cannot password protect the standard library for some reason.

you can find the entire document here

Ideally, I would love to password protect the macros after I’m done with them.

[JohnSUN]

By the way, if you want to get something like this

you don’t need to write a macro. Just use the MONEYTEXT() function, which is part of the Numbertext extension (details here)

[KonstantinosFroudarakis]

Yeah, something like that. But I don’t like that “y” instead of “KAI” that should be there. And besides, I have the code ready and working in both excel and Calc, and if I give it to someone, he doesn’t need to download any extensions. It’s self-contained.

[Lupp]

I would tend to suspect this to be a useless game. But…
Only if you make available your code (including module options, helper Sub or Function and the like), best included with an example document, someone might find a clue. After all the behavior you describe isn’t any kind of “standard issue” many users will have experienced.
Anyway, if you want to replace isolated letters “y” by your preferred Greek word meaning “and”, LibreOffice has the means to do so. (=REGEX("διακόσια πενήντα ένα ευρώ y εβδομήντα έξι λέπτα";"\by\b";"και";"g") e.g.)
(Using VBA code you should always expct errors.)

[KonstantinosFroudarakis]

This might be a game to you, but it’s work for me.
The entire document, along with code, formatting and everything else is available
The behaviour I described is fairly common and there are unanswered threads about it on stackoverflow and GitHub.
It’s not VBA. It’s fairly standard Visual Basic, and it works perfectly now.

[Lupp]

Quoting @KonstantinosFroudarakis: “The entire document, along with code, formatting and everything else is available…”
Where? If I try to get it from silly google, they tell me it’s in the trash bin of the owner. I wouldn’t start to study drive.google now.
Quoting @KonstantinosFroudarakis: “…unanswered threads about it on stackoverflow…”
You didn’t link to these threads. And there might be a reason for the missing of an answer.

[KonstantinosFroudarakis]

LibreOffice macro doesn't work after saving and exiting

LibreOffice Macro always show #NULL! after reopening the file

InvoiceDev.ods

There you go m8. Although, you’ll have to go to “silly” google.

[KonstantinosFroudarakis]

Moving the function back to the “Standard” library of the document and putting it in the pre-existing “Module1” makes it work as expected again.

[JohnSUN]

You don’t have to transfer a function from one library to another. It was enough to create a function in the Standard library that loads your library and calls the function from there.

Function SPELLNUM(ByVal MyNumber)
	BasicLibraries.LoadLibrary( "InvMacroLib" )
	SPELLNUM = SPELLNUMGREEK(ByVal MyNumber)
End Function

Of course, in a cell you need to call a function from the Standard library =SPELLNUM(F31)

However, this will only work well until you encrypt your library with a password. After that, you will not be able to get the correct inscription in your cell Ολικό Σύνολο Ολογράφως:

This is logical - if you hide the code with a password, then perhaps you are not interested in to get texts from there using external subroutines or functions. For numerical functions that perform calculations and return numbers, such difficulties will not arise.

Listen to @Lupp - password encryption is “a useless game”. Believe me, the code serving the invoice is not so great as to puzzle over its protection. From the Community you get much more than you are going to not give to users.

[mikekaganski]

However, this will only work well until you encrypt your library with a password. After that, you will not be able to get the correct inscription in your cell Ολικό Σύνολο Ολογράφως

That only partially correct. Currently, the encrypted libraries can contain 1-byte-encoded text, which allows for “system locale” - so if OP has Greek as system locale, the text would be okay for them.

… and correct Unicode in password-protected Basic libraries was implemented for 7.0 in tdf#57113.

[KonstantinosFroudarakis]

Dear JohnSUN,

Thanks for taking the time to write that answer for me. Really nice solution and I am sure I will use that in the future.

But please allow me to say this. It’s not about giving or taking from the community, it’s not about puzzling over hiding simple or complicated code. It’s about keeping people from breaking it accidentally. And about making it a little hard to tamper with saved records, since this is an invoice after all. At least I want to be able to say I tried to make it “harder” for people to cheat with this file. That’s all.

[JohnSUN]

Dear @KonstantinosFroudarakis , I am glad that I was mistaken in my assumptions. I’m sorry for my doubts about your generosity.

However, I believe that spending time protecting something from users is a futile task. In any case, sooner or later there will be either a stupid performer or a too smart user and still break everything that he can.