Very basic help to get started

English
,
#1

I do my VBA Dev in Excel, but want to provide the same features to LibreOffice users of the game tools I am writing.

Things seemed to work fine, but then I keep hitting issues.

Reading here I see all sorts of advice, but cannot make use of it because I do not understand it.

I see I probably should include :

Option VBASupport 1

But that it has to be before executable code. I try and find how to do this… and cannot … but then the next run fails on that very line, expecting a sub? Even though I am sure I did not add the line between finding I need it, and it failing…

The following code is in a module… and is called from Excel when it starts up.
Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
’
’ OnStartUp
’ This Sub sets game specific stuff related to the Tool WB, and is called at start of every process

Public Sub OnStartUp()
Dim cOpSys As String, nRow As Long, nOK As Long, cData As String

Call HideUpdates

If pcSlash = “” Then
Set pbTool = ActiveWorkbook
Set psTool = pbTool.Sheets(“Tool”)
psTool.Visible = True

  Call HideTabs
  cOpSys = Application.OperatingSystem
  If InStr(1, cOpSys, "Windows") > 0 Then
     pcSlash = "\"
  ElseIf InStr(1, cOpSys, "Mac") > 0 Then
     pcSlash = "/"
  ElseIf InStr(1, cOpSys, "Linux") + InStr(1, cOpSys, "Unix") > 0 Then
     pcSlash = "/"
  Else
     Msg ("Unknown Operating System:" & cOpSys)
     pcSlash = "\"
  End If

  pcCurDrive = ActiveWorkbook.Path & pcSlash

This seems to work initially… but then I get errors on various lines. Even the line starting REM and the first starting '. I think the REM line was added by VBA/Excel …but I no longer have any confidence in that.

Hideupdates seemed to work… it is a Sub that I have now had to comment out all the lines because Application. anything seems not to work.

Public Sub HideUpdates()

’ Application.ScreenUpdating = False
’ Application.EnableEvents = False
’ Application.AskToUpdateLinks = False
’ Application.DisplayAlerts = False
’ Application.Calculation = pxlManual
’ Application.StatusBar = True
plAutoPhase = True

End Sub

Many of the answers in other posts assume some knowledge i do not have. I know this is a long question… but… help! please!

#3

like what ?

like which ?

AI can probaly guide you to realizing your XL code won’t work easily on LO without a bit of learning curve.
(it will also hallucinate sometimes :expressionless: )

then come back here with more specific questions.and error messages if any left,

#4

Use AI - Well that is not going to happen! Also you seem to agree that it leads to just understanding learning curve will not be trivial… which I already know. .

You asked: like what ?
Well, there’s a lot of it, although none really helps for reasons I specified.

Specific Question 1:
How do I include code that executes first. I am a user, not a developer, of LO. Where do I put it?

Specific Question 2:
Answers/Info often says to add an event trigger. I have no idea how. Such as OnStartApp

#5

The best option for those who know VBA and want to switch to LibreOffice Basic is the famous book by A. Pitonyak OpenOffice.org Macros Explained.odt V4.1

1 Like
#11

This is very useful, though sadly I see no specifics for VBA to Basic conversion - but now I have a manual with some of the answers to compare with VBA ‘learnt as I used’ knowledge.

Thanks

#6

The above works, but then after a while stops working. It is as if LO is forgetting Application (and I think Publics as well)

#7

Is there a way to easily tell whether running in Excel or LO - that works in both?

#8

Please do not use the “Suggest a solution” button. This button is not intended for ongoing discussions. Use the “Comment” button located after each post instead.

The object models of LibreOffice (LO) Calc and MS Excel are fundamentally different.
LO developers have done a great deal of work to emulate the behavior of Excel objects in the LO environment (in Option VBASupport 1 mode). Some (simple) Excel macros will work when run in the LO environment. The primary method for verifying functionality is experimentation.

2 Likes
#9

Like this? It was greyed out so I assumed inactive!

Thanks for the info

#10

The primary method for verifying functionality is experimentation.

Sure but sometimes something works and then shortly after does not. I think debug in LO is a little more likely to lose Publics and even Application… so if there was a way to determine whether was Excel or LO … but even then compile might object to the LO Specific code in Excel, and vice-versa.

I am going to re-ask, as they were in wrong place.

Specific Question 1:
How do I include code that executes first. I am a user, not a developer, of LO. Where do I put it?
(I think this is in a Module, before first Sub/Function. I have a module with all my Publics, would putting it here be good, or would it need to go in every Module?)

Specific Question 2:
Answers/Info often says to add an event trigger. I have no idea how. Such as OnStartApp

(This would help me a lot!)

#12

as a side note:

#13

It would be xlsm or ods

#14

image

What works for me with .ods in LO on windows gives this error on friends Ubuntu - seems to be running any Macro attached to a button - New_Position.LoadStatusMacro.

Any ideas why?

#15

Ok so if anyone else is stuck here in the future…

Tools->Customize->Events
Click on event and Macro and choose a Macro. Though seems has to be in the My Macros & Dialogs and not the VBA Project and cannot even call VBA Project code!

#16

From personal experience.
I started studying LO in 2018 after 25 years of professional work with MS Office. I can say that A. Pitonyak’s book was an excellent guide. In any case, I estimate the minimum period of familiarization with the LO object model is at least six months.
Regarding specific questions.
Let’s clarify your goals.

  • You want to create documents with macros that work in both Excel and Calc (this is difficult).
  • You want to create documents with macros that are designed to work effectively in Calc.
#17

I have been working towards the former, but by dividing the deployment into Calc-specific, Excel-specific and joint.

There are specific things I can do in Excel that I have not been able to do in Calc.

I’m brute-forcing by try, try, try… reading here and there… I find Documentation for Excel and Calc equally baffling.

Some I can work around, such as detecting a change in a worksheet and being able to know which cell. I’m on my third type of button, finally having a type that works in both Excel and Calc to my satisfaction.

Others I am still struggling with. Well simply cannot do, like call Macros from the Document from within ‘My Macros’, which is a bit of a pain because only Macros within My Macros work when assigned to events.

#18

Macros created by the user can be stored (excluding “exotic” cases):

  1. Within the document (essentially in the \Basic\ folder of the archive structure)
  2. In My Macros… (essentially in the ...\LibreOffice\4\user\basic folder on the disk)

We can assign event handlers (listeners) to both groups of macros.

If an .xlsm document is opened in LibreOffice, the event handlers of this document (created in VBA and contained in the project) will be called.

#19

We can assign event handlers (listeners) to both groups of macros.

But I can never get those in the document to work…

Just tested this (document finished loading event) with .ods and does not work… and with xlsm loses the assignment on save.

#20

Within the document (essentially in the \Basic\ folder of the archive structure)

image

I have my FoTS specific in the My Macros and VBA modules in VBAProject-> Modules

I am assuming the archive structure is within the xlsm file, which I am loathe to even look at, let alone edit!

#21

Use “Open Document” event.
OnLoad.ods (8.1 KB)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.