• Categories

  • Wayback Machine

  • My Defunct Podcast

    The Bungee Line was an audio podcast for web developers, covering web API's, software development, and the creation of richly interactive web applications.

    podcast feed  Main Feed

OpenOffice.org and Excel VBA Macros

[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)]

Hypocycloid VBA Macro ExampleYou 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.]

About these ads

25 Responses

  1. Hello,

    Excellent work. Macros, an area that needs to be made more accessible to the medium/advanced user.

    Keep up the good work.

  2. Hi, can you point me to the same thing for MS Office/Open Office Macros.

  3. [...] 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 [...]

  4. [...] 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 [...]

  5. hola alguien me podra decir como abrir la macros que tengo en excel en calc. me urge si alguien
    me podra ayudar gracias.

    gracias

  6. 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

  7. 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.

  8. 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

  9. i would like to know how mr.michael do it..

  10. Is this Macro enabled in the published Novell Edition of OpenOffice in SuSE?
    How to find the function? Thanks

  11. @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

  12. 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

  13. @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

  14. @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.

  15. 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

  16. @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

  17. 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

  18. Thank you very much Ted.

    Brg, Stefan

  19. That´s excellent, thanks Jim!

    -Stefan

  20. How to use macro in openoffice 2.3?
    It wont work with same feature as excel has?
    Can you please give me the solution

  21. 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???

  22. 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?

  23. 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

  24. hi all,
    how can i add code in module in excel Macro runtime throug java poi

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: