Flow Control

The flow-control functions listed in Table 4 either direct flow or provide functionality similar to flow control. For example, the IIF (immediate if) function provides functionality that would otherwise require an If-Then-Else statement.

Table 4: OOo Basic functions related to flow control.

Function	Description
Choose(number, argument_list)	Flow control
IIF(condition, TrueExpression, FalseExpression)	Flow control
Stop	Stop execution now
Wait( milliseconds )	Pause the macro for a short time
Declare	Declare a DLL that you want to call
DDEExecute(nDDEChannel, command)	Execute a DDE command
DDEInitiate(Server, Topic)	Open a DDE channel
DDEPoke(nDDEChannel, item, data)	Set data on the DDE server through the channel
DDERequest(nDDEChannel, item)	Post a DDE request over an open channel
DDETerminateAII()	Close all DDE connections
FreeLibrary	Free a DLL library
Shell	Run a command through the command shell

Return an Argument

The IIF function (Immediate If) and the Choose function both return an argument based on the value of the first argument. The IIF function returns one of two values based on a conditional expression. This works as a great one-line If-Then-Else statement.

 max_age = IIF(johns_age > bills_age, johns_age, bills_age) 

The IIF function takes three arguments. The first argument is a Boolean value, which determines which argument to return; one of the next two arguments is returned. Listing 3 shows how you can write the IIF function yourself.

Listing 3: The IIF function, if you wrote it yourself.

 Function myIIF(conditional, true_arg, false_arg) As Variant   If CBool(conditional) Then     myIIF = true_arg   Else     myIIF = false_arg   End If End Function 

 

All arguments to a routine are evaluated before the routine is called. Because IIF is a function, all arguments are evaluated when the function runs. With an If statement, the conditional code runs only if necessary. See Listing 4 .

Listing 4: If the denominator is zero, the division does not occur.

 If denominator <> 0 Then   result = numerator / denominator else   result = 0 End If 

 

In Listing 4, if the denominator is zero, the division is not done and zero is returned instead. In case your mathematical skills are a little rusty, it is not valid to divide a number by zero; a run-time error occurs. On the other hand, if your mathematical skills are not rusty, you know that returning zero when the denominator is zero isn't really correct, either. The macro in Listing 5 demonstrates that the functions f1 and f2 are both called, even though only the value from f2 is returned. In other words, IIF(x <> 0, 1/x, 0) causes a division-by-zero error if x is zero.

Listing 5: ExampleIIF is found in the Misc module in this chapter's source code files as SC08.sxw.

 Sub ExampleIIF   REM This demonstrates that ALL expressions are evaluated   REM This prints   REM "I am in function f1"   REM "I am in function f2"   REM "f2"   Print IIF(1>2, f1(), f2()) End Sub Function f1$   Print "I am in function f1"   f1 = "f1" End Function Function f2$   Print "I am in function f2"   f2 = "f2" End Function 

 

The Choose function is similar to the IIF function in that it returns an argument based on the value in the first argument. However, it differs because it can have more than two possible return values, and the first argument is an integer, rather than a Boolean, that indicates which of those possibly many arguments to return.

 Choose (expression, Select_1[, Select_2, ... [,Select_n]]) 

The Choose statement returns a null if the expression is less than 1 or greater than the number of selection arguments. Choose returns Select_1 if the expression evaluates to 1 and Select_2 if the expression evaluates to 2. The result is similar to storing the selections in an array with a lower bound of 1 and then indexing into the array. Each argument is evaluated regardless of which one is returned. See Listing 6 .

Listing 6: ExampleChoose is found in the Misc module in this chapter's source code files as SC08.sxw.

 Sub ExampleChoose   Dim i%, v   i% = CStr(InputBox("Enter an integer 1-4 (negative number is an error)"))   v = Choose(i, "one", "two", "three", "four")   If IsNull(v) Then     Print "V is null"   Else     Print CStr(v)   End If End Sub 

 

Pause or End the Macro

The Stop command causes the macro to stop running. That's it, it is done, finished! You must start again. The Wait statement, on the other hand, only pauses macro execution (see Listing 7 ). After the specified number of milliseconds, the macro starts running again. A run-time error occurs if the number of milliseconds to wait is negative.

Listing 7: ExampleWait is found in the Misc module in this chapter's source code files as SC08.sxw.

 Sub ExampleWait   On Error Goto BadInput   Dim nMillis As Long   Dim nStart As Long   Dim nEnd As Long   Dim nElapsed As Long   nMillis = CLng(InputBox("How many milliseconds to wait?"))   nStart = GetSystemTicks()   Wait(nMillis)   nEnd = GetSystemTicks()   nElapsed = nEnd - nStart   MsgBox "You requested to wait for " & nMillis & " milliseconds" &_      CHR$(10) & "Waited for " & nElapsed & " milliseconds", 0, "Example Wait" BadInput:   If Err <> 0 Then     Print Error() & " at line " & Erl   End If   On Error Goto 0 End Sub 

 

