Macro Security Settings

users
#1

Version 3.6.5.2 (Build ID: 5b93205)

I wrote a program? macro? many years ago in Excel 2000 that I haven't used in almost as long, but now I have need of it again.

When I try to run it, Libre Calc complains about the security settings. I changed them in tools->options-->LibreOffice--->Security-->Macro Security to "Low" and it still gives me the same error and refuses to run the macro.

Any help?

Thank you in advance

#2

Perhaps macro is not run due to error?

LibreOffice does not use VBA, as MS Office does; it uses Star Basic instead. It
tries to translate VBA to SB on the fly, but most of time it fails to do so. I
bet this is your case.

I am afraid you must rewrite your macro to Star Basic, so LO will be able to
run it. Or try with MS Office (although they might have changed API in past 10
years, in which case your macro will not run anyway).

#3

Thanks for the response, but that doesn't sound right to me. If it were a run error I'd expect to get some kind of runtime error, not a security warning. But perhaps someone else knows?

#4

Version 3.6.5.2 (Build ID: 5b93205)

I wrote a program? macro? many years ago in Excel 2000 that I haven't used
in almost as long, but now I have need of it again.

When I try to run it, Libre Calc complains about the security settings. I
changed them in tools->options-->LibreOffice--->Security-->Macro Security to
"Low" and it still gives me the same error and refuses to run the macro.

Did you restart LibreOffice after changing that setting?
When does it complain? When you start the macro or when you open the
file with LibreOffice?
Exactly what does the error message say?

For security reasons I think it's better to keep the highest level,
just add paths in the ”Trusted sources” (or whatever it's called in
English version) tab whenever LibreOffice complains about macro
security.

Johnny Rosenberg

#5

I did not. And that has resolved the problem. Thank you!

I now have the problem Miroslaw has already commented on - star office vs. VBA. Ouch. I've received "Basic runtime error 1". Is there a translation guide?

This is not just a simple one line macro, this is a program of relatively large proportion - I estimate hundreds of lines of code. And I have others I will probably need to do the same.

By the way, it sure would be nice if I could copy & paste error messages directly from the error dialogs (the same way I was able to copy & paste the version string), instead of having to retype them.

Thank you

#6

Version 3.6.5.2 (Build ID: 5b93205)

I wrote a program? macro? many years ago in Excel 2000 that I haven't
used
in almost as long, but now I have need of it again.

When I try to run it, Libre Calc complains about the security settings.
I
changed them in tools->options-->LibreOffice--->Security-->Macro Security
to
"Low" and it still gives me the same error and refuses to run the macro.

Did you restart LibreOffice after changing that setting?

I did not. And that has resolved the problem. Thank you!

I now have the problem Miroslaw has already commented on - star office vs.
VBA. Ouch. I've received "Basic runtime error 1". Is there a translation
guide?

I'm not sure, I don't think so. I have never tried to load an Excel
macro in LibreOffice and I didn't use Excel since I don't know when,
2006?
Does it look like LibreOffice has tried to translate the macro somehow
or does it look exactly like the original Excel macro?
What does the line look like that currently is producing an error? Are
there many lines like that?

Andrew Pitonyak's macro document is a good start if you want to learn
LibreOffice Basic macro programming. Search for AndrewMacro and you
will find a PDF and an ODF (same document, different formats).
Installing something called ”xray” is also a very good idea. It's a
macro that helps you investigate objects, for example cell ranges,
selections and much more.
If you just want to know a couple of simple things, like how to get
and set properties for a cell range, you can ask here if you can't
find the answer by searching.

Johnny Rosenberg

#7

Hi :slight_smile:
You can find Andrew Pitonyak's Macro Guide at
https://wiki.documentfoundation.org/Documentation/Other_Documentation_and_Resources#Programmers
That link might be redirected to something like
https://wiki.documentfoundation.org/Documentation/Publications/Unofficial/Programmers
or
https://wiki.documentfoundation.org/Documentation/Publications/ThirdParty/Programmers
if i ever get around to creating such a page and copying the right contents
into it.

