1

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?

keylargo
  • 11
  • 4
  • And which line of code causes the syntax error? – Cindy Meister Feb 24 '16 at 08:05
  • This line of code causes the syntax error: oSheet in ThisComponent.CurrentController.ActiveSheet.getCellDim oSheet as Object – keylargo Feb 24 '16 at 09:24
  • Thanks, that will help people who know something about OO Basic. Since you're new you may not know that you can click the "Edit" button under your question to add additional information. I've done that for you, in this instance :-) Good luck with your migration! – Cindy Meister Feb 24 '16 at 09:34

1 Answers1

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

Jim K
  • 12,824
  • 2
  • 22
  • 51
  • Good answer, though I would disagree with your remark that asking "This is the code in Excel VBA; what is the code in OpenOffice Basic?" They are different platforms and languages, but they are both spreadsheets and OpenOffice is even by design somewhat similar to Microsoft Office. The fact that they are fairly different in many ways just implies that the needed porting isn't purely mechanical. – John Coleman Feb 24 '16 at 14:42
  • @JohnColeman: Okay, I toned back my statement somewhat. Really I am not all that familiar with VBA. When I "port" macros, I typically look at the GUI front end and read the help for the Excel macro, and then rewrite that functionality in one of the OpenOffice UNO-enabled languages. – Jim K Feb 24 '16 at 15:24