In all of my experiments, the Wait statement has been accurate. The macro waits and then starts when it should. In previous versions of OOo, the Wait statement was inefficient and it used a lot of CPU time while running. This problem has been fixed in the current versions of OOo.

Dynamic Link Libraries

While a macro runs, it may directly call numerous subroutines and functions. A macro can also call routines and applications that are not related to OpenOffice.org. A Dynamic Link Library (DLL) is a collection of routines, or programs, that can be called when required. Each DLL is packaged into a single file with the extension DLL-the file does not always have the suffix ".dll", but it usually does. There are two very nice things about DLL files: Many programs can share a single DLL, and they are not usually loaded into memory until they are needed. The usage of DLLs promotes code reuse and does not waste memory. To tell OOo Basic about a routine in a DLL, use the Declare statement.

Note  

DLLs are not supported on Linux.

 Declare Sub Name Lib "LibName" (arguments) Declare Function Name Lib "Libname" (arguments) As Type 

LibName is the name of the DLL that contains the routine named Name. It is common to use a DLL that you did not write, so you often have no control over the name of the routine that you call. Naming can be a problem if your macro already contains a routine with the same name or if you call another routine by the same name in another DLL. As a result, the Declare statement supports the Alias keyword, which you can use to overcome this hurdle . In this case, the RealName is the name used by the DLL, and myName is the name used by your macro.

 Declare Sub myName Lib "LibName" Alias "RealName" (arguments) Declare Function myName Lib "Libname" Alias "RealName" (arguments) As Type 

For functions, the type declaration should use the standard types. You must, of course, know the type so that you can declare it. The argument list contains the arguments that are passed to the external routine. You must use the keyword By Val if an argument is passed by value rather than by reference. Listing 8 calls a DLL.

Listing 8: ExampleCallDLL is found in the Misc module in this chapter's source code files as SC08.sxw.

 Declare Sub MyMessageBeep Lib "user32.dll" Alias "MessageBeep" ( Long ) Declare Function CharUpper Lib "user32.dll" Alias "CharUpperA"_    (ByVal lpsz As String) As String Sub ExampleCallDLL   REM Convert a string to uppercase   Dim strIn As String   Dim strOut As String   strIn = "i Have Upper and Lower"   strOut = CharUpper(strIn)   MsgBox "Converted:" & CHR$(10) & strIn & CHR$(10) &_          "To:" & CHR$(10) & strOut, 0, "Call a DLL Function"   REM On my computer, this plays a system sound   Dim nBeepLen As Long   nBeepLen = 5000   MyMessageBeep(nBeepLen)   FreeLibrary("user32.dll" ) End Sub 

 

Tip  

By default OOo Basic passes arguments by reference. This means that if the called subroutine changes the argument, it is also changed in the calling program. The ByVal keyword causes an argument to be passed by value rather than by reference.

A DLL is not loaded until a routine in the DLL is called. To remove the DLL from memory, use the FreeLibrary statement. The FreeLibrary statement accepts one argument: the name of the DLL to unload.

Calling External Applications

Use the Shell statement to run an external application. The Shell command is disabled for users connecting by a virtual portal unless they happen to be the same user that started OOo in the first place. This statement does not obtain any information from an application; it simply runs another application or command.

 Shell(Pathname, Windowstyle, Param, bSync) 

Warning  

The Shell command is a potential security hole.

The only required argument is the first; the rest are optional. The first argument is the full path name of the external application. The application path may be in URL notation, but need not be. The Shell statement has problems if the path or application name contains a space. You can solve this problem the same way that your Web browser solves it: Substitute "%20" for each space. The ASCII value of a space is 32, which is 20 in hexadecimal. This technique can also be used to substitute other characters if they cause you problems.

 Shell("file:///C/Andy/My%20Documents/oo/tmp/h.bat",2) 'URL notation uses / Shell("C:\Andy\My%20Documents\oo\tmp\h.bat",2)  'Windows notation uses \ 

The second argument (optional) indicates the window style of the started application. Table 5 lists valid values for the Shell window style argument.

Table 5: Window styles for the Shell statement.

Style	Description
	Focus is on the hidden program window.
1	Focus is on the program window in standard size .
2	Focus is on the minimized program window.
3	Focus is on the maximized program window.
4	Standard size program window, without focus.
6	Minimized program window, but focus remains on the active window.
10	Full-screen display.