If you use Nabble to post to the Users List instead of just replying to
emails it lets you upload files so you could take a screenshot of the pop-up
error message and everyone could see it fairly easily then. No need to
re-type it but it's still not exactly smooth and easy.

Your suggestion about making the contents of those pop-ups easy to
copy&paste is good and it's something the devs are working on. They might
have done it already for the 4.0.1 but i'm not sure. It's probably quite
tricky to do isn't it?
Regards from
Tom :slight_smile:

#8

I have this sinking feeling that converting to star basic is a step backwards in time, not forward. Is there any other *current* program this BASIC is compatible with? Or does this lock me into LibreOffice? Maybe I'd be better off converting to java if I'm going to do a major rewrite?

Maybe the differences/changes are minor. I looked at the macro guide you mentioned and... well, it seems like it's a whole different language.

Here is the first thing it errored out on. The "set rng=" statement. I include the entire subroutine so that you have some context. Since this is the entry point to my program I suppose you could just copy & paste it to your own empty file called "newinputs.xls" and see what happens when you run it. Thank you

Private Sub UserForm_Initialize()
Dim rng As Range
Dim myPath As String
Dim appl As String
Dim myName As String
Dim lastRow As Integer
Dim lastColumn As Integer
Dim pro As Worksheet

'frmPropertyEntry.Hide
  dWidth = Me.Height

appl = "newinputs.xls"

myPath = ActiveWorkbook.Path
myName = ActiveWorkbook.Name

If myName <> appl Then
     MsgBox "I'm confused! Please close " & myName & " before trying to run " & appl
     Exit Sub
End If

Set pro = ThisWorkbook.Worksheets("Processed")
pro.Activate

lastRow = pro.UsedRange.Rows.count
lastColumn = pro.UsedRange.Columns.count

*Set rng = pro.Range("b2", pro.Cells(lastRow, lastColumn).Address)*
frmPropertyEntry.PropertyInfo.RowSource = vbNullString
frmPropertyEntry.PropertyInfo.RowSource = rng.Address
page = "Processed"

Set rng = Nothing
Set pro = Nothing

'frmPropertyEntry.Show (vbModeless)
End Sub

#9

Hi :slight_smile:
I think there are 4 different languages that can be used to create macros for LibreOffice.  Java is going down the pan now that Oracle run it so it might be best to use Python?  The LibreOffice devs are currently converting all java stuff to other languages, i think mostly to Python or C++.
Regards from
Tom :slight_smile:

#10

Hi Andrew,

>VBA. Ouch. I've received "Basic runtime error 1". Is there a
translation
>guide?

There used to be one available for paying customers of StarOffice, at
the time supplied by Sun, not sure whether it is still available anywhere.

>This is not just a simple one line macro, this is a program of
relatively
>large proportion - I estimate hundreds of lines of code. And I have
others I
>will probably need to do the same.

You will need to take a deep breath, select a few choice expletives, and
just knuckle down to it or give up, unfortunately. There is no miracle
cure for converting a VBA based Excel app to LO Basic.

>
>By the way, it sure would be nice if I could copy & paste error
messages
>directly from the error dialogs (the same way I was able to copy &
paste the
>version string), instead of having to retype them.

I thought this was already possible from version 3.6.x onwards ? Perhaps
it was only introduced in 3.6.4 or later, but I can copy the messages by
selecting with the mouse and then right mouse button clicking on my 4.1
test build from the master source repository.

I have this sinking feeling that converting to star basic is a step
backwards in time, not forward. Is there any other *current* program
this BASIC is compatible with? Or does this lock me into LibreOffice?
Maybe I'd be better off converting to java if I'm going to do a major
rewrite?

LO Basic is a particular variant of a Basic type language, dependent on
the UNO API. Objects and methods are not always the same in LO Basic and
VBA, for example.

The UNO API is also fairly old, and has only recently started undergoing
more changes - LO Basic is just one way of accessing the UNO API. If you
have no choice but to rewrite (which seems to be the case), you might be
better off with Python, which appears to be the language of choice for
much of the ongoing/upcoming scripting functionality. That's not to say
that there aren't other language bindings, because there are :

