[Solved] Things Were Going So Well!

[FrankBlissett]

I have done minor coding through the years, but decided I needed to figure out this whole "macro" thing for a project.

Basically, I will need to preform an operation on the word-count of various documents. For my test run, I wrote the following (to be changed later):

Code: Select all

Function WordCount(Optional x)
  Dim WCValue as Single
  WCValue = 0.0
  If NOT IsMissing(x) Then
    If NOT IsArray(x) Then
      WCValue = x^.5
    End If
  End If
  WordCount = WCValue
End Function

I followed the instructions at "https://wiki.openoffice.org/wiki/Docume ... _functions" and got the function to work in the spreadsheet.

Then I got the 2nd error seen on the page "https://wiki.openoffice.org/wiki/Docume ... _functions", ("This document contains macros"). I enabled Macros through "Tools > Macros > Organize Macros > OpenOffice.org Basic", but the function was still broken.

From there I could not figure out from the instructions how to get the function working again.

Any advice?

[FJCC]

Try going to the menu Tools -> Options, expand the OpenOffice list on the left, choose Security and click Macro Security. Select the Medium level, reopen your file and see if the macro works.

[FrankBlissett]

Thanks for the response, FJCC.

I had already set macro security to "Low". Tried resetting it to "Medium". ... That *did* give me the "Enable Macros" option on opening the spreadsheet.

The cells were still giving a "#NAME?" error, so I deleted them, and re-entered "=wordcount(c3)". That worked, and gave the expected result.

I saved, closed & re-opened the spreadsheet, and again was prompted to enable macros. I did so, but found a "#NAME?" error again. ... Surely I do not need to delete & re-enter the function every time I want to use it? Eventually the sheet will have thousands of entries.

[Villeroy]

Works for me. There is no switch to simply enable macros. You either store macros publicly under "My Macros". The you can use your function in all spreadsheets without macro warning. Or you store documents with contained macros in the previously declared trusted directories (Tools>Options>Security>Macro Security... high level ... trusted sources ...
Modules with sheet functions written in Basic need to be stored within the "Standard" library of the respective container. The "Standard" ibraries are loaded automatically.

[FrankBlissett]

That seems to have fixed it. ... The instructions on the OO wiki has one create a new library for the macro. I fixed the problem by creating a different macro in "Standard", and that new macro works fine.

Thanks, Villeroy!

[UnklDonald418]

I didn't see where anyone mentioned that to avoid getting the Enable Macro message every time you load your spreadsheet, go to Tools->Options->Security->Macro Security with the Security Level set to Medium click on the Trusted Sources tab and Add the directory where your spreadsheet file is stored to the Trusted File Locations list.