[Note to All Readers: I no longer work at Novell, so I have stopped taking submissions for Excel VBA macros to test. Thanks to all those who provided the OOo team with macros to try. –Ted (05May08)]
[Mac Users concerned about VBA Macros: Help us keep improving VBA macro support in Calc. See below about sending us your macro-laden XLS files. Thanks, Ted (10Aug06)]
You have to love a guy like Michael Meeks. Widely renowned for his bare-bones blog style, Michael is also the foremost contributor to OpenOffice.org outside of the maintainers at Sun .
One of Michael’s latest areas of endeavor is getting Microsoft Excel VBA macros to execute in OpenOffice Calc.
This screenshot shows a fairly geeky example of a hypocycloid generator. The generator uses a VBA macro, shown lower left, to generate source values from the positions of three interactive sliders on the left near where it says “Parameters.” The data is dumped into the table at the far right. Another macro chews up the data and spits out the hypocycloid graphic. Still more macros allow you to click through some pre-set values, generate random values, and so on.
The point, however, is not what the example macros shown here do. The point is that they are Excel macros correctly doing work in Calc.
Want to contribute? When I talked with Michael at GUADEC, he said that he doesn’t have enough real world Excel macros to test against.
So if you have a good example, send it my way and I’ll send it to Michael in his secret OpenOffice.org laboratory.
I’m thaeger at that novell dot com place.
[21AUG06: Update Posted.]
Filed under: Free Software, Novell |
Reverend Ted's Blog 
Hello,
Excellent work. Macros, an area that needs to be made more accessible to the medium/advanced user.
Keep up the good work.
Hi, can you point me to the same thing for MS Office/Open Office Macros.
[…] big, heavy-lifting projects. For example, the team made a massive code contribution to enable OOo Calc to run Excel VBA Macros. These contributions sometimes involve thousands of lines of code, so they take a long time for the […]
[…] big, heavy-lifting projects. For example, the team made a massive code contribution to enable OOo Calc to run Excel VBA Macros. These contributions sometimes involve thousands of lines of code, so they take a long time for the […]
hola alguien me podra decir como abrir la macros que tengo en excel en calc. me urge si alguien
me podra ayudar gracias.
gracias
Koalitas [5]:
No te puedo ayudar bien con abrir sus macros en Excel a Calc. Yo no trabajo aun a Novell, y entonces no tengo contacto con los enginerios de OpenOffice.org en la misma capacidad que antes saliendo. Yo te recommendo que tu contactaria la gente en la communidad de OpenOffice.org. Buen suerte,
Ted
I have just downloaded OO 2.1 and the OpenXML Translator for Windows, and will give it a try on XP. While that was going on I pdated both my Suse 10.2 OpenOffice installs, and will be giving them a good work out tomorrow. So far it looks very good. Please thank Michael & his team for their hard work. It looks like there will shorlty be no excuse for not being able to use OpenOffice in the business environement.
Sadiq [7]:
With our recent email interaction, you should now have Michael and Noel’s email addresses. Send them a thank you praising their glorious efforts. 🙂 They will appreciate it far more from you than from me. After all, I’m just a fanboy. 🙂
–Ted
i would like to know how mr.michael do it..
Is this Macro enabled in the published Novell Edition of OpenOffice in SuSE?
How to find the function? Thanks
@Rui SuYing [10]:
Macro support is in the Novell edition of OOo, which is the branch that many other distros include. To use it, simply open an Excel document that includes VBA macros.
Good luck,
Ted
What OpenOffice build is the best one to use for the purpose of testing VBA macros on the Mac? I’ve been using NeoOffice 2.2.1 and find that the VBA environment has a good start, but needs a lot of fine tuning. I have examples of code that don’t work as expected. OpenOffice 2.3.1 doesn’t see my JRE even when I point directly to it in Tools > Options. I haven’t tried OO v 2.4, and I heard rumors about the existence of v 3.0. I don’t want to waste time reporting things that have already been fixed.
Thanks.
-Jim
@Jim Gordon [12]:
Thanks for your question, Jim. I should know this one, since I am using a Mac nowadays. But, unfortunately, I have not done any such research yet.
I’ll see whether I can get any info from Michael or Noel on this.
–Ted
@Jim Gordon [12]:
Sounds interesting – if you can help us analyse the type of problems you are seeing, that would be great. The NeoOffice guys will be doing a new beta of an updated version soon I believe. We track our missing features here:
http://svn.gnome.org/svn/ooo-build/trunk/test/macro/missing_vbafeatures.ods
We’re interested in help improving the analysis there of course, if you want to help out. Mail me: michael.meeks@novell.com.
Hi there guys,
Can you tell me how I write a macro that generates a sound when for example a cell reaches a certain value or goes below a certain value?
I´m working online with live floating currencies and I want the computer to ,,let me know” when the exchange rates move ,,to much” in either direction. Can you help me solve this problem?
Best regards,
Stefan
@Stefan [15]:
Sorry, I don’t know how to do that. I recommend consulting Microsoft’s documentation for Excel, since that what VB Macros were originally designed for.
–Ted
Stefan,
Please post your question in the Excel forums: http://www.officeformac.com/ProductForums/Excel/ where I am sure you will get a detailed answer.
You can use a web query in Excel (probably Calc) that has a built-in timer and a macro that fires an alert.
-Jim
Thank you very much Ted.
Brg, Stefan
That´s excellent, thanks Jim!
-Stefan
How to use macro in openoffice 2.3?
It wont work with same feature as excel has?
Can you please give me the solution
Hi I have an Excel Macros which updates the test cases entered in the sheet.
How do I use the same macros in Open Office2.3 enviraonment???
I need help getting my macros to work in Open Office. I imported an excel sheet and can’t get it to work. Can anyone help?
have this excel macro to produce catenary curves but I keep getting error
Basic runtime error 420 invalid object reference…help please…the editor points to: Set objRegExp = new RegExp…here is the code, thanks for any help.
Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Private Sub FeedtheCat()
‘ This macro was created by lvleph
Dim alpha As Double
Dim SagFactor As Double
Dim H As Double
Dim L As Double
Dim Epsilon As Double
Dim Nmax As Integer
Dim i As Integer
Dim objRegExp As RegExp
Dim Pi As Double
Pi = 4# * Atn(1#)
Epsilon = 0.00001
Nmax = 1000
Sheets(“BlackCat”).Unprotect ‘ Make sure sheet is uprotected
Range(“F2:F1000”, “G2:G1000”).Select
Selection.ClearContents
Selection.Interior.ColorIndex = xlNone
Set objRegExp = new RegExp
objRegExp.Pattern = “\d+\.?\d*” ‘ Regular Expression
‘ Ensure L, c, and Sag are numbers.
If objRegExp.Test(Sheets(“BlackCat”).Range(“B8”)) And _
objRegExp.Test(Sheets(“BlackCat”).Range(“B10”)) Then
L = Sheets(“BlackCat”).Range(“B8”)
Else
MsgBox (“L and Sag/ft must be numbers.”)
End
End If
SagFactor = Sheets(“BlackCat”).Range(“B10″)
H = L * SagFactor / 12
H = Int(Abs(H)) + (Round(8 * Abs(H – Int(Abs(H))), 0)) / 8 ‘ Display H in 1/8”
With Sheets(“BlackCat”)
.Range(“B11”).Value = H
End With
‘ Solve for alpha
Newton alpha, L, H, Epsilon, Nmax
‘ Print solution
If Abs(f(alpha, L, H)) Epsilon And i < Nmax
alpha = alpha – f(alpha, L, H) / fp(alpha, L, H)
i = i + 1
Loop
End Sub
Sub PrintMeasure(H As Double, L As Double, alpha As Double)
Dim x As Double
Dim y As Double
Dim z As Double
Dim column As Integer
Dim wSheet As String
column = 6 ‘ Set working column
wSheet = “BlackCat” ‘ Set working sheet
For j = 0 To Int(L)
x = j ‘ x value
z = (L / 2 – x) / alpha
y = H – alpha * (Cosh(z) – 1) ‘ Value of sag
Sheets(wSheet).Cells(j + 2, column).Value = Int(Abs(x)) + _
(Round(8 * Abs(x – Int(Abs(x))), 0)) / 8 ‘ Display x in 1/8″ intervals
Sheets(wSheet).Cells(j + 2, column + 1).Value = Int(Abs(y)) + _
(Round(8 * Abs(y – Int(Abs(y))), 0)) / 8 ‘ Display y in 1/8″ intervals
Sheets(wSheet).Cells(j + 2, column).Interior.ColorIndex = 38
Sheets(wSheet).Cells(j + 2, column + 1).Interior.ColorIndex = 39
Next j
End Sub
Sub FormatChart(H As Double, L As Double)
‘ Format Chart
Sheets(“BlackCat”).DrawingObjects(“Chart 12”).Locked = False
Sheets(“BlackCat”).ChartObjects(“Chart 12”).Activate
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 4 * H
.MinorUnit = 10
.MajorUnit = 10
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = L
.MinorUnit = 10
.MajorUnit = 10
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Sheets(“BlackCat”).DrawingObjects(“Chart 12”).Locked = True
End Sub
Private Function f(alpha As Double, L As Double, H As Double) As Double
Dim z As Double
z = L / (2 * alpha)
f = H – alpha * (Cosh(z) – 1)
End Function
Private Function fp(alpha As Double, L As Double, H As Double) As Double
Dim z As Double
z = L / (2 * alpha)
fp = z * Sinh(z) – Cosh(z) + 1
End Function
Private Function Cosh(x As Double) As Double
Cosh = 0.5 * (Exp(x) + Exp(-x))
End Function
Private Function Sinh(x As Double) As Double
Sinh = 0.5 * (Exp(x) – Exp(-x))
End Function
hi all,
how can i add code in module in excel Macro runtime throug java poi
Abhi: Please ask on the OpenOffice.org website. I’m not affiliated with the project anymore. –Ted