Krishnan's weblog – an assortment of things. A scribble pad. A hybrid microblog. Mostly bread crumbs of an internet journey.
For many years till yesterday, I knew about OpenOffice.Org Calc. Today, I’m excited about it.
I tried OpenOffice many years ago. It could do few things back then, but I immediately dismissed as inadequate for my needs. I have always been a fan of what VBA could do for Excel.
Last week, I completed the most complex piece of VBA application I’d ever written. I am a VBA newbie and it took me a week to finish my code that could be printed on 28 A4 pages. My spreadsheet till date works flawlessly on XP/Office 2003 like it is supposed to, but I wrote most of my code in Vista/Office 2007.
When I upgraded to Debian Squeeze, Openoffice.org 3.1.1 came with it. I checked out the new version and was impressed that one could write macros in Python, Javascript, BeanShell in addition to BASIC.
I tried to open my most complex piece of VBA code in Openoffice.org and I was bombarded with hundreds of repetitive error messages that I had to kill the spreadsheet from the command line. I was convinced that VBA and OOoCalc are not compatible, till I discovered this site by accident when trying to learn to write Javascript Macros in OOo.
OK. All I needed to add was “Option VBA Support 1″. That didn’t sound too difficult, so I tried opening my spreadsheet again and had to again kill everything from command line.
Some thing was not right and I wasn’t being able to run VBA despite the promised compatibility.
So I decided to go slow. No matter how many times, I couldn’t run the macros when I opened the native excel file. So I decided to open the excel file with macros disabled. That let me open the spreadsheet, but nothing was working. I found that OOoCalc automatically had added “Option VBA Support 1″ to my modules.
I saved the file as .ODS and proceeded to debug by enabling macros again. This time, errors didn’t come in a flood.They came one at a time and it was easier to debug.
The first set of errors related to variables which did not have an explicit Dim statement. VBA in Excel seemed more forgiving in handling varaibles without proper Dim statements. But OOo Calc didn’t like it. So I ended up adding a few Dim statements for some variables.
The next set of errors was with Excel UDF names. OOo Calc likes to see Functions being invoked with uppercase letters. So if you’d used lower case letters in Excel, they would show up as errors. This could be easily fixed by use of find and replace feature.
With just these two things, fixed my new .ODS file worked perfectly. I then saved it as .xls and re-opened the .xls and everything was still working. Though OOo Calc documentation still says that not all VBA features are supported, VBA runs well on OOoCalc for all practical engineering calculations. So now is the time for me to make the switch.
Now if you have difficulties, in getting your VBA code to work in OOo Calc, dont give up soon.
Related posts:
Learning Daily is a personal blog that is just as random as my thoughts. The navigation bar at the top and the search box may be the only things that will help you find something.
My present interests lie in sustainability, peak oil, debian, Web after 2.0, creativity and epistemology. I learn daily. I learn about everything under the sun and even those beyond it. I am a perpetual student whose believes that wisdom is the real reward for learning.
Hi,
I have tried doing what u have mentioned, but still it doesn’t work.
if SHEET1.CELLS(7, 4) = “” Or SHEET1.CELLS(9, 4) = “” Or SHEET1.CELLS(10, 4) = “”
In this line i am getting property or method not found.
The syntax is obviously incorrect as I can see.
If, Then, Else are the keywords to be used. Not Or.
ingnoring the “rems”
how would you right this in OOo ?? i cant work out OOo – pls get me started ??
Rem Sub Words()
Rem ‘
Rem ‘ Words Macro
Rem ‘ Macro recorded 07/02/2010 by Owner
Rem ‘
Rem ‘ Keyboard Shortcut: Ctrl+w
Rem ‘
Rem Dim NumWords As Integer
Rem Dim WordLength As Integer
Rem Dim Count As Integer
Rem Dim Count2 As Integer
Rem Dim Lcase As Integer
Rem
Rem Dim Word As String
Rem Dim MixedWord As String
Rem Dim Letter As String
Rem
Rem ‘ Worksheets(“Words”).Activate
Rem
Rem Set wordRange = Worksheets(“Words”).Range(“A1:A500″)
Rem NumWords = Application.WorksheetFunction.CountA(wordRange)
Rem
Rem For Count = 1 To NumWords
Rem WordLength = Len(Cells(Count, 1))
Rem
Rem
Rem ‘ Application.EnableSound = False
Rem
Rem ‘ Application.EnableSound = True
Rem Word = Cells(Count, 1)
Rem ‘ Cells(Count, 1).Activate
Rem ‘ ActiveCell.Characters(1, 3).PhoneticCharacters = Word
Rem MixedWord = “”
Rem For Count2 = 1 To WordLength
Rem Letter = Mid(Cells(Count, 1), Count2, 1)
Rem ‘ Application.Speech.Speak (Letter)
Rem Lcase = Round(Rnd)
Rem
Rem
Rem If Lcase = 1 And Asc(Letter) >= 97 Then Letter = Chr(Asc(Letter) – 32)
Rem
Rem MixedWord = MixedWord + Letter
Rem Next Count2
Rem Cells(1, 5) = MixedWord
Rem
Rem Dim Message, Title, Default, MyValue
Rem Message = “Ready Monkey ?”
Rem Title = “Honors spelling game.”
Rem Default = “YES”
Rem MyValue = InputBox(Message, Title, Default)
Rem
Rem
Rem For Count2 = 1 To WordLength
Rem Letter = Mid(Cells(Count, 1), Count2, 1)
Rem Application.Speech.Speak (Letter)
Rem Next Count2
Rem Application.Speech.Speak (Word)
Rem
Rem If MyValue “YES” Then
Rem Application.Speech.Speak (“That’s wrong pooey – you entered”)
Rem WordLength = Len(MyValue)
Rem For Count2 = 1 To WordLength
Rem Letter = Mid(MyValue, Count2, 1)
Rem Application.Speech.Speak (Letter)
Rem Next Count2
Rem Application.Speech.Speak (MyValue)
Rem End If
Rem
Rem
Rem Next Count
Rem
Rem
Rem End Sub
Rem