[Solved] Macros in Calc

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
sts8500man
Posts: 8
Joined: Sat Oct 13, 2018 6:23 pm

[Solved] Macros in Calc

Post by sts8500man »

I have done tons of searching on the net for information that will help me decide to download and use Open Office. I am pretty tired of dealing with the whelms of Microsoft, particularly where it comes to Excel. I am contemplating going to Open Office specifically for the use of "Calc"; however, I am having some trepidation about doing it. I am afraid I will get into a situation where I use tons of my time without accomplishing my objectives of moving all my spreadsheets from Excel to Calc. If it were just the spreadsheets with internal formulas etc. I don't think I would hesitate. That is not all there is to it. I have tons of VBA macros attached to each spreadsheet. I do not look forward to taking the time to re-create each of those macros in Open Office Calc. If Calc can use my VBA macros then I am just fine with going to Calc but as I search the net for information about this, I am seeing conflicting claims about it. Also, I cannot find any instructions about building macros for Calc spreadsheet applications that made sense to me; I believe that is "Starbasic", right? Does anyone have any words of wisdom about this subject before I launch into trying to use Calc?

Thank you for reading this, even if you cannot help.

sts8500man
Last edited by sts8500man on Wed Nov 14, 2018 5:34 pm, edited 1 time in total.
OpenOffice 4.1.5
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: macros in calc

Post by FJCC »

Unless your VBA macros are very simple, they will not work in OpenOffice. I hear that LibreOffice, a product that forked from OpenOffice several years ago, has somewhat better support for VBA code. You could try that, but honestly, if you have tons of macros and they are non-trivial, you are headed for a lot of work to get them running in anything but Excel. Translating code is much like translating a spoken language. Tiny snippets passed to an on line translator work ok to get the general sense of the statement but anything more than that is very frequently a barely intelligible mess.

You might want to investigate whether you actually need the macros. We often see people trying to use macros where better data layout and use of formulas can solve the problem and that is much easier to maintain than evil macro code. If you want to consider that, try asking for a formula based solution for something you currently do with a macro.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
sts8500man
Posts: 8
Joined: Sat Oct 13, 2018 6:23 pm

Re: macros in calc

Post by sts8500man »

FJCC,

Thank you for your response. It was not what I wanted to see but was what I expected to see. I admit that I have not explored the possibility of conducting my spreadsheets without the use of extensive macros, at least not in anything other than Excel. Since I do not know how Open Office Calc works, maybe it is a good idea for me to download Open Office and try using Calc for a couple of my simple spreadsheets. If I can make it work for a couple simple spreadsheets, making I can figure out how to extend that to those more complicated. I am going to give that some thought.

Thank you again for your response.

sts8500man
OpenOffice 4.1.5
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Macros in Calc

Post by JeJe »

I'll just add that you'll probably find learning to write macros in OOBasic a lot harder than VBA... this is based on my experience of starting with VBA in word and moving to Writer.

You'll see from the size of this place that not many people are doing macro programming in OO - the steep learning curve is probably the reason. Not what you want to hear - but there it is. There are also some things in VBA that aren't in OOBasic.

On the plus side OO allows access to things in code that VBA doesn't (almost every aspect of the suite) - which means your code can potentially be more powerful: that's one reason why I'm using it.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
sts8500man
Posts: 8
Joined: Sat Oct 13, 2018 6:23 pm

Re: Macros in Calc

Post by sts8500man »

JeJe,

Thank you for your input. I will attempt to organize my spreadsheets in such a manner as to reduce and simplify my needs for macros - VBA or otherwise. That is the reason I am willing to spend extra time trying to do this in Open Office. My whole motivating factor is for simpler means of doing dynamic charting. If I understand correctly, Calc's default is for dynamic charting. What MS has done to dynamic charting in Office 365 should be criminal. It is horrible what they have done to that function!

sts8500man
OpenOffice 4.1.5
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macros in Calc

Post by Villeroy »

What is "dynamic charting"? What I can find on the web is trivial. Spreadsheet charts are dynamic by design. Of course you have to know how to use a spreadsheet adequately.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
sts8500man
Posts: 8
Joined: Sat Oct 13, 2018 6:23 pm

Re: Macros in Calc

Post by sts8500man »

Dynamic charting = chart updating simultaneously with data input into the spreadsheet.

Excel's charts are not dynamic by default. One has to go through various hoops to make them dynamic.

According to the research I did on Open Office Calc, the charts are dynamic by default. I cannot say for sure yet since I have yet to try using Calc, but that is what I found when doing some research on it.

I had a bunch of charts attached to spreadsheets that worked just fine with previous versions of MS Office Excel but Microsoft made changes to the charting function in Office 365 that makes it almost impossible to figure out how to do dynamic charting. it just seems like they on purpose made something simple into something much more difficult than it should be for no reason that I can ascertain.

I have downloaded Open Office Suite and will soon work at using Calc. I still have not found a Users Manual for Open Office. If you know how to get a Users Manual for it, particularly with respect to Calc and using Macros in calc, I would appreciate knowing how to get that manual.

sts8500man
OpenOffice 4.1.5
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macros in Calc

Post by FJCC »

Old but still useful: User Guides
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Macros in Calc

Post by Zizi64 »

I still have not found a Users Manual for Open Office. If you know how to get a Users Manual for it, particularly with respect to Calc and using Macros in calc, I would appreciate knowing how to get that manual.
Here are some publications for the LibreOffice:
https://wiki.documentfoundation.org/Doc ... blications
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.
sts8500man
Posts: 8
Joined: Sat Oct 13, 2018 6:23 pm

Re: Macros in Calc

Post by sts8500man »

Thank you for that link. I had found that page during my research on Open Office but wondered if something that would apply to the newer version of Open Office might be available and I just had not found it. Your message confirmed that the documentation associated with each subject found on that page will suffice for my needs in using version 4.1.x.

I think I have just about exhausted what information I can gather about this subject to help me before I try using Calc. I will now mark this subject "solved". Thanks for your help.

sts8500man
OpenOffice 4.1.5
Post Reply