Re: [libreoffice-users] Macros in spreadsheets

I am having trouble following what is happening (and I guess I missed the first reply.

First, it looks like the cell D2 is calling a macro. 

If I understand, the cells are NOT recalculating so you are causing each one to run manually. What 
happens if you use 

Data > Calculate > Recalculate (or F9) to cause every cell to recalculate?

If that works, you can also write a macro to cause it to recalculate. I do not remember off hand 
how to do that (and I don't want to take the time to look it up), but, I did record this macro that 
uses the dispatcher to do it. I feel like it is cheating but it should work just fine.

sub recalculate
  document   = ThisComponent.CurrentController.Frame
  dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
  dispatcher.executeDispatch(document, ".uno:Calculate", "", 0, Array())
end sub


On Monday, January 02, 2023 20:22 EST, Stephen Morris <samorris@netspace.net.au> wrote:
 On 3/1/23 09:13, Michael Tiernan wrote:
> On 12/23/22 11:02 AM, Andrew Pitonyak wrote:
> > If you are looking to cause functions to recalculate,
>
> Thanks for the reply.
>
> Here's the thing. It's not that I want to force a recalculate, it's
> that I have to in order to get the macros to respond.
>
> Here's an example: (What I expect when the spreadsheet finishes opening.)
>
> /*Cell*//*
> */     /*Contents*//*
> */     /*Display*//*
> */
> D1
>     192.168.17.231/24
>     192.168.17.231/24
> D2
>     =ipwithoutmask(D1)
>     192.168.17.231
> D3
>     =ipmask(D1)
>     255.255.255.0
> D4
>     =ipmasklken(D3)
>     24
>
> Now, when I actually do open the spreadsheet, it says:
>
> /*Cell*//*
> */     /*Contents*//*
> */     /*Display*//*
> */
> D1
>     192.168.17.231/24
>     192.168.17.231/24
> D2
>     =ipwithoutmask(D1)
>     #NAME?
> D3
>     =ipmask(D1)
>     #NAME?
> D4
>     =ipmasklken(D3)
>     #NAME?
>
> So, if I say it differently, it *seems* like the Macros aren't run
> when the spreadsheet is opened.
>
> The macro library has a "Main" entry that is empty. If I choose
> "Tools" -> "Macros" -> "Run macro" and then select "Main" and execute
> it, there's no visible change.
>
> BUT, if I go into the contents of "D2" and add a space at the end of
> the formula "=ipwithoutmask(D1)" to read "=ipwithoutmask(D1) " and hit
> return and the macro executes and displays the proper value.
>
> HOWEVER, none of the other cells change so I have to go into each one
> and do the same sort of thing, add a space at the end of the line or
> between the macro and the parenthesis, each time, hit return and it
> works properly.
>
> This is obviously not the correct way it should work.
>
> Hence my query about "starting" macros when the document is opened.
>
> So, that leads to, am *I* doing something wrong?
>
> Thanks for everyone's time.
Not being familiar with Calc's macro functionality, what you are showing
as your macro references looks like a formula, so I would check two
things, which you may have already checked as I also haven't been
following this thread, check your macro security options as they may be
preventing your macros from running as the default option is to prevent
unsigned macros from running. Also check your formula "recalculate of
load" options as they may be set to not recalculate.

With your main entry being empty I would expect running that to actually
do nothing.
Looking at the existing macro definitions and the way they are shown
when placing the cursor in them, I would assume your macros are within a
module, so when you reference the macro in your formula, do you need to
reference is as, for example, <module name>.ipwithoutmask(D1)?

regards,
Steve

 

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy