[Solved] Can't run the macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
beyfendi
Posts: 3
Joined: Tue Nov 05, 2019 8:50 am

[Solved] Can't run the macro

Post by beyfendi »

The macro I wrote below didn't work, it gave an error, I can't find where I'm giving errors or I'm making a mistake, can you please help me. thank you.

Code: Select all

Sub Frmlkpyl()
ActiveSheet.Unprotect Password:="8453"
Range("G1").Select
Selection.FormulaR1C1 = "=ADDRESS(7,MATCH(""ÜCRETLİ GÜN TOP."",R[5],0)-1)"
Range("G2").Select
Selection.FormulaR1C1 = "=ADDRESS(101,MATCH(""ÜCRETLİ GÜN TOP."",R[4],0)-1)"
Range("A7:" & [G1].Value).Select
Selection.Copy
Selection.AutoFill Destination:=Range("A7:" & [G2].Value)
Range("G1:G2").Value = ""
Range("b6").Select
ActiveSheet.Protect Password:="8453"
End Sub
Last edited by Hagar Delest on Tue Nov 12, 2019 10:20 am, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1.6
Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: PLEASE HELP I CAN'T RUN THE MACRO

Post by RoryOF »

What error message do you get, and whereabouts in the code does it occur?
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: PLEASE HELP I CAN'T RUN THE MACRO

Post by Zizi64 »

_______________________
OpenOffice 3.2
Your code seems as an MS VBA macro. The OpenOffice 3.2 is a very old and obsolete version.

The OpenOffice and the newer AOO are not compatible with MS VBA macros. You must rewrite them based on the StarBasic and the AOO API. (API: Application Programming Interface)

The LibreOffice can run some MS VBA macros by usage the compatibility option switch:

Code: Select all

Option VBASupport 1
in the first line of the code.
But LO has not 100% compatibility with the VBA macros, therefore it always better to use the native tools for controlling the office suite:
the supported programming languages and the API.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: PLEASE HELP I CAN'T RUN THE MACRO

Post by JeJe »

Putting Option VBASupport 1 at the top as suggested and running on a blank document:

In my OpenOffice it runs with no error - this will be because it skips lines it knows are VBA and can't run

In LibreOffice the first error is given on this line:

Range("A7:" & [G1].Value).Select

Its generally better to use OOBasic not VBA... a macro recorder is available to help you, though learning the API is better.

Edit: corrected mistake about Openoffice not running Range("G1:G2").Value = "", it does.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Can't run the macro

Post by Lupp »

As already mentioned you need to set Option VBAsupport 1 as the first line (except REM) of the module you put the macro in.

Your formulas use the comma as the parameter ("Function") separator (on the Calc level). Running any AOO you need to replace them by semicolons.
[[In LibreOffice you can set the alternative character under Options (Not recommended. Stick to the semicolon.)]]
Otherwise the formulas you write into the helper range G1:G2 will not be parsed correctly.

The mentioned formulas also contain all-column-range addressing which is not supported in AOO, and only in rather recent LibO.

