Re: [libreoffice-users] Macros in spreadsheets
OK, found the sheet. Had to copy it from the machine I used the sheet on last month as can't access physically the machine ATM. The sheet was working fine on that machine but produces the symptoms you mention on this laptop. I will see if I can find the cause. Steve On 4/01/23 8:51 am, Steve Edmonds wrote: I understand what you are seeing, I had the same issue with a macro for a function, I called it money() for rounding financial tax calculations appropriately. At some point when I updated LO it just stopped calculating displaying as you describe and I had to go through the same rigmarole you are experiencing for it to recalculate. Not sure if I resolved the issue or gave up, I will need to dig the sheet out. Steve On 3/01/23 11:13 am, 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. -- To unsubscribe e-mail to: users+unsubscr...@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
Re: ***SPAM*** Re: [libreoffice-users] Macros in spreadsheets
That happens to me with some list emails, easiest solution is to add a rule to prevent classing as spam. Steve On 5/01/23 5:36 am, Tim-L wrote: Your mail list emails are now being marked as spam. Why, I cannot find out. On 1/3/23 14:51, Steve Edmonds wrote: I understand what you are seeing, I had the same issue with a macro for a function, I called it money() for rounding financial tax calculations appropriately. At some point when I updated LO it just stopped calculating displaying as you describe and I had to go through the same rigmarole you are experiencing for it to recalculate. Not sure if I resolved the issue or gave up, I will need to dig the sheet out. Steve On 3/01/23 11:13 am, 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. -- To unsubscribe e-mail to: users+unsubscr...@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
Re: ***SPAM*** Re: [libreoffice-users] Macros in spreadsheets
Your mail list emails are now being marked as spam. Why, I cannot find out. On 1/3/23 14:51, Steve Edmonds wrote: I understand what you are seeing, I had the same issue with a macro for a function, I called it money() for rounding financial tax calculations appropriately. At some point when I updated LO it just stopped calculating displaying as you describe and I had to go through the same rigmarole you are experiencing for it to recalculate. Not sure if I resolved the issue or gave up, I will need to dig the sheet out. Steve On 3/01/23 11:13 am, 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. -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Macros in spreadsheets
I understand what you are seeing, I had the same issue with a macro for a function, I called it money() for rounding financial tax calculations appropriately. At some point when I updated LO it just stopped calculating displaying as you describe and I had to go through the same rigmarole you are experiencing for it to recalculate. Not sure if I resolved the issue or gave up, I will need to dig the sheet out. Steve On 3/01/23 11:13 am, 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. -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Macros in spreadsheets
Recalculate: Force recalculate - F9 by default, but could be reassigned. OR on the menu at Data --> Calculate --> Recalculate There's also "recalculate hard" which is (probably misunderstood by me) like the first calculate on load. CTRL-F9 or the same menu as recalculate. ___ Macros: Tools --> options --> Libreoffice --> Security --> Macro Security On Fri, Dec 23, 2022 at 9:51 AM Michael Tiernan wrote: > I have a couple of BASIC macros in a spreadsheet. > > I keep thinking that there must be a "init" function that isn't being > called because each time I open the spreadsheet, the macros don't run. I > have to mess about a bit and then force each cell to recalculate > manually. (I'm sure that there's a global 'recalculate' button/tool > somewhere but that's secondary to the problem.) > > I know there's a lot of documentation out there on macros (almost too > much) but I'm not sure where to start reading, does anyone have a > pointer that would address, not how to create a macro but how to > incorporate it to become automatic? > > Thanks. > > -- ><< MCT >> Michael C Tiernan. http://www.linkedin.com/in/mtiernan >Non Impediti Ratione Cogatationis >Women and cats will do as they please, and men and dogs > should relax and get used to the idea. -Robert A. Heinlein > > > -- > To unsubscribe e-mail to: users+unsubscr...@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 > -- To unsubscribe e-mail to: users+unsubscr...@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
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
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, .ipwithoutmask(D1)?
regards,
Steve
--
To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Macros in spreadsheets
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, .ipwithoutmask(D1)? regards, Steve -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Macros in spreadsheets
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. -- << MCT >> Michael C Tiernan.http://www.linkedin.com/in/mtiernan Non Impediti Ratione Cogatationis Women and cats will do as they please, and men and dogs should relax and get used to the idea. -Robert A. Heinlein -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Macros in spreadsheets
No computer access Right now so everything I say is going to be a guess. If you are looking to cause functions to recalculate, I think that might be a setting not related to macros. Because you can turn off auto recalculation. Also, you can force a full update. I know it's on the menu. I don't remember the key strokes but I know it's there something like tools recalculator something or maybe it's update all I guess That said, you can associate events to macros. I do believe. I don't do it often, but I believe you can. So you can set a macro to run when a document opens and maybe that's what you want. I don't remember offhand how to get to events. You can associate a macro to run when an event occurs, but I have done it. I designed some macros for a company that wanted things to auto calculate when they opened a template. For me, learning macros was easier by simply looking at examples and then modifying them for my own uses. But you're asking how to cause macros to run based on certain conditions, not how do you accomplish a specific task. Best of luck. Get BlueMail for Android On Dec 23, 2022, 10:51 AM, at 10:51 AM, Michael Tiernan wrote: >I have a couple of BASIC macros in a spreadsheet. > >I keep thinking that there must be a "init" function that isn't being >called because each time I open the spreadsheet, the macros don't run. >I >have to mess about a bit and then force each cell to recalculate >manually. (I'm sure that there's a global 'recalculate' button/tool >somewhere but that's secondary to the problem.) > >I know there's a lot of documentation out there on macros (almost too >much) but I'm not sure where to start reading, does anyone have a >pointer that would address, not how to create a macro but how to >incorporate it to become automatic? > >Thanks. > >-- > << MCT >> Michael C Tiernan. http://www.linkedin.com/in/mtiernan > Non Impediti Ratione Cogatationis > Women and cats will do as they please, and men and dogs >should relax and get used to the idea. -Robert A. Heinlein > > >-- >To unsubscribe e-mail to: users+unsubscr...@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 -- To unsubscribe e-mail to: users+unsubscr...@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
[libreoffice-users] Macros in spreadsheets
I have a couple of BASIC macros in a spreadsheet. I keep thinking that there must be a "init" function that isn't being called because each time I open the spreadsheet, the macros don't run. I have to mess about a bit and then force each cell to recalculate manually. (I'm sure that there's a global 'recalculate' button/tool somewhere but that's secondary to the problem.) I know there's a lot of documentation out there on macros (almost too much) but I'm not sure where to start reading, does anyone have a pointer that would address, not how to create a macro but how to incorporate it to become automatic? Thanks. -- << MCT >> Michael C Tiernan. http://www.linkedin.com/in/mtiernan Non Impediti Ratione Cogatationis Women and cats will do as they please, and men and dogs should relax and get used to the idea. -Robert A. Heinlein -- To unsubscribe e-mail to: users+unsubscr...@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
