VBA
I’m new here and I want to go with LibreOffice Calc in stead of Excel.
I’m using some macro’s in VBA that I would not miss in Calc.
Example :
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
If Target = Range(“A1”) Then
If Mid(Target, 1, 2) = “• “ Then
NaamTabblad = Mid(Target, 3, Len(Target) – 2) ‘Wanneer de kop begint met • dan moet het • eraf
Else
NaamTabblad = Target ‘Bij het ontbreken van het • dan de complete inhoud
End If
ActiveSheet.Name = NaamTabblad
End If
End Sub
When I fill A1 then the Name of the tab should change to the input of A1.
I would learn basis, but I dont the steps.
Can anybody learn the steps to go from VBA to Basis/Python/Java…
LibreOffice is providing this access mainly via uno. In addition there are only a few specific predefined variables and functions. As far as I understand with VBA everything seems to be integrated directly. Please note: I am not an expert concerning programming for LibO, and I know next to nothing about VBA, to put it in clear words.
There is partial support for VBA constructs in LibO BASIC (option VBAsupport 1), however, and it is geting improved, but still very limited.
I can see three ways for you.
1. Abandon the usage of old subs and find new ways to do things. To change a sheet’s name e.g. entering something into a cell is a way to achieve that goal, very many users never miss.
2. Try if the present level of VBA support is already sufficient.
3. Develop your “some macros” anew for LibO and take this as an opportunity to learn. There are some documents you may start with linked in this sticky thread.
To give an example for the different ways of LibO, I attach a Calc document containing a BASIC sub doing the paradox renaming of sheets. It is also demonstrating a second solution based on a side effect of a user function. If wanting at all to rename a sheet by editing a cell I would surely prefer the second way.
| Attachment | Size |
|---|---|
| lof13357StrangeSheetRenaming001.ods | 30.32 KB |
Recording macros is normally not recommended. Even with Excel it’s not a good way to create macros, but if it’s simple enough you probably can get away with it in Excel.
Would you be able to always save your new csv file as a .ods file to a known location on your PC? If you do this then you an use the INDIRECT function to refer to values from your csv file. This would avoid using macros.
Example put this file name in cell A1 of the spreadsheet where you want the data to be copied. Then in a cell where you want to copy the data use
=INDIRECT("'file:///" & $A$1 & "'#$Sheet1.A4" ).
Or you could include the reference filename in the INDIRECT function,
=INDIRECT("'file:///D:/UserData/Mark/Desktop/Untitled 1.ods'#$Sheet1.A4" ).
Note the single quotes ‘ inside the above strings are required.
After changing the reference file you might need to use ctrl-shift-F9 to force recalculation of the values in your template. The reference file also needs to be open in calc, and it needs to be saved to a local file.
If you know VBA then it should be easy to start with Basic in LibreOffice. Although you will find all the objects are different structures, basic language is almost identical.
Example below based on your VBA.
From Calc, Tools, Macros, Organise Macros, LibreOffice Basic.
To put your macro in your workbook, select the workbook in the left menu, select standard, then use New to create a module.
To set up the event, right click on the tab of your sheet, select “Sheet Events”, choose “Content changed”, Assign Macro, select the macro name and click OK.