[.[The macro is very strange. Do you actually need it? Why don't you rewrite it to AOO/LibO Basic and API?]]
(AOO V 4.1.5 was not able to convert RC-addressing.)

The relative RC-style row addresses ("R[5]", "R[4]" respectively) should anywaybe replaced by their absolute equivalents ("R6" in both cases). After all the cells they have to work in are identified by their names. No need to reference relative!
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Can't run the macro

Post by Lupp »

Are you ("beyfendi" Why did you omit the middle "e"?) aware of the fact that a second run of your Sub (if assumed it's rectified) would NOT erase the additionally filled columns to the right if the keyword was moved to the left since the first run? Is this actually what you want?
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
beyfendi
Posts: 3
Joined: Tue Nov 05, 2019 8:50 am

Re: Can't run the macro

Post by beyfendi »

first of all thank you for your help and the version I am using office 4.1.6

What I want to do is have the formulas in the cells between A7 and H7 and I want to select A7 to H7 downward and make H100 formula refresh.
OpenOffice 4.1.6
Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Can't run the macro

Post by Zizi64 »

first of all thank you for your help and the version I am using office 4.1.6
Please update your signature in this forum:

User Control Panel - Profile TAB - Edit signature subTAB
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Can't run the macro

Post by Lupp »

For what the MATCH() calls if you already know you want to include columns up to H (A7:H7)?

Well. I don't know anything about Excel-VBA, and the code you posted surely was recorded without much of considerations.

Insisting on doing such a strange thing by "macro" in AOO, and the question from the first line above aside, you may use the following code:

Code: Select all

Sub sameInAoo_BasicAndApi()      REM Avoiding strange detours 
doc      = ThisComponent
currC    = doc.CurrentController
sheet    = currC.ActiveSheet     REM Doubtable! Why not get the sheet by its name?
ultC     = 1023                  REM Column index 0-based
endR     = 100                   REM Row index 0-based
lookupRg = sheet.GetCellRangeByPosition(0, 5, ultC, 5) 
           REM That's A6:AMJ6 (aka 6:6 in full-row-syntax).
lookupDA = lookupRg.getDataArray
findStr  = "ÜCRETLİ GÜN TOP."
For c=0 To ultC                  REM This works instead of the doubled MATCH calls
                                 REM in the original Sub without formula hokum.
  If lookupDA(0)(c)=findStr Then Exit For
Next c
If c>ultC Then
  Print "No match!"                REM Find a better Exit!
  Exit Sub
End If
rgToFill = sheet.getCellRangeByPosition(0, 6, c, endR)
contCell = sheet.getCellRangeByName("B6")
sheet.Unprotect("8453")
rgToFill.fillAuto(0, 1) REM Assuming that's what MS Excel AutoFill does. You may need
                        REM rgToFill.fillSeries(0, 0, 0, 0, 0) (constant series).
currC.select(contCell)
sheet.protect("8453")
End Sub
It looks longer, but is basically clearer, and it avoids the intricate usage of a Calc formula.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
beyfendi
Posts: 3
Joined: Tue Nov 05, 2019 8:50 am

Re: Can't run the macro

Post by beyfendi »

Lupp wrote:For what the MATCH() calls if you already know you want to include columns up to H (A7:H7)?

Well. I don't know anything about Excel-VBA, and the code you posted surely was recorded without much of considerations.

Insisting on doing such a strange thing by "macro" in AOO, and the question from the first line above aside, you may use the following code:

Code: Select all

Sub sameInAoo_BasicAndApi()      REM Avoiding strange detours 
doc      = ThisComponent
currC    = doc.CurrentController
sheet    = currC.ActiveSheet     REM Doubtable! Why not get the sheet by its name?
ultC     = 1023                  REM Column index 0-based
endR     = 100                   REM Row index 0-based
lookupRg = sheet.GetCellRangeByPosition(0, 5, ultC, 5) 
           REM That's A6:AMJ6 (aka 6:6 in full-row-syntax).
lookupDA = lookupRg.getDataArray
findStr  = "ÜCRETLİ GÜN TOP."
For c=0 To ultC                  REM This works instead of the doubled MATCH calls
                                 REM in the original Sub without formula hokum.
  If lookupDA(0)(c)=findStr Then Exit For
Next c
If c>ultC Then
  Print "No match!"                REM Find a better Exit!
  Exit Sub
End If
rgToFill = sheet.getCellRangeByPosition(0, 6, c, endR)
contCell = sheet.getCellRangeByName("B6")
sheet.Unprotect("8453")
rgToFill.fillAuto(0, 1) REM Assuming that's what MS Excel AutoFill does. You may need
                        REM rgToFill.fillSeries(0, 0, 0, 0, 0) (constant series).
currC.select(contCell)
sheet.protect("8453")
End Sub
It looks longer, but is basically clearer, and it avoids the intricate usage of a Calc formula.

was, very very thank you.
OpenOffice 4.1.6
Windows 10
Post Reply