- Javascript
- Beanshell
- REXX (this works/worked on OpenOffice.org, not sure about LO or
ApacheOO though)

Here is the first thing it errored out on. The "set rng=" statement. I
include the entire subroutine so that you have some context. Since this
is the entry point to my program I suppose you could just copy & paste
it to your own empty file called "newinputs.xls" and see what happens
when you run it. Thank you

You might want to start looking here :

http://api.libreoffice.org/docs/common/ref/index-files/index-18.html

Unfortunately, the documentation is lacking somewhat. You might have
better luck trying to find the OpenOffice.org Developer's Guide,
however, the link provided on the LibreOffice wiki to this document
returns a "404 not found", so it has obviously been moved somewhere else.

Alex

#11

I now have the problem Miroslaw has already commented on - star office
vs.
>VBA. Ouch. I've received "Basic runtime error 1". Is there a
> translation
>guide?

I'm not sure, I don't think so. I have never tried to load an Excel
macro in LibreOffice and I didn't use Excel since I don't know when,
2006?
Does it look like LibreOffice has tried to translate the macro somehow
or does it look exactly like the original Excel macro?
What does the line look like that currently is producing an error? Are
there many lines like that?

Andrew Pitonyak's macro document is a good start if you want to learn
LibreOffice Basic macro programming. Search for AndrewMacro and you
will find a PDF and an ODF (same document, different formats).
Installing something called ”xray” is also a very good idea. It's a
macro that helps you investigate objects, for example cell ranges,
selections and much more.
If you just want to know a couple of simple things, like how to get
and set properties for a cell range, you can ask here if you can't
find the answer by searching.

Johnny Rosenberg

>
>This is not just a simple one line macro, this is a program of
> relatively
>large proportion - I estimate hundreds of lines of code. And I have
> others I
>will probably need to do the same.
>
>By the way, it sure would be nice if I could copy & paste error messages
>directly from the error dialogs (the same way I was able to copy & paste
> the
>version string), instead of having to retype them.

I have this sinking feeling that converting to star basic is a step
backwards in time, not forward.

Then you should keep using Excel.

Is there any other *current* program this
BASIC is compatible with?

Apache OpenOffice, pretty much anyway (same Basic, maybe 99 % same API).

Or does this lock me into LibreOffice?

Yes, maybe a little bit. As you are already locked into Excel, maybe
your best choice is to continue using Excel.

Maybe I'd
be better off converting to java if I'm going to do a major rewrite?

Maybe the differences/changes are minor. I looked at the macro guide you
mentioned and... well, it seems like it's a whole different language.

Well, the language is basically (!) the same (not 100 % though), the API isn't.

Here is the first thing it errored out on. The "set rng=" statement. I
include the entire subroutine so that you have some context. Since this is
the entry point to my program I suppose you could just copy & paste it to
your own empty file called "newinputs.xls" and see what happens when you run
it. Thank you

Private Sub UserForm_Initialize()
Dim rng As Range
Dim myPath As String
Dim appl As String
Dim myName As String
Dim lastRow As Integer
Dim lastColumn As Integer
Dim pro As Worksheet

”As Range” and ”As Worksheet” doesn't exist in LibreOffice Basic (nor
does it exist in Apache OpenOffice Basic, nor StarBasic).
Usually you declare those ”As Object”.

'frmPropertyEntry.Hide
dWidth = Me.Height

appl = "newinputs.xls"

myPath = ActiveWorkbook.Path
myName = ActiveWorkbook.Name

If myName <> appl Then
    MsgBox "I'm confused! Please close " & myName & " before trying to run
" & appl
    Exit Sub
End If

Set pro = ThisWorkbook.Worksheets("Processed")
pro.Activate

lastRow = pro.UsedRange.Rows.count
lastColumn = pro.UsedRange.Columns.count

*Set rng = pro.Range("b2", pro.Cells(lastRow, lastColumn).Address)*

The API is very different, so this line needs to be totally reworked.
So does most of the lines.
Excel for you, I guess. Or a lot of work…