[Solved] Can't run the macro

Creating a macro - Writing a Script - Using the API

[Solved] Can't run the macro

Postby beyfendi » Tue Nov 05, 2019 9:00 am

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   Expand viewCollapse view
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
beyfendi
 
Posts: 3
Joined: Tue Nov 05, 2019 8:50 am

Re: PLEASE HELP I CAN'T RUN THE MACRO

Postby RoryOF » Tue Nov 05, 2019 10:56 am

What error message do you get, and whereabouts in the code does it occur?
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29912
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: PLEASE HELP I CAN'T RUN THE MACRO

Postby Zizi64 » Tue Nov 05, 2019 11:09 am

_______________________
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   Expand viewCollapse view
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; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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
Zizi64
Volunteer
 
Posts: 8549
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: PLEASE HELP I CAN'T RUN THE MACRO

Postby JeJe » Tue Nov 05, 2019 12:01 pm

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.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 629
Joined: Wed Mar 09, 2016 2:40 pm

Re: Can't run the macro

Postby Lupp » Tue Nov 05, 2019 2:20 pm

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 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2564
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Can't run the macro

Postby Lupp » Tue Nov 05, 2019 4:02 pm

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 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2564
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Can't run the macro

Postby beyfendi » Fri Nov 08, 2019 4:40 pm

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
beyfendi
 
Posts: 3
Joined: Tue Nov 05, 2019 8:50 am

Re: Can't run the macro

Postby Zizi64 » Fri Nov 08, 2019 5:25 pm

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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
Zizi64
Volunteer
 
Posts: 8549
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Can't run the macro

Postby Lupp » Fri Nov 08, 2019 6:35 pm

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   Expand viewCollapse view
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 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2564
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Can't run the macro

Postby beyfendi » Mon Nov 11, 2019 8:02 am

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   Expand viewCollapse view
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
beyfendi
 
Posts: 3
Joined: Tue Nov 05, 2019 8:50 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests