[Solved] Faster/more efficient macro to hide columns?

Creating a macro - Writing a Script - Using the API

[Solved] Faster/more efficient macro to hide columns?

Postby equaad » Sun Aug 05, 2018 3:22 am

Hi everyone,

Gratefully building on posts I found here on how to hide a series of columns, I am finding that the macros I put together from them works, but take a fair amount of time.

Probably because the way I wrote the code is not terribly efficient, or maybe the function I am using is not very fast.

I suspect there's a better way.

If someone has input, I would welcome it. Code is below.

The first one keeps the first 7 columns of the spreadsheet (starting at A1) visible, then hides the next 42, then shows the next 7, then hides the next 42, etc, until some number of columns that I have hard-coded into the subroutine.

The second one keeps the first 7 columns visible (starting at the column that contains the selected cell), and then hides the next 42 columns, and that's it.

Although I'm not sure the "why" is important in speeding up the subroutines, in case it is, here's why I want to do this: I have a spreadsheet that I use to tally information for each day of the week. Each day of the week is 7 columns wide. In the first sub, I want to be able to hide everything but the first day of the week for all the weeks in the spreadsheet (I usually do about 6-8 weeks at a time). In the second sub, I want to hide everything but the first day of the current week (so, hiding the other days of just a single week), where the selected cell is on the first column of the first day of the week that I want to hide.

I hope that makes sense.

Many thanks to anyone who has a suggestion of how I might do this faster.

- Ellen


--------------------------------

Code: Select all   Expand viewCollapse view
sub hide_all_weeks
   rem ----------------------------------------------------------------------
   rem define variables
   dim document   as object
   dim dispatcher as object
   rem ----------------------------------------------------------------------
   rem get access to the document
   document   = ThisComponent.CurrentController.Frame
   dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
   
   rem set the properties for moving right
   dim args2(1) as new com.sun.star.beans.PropertyValue
   args2(0).Name = "By"
   args2(0).Value = 1
   args2(1).Name = "Sel"
   args2(1).Value = false

   rem make thecurrent column counter
   dim cCol as integer
   cCol = 1
   mCol = cCol
   rem Ellen's hack so you don't keep going forever, but for just nCol columns
   nCol = 500
   
   
   rem goto the first column
   dim args1(0) as new com.sun.star.beans.PropertyValue
   args1(0).Name = "ToPoint"
   args1(0).Value = "$A$1"
   
   dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
   
   rem loop until you get to the max
   Do Until cCol > nCol   

        if mCol > 6 then
         rem ----------------------------------------------------------------------
          dispatcher.executeDispatch(document, ".uno:HideColumn", "", 0, Array())
   
      End if
     
      rem goto the right nad increment the column counter
      dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args2())
      cCol = cCol + 1
      rem show the first 7 columns, then hide the next 42 columns, then show 7 columns, then hide the next 42, etc
      mCol = cCol mod 49
   
   Loop

End sub


sub hide_one_week
   rem ----------------------------------------------------------------------
   rem define variables
   dim document   as object
   dim dispatcher as object
   rem ----------------------------------------------------------------------
   rem get access to the document
   document   = ThisComponent.CurrentController.Frame
   dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
   
   rem get the current column - for this to work, have to select a cell in the first column of that week's Monday
   currCol = ThisComponent.CurrentSelection.CellAddress.Column

   rem set the properties for moving right
   dim args2(1) as new com.sun.star.beans.PropertyValue
   args2(0).Name = "By"
   args2(0).Value = 1
   args2(1).Name = "Sel"
   args2(1).Value = false

   rem make thecurrent column counter
   dim cCol as integer
   cCol = 1
   mCol = cCol
   rem Stop when reach the end of the week
   nCol = 49
   
   
   
   
   rem goto the first column
   rem dim args1(0) as new com.sun.star.beans.PropertyValue
   rem args1(0).Name = "ToPoint"
   rem args1(0).Value = "$A$1"
   
   rem I commented out the line below bc I *don't* want to go to the first column. I want to start where we are.
   rem dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
   
   rem loop until you get back to the selected cell
   Do Until cCol > nCol
      
     
      rem hide if the cell value is 0
      rem if ThisComponent.CurrentSelection.string <> "" and ThisComponent.CurrentSelection.value = 0 then
   
        if mCol > 6 then
         rem ----------------------------------------------------------------------
          dispatcher.executeDispatch(document, ".uno:HideColumn", "", 0, Array())
   
      End if
     
      rem goto the right nad increment the column counter
      dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args2())
      cCol = cCol + 1
      mCol = cCol mod 49
   
   Loop

End sub
Last edited by Hagar Delest on Mon Aug 06, 2018 12:17 pm, edited 2 times in total.
Reason: tagged solved
OpenOffice 4.1.3 on Windows 7
equaad
 
Posts: 10
Joined: Wed Aug 01, 2018 1:44 pm

Re: Faster/more efficient macro to hide series of columns?

Postby FJCC » Sun Aug 05, 2018 6:49 am

This leaves the first 7 columns of the active sheet visible, hides the next 42 and repeats that pattern for the first 490 columns
Code: Select all   Expand viewCollapse view
oSheet = ThisComponent.CurrentController.ActiveSheet
oCols = oSheet.Columns
for i = 7 to 489
   if i mod 49 > 6 then
      oColumn = oCols.getByIndex(i)
      oColumn.isVisible = False
   end if
next i


This starts at the current cell, leaves its column and the next 6 columns visible and hides the next 42
Code: Select all   Expand viewCollapse view
oSheet = ThisComponent.CurrentController.ActiveSheet
oCols = oSheet.Columns
oSel = ThisComponent.CurrentSelection
ColIdx = oSel.CellAddress.Column
for i = ColIdx + 7 to ColIdx + 48
   oColumn = oCols.getByIndex(i)
   oColumn.isVisible = False
next i

Note that there is no checking that the current selection is a cell. The macro assumes the user knows what she is doing!
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7018
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Faster/more efficient macro to hide series of columns?

Postby Zizi64 » Sun Aug 05, 2018 7:33 am

You must WRITE your macros based on the API functions, instead of RECORDING the macros - if you want work efficiently with the macros. See FJCC's sample macro above: those lines contain API functions.
The API functions can work much faster than the recorded (Dispatcher related) macros.

One of the reasons, why the Dispatcher related macros are slower: a simple step - like the moving the cursor right by one column - requires the fully re-rendering of the screen content graphically for every "GoRight" step in the loop. The re-rendering will be executed only once when you use th API based macros.

Code: Select all   Expand viewCollapse view
dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args2())

You not need the re-rendering the sheet at every "moving to" step.
You not need even the "moving to" step - if you can get and control the columns by another (simpler) way.

The code list will be shorter, the running speed will be much faster by usage of the API functions. I suggest you: study and use the API functions.
( API : Application Programming Interface )

Start with Andrew Pitonyak' free (and excellent) macro programming books:
http://www.pitonyak.org/oo.php
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.0 and AOO4.1.5
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: 7712
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Faster/more efficient macro to hide series of columns?

Postby Lupp » Sun Aug 05, 2018 2:18 pm

Preliminary remark:
I used spreadsheets a lot when I was not yet retired - and I NEVER relied on user code then though I was somehow fascinated by programming since about 1965. I simply designed the spreadsheets in a way that didn't require additional code.
(Well, for a short time using sheets in MS Works for DOS I created and edited recorded macros -actually being key-logged macros then- but I finally found it was wasted time.)
Nowadays, since I am retired, I sometimes take an opportunty to dive again a bit into programming. Its for basic interest in the field, and the poor language Basic often meets well my approach. I sometimes like to do things the tinkerer way without using much prefab.
Nonetheless I don't want to rewrite next to identical core code again and again because it was everytime too specialised to also be able to serve in the next similar case. In addition: If I just give a few lines of Q&D code solving a highly specialised task, I'm afraid to give a bad example to beginners though I am rather experienced.

In the current case I made the attached demo which may help the questioner, but which also is a kind of proposal concerning programming for Calc: If you need user code at all, first try to find a better structure for your sheets avoiding that need. In case of no success so far, write clean code. Of course I am interested in criticism and suggestions.

The next step towards clean reusable code as induced by this case would require a rather elaborate system of helper routines for general parameter management. To pass parameters to Sub is a kind of challenge, isn't it? Doing it Q&D every time again is boring and inefficient even beyond my standards.

The demo was made with (still buggy) LibO V 6.1.0.1 and tested with AOO V 4.1.5.
Attachments
hideThem.ods
(18.16 KiB) Downloaded 38 times
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2313
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Faster/more efficient macro to hide series of columns?

Postby Villeroy » Sun Aug 05, 2018 3:26 pm

menu:Data>Group>group columns without macro
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26267
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Faster/more efficient macro to hide series of columns?

Postby equaad » Sun Aug 05, 2018 11:27 pm

Ah, thank you!

I had a feeling there was a better way.

I downloaded the book Zizi64 mentioned, and also tried typing this new code from FJCC into a macro, wrapped in a sub, like so:

sub hide
oSheet = ThisComponent.CurrentController.ActiveSheet
oCols = oSheet.Columns
for i = 7 to 489
if i mod 49 > 6 then
oColumn = oCols.getByIndex(i)
oColumn.isVisible = False
end if
next i
end sub

But when I try to run macro "hide" nothing happens. I have a feeling I am missing something really obvious. I downloaded the book that Zizi64 recommended but I couldn't find it there.

I did see the other downloadable file, and am grateful to "Lupp" for taking the time to put that together. That said, I'd love to know how to make that code from FJCC work, as I see a lot of code that looks like that here and knowing what I need to wrap around that would open up a whole lot of code to me.

Thank you all, for your help!

- Ellen
OpenOffice 4.1.3 on Windows 7
equaad
 
Posts: 10
Joined: Wed Aug 01, 2018 1:44 pm

Re: Faster/more efficient macro to hide series of columns?

Postby equaad » Sun Aug 05, 2018 11:36 pm

Oh forgive me. I just tried running it again and it worked. It took about about 25 seconds to run so I assumed it hadn't kicked off. But I just look again and it had hidden the requisite columns.

Thank you all,

- Ellen
OpenOffice 4.1.3 on Windows 7
equaad
 
Posts: 10
Joined: Wed Aug 01, 2018 1:44 pm

Re: Faster/more efficient macro to hide series of columns?

Postby equaad » Mon Aug 06, 2018 12:25 am

Villeroy wrote:menu:Data>Group>group columns without macro


This did it. Very fast. Simple.

Thank you to all.

- Ellen
OpenOffice 4.1.3 on Windows 7
equaad
 
Posts: 10
Joined: Wed Aug 01, 2018 1:44 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: susanr and 5 guests