OpenOffice.org Calc adds Excel VBA support

If you are familiar with the Excel spreadsheet program in Microsoft Office, you may also notice that OpenOffice does not support Visual Basic for Applications (VBA), Microsoft Office's macro language. If you've spent years building hundreds of Excel macros, the fear of losing your data makes it easy to keep them in Office. But now Novell has used OpenOffice.org's source code to create its own version that supports Excell VBA.

Noel Power is Novell's developer in charge of introducing Excel VBA manipulation components for OpenOffice.org Calc. According to him, the reason these operations are completed by:

1. It allows Excel VBA macros to run naturally within OpenOffice.org.
2. Provide compatible object model.
3. Continuously upgrade compatible models by identifying and deploying the most widely used and useful APIs.
4. Extend the symbol for the basic level ooo-basic, including compatible APIs.
5. Modify the core ooo-basic runtime (core ooo-basic runtime) to control Excel VBA syntax.

" Novell is bringing a bigger community to its product. Source code is completely free on OpenOffice.org Hackers Pages, a tutorial on how to build on the OpenOffice.org build page is available to everyone. Moreover, we are pleased to say that we have added the VBA manipulation component to Novell's OpenOffice.org versions for Windows , "Noel said.

If you want to build a new version of OOo, the following steps are simple and easiest to do:

1. Download the source code.
2. Proceed to system configuration. This is the part that requires the most intensive level of work in the program. The feedback you obtained is very helpful. The problem is that you have to make an exact decision about the additional file the system requires or the part of the installation program you want to disable.
3. Download real OOo packages (you just need to type "./download" after each configuration is complete).
4. Create an application.

Even so, you may not have to do all the work yourself. So far VBA has been supported in the following OpenOffice.org versions:

• Ark
• Debian
• DroplineGNOME
• Frugalware
• Gentoo
• Mandriva
• QiLinux
• Red Hat (FC6)
• Ubuntu
• SUSE and SUSE SLED

Unfortunately, there is no easy way to determine if your program supports VBA split operations except how to try some VBA code. According to Noel: " You can use ooo-basic basic organization set ( Tools -> Macros -> Organize Macros -> OpenOffice.org Basic ) and redirect to text if the text contains marco. Like and most likely ingredients are allowed to work ".

If you installed a supported distribution or Novell's OpenOffice.org version on the Linux operating system you are using or Novell's OpenOffice.org installation for Windows, will any Excel macros work in Calc? The answer is no! VBA is not fully supported to the fullest extent. But according to Noel: " We think we have covered a large number of common uses. The macros we offer mostly use subsets of objects in Excel API (like Range, Worksheet, Workbook). .) We focus on supporting those objects and their methods and properties ".

If you have a separate Excel program, you can try it again and see what happens. The macros will run like you're opening Excel documents. If you don't have Microsoft Office Excel, you can use hypocycloid-demo.xls. A hypocycloid determines the path of a fixed point in the circle as it rotates inside the larger circle. The spreadsheet will create other hypocycloids for you. If you're using OpenOffice.org with VBA support, all the buttons and sliders will work, changing the background image on the screen, despite the fact that all the source code is written from Excel VBA.

How else if you load hypocycloid-demo.xls into a version of OpenOffice.org that doesn't support VBA? OpenOffice.org will then open an Excel worksheet even if it contains VBA macros. But when you press any button, nothing happens. And the most important thing is that you do not receive any error messages.

Let's look at the source code in the OOo Basic Editor. Go to OpenOffice.org menu, click on Tools -> Macros -> Organize Macros -> OpenOffice.org Basic .

Sub VBA_Library
Rem Sub openWorkbooks (iFiles ())
Rem Dim wBook ​​as Workbook
Rem Dim wList as String
Rem Dim iFile as String
Rem
Rem For Each iFile in iFiles
Rem Workbooks.Open iFile
Rem Next iFile
Rem
Rem For Each wBook ​​In Workbooks
Rem wList = wList & wBook.Name & chr (13)
Rem Next wBook
Rem Worksheets ("Sheet2"). Range ("A1") = Now ()
Rem msgbox Workbooks.Count & "files open:" & chr (13) & chr (13) & wList
Rem End Sub
End Sub

You will see OpenOffice.org standard controlling VBA code in a module. All VBA codes are included in a comment, then packaged into a new subroutine routine with the same name as the module.

If you use OpenOffice.org with VBA support, you will see another:

Option VBASupport 1
Sub openWorkbooks (iFiles ())
Dim wBook as Workbook
Dim wList as String
Dim iFile as String

For Each iFile in iFiles
          Workbooks.Open iFile
Next iFile

For Each wBook in Workbooks
          wList = wList & wBook.Name & chr ( 13 )
Next wBook
     Worksheets ( "Sheet2" ) .Range ( "A1" ) = Now ()
     msgbox Workbooks.Count & "files open:" & chr ( 13 ) & chr ( 13 ) & wList
End Sub

OpenOffice.org Calc version adds an extra line of code to any module loaded and contains VBA code.

Option VBASupport 1

This extension does not affect the standard Basic OOo code but ensures that the identification of VBA operations is correct. If you prefer VBA commands (or if you use it more often than a certain way), you have to remember to add it when creating your own VBA modules in Calc.

Still there is no solution for the main OOo line. Sun has built a proprietary solution but has some limitations. At OOoCon this year, Novell conducted a number of frank discussions with Sun developers and achieved a number of points in the cooperation agreement between the two companies. Novell is planning to complete the program in the next two months.

At the present time, the VBA manipulation component only stops at common operations using the ooo-build construction standard. But some improvements have been made to get closer to the goal of integrating all of these activities into one major OpenOffice.org source. Balanced core basic mode modifications have been included in the 'vanilla' version of OpenOffice.org, but compatible APIs and some other hooks do not. Currently Novell and Sun are continuing to discuss agreements to bring the entire addendum into a mainstream. Since this is a complex component and touches many parts of the source code, this work cannot be underestimated and takes a lot of time.