Macro that will run in MS Excel and LibreOffice Calc

Question

I have a spreadsheet that users will be working with in both Excel and LibreOffice Calc. I'd like to design macros that will work in both. I was thinking of code that would say: If Excel Then [VBA code], Else [Basic or Python code]

I think the two keys would be an IF/THEN statement (or equivalent) that both programs could read, and a way to ensure that both programs would ignore the code that isn't applicable to them.

I've already tried running it in LibreCalc but it won't work. Here's the code I need to run in Excel and Calc:

Public Sub spubInsertCurrDateTimeText()
'DESCRIPTION:  inserts current date and time into the active cell in text format.

Dim dtmNow As Date
Dim CurrTemp As Date
Dim CurrTot As Date
Dim NewTot As Date
Dim StartCol As Integer
Dim StopCol As Integer
Dim ClockTimerCol As Integer
Dim TotalTimeCol As Integer

dtmNow = Now 'sets variable to value of function NOW (current date/time)
StartCol = 1 'this is the column where the user enter the starting time
StopCol = 2 'this is the column where the user enter the ending or stop time
ClockTimerCol = 3 'this is the column that calculates elapsed time (StopCol minus StartCol)
TotalTimeCol = 4 'this is the column that tracks the total time elapsed in minutes

If ActiveCell.Column = StartCol Then

ActiveCell = dtmNow 'inserts variable into the active cell
Worksheets(ActiveSheet.Name).Cells(ActiveCell.Row, StopCol).Value = 0

ElseIf ActiveCell.Column = StopCol Then

ActiveCell = dtmNow 'inserts variable into the active cell

CurrTot = Worksheets(ActiveSheet.Name).Cells(ActiveCell.Row, TotalTimeCol).Value
CurrTemp = Worksheets(ActiveSheet.Name).Cells(ActiveCell.Row, ClockTimerCol).Value

NewTot = CurrTot + (CurrTemp * 1440)

Worksheets(ActiveSheet.Name).Cells(ActiveCell.Row, TotalTimeCol).Value = NewTot

Worksheets(ActiveSheet.Name).Cells(ActiveCell.Row, StartCol).Value = 0
Worksheets(ActiveSheet.Name).Cells(ActiveCell.Row, StopCol).Value = 0

Else

ActiveCell = dtmNow 'inserts variable into the active cell

End If

End Sub

Any suggestions?

Answer 1

The API for Calc is very different than the API for Excel, so even though VBA has a lot of similarity to StarBasic you would need to write separate code to handle the different API calls. To test if the file being worked on is Calc this works in StarBasic:

If oDoc.supportsService("com.sun.star.sheet.SpreadsheetDocument") Then
    REM Run code for Calc
Else
    REM Run code for Excel
End If

The bigger problem I see is that you are talking about running this code from inside the spreadsheet. If the spreadsheet is saved as a .xls or .xlsx then OpenOffice and LibreOffice will disable any macros when the file is opened. I believe it's vice versa for Excel if the spreadsheet is saved as a .ods file. Even if you write all the code correctly, at least one of the programs will just refuse to execute it.

While it's possible for an outside program to test if it's Excel or Calc that opened a spreadsheet and execute the applicable block of code, I don't see this working with any macros embedded in the spreadsheet.