The third argument (optional) is a string that is passed to the application. Each space in the argument string is read by the called application as delimiting a separate argument. To pass an argument with an embedded space, place an extra set of double quotation marks around the arguments.

 Shell("/home/andy/foo.ksh", 10, """one argument"" another")   ' two arguments 

Note  

The string """one argument"" another" is correct and as intended; think about it!

The final optional argument determines if the Shell command returns immediately while the application is running (the default behavior) or if it waits until the application is finished. Setting the final argument to True causes the Shell statement to wait.

 Sub ExampleShell   Dim rc As Long   rc = Shell("C:\andy\TSEProWin\g32.exe", 2, "c:\Macro.txt")   Print "I just returned and the returned code is " & rc ' rc = 0   Rem These two have spaces in their names   Shell("file:///C/Andy/My%20Documents/oo/tmp/h.bat",2)   Shell("C:\Andy\My%20Documents\oo\tmp\h.bat",2) End Sub 

The Shell function returns a long with the value zero. If the program does not exist, a run-time error occurs and nothing is returned. When some applications run, they return a value that can be used to indicate an error. This value is not available from the Shell command. Intuitively, it isn't possible to obtain the final return value from an application when the Shell function returns before the application is finished running.

Campatibility  

In Visual Basic, the arguments for the Shell function are different: Shell(path, window_style, bsync, Timeout). The Timeout value indicates how long to wait for the external application to finish. Arguments follow the application name as part of the same string, delimited by spaces.

VB does not use a separate argument to specify the arguments to send to the application launched by the Shell command. Instead, the arguments follow the name of the application, separated by spaces, inside the same quotation marks that contain the function path and name. This method also works with OOo Basic, as an alternative way to specify the Shell command arguments. If only function, arguments, and window style are required, this alternative way to write the Shell command allows you to have identical statements for VB or OOo routines. If you want to specify bsync or Timeout parameters, the VB and OOo environments are not compatible.

 Shell("/home/andy/foo.ksh hello you") ' two arguments, "hello" and "you" 

Dynamic Data Exchange

Dynamic Data Exchange (DDE) is a mechanism that allows information to be shared between programs. Data may be updated in real time or it may work as a request response. Some years ago, I worked on a macro using another word processor that uses DDE to extract data from Logos, an application written by Logos Research Systems. DDE is used to request reference text from Logos, which is then manually inserted into the text document. Logos acted as a server, serving up data when requested. My macro worked as a client, requesting the text that it desired.

Although the DDE commands accepted by a DDE server are specific to the individual server, the general syntax is the same. Most DDE commands require a Server, Topic, and Item. The Server is the DDE name for the application that contains the data. The Topic, usually a file name, contains the location of the referenced Item. The example in Listing 9 uses the DDE function in a Calc spreadsheet to extract data in cell A1 from an Excel spreadsheet.

Listing 9: Use DDE as a Calc function to extract cell A1 from a document.

 =DDE("soffice";"/home/andy/tstdoc.xls";"al") 'DDE in Calc to obtain a cell ='file:///home/andy/TST.sxc'#$sheetl.Al      'Direct reference to a cell 

 

The second line shows how a cell can be directly referenced in another Calc spreadsheet without using DDE. OOo Basic supports DDE commands directly (see Table 6 ).

Table 6: DDE commands supported by OOo Basic.

Command	Description
DDEExecute(nDDEChannel, command)	Execute a DDE command.
DDEInitiate(Server, Topic)	Open a DDE channel and return the channel number.
DDEPoke(nDDEChannel, item, data)	Set data on the DDE server.
DDERequest(nDDEChannel, item)	Post a DDE request over an open channel.
DDETerminateAII()	Close all DDE connections.

First, the DDEInitiate function makes a connection to the DDE server. The first argument is the name of the server-for example, "soffice" or "excel". The second argument specifies the channel to use. A common value for a channel is a file name. The opened channel is identified by an integer, which is returned by the DDEInitiate command. A channel number of 0 indicates that the channel was not opened. Attempting to establish a DDE connection to OOo for a file that is not currently open returns 0 for the channel. See Listing 10 .

Listing 10: ExampleDDE is found in the Misc module in this chapter's source code files as SC08.sxw.

 Sub ExampleDDE   Dim nDDEChannel As Integer   Dim s As String   REM OOo must have the file open or the channel will not be opened   nDDEChannel = DDEInitiate("soffice", "c:\TST.sxc")   If nDDEChannel = 0 Then     Print "Sorry, failed to open a DDE channel"   Else     Print "Using channel " & nDDEChannel & " to request cell A1"     s = DDERequest(nDDEChannel, "A1")     Print "Returned " & s     DDETerminate(nDDEChannel)   End If End Sub 

 

The usable commands and syntax for each server are server dependent, so a complete coverage of DDE is beyond the scope of this book.

Bug  

Although the macro in Listing 10 runs and returns a value, it crashes OOo.