Excel Help for Developing Vba Code Compatible With Neooffice And Excel

Business Spreadsheets offers a solution for developing VBA code that is compatible with both NeoOffice and Microsoft Excel.

NeoOffice for the Mac OS X operating system has recently added limited support for Visual Basic for Applications (VBA). Creating VBA driven spreadsheet applications that are compatible with both NeoOffice and Microsoft Excel can serve as cross platform and highly portable business solutions. This concept is significant as Microsoft plans to exclude support for VBA in the new version of Office Mac 2008.

Furthermore Microsoft is making every effort to base their Office document format on their own Office Open XML (OOXML) schema with proprietary ties rather than already accepted Open Document Format (ODF) ISO standard. This adds further complexity and difficulty to create cross platform and even cross application spreadsheet solutions.

Since the NeoOffice emulation of VBA does not encompass the entire library, the key to creating compatible code is by detecting the application used and using conditional statements to provide code suited to that application i.e. either NeoOffice or Excel.

The following VBA code can be attached to the Workbook object.

VBA Code:

Public appname As String
Private Sub Workbook_Open()
    On Error GoTo neoJump
    appname = Application.Name
    Exit Sub
neoJump:
    appname = "NeoOffice"
End Sub

Since NeoOffice doesn't implement Application.Name we can catch the error , detect that the application is NeoOffice and put this into a globally available variable "appname". Of course if NeoOffice did implement this code, we could use the actual value in the same way to indicate that it is NeoOffice.

Now whenever some VBA code is not recognized by NeoOffice we can put a conditional statement around it to either offer an alternative or omit the functionality from the solution e.g for a dialogue box:

VBA Code:

Sub TryInput()
    If appname <> "NeoOffice" Then
        InputData.Show
    Else
        MsgBox "The input assistance form is not compatible with NeoOffice"
    End If
End Sub

A working example of this with open source code can be found by downloading our Bond Yield to Maturity calculator here. This download also includes an OpenOffice version to illustrate how to convert Excel VBA to Open Office basic which is detailed here.

Business Spreadsheets praises the work done by NeoOffice to support VBA code and we encourage anyone to support their effort at [http://www.neooffice.org/neojava/en/donate.php].

Other related posts are:
Migrating Excel VBA projects to OpenOffice Basic
Converting Excel VBA for Lotus Symphony Spreadsheets

Developing VBA code compatible with NeoOffice and Excel

Excel Help for Developing Vba Code Compatible With Neooffice And Excel in Developing and Auditing Analytical Models

Developing Vba Code Compatible With Neooffice And Excel