How to port Excel VBA to OpenOffice basic?

Question

How to port Excel VBA macro to OpenOffice basic macro?

Here is macro for refresh in every 5 seconds.

Private Sub Workbook_Open()
' Written in ThisWorkbook
Call RefreshTime
End Sub

Sub RefreshTime()
' Written in a module
Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
Application.OnTime Now + TimeValue("00:00:05"), "RefreshTime"
Range("B10").Value = "My Current Time of the System:"
Range("C10").Value = Format(Now, "hh:mm:ss AM/PM")
Beep
Application.ScreenUpdating = True

End Sub

I tried convert this macro to OpenOffice macro (using http://www.business-spreadsheets.com/vba2oo.asp)

Private Sub Workbook_Open()
' Written in ThisWorkbook
Call RefreshTime
End Sub
Sub RefreshTime()
' Written in a module
ThisComponent.LockControllers
ThisComponent.RefreshAll
Application.OnTime Now + TimeValue("00:00:05"), "RefreshTime"
ThisComponent.CurrentController.ThisComponent.CurrentController.ActiveSheet.getCellDim oSheet as Object[n]oSheet = ThisComponent.CurrentController.ActiveSheet[n]oSheet.getCellRangeByName($1)ByName(("B10")).Value = "My Current Time of the System:"
ThisComponent.CurrentController.ThisComponent.CurrentController.ActiveSheet.getCellDim oSheet as Object[n]oSheet = ThisComponent.CurrentController.ActiveSheet[n]oSheet.getCellRangeByName($1)ByName(("C10")).Value = Format(Now, "hh:mm:ss AM/PM")
Beep
ThisComponent.UnlockControllers
End Sub

This line of code causes the syntax error is :

ThisComponent.CurrentController.ThisComponent.CurrentController.ActiveSheet.getCellDim oSheet as Object[n]oSheet =  ThisComponent.CurrentController.ActiveSheet[n]oSheet.getCellRangeByName($1)ByName(("B10")).Value = "My Current Time of the System:"

But got errors

BASIC syntax error. Expected:,.

in the oSheet as Object.

How to make it work in OpenOfffice?

Answer 1

It looks like there are a number of issues with this code. Let's take a look at this line:

ThisComponent.CurrentController.ThisComponent.CurrentController.ActiveSheet.getcellDim oSheet as Object[n]oSheet = ThisComponent.CurrentController.ActiveSheet[n]oSheet.getCellRangeByName($1)ByName(("B10")).Value = "My Current Time of the System:"

• It is way too long. You need to press Enter to add several line breaks.
• It says "ThisComponent.CurrentController" twice.
• Dim oSheet as Object[n] -- but n was never declared or defined.
• ActiveSheet.getcell -- I am not aware of any such method. See https://wiki.openoffice.org/wiki/Documentation/BASIC_Guide/Cells_and_Ranges.
• ByName(("B10")) -- too many parentheses, and again, there is no such method as "ByName".
• What is $1? Perhaps you mean a range like "$A1:$A5".

Also:

• Private Sub Workbook_Open -- that looks like VBA, not OpenOffice Basic.

For an introduction to OpenOffice macros with many excellent examples, see Andrew Pitonyak's Macro Document.

Instead of saying: "This is the code in Excel VBA; what is the code in OpenOffice Basic?", ask a question on stackoverflow like the following:

"I need to select cell A1 in OpenOffice Basic. Looking at (online source), I tried X but it gave Y error message about